use recordset as source for form

[Joseph Atie]

I have a form with a subform.

when this form loads I want to use a recordset to populate the subform.

is this possible?

I have tried 2 different methods, but neither seesm to reference the
recordset for the subform.

Me!trans_sheet_in.Recordset = rs


but it returns the error: runtime error 24676: The expression you entered
refers to an object that is closed or doesnt exist.

Is this possible and if so how do I do it.

 

[Tom van Stiphout]

On Wed, 26 Aug 2009 21:05:01 -0700, Joseph Atie

Use the correct syntax to access the form within the subform control:
Me.mySubformControl.Form.Recordset

-Tom.
Microsoft Access MVP

 

[Dirk Goldgar]

On Wed, 26 Aug 2009 21:05:01 -0700, Joseph Atie

Use the correct syntax to access the form within the subform control:
Me.mySubformControl.Form.Recordset

He'll have to use the Set keyword on the assignment statement, too:

Set Me.mySubformControl.Form.Recordset = rs

 

[Joseph Atie]

Tom, I tried adding the following code

Me.[Child20].Form.Recordset = rs

but it is still giving me the same error

The complete function is below, i dont understand why it wont work.

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
Me.[Child20].Form.Recordset = rs
End Sub

 

[Joseph Atie]

Still not joy, still getting the same error.

He'll have to use the Set keyword on the assignment statement, too:

Set Me.mySubformControl.Form.Recordset = rs


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Allen Browne]

My experience is that this doesn't work very well in a subform.

In the most common setup, the subform's LinkMasterFields/LinkChildFields
properties are set so the subform shows only the records that relate to the
one in the main form. Access automatically loads the subform each time you
move record in the main form. So, if you set the RecordSet of the subform,
that only lasts until you move record in the main form, and so does not
achieve what you need.

So, this reply is just to warn you that if you have that kind of setup,
although it is possible to set the subform's RecordSet, it still may not
achieve what you want.

 

[Joseph Atie]

Thanks Allen, I will use a different method, this just seemed like a really
neat way to get what i wanted.

i guess it was too good to be true.

 

[Dirk Goldgar]

My experience is that this doesn't work very well in a subform.

In the most common setup, the subform's LinkMasterFields/LinkChildFields
properties are set so the subform shows only the records that relate to
the one in the main form. Access automatically loads the subform each time
you move record in the main form. So, if you set the RecordSet of the
subform, that only lasts until you move record in the main form, and so
does not achieve what you need.

So, this reply is just to warn you that if you have that kind of setup,
although it is possible to set the subform's RecordSet, it still may not
achieve what you want.

I just did a little testing, and I find that I can set the recordset of the
subform just fine, so long as I do it in the main form's Current event and
include criteria to return only those subform records that will match the
LinkMasterFields of the main form. However, I can't add new records to the
subform via the normal method, because it seems that the automatic entry of
the link-master field(s) into the link-child field(s) doesn't occur.

Maybe this could be worked around. If not, it might be useful for a
read-only subform. However, I can't think under what circumstances it would
be preferable to using a subform in the ordinary way.

 

[Allen Browne]

The main reason I experimented with this, Dirk, was to try to wrap a main
form and subform in a transaction, so you got the chance to commit/rollback
all changes when the form was closed.

But the fact that Access reloaded the subform whenever the main form changed
record meant that the recordset (opened inside a transaction and then
assigned to the subform) was not useful for this purpose.

 

[Dirk Goldgar]

The main reason I experimented with this, Dirk, was to try to wrap a main
form and subform in a transaction, so you got the chance to
commit/rollback all changes when the form was closed.

Yes, that would be a useful ability. I never tried it myself, but I've read
of people -- maybe you! -- trying it with no luck.

But the fact that Access reloaded the subform whenever the main form
changed record meant that the recordset (opened inside a transaction and
then assigned to the subform) was not useful for this purpose.

I see.

 

[Joseph Atie]

dirk you read my mind,

i dont actually need to use the parent child link.

the main form will not actually change record at all.

I belive if i can get this piece of code to worlk that it will do what I
want it to.

and i believe even if it did i can handle it an afterupdate even on the
field that would be the parent child link in a normal situation.

as you can read if you look at my code it is based on the ucode variable
which i could simply recollect with the afterupdate event on the field in the
main form. then it would be a simple requery on the recordset and reupload to
the subform.

I just need to get this 1 line of code to work.

Me.[Child20].Form.Recordset = rs

by the way thanks a bunch you guys your help is greatly appreciated.

 

