recordset for a subform

[Joseph Atie]

i have a piece of vba code that collects a variable and uses that variable to
generate a recordset.

So far so good

now i want to use that record set to populate a subform. problem is it all
seems to work except instead of getting my data i get #name?

what am i doing wrong?

here is the code

rstr = "SELECT Transactions.Transcode, Transactions.Usercode,
Transactions.[Out date], Transactions.transfer FROM Transactions WHERE
Transactions.Usercode= " & fcode & ";"

Set rs = CurrentDb.OpenRecordset(rstr)
Set Forms!equip_tfr_3.[Child20].Form.Recordset = rs

the subform is unbound and not linked to the main form, but the fields are
bound to the names of the recordset columns.

 

[Allen Browne]

How have you declared your rstr variable? If it's declared within a
procedure, it ceases to exist once the procedure ends, so there's nothing
left to assign to your form.

Try declaring it in the General Declarations section of a standard module
(not the module of a form/report):
dim gDb As DAO.Database
Dim gRst AS DAO.Recordset
Then start with a stand-alone form (not as a subform) and in its Open event
assign the recordset variable and assign it to the form:
Private Sub Form_Open(Cancel As Integer)
Set gDb = CurrentDb()
Set gRs = gDb.OpenRecordset(strSql)
Me.Recordset = gRs
End Sub
with the matching code to close recordset and set both objects to Nothing in
Form_Close (with error handling in case it got reset.)

Once you get that working, you can try it as a subform. However, you will
not be able to use anything in the subform control's
LinkMasterFields/LinkChildFields. If you use these properties, Access
reloads the subform every time the record in the main form changes, so you
attempt to set the Recordset does not survive.

 

[Joseph Atie]

here is my full code allen

Private Sub Command7_Click()
'dec
Dim rs As Recordset
Dim rstr As String
Dim fcode As Integer
Dim fname As String
Dim tcode As Integer
Dim tname As String


'init
fcode = Me.Text4.Value
fname = Me.text5.Value
tcode = Me.Text3.Value

rstr = "SELECT Users.[First Name], Users.Surname, Users.Usercode FROM Users
WHERE Users.Usercode=" & tcode & ";"

'recordset to find users name
Set rs = CurrentDb.OpenRecordset(rstr)
tname = rs.Fields("First Name")
tname = tname & " " & rs.Fields("Surname")

'switch forms
DoCmd.OpenForm "equip_tfr_3", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "Equip_tfr_2", acSaveNo

'populate next form
Forms!equip_tfr_3.Text3 = fcode
Forms!equip_tfr_3.Text4 = fname
Forms!equip_tfr_3.text5 = tcode
Forms!equip_tfr_3.text6 = tname

'close recordset
rs.Close

'build subform
rstr = "SELECT Transactions.Transcode, Transactions.Usercode,
Transactions.[Out date], Transactions.transfer FROM Transactions WHERE
Transactions.Usercode= " & fcode & ";"
Set rs = CurrentDb.OpenRecordset(rstr)
Set Forms!equip_tfr_3.[Child20].Form.Recordset = rs
Forms!equip_tfr_3.[Child20].Form.Refresh

'clear the recordset
rs.Close
Set rs = Nothing
End Sub

i based it on another piece of code that yourself and dirk goldgar helped me
with

http://www.microsoft.com/communitie...3bc70e8bebb1&lang=en&cr=US&sloc=en-us&m=1&p=1

here is the other piece of code that works perfectly

Private Sub Command7_Click()
'dec
Dim rs As Recordset
Dim ucode As Integer
Dim qry1 As String
Dim qry2 As String
Dim Name As String

'init
ucode = Me.Text3
qry1 = "SELECT Users.[First Name], Users.Surname, Users.Usercode FROM Users
WHERE Users.Usercode=" & ucode & ";"
qry2 = "SELECT Transactions.Usercode, Transdata.Barcode, Transdata.[In] FROM
Transactions INNER JOIN Transdata ON Transactions.Transcode =
Transdata.Transcode WHERE Transactions.Usercode =" & ucode & "AND
Transdata.[In] Is Null;"

'init recordset to find users name
Set rs = CurrentDb.OpenRecordset(qry1)
Name = rs.Fields("First Name")
Name = Name & " " & rs.Fields("Surname")
rs.Close

'init recordset to populate subform
Set rs = CurrentDb.OpenRecordset(qry2)

'switch forms
DoCmd.OpenForm "equip_in_2", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "equip_in_1", acSaveNo

'populate new form
Forms!equip_in_2.Text3 = ucode
Forms!equip_in_2.Text7 = Name
Set Forms!equip_in_2.[Child20].Form.Recordset = rs

'clear recordset
rs.Close
Set rs = Nothing
End Sub

i cant understand why it worked the last time but not this time. i believe i
am using the same method. they only different this time is the sub-subform.

How have you declared your rstr variable? If it's declared within a
procedure, it ceases to exist once the procedure ends, so there's nothing
left to assign to your form.

