Lookup Subform Value

[velcrowe]

I am using this Access code (as provided by the lookup wizard) to search a field on my form and on the click event of the lookup list, i will go to that record.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtPurchaseOrderNo] = '" & Me![List109] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I have a subform on the form and wondered is there a way to set the recordset (rs) to a field on the subform and not the form. However, I need the lookup field to exist on the form. Thank you

 

[pbaldy]

Sure; just change the "Me" to the appropriate reference to the subform:

http://www.mvps.org/access/forms/frm0031.htm

 

[velcrowe]

I tried this and get the error message Access can't find the subform

Set rs = Forms!frmConsultantSubform.Recordset.Clone
rs.FindFirst "[txtPurchaseOrderNo] = '" & Me![List109] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I am trying to set the rs to the txtpurchaseOrderNo field on the subform "frmConsultantSubform"

Thank you again

 

[pbaldy]

I don't think you'll find that syntax anywhere on that link.

Try

Set rs = Me.frmConsultantSubform.Recordset.Clone

 

[boblarson]

Or perhaps if you make sure that the CONTAINER that houses the subform is also named frmConsultantSubform (as that is the name that should be used anyway) AND you use the .Form. part to show that you want something on the subform and not the subform container control:

Set rs = Me.frmConsultantSubform.Form.Recordset.Clone

 

[velcrowe]

I am sorry but I think I making this more difficult than necessary

I changed the syntax and now I get that the Method or Data Member is not found, because me.frmConsultantSubform.recordset is not an available method. I don't know what to do

 

[boblarson]

me.frmConsultantSubform.recordset is not an available method. I don't know what to do

Because

1. Is frmConsultantSubform the actual name of the subform CONTAINER which houses the subform on the main form? If you aren't sure, see the first screenshot here to help you identify it:
http://www.btabdevelopment.com/main...rhowtoreferencesubforms/tabid/76/Default.aspx

2. You need to add the literal .Form. between frmConsultantSubform and Recordset so like: Me.frmConsultantSubform.Form.Recordset

 

[velcrowe]

Sorry about that, I added the form.recordset.clone and I no longer receive the data member message but as I click the consultant name it's not taking me to that actual record, but i am working on it. thank boblarson and pbaldy

 

[velcrowe]

When I click on the consultant name, why doesn't Access go the the record where the consultant name appears on the subform.

 

[pbaldy]

What is your full code now?

 

[velcrowe]

The code now is

Private Sub List109_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.frmConsultantsSubform.Form.Recordset.Clone
rs.FindFirst "[txtPurchaseOrderNo] = '" & Me![List109] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

frmConsultant is my main form
frmConsultantsSubform is my subform

 

[pbaldy]

Try

If Not rs.EOF Then Me.frmConsultantsSubform.Form.Bookmark = rs.Bookmark