Finding a Record with a Multiple Combo Boxes

[RobESD]

Still struggling with this. I have form whose record
source is a table. I want the user to be able to go to a
particular record on form based on criteria entered. The
problem is the criteria has to be based on three fields in
the table. It is only by the combination of the three
fields that a particular record can be identified.

I want the user to be able to enter criteria in three
separate combo or text boxes. Then click a command button
and then have form go to that particular records based on
the criteria.

I have tried GotoRecord, DLookup, FindRecord, etc. Can
anyone get started in the right direction? Here is an
example of one of my failures:

DoCmd.FindRecord Me![cmbID] & Me![cmbTransmittal] & Me!
[Box], acAnywhere, 0, _
acSearchAll, True, acAll, True

Thanks for any help.

 

[Ted]

Hi,

I have used the following code to find a record. You
would only have to modify it by changing the field name
and criteria in the FindFirst statement. I have included
it two ways, once without comments and once with (portion
with comments is somewhat hard to read due to wrapping).
The last statement to reset the combo box to Null is
optional, but if you chose to do it you would probably
want to do it for all three of your combo boxes.

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Cbo_FindProject],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Cbo_FindProject.Value = Null

Once again, with comments:

Dim rs As Object
'Create a recordset that is a clone of the form
recordset
Set rs = Me.Recordset.Clone

'Find the selected project ID in the recordset clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Cbo_FindProject],
0))
'If the project ID is found, set the bookmark of the
form to the bookmark of the
'Clone recordset
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
'Reset the Combo Box value to Null to prepare for
future navigation
'and to avoid confusion after manually scrolling to
other projects
'(because the ID/Project displayed in the combo box
would be different than
'the active project)
Me.Cbo_FindProject.Value = Null

Hope that is helpful.

-Ted

 

[SteveS]

I was going thru some old printouts from the web, and I found an article
that looks like what you want:

"SEARCH MULTIPLE FIELDS SIMULTANEOUSLY ans SET FORM to RECORD FOUND"

the URL is:

http://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/27/pid/702/fid/1177


HTH

Steve