Try declaring it in the General Declarations section of a standard module
(not the module of a form/report):
dim gDb As DAO.Database
Dim gRst AS DAO.Recordset
Then start with a stand-alone form (not as a subform) and in its Open event
assign the recordset variable and assign it to the form:
Private Sub Form_Open(Cancel As Integer)
Set gDb = CurrentDb()
Set gRs = gDb.OpenRecordset(strSql)
Me.Recordset = gRs
End Sub
with the matching code to close recordset and set both objects to Nothing in
Form_Close (with error handling in case it got reset.)

Once you get that working, you can try it as a subform. However, you will
not be able to use anything in the subform control's
LinkMasterFields/LinkChildFields. If you use these properties, Access
reloads the subform every time the record in the main form changes, so you
attempt to set the Recordset does not survive.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

i have a piece of vba code that collects a variable and uses that variable
to
generate a recordset.

So far so good

now i want to use that record set to populate a subform. problem is it all
seems to work except instead of getting my data i get #name?

what am i doing wrong?

here is the code

rstr = "SELECT Transactions.Transcode, Transactions.Usercode,
Transactions.[Out date], Transactions.transfer FROM Transactions WHERE
Transactions.Usercode= " & fcode & ";"

Set rs = CurrentDb.OpenRecordset(rstr)
Set Forms!equip_tfr_3.[Child20].Form.Recordset = rs

the subform is unbound and not linked to the main form, but the fields are
bound to the names of the recordset columns.

 

[Allen Browne]

Okay: thanks for posting your code. Now you might like to read the
suggestions and issues discussed in the previous reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

here is my full code allen

Private Sub Command7_Click()
'dec
Dim rs As Recordset
Dim rstr As String
Dim fcode As Integer
Dim fname As String
Dim tcode As Integer
Dim tname As String


'init
fcode = Me.Text4.Value
fname = Me.text5.Value
tcode = Me.Text3.Value

rstr = "SELECT Users.[First Name], Users.Surname, Users.Usercode FROM
Users
WHERE Users.Usercode=" & tcode & ";"

'recordset to find users name
Set rs = CurrentDb.OpenRecordset(rstr)
tname = rs.Fields("First Name")
tname = tname & " " & rs.Fields("Surname")

'switch forms
DoCmd.OpenForm "equip_tfr_3", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "Equip_tfr_2", acSaveNo

'populate next form
Forms!equip_tfr_3.Text3 = fcode
Forms!equip_tfr_3.Text4 = fname
Forms!equip_tfr_3.text5 = tcode
Forms!equip_tfr_3.text6 = tname

'close recordset
rs.Close

'build subform
rstr = "SELECT Transactions.Transcode, Transactions.Usercode,
Transactions.[Out date], Transactions.transfer FROM Transactions WHERE
Transactions.Usercode= " & fcode & ";"
Set rs = CurrentDb.OpenRecordset(rstr)
Set Forms!equip_tfr_3.[Child20].Form.Recordset = rs
Forms!equip_tfr_3.[Child20].Form.Refresh

'clear the recordset
rs.Close
Set rs = Nothing
End Sub

i based it on another piece of code that yourself and dirk goldgar helped
me
with

http://www.microsoft.com/communitie...3bc70e8bebb1&lang=en&cr=US&sloc=en-us&m=1&p=1

here is the other piece of code that works perfectly

Private Sub Command7_Click()
'dec
Dim rs As Recordset
Dim ucode As Integer
Dim qry1 As String
Dim qry2 As String
Dim Name As String

'init
ucode = Me.Text3
qry1 = "SELECT Users.[First Name], Users.Surname, Users.Usercode FROM
Users
WHERE Users.Usercode=" & ucode & ";"
qry2 = "SELECT Transactions.Usercode, Transdata.Barcode, Transdata.[In]
FROM
Transactions INNER JOIN Transdata ON Transactions.Transcode =
Transdata.Transcode WHERE Transactions.Usercode =" & ucode & "AND
Transdata.[In] Is Null;"

'init recordset to find users name
Set rs = CurrentDb.OpenRecordset(qry1)
Name = rs.Fields("First Name")
Name = Name & " " & rs.Fields("Surname")
rs.Close

'init recordset to populate subform
Set rs = CurrentDb.OpenRecordset(qry2)

'switch forms
DoCmd.OpenForm "equip_in_2", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "equip_in_1", acSaveNo

'populate new form
Forms!equip_in_2.Text3 = ucode
Forms!equip_in_2.Text7 = Name
Set Forms!equip_in_2.[Child20].Form.Recordset = rs

'clear recordset
rs.Close
Set rs = Nothing
End Sub

i cant understand why it worked the last time but not this time. i believe
i
am using the same method. they only different this time is the
sub-subform.

How have you declared your rstr variable? If it's declared within a
procedure, it ceases to exist once the procedure ends, so there's nothing
left to assign to your form.

