Using findfirst with a subform

[Deb H]

I have a mainform named Registrations with a subform named Childsubform. I am
trying to create an unbound form named FindChild that I will use to find a
record on the subform. I have an unbound listbox named List2 that displays
all the records from the record source of the subform and a showrecord2
button. This is the code I have written for the click event on the
ShowRecord2.

Private Sub ShowRecord2_Click()
'Find the selected record and then close the dialog box
Dim rst As DAO.Recordset
Set rst = Forms![Childsubform].RecordsetClone

DoCmd.OpenForm "Registrations", acNormal

rst.Forms![Registrations]![Childsubform].FindFirst "ChildID = " & List2

'Set the form's bookmark property to move to the record
Forms![Childsubform].Bookmark = rst.Bookmark

DoCmd.Close acForm, "FindChild"

End Sub

When I use the form to show a record, I get a compile error: method or data
member not found. Thanks in advance for any help you can give me.

 

[Deb H]

In trying to troubleshoot this search form, I think part of my problem is in
referring to the subform. I now get an error stating that it can't find the
ChildSubform. Where am I messing up?

 

[Larry Linson]

Yes, I'd agree... to quote from your original post:

Set rst = Forms![Childsubform].RecordsetClone

The Form embedded in a Subform Control (there is no object in Access called
a "Subform" -- it's just often used as verbal shorthand and confuses the
issue), when displayed in the Subform Control, is not "open," and thus, is
not in the Forms collection which only contains _open_ forms. Instead, when
it is displayed, it is the Form property of the Subform Control. To refer to
it from the main Form's code would be:

Set rst = Me![nameofsubformcontrol].Form.RecordsetClone

from elsewhere,

Forms![mainformsname]![nameofsubformcontrol].Form.RecordsetClone

And once rst is Set/Opened, you refer to it directly without repeating the
reference to the Subform Control... so what you coded as

rst.Forms![Registrations]![Childsubform].FindFirst "ChildID = " & List2

would more likely be

rst.FindFirst "ChildID = " & List2

And setting the bookmark would not be

Forms![Childsubform].Bookmark = rst.Bookmark

but would be

Me![nameofsubformcontrol].Form.Bookmark = rst.Bookmark

Larry Linson
Microsoft Access MVP

In trying to troubleshoot this search form, I think part of my problem is
in
referring to the subform. I now get an error stating that it can't find
the
ChildSubform. Where am I messing up?

I have a mainform named Registrations with a subform named Childsubform.
I am
trying to create an unbound form named FindChild that I will use to find
a
record on the subform. I have an unbound listbox named List2 that
displays
all the records from the record source of the subform and a showrecord2
button. This is the code I have written for the click event on the
ShowRecord2.

Private Sub ShowRecord2_Click()
'Find the selected record and then close the dialog box
Dim rst As DAO.Recordset
Set rst = Forms![Childsubform].RecordsetClone

DoCmd.OpenForm "Registrations", acNormal

rst.Forms![Registrations]![Childsubform].FindFirst "ChildID = " &
List2

'Set the form's bookmark property to move to the record
Forms![Childsubform].Bookmark = rst.Bookmark

DoCmd.Close acForm, "FindChild"

End Sub

When I use the form to show a record, I get a compile error: method or
data
member not found. Thanks in advance for any help you can give me.

 

[Deb H]

Thank you for your help. However, I have one more problem. Now when I use my
search form it will only display a record on the subform if it is related to
the first record on the main form. It would seem that the recordset of the
main form needs to be included as well, but I can't seem to get the code
right for this statement:

Set rst = Me![nameofsubformcontrol].Form.RecordsetClone

Any help is very much appreciated.

Yes, I'd agree... to quote from your original post:

Set rst = Forms![Childsubform].RecordsetClone

The Form embedded in a Subform Control (there is no object in Access called
a "Subform" -- it's just often used as verbal shorthand and confuses the
issue), when displayed in the Subform Control, is not "open," and thus, is
not in the Forms collection which only contains _open_ forms. Instead, when
it is displayed, it is the Form property of the Subform Control. To refer to
it from the main Form's code would be:

Set rst = Me![nameofsubformcontrol].Form.RecordsetClone

from elsewhere,

Forms![mainformsname]![nameofsubformcontrol].Form.RecordsetClone

And once rst is Set/Opened, you refer to it directly without repeating the
reference to the Subform Control... so what you coded as

rst.Forms![Registrations]![Childsubform].FindFirst "ChildID = " & List2

would more likely be

rst.FindFirst "ChildID = " & List2

And setting the bookmark would not be

Forms![Childsubform].Bookmark = rst.Bookmark

