You cannot apply the criteria to the form you are opening, because that form
does not have the value you are seeking for. Instead you will need to:
1. Open the form.
2. Find the right record in the main form (so the record is loaded into the
subform).
3. Find the record in the subform.
Step 2 will depend on how the subform is linked to the main form. For
example, if your [Form with the tabs] is bound to the Product table, and
then its subform shows the vendors who sell those products, there could be
multiple products that have that vendor. How would you decide which product
to load into the main form, so you could then find the vendor in the
subform?
Or perhaps [Forms with the tabs] is an unbound form that just holds all the
subforms on various tab pages?
Ultimately, locating the record in the subform will consist of a FindFirst
on the RecordsetClone of the subform. This example omits step 2, but shows
how to find a record in the subform:
Dim rs As DAO.RecordsetClone
Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.VendorID) Then
'Open the target form.
stDocName = "Form with the tabs"
DoCmd.OpenForm stDocName
'Find the record in the subform.
With Forms(stDocName)![NameOfYourSubformControlHere].Form
Set rs = .RecordsetClone
strWhere = "[VendorID]=" & Me![VendorID]
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "not found"
Else
.Bookmark = rs.Bookmark
End If
End With
End If
Set rs = Nothing
Note: If VendorID is a Text type field (not a Number type), you need extra
quotes:
strWhere = "[VendorID]=""" & Me![VendorID] & """"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
DM said:
Hello, Could someone please tell me how to refer to a control on a subform
that's on a tab control. I have a button on 1 form that when clicked
should
open a form that has tabs and find a specific record on the first tab.
The
record is in a subform on the 1st tab. I hope someone can understand what
I'm trying to say. Here's what I have, but it's not working:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Form with the tabs"
stLinkCriteria = "[VendorID]=" & Me![VendorID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
What am I doing wrong??
Thanks
