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.

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Joseph Atie" <(E-Mail Removed)> wrote in message
news:42C1B92E-7E53-4280-8CCC-(E-Mail Removed)...
> 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.
>

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/communities...=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.

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "Joseph Atie" <(E-Mail Removed)> wrote in message
> news:42C1B92E-7E53-4280-8CCC-(E-Mail Removed)...
> > 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.
> >
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Joseph Atie" <(E-Mail Removed)> wrote in message
news:A4ADE512-70F6-4B28-8DE8-(E-Mail Removed)...
> 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/communities...=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.
>
> "Allen Browne" wrote:
>
>> 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
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>>
>> "Joseph Atie" <(E-Mail Removed)> wrote in message
>> news:42C1B92E-7E53-4280-8CCC-(E-Mail Removed)...
>> > 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 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?

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Joseph Atie" <(E-Mail Removed)> wrote in message
news:E1EF19F5-0AEB-4452-BC67-(E-Mail Removed)...
> 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?
>
>

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" wrote:

> 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.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "Joseph Atie" <(E-Mail Removed)> wrote in message
> news:E1EF19F5-0AEB-4452-BC67-(E-Mail Removed)...
> > 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?
> >
> >
>

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Joseph Atie" <(E-Mail Removed)> wrote in message
news:28968EB6-05A4-4282-BEBB-(E-Mail Removed)...
> 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" wrote:
>
>> 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.)
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>>
>> "Joseph Atie" <(E-Mail Removed)> wrote in message
>> news:E1EF19F5-0AEB-4452-BC67-(E-Mail Removed)...
>> > 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?
>> >
>> >
>>

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" wrote:

> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "Joseph Atie" <(E-Mail Removed)> wrote in message
> news:28968EB6-05A4-4282-BEBB-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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.)
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >>
> >> "Joseph Atie" <(E-Mail Removed)> wrote in message
> >> news:E1EF19F5-0AEB-4452-BC67-(E-Mail Removed)...
> >> > 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?
> >> >
> >> >
> >>
>

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?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Joseph Atie" <(E-Mail Removed)> wrote in message
news:8E3B5E7B-5F7C-47A8-B138-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>>
>> "Joseph Atie" <(E-Mail Removed)> wrote in message
>> news:28968EB6-05A4-4282-BEBB-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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.)
>> >>
>> >> --
>> >> Allen Browne - Microsoft MVP. Perth, Western Australia
>> >> Tips for Access users - http://allenbrowne.com/tips.html
>> >> Reply to group, rather than allenbrowne at mvps dot org.
>> >>
>> >>
>> >> "Joseph Atie" <(E-Mail Removed)> wrote in message
>> >> news:E1EF19F5-0AEB-4452-BC67-(E-Mail Removed)...
>> >> > 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?
>> >> >
>> >> >
>> >>
>>