but would be

Me![nameofsubformcontrol].Form.Bookmark = rst.Bookmark

Larry Linson
Microsoft Access MVP

In trying to troubleshoot this search form, I think part of my problem is
in
referring to the subform. I now get an error stating that it can't find
the
ChildSubform. Where am I messing up?

I have a mainform named Registrations with a subform named Childsubform.
I am
trying to create an unbound form named FindChild that I will use to find
a
record on the subform. I have an unbound listbox named List2 that
displays
all the records from the record source of the subform and a showrecord2
button. This is the code I have written for the click event on the
ShowRecord2.

Private Sub ShowRecord2_Click()
'Find the selected record and then close the dialog box
Dim rst As DAO.Recordset
Set rst = Forms![Childsubform].RecordsetClone

DoCmd.OpenForm "Registrations", acNormal

rst.Forms![Registrations]![Childsubform].FindFirst "ChildID = " &
List2

'Set the form's bookmark property to move to the record
Forms![Childsubform].Bookmark = rst.Bookmark

DoCmd.Close acForm, "FindChild"

End Sub

When I use the form to show a record, I get a compile error: method or
data
member not found. Thanks in advance for any help you can give me.

 

[Deb H]

This is now the code for the command button (ShowRecord2) on my search form:

Private Sub ShowRecord2_Click()
'Find the selected record and then close the search form
Dim rst As DAO.Recordset

Set rst = Forms![Registrations]![Childsubform].Form.RecordsetClone

'Locate the record for the selected subscriber
rst.FindFirst "ChildID = " & List2

'Set the form's bookmark property to move to the record
Forms!Registrations!Childsubform.Form.Bookmark = rst.Bookmark

DoCmd.Close acForm, "FindChild"

End Sub

I am using the unbound search form to show a record from a subform. My
search form contains an unbound combo box (List2) of all the subform records.
The search form only works if I select a subform record that is related to
the first record on the main form (Registrations). If I select some other
record, I don't get an error message. It simply takes me to the first record
in the main form. How can I get it to move to the correct record on the main
form and highlight the selected record in the subform?
I am a novice in using VBA so any further help is much appreciated.

Thank you for your help. However, I have one more problem. Now when I use my
search form it will only display a record on the subform if it is related to
the first record on the main form. It would seem that the recordset of the
main form needs to be included as well, but I can't seem to get the code
right for this statement:

Set rst = Me![nameofsubformcontrol].Form.RecordsetClone

Any help is very much appreciated.

Yes, I'd agree... to quote from your original post:

Set rst = Forms![Childsubform].RecordsetClone

The Form embedded in a Subform Control (there is no object in Access called
a "Subform" -- it's just often used as verbal shorthand and confuses the
issue), when displayed in the Subform Control, is not "open," and thus, is
not in the Forms collection which only contains _open_ forms. Instead, when
it is displayed, it is the Form property of the Subform Control. To refer to
it from the main Form's code would be:

Set rst = Me![nameofsubformcontrol].Form.RecordsetClone

from elsewhere,

Forms![mainformsname]![nameofsubformcontrol].Form.RecordsetClone

And once rst is Set/Opened, you refer to it directly without repeating the
reference to the Subform Control... so what you coded as

rst.Forms![Registrations]![Childsubform].FindFirst "ChildID = " & List2

would more likely be

rst.FindFirst "ChildID = " & List2

And setting the bookmark would not be

Forms![Childsubform].Bookmark = rst.Bookmark

but would be

Me![nameofsubformcontrol].Form.Bookmark = rst.Bookmark

Larry Linson
Microsoft Access MVP

In trying to troubleshoot this search form, I think part of my problem is
in
referring to the subform. I now get an error stating that it can't find
the
ChildSubform. Where am I messing up?

:

I have a mainform named Registrations with a subform named Childsubform.
I am
trying to create an unbound form named FindChild that I will use to find
a
record on the subform. I have an unbound listbox named List2 that
displays
all the records from the record source of the subform and a showrecord2
button. This is the code I have written for the click event on the
ShowRecord2.

Private Sub ShowRecord2_Click()
'Find the selected record and then close the dialog box
Dim rst As DAO.Recordset
Set rst = Forms![Childsubform].RecordsetClone

DoCmd.OpenForm "Registrations", acNormal

rst.Forms![Registrations]![Childsubform].FindFirst "ChildID = " &
List2

'Set the form's bookmark property to move to the record
Forms![Childsubform].Bookmark = rst.Bookmark

DoCmd.Close acForm, "FindChild"

End Sub

When I use the form to show a record, I get a compile error: method or
data
member not found. Thanks in advance for any help you can give me.