Subform problems (what a surprise!)

[Lee]

Hello everyone,
I'm trying to create a 'Find' type of form to locate
particular people in a subform.
The main form holds the details of an Order, and the
subform lists the products that comprise that order.
I've got a form (called frmGoTo) where I would like the
users to put in a particular product name and the main
form/subform will 'jump' to the first applicable order
for that product. Here's the code on this 'Find' form:

Dim frm As Form
Dim rec As Recordset

Set frm = Forms![Orders]![Products]
Set rec = frm.RecordsetClone

If IsNull(Me!GoTo) Then
MsgBox "First enter a Product to look up!",
vbExclamation, "Oops!"
Me!GoTo.SetFocus
Else
rec.FindFirst "Forms![Orders]![Products]![ProdName]="
Like "*" & "'" & Me!GoTo & "'" & "*"
End If

If rec.NoMatch Then
MsgBox "Unable to locate the Product. Try again.",
vbExclamation, "Product not found..."
Else
frm.Bookmark = rec.Bookmark
End If

rec.Close

I'm get 'type mismatch' errors and other things I've
tried just make it worse! Can you help??
TIA
Regards,
Lee

 

[Allen Browne]

The subform contains only records that match the main form.
Your code searches the subform, but it will not move the main form to a
record where there is a match.

This article explains how to set the RecordSource of the main form so it
contains only records that have a match in the subform:
Filter a Form on a Field in a Subform
at:
http://users.bigpond.net.au/abrowne1/ser-28.html


If you are concerned about the errors you were receiving, these 3
suggestions won't make the search work but they may help you understand the
error messages:

1, The ADO library and the DAO library both have a Recordset object.
Assuming your data is in Access (Jet), the RecordsetClone would be DAO, so
you would use:
Dim rst As DAO.Recordset

2. To refer to the form in a subform control, you need the ".Form" bit,
i.e.:
Set frm = Forms![Orders]![Products].Form
Details
http://users.bigpond.net.au/abrowne1/casu-04.html

3. The FindFirst needs to specify a field name (not a form reference), so
you cannot include the "Forms![Orders]![Products]!" bit. Also, you can use
the = operator or the Like operator, but not both:
rec.FindFirst "[ProdName] = '" & Me!GoTo & "'"