[Dirk Goldgar]

dirk you read my mind,

i dont actually need to use the parent child link.

the main form will not actually change record at all.

I belive if i can get this piece of code to worlk that it will do what I
want it to.

and i believe even if it did i can handle it an afterupdate even on the
field that would be the parent child link in a normal situation.

as you can read if you look at my code it is based on the ucode variable
which i could simply recollect with the afterupdate event on the field in
the
main form. then it would be a simple requery on the recordset and reupload
to
the subform.

I just need to get this 1 line of code to work.

Me.[Child20].Form.Recordset = rs

As I said in my first post to this thread, you need to use the Set keyword:

Set Me.[Child20].Form.Recordset = rs

 

[Joseph Atie]

dirk you read my mind,

i dont actually need to use the parent child link.

the main form will not actually change record at all.

I belive if i can get this piece of code to worlk that it will do what I
want it to.

and i believe even if it did i can handle it an afterupdate even on the
field that would be the parent child link in a normal situation.

as you can read if you look at my code it is based on the ucode variable
which i could simply recollect with the afterupdate event on the field in
the
main form. then it would be a simple requery on the recordset and reupload
to
the subform.

I just need to get this 1 line of code to work.

Me.[Child20].Form.Recordset = rs

As I said in my first post to this thread, you need to use the Set keyword:

Set Me.[Child20].Form.Recordset = rs


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I tried it before and just tried it again, even with the set in front it
still gives me the exact same error.

runtime error 24676: The expression you entered refers to an object that is
closed or doesnt exist.

 

[Dirk Goldgar]

As I said in my first post to this thread, you need to use the Set
keyword:

Set Me.[Child20].Form.Recordset = rs

I tried it before and just tried it again, even with the set in front it
still gives me the exact same error.

runtime error 24676: The expression you entered refers to an object that
is
closed or doesnt exist.

Then there's something we don't understand about the way you have your form
or subform set up. First, is this code running on the main form? (It
should be.) Second, is "Child20" the name of the subform control on the
main form? (It sure sounds like the name of a subform control.)

I'm looking back to where you posted your original code and I have a further
question. You posted this:

'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
Me.[Child20].Form.Recordset = rs

What form is this code running on? Is it "equip_in_1", which you have just
closed? In the context of this code, what object is "Me"?

 

[Joseph Atie]

As I said in my first post to this thread, you need to use the Set
keyword:

Set Me.[Child20].Form.Recordset = rs

I tried it before and just tried it again, even with the set in front it
still gives me the exact same error.

runtime error 24676: The expression you entered refers to an object that
is
closed or doesnt exist.

Then there's something we don't understand about the way you have your form
or subform set up. First, is this code running on the main form? (It
should be.) Second, is "Child20" the name of the subform control on the
main form? (It sure sounds like the name of a subform control.)

I'm looking back to where you posted your original code and I have a further
question. You posted this:

'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
Me.[Child20].Form.Recordset = rs

What form is this code running on? Is it "equip_in_1", which you have just
closed? In the context of this code, what object is "Me"?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I think you have it there, me would relate to the form the script is called
from correct?

in which case me is closed and the error makes sense.

so I have fixed that issue only to find another one

the subforms shows there are 14 records which is corect but they are all
blank. it doesnt actually display any info just 14 blank records

do i need to manually push the data to each field?

 

[Dirk Goldgar]

I think you have it there, me would relate to the form the script is
called
from correct?
Correct.

in which case me is closed and the error makes sense.

so I have fixed that issue only to find another one

Great, we're making progress!

the subforms shows there are 14 records which is corect but they are all
blank. it doesnt actually display any info just 14 blank records

do i need to manually push the data to each field?

You shouldn't. Are the controls on the subform bound to fields in the
recordset? Their ControlSource properties should be set to the names of
fields returned by your query: "Usercode", "Barcode", and "[In]", if I read
your code correctly.

 

[Joseph Atie]

Dirk, you sir are a gentleman

thank you for all your advice. I have th form operational, the way I
intended it to work.

I think you have it there, me would relate to the form the script is
called
from correct?
Correct.

in which case me is closed and the error makes sense.

so I have fixed that issue only to find another one

Great, we're making progress!

the subforms shows there are 14 records which is corect but they are all
blank. it doesnt actually display any info just 14 blank records

do i need to manually push the data to each field?

You shouldn't. Are the controls on the subform bound to fields in the
recordset? Their ControlSource properties should be set to the names of
fields returned by your query: "Usercode", "Barcode", and "[In]", if I read
your code correctly.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)