Subform problems (what a surprise!)

  • Thread starter Thread starter Lee
  • Start date Start date
L

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
 
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 & "'"
 
Back
Top