Try declaring it in the General Declarations section of a standard module
(not the module of a form/report):
dim gDb As DAO.Database
Dim gRst AS DAO.Recordset
Then start with a stand-alone form (not as a subform) and in its Open
event
assign the recordset variable and assign it to the form:
Private Sub Form_Open(Cancel As Integer)
Set gDb = CurrentDb()
Set gRs = gDb.OpenRecordset(strSql)
Me.Recordset = gRs
End Sub
with the matching code to close recordset and set both objects to Nothing
in
Form_Close (with error handling in case it got reset.)

Once you get that working, you can try it as a subform. However, you will
not be able to use anything in the subform control's
LinkMasterFields/LinkChildFields. If you use these properties, Access
reloads the subform every time the record in the main form changes, so
you
attempt to set the Recordset does not survive.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

i have a piece of vba code that collects a variable and uses that
variable
to
generate a recordset.

So far so good

now i want to use that record set to populate a subform. problem is it
all
seems to work except instead of getting my data i get #name?

what am i doing wrong?

here is the code

rstr = "SELECT Transactions.Transcode, Transactions.Usercode,
Transactions.[Out date], Transactions.transfer FROM Transactions WHERE
Transactions.Usercode= " & fcode & ";"

Set rs = CurrentDb.OpenRecordset(rstr)
Set Forms!equip_tfr_3.[Child20].Form.Recordset = rs

the subform is unbound and not linked to the main form, but the fields
are
bound to the names of the recordset columns.

 

[Joseph Atie]

allen i understand the issues you talked about regarding linking parent and
child forms, you told me this the last time. it doesnt effect the function i
wish to perform.

i have a piece of code that works in one instance and not in another.

i belive im using the same method in both instances.

all im trying to do is understand why that is.

i see that you presented a different method, but as i asked in my last post,
if you know can you tell me why my code works in one instance but not
another.

anyway back to your suggestion.

what your saying is that once the form that executed the code is closed that
recordset no longer exists, is that correct?

in reply to that, once the recordset has been loaded to the form i should no
longer need it?

or must the recordset exist as long as the form exists?

i tend to close and open record sets as i need them, is there a reason they
need to have more permanent existance?

 

[Allen Browne]

I would expect that the lifetime of the recordset variable would need to be
at least as long as the time you need to use it (for your form.)

 

[Joseph Atie]

oddly your globabl variable solution gave me an error

runtime error 91

object variable or with block variable not set

this related to

me.recordset = gRs.

that makes no sense to me.

by moving to the form and not the subform i am able to get my method to
populate a form, but i cant get your method to work for a form nor can i get
either method to work for a subform.

im now more confused than ever as to why this doesnt work. ive used the same
method on another form and it works fine.

using my method i was even able to get the subform (which would be the sub
subform from my original request) to work as I want it to. but when i try to
load all that onto another form it all falls apart

 

[Allen Browne]

Did you include the SET keyword?

Did you declare the variable from the right library, e.g.:
... AS DAO.Recordset

Did you declare it in a standard module (not that of a form)?

Try Public rather than Dim
Public gRst AS DAO.Recordset

 

[Joseph Atie]

yep, used set

declared in module that shows up in the module section of the db window

do i need to wrap the declaration in anything? its just sitting there in
space no part of a function or anything.

and yes i used dao.recordset, is this just a explicit way of writing
recordset or is dao.record different to the standard recordset?

i know im annoying but im just trying to understand how these things work so
i can build on them. im very rusty, i havent programmed in 2 years.

thanks in advance for your time.

 

[Allen Browne]

Sounds like you used a class module rather than the a standard module. Does
it show up in the Modules tab of the Database Windows/Navigation Pane?

 

[Joseph Atie]

yes it shows up in the module window.

its just a module, it comes up under the modules heading

i can then add a class module that comes up under the class module heading
in the vba editors module selector.

 

[Allen Browne]

Please re-read the last question. I did not ask if it shows up in the VBA
window.

 

[Joseph Atie]

sorry i answered your question i just wasnt specific enough

it shows up under modules of both vba editor and access

 

[Allen Browne]

Okay: I don't know what else to suggest.

If it shows up in the main Access window on the Modules tab of the
Navigation Pane/DB window, then you have used a standard module (not a class
module.)

If you declared the recordset variable in the General Declarations section
of that module, then its lifetime is not limited any particular procedure.

If you then set that variable before opening the form (or in Form_Open),
there's no problem with timing or lifetime.

If you used the right kind of variable (DAO versus ADO), that's not the
issue.

If the code compiles, it is not an issue with library references.

And if there is nothing in the LinkMasterFields/LinkChildFields, then Access
should not be reloading the subform and losing touch with your recordset.

I guess you could start investigating whether the Recordset still exists (if
not, it is a lifetime issue), and whether Me.Recordset IS gRst (if it was
but is no longer, than Access is re-loading the form with something else.)

Don't know what else to offer

 

[Joseph Atie]

what do you mean by general declarations section?

This is all the module contains

Option Compare Database

Dim gDb As DAO.Database
Dim gRst As DAO.Recordset

there isnt anything that should stop me from doing waht i want to do is there?

can i assign a recordset to a subform? <- this one i know i can do
can i assign a recordset to a subsubform?