FindRecord
~~~

Hi Smoki,

you need to set the form RecordSource to the query

if you wanto to make a combo to find a record that is already there, do
this:

Make one or more unbound combos on your form (like in the header).  Let
the first column be invisible and be the primary key ID of the
RecordSource of your form and then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

   'if nothing is picked in the active control, exit
   If IsNull(Me.ActiveControl) Then Exit Function

   'save current record if changes were made
   If me.dirty then me.dirty = false

   'declare a variable to hold the primary key value to look up
   Dim mRecordID As Long

   'set value to look up by what is selected
   mRecordID = Me.ActiveControl

   'clear the choice to find
   Me.ActiveControl = Null

   'find the first value that matches
   Me.RecordsetClone.FindFirst "SomeID = " & mRecordID

   'if a matching record was found, then move to it
   If Not Me.RecordsetClone.NoMatch Then
      Me.Bookmark = Me.RecordsetClone.Bookmark
   End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in the
RecordSource of the form -- assuming your primary key is a Long Integer
data type (autonumbers are long integers)

Remember that the Rowsource for a combo can come from anywhere -- it can
pull from multiple tables or only use one ... just make sure that the
first column is the primary key ID of the table you want to search (and
that field is part of the RecordSource for the form you are searching).

If you are searching the recordset on another form, change the
FindRecord name to be specific (like FindRecord_Order) and, substitute

Me --> forms!formname

If on a subform:
Me --> Me.subform_controlname.form

Warm Regards,
Crystal

 *
   (: have an awesome day :)
 *