find a record in a recordset

Three easy ways to jump to a record in a combobox but still be able to scroll through all the other records The techniques can be used in ways other than a Combo box:  

1) The combo box and list box wizard will do this for you automatically. In the first or second step (depending on you version of Access), choose the third option: "Find a record on my form based on the value selected in my combo box" This must be done on a BOUND form.  

2) Have the record ID number as one of the fields in the list/combo box and in the form (they can both be hidden).  The bound column of the list/combo box is the record ID field.  Then the On After Update Event of the list/combo box would look like:

Private Sub List/ComboBoxName_AfterUpdate()       'Use the name of your list/combo box
txtRecordID.SetFocus       'Use the name of your text box
DoCmd.FindRecord List/ComboBoxName       'Use the name of your list/combo box
End Sub

3) Again, the bound column of the list/combo box is the record ID field.

Private Sub List/ComboBoxName_AfterUpdate()       'Use the name of your list/combo box
Me.RecordSet.FindFirst "txtRecordID = " & List/ComboBoxName       'Use the name of your list/combo box and text box
End Sub

Two easy ways to filter the records to show only the record selected:

1) With the bound column of the list/combo box is the record ID field.

Private Sub List/ComboBoxName_AfterUpdate()       'Use the name of your list/combo box
Me.Filter = "txtRecordID = " & List/ComboBoxName       'Use the name of your list/combo box and text box
Me.FilterOn = True
End Sub

2) Again, the bound column of the list/combo box is the record ID field.

In the query that is the record source for the form, create a criteria like this:
[Forms].[YourFormName].[ComboBoxName]       'Use the name of your list/combo box and form name

Additional information:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209537
http://allenbrowne.com/ser-03.html
http://www.rogersaccesslibrary.com/download3.asp?SampleName=ComboChoosesRecord.mdb

Hi thenelson

The table only has one field called Log, so I would assume the code should be:

rstData.FindFirst rstData!Log = "Serial Number*"

but this does not work!

<rstData.FindFirst rstData!Log = "Serial Number*">

1. If you have only one filed called "Log" (as you say), then where is "Serial Number" coming from?
Is this a value in the Field?, A Field Name?, A variable?

2. If you are trying to: "find a record in a recordset"
"A",  being the key word here, then why are you using a wildcard ("*")?

AFAICT, The syntax would be exactly what Pete posted:
    rst.findfirst "Log=" & value
(If Log is numeric)
Or
    rst.findfirst "Log=" & "'" & value & "'"
(If Log is Text)

Also note that you seem to be using spaces in "Serial Number".
(Please try to avoid this because of issues like this, see #3 here:
http://www.mvps.org/access/tencommandments.htm)

So if the value is : "Serial Number", then you should enclose it in square brackets:
    [Serial Number]

 So might this do what you need?:

       rst.findfirst "Log=" & "'" & [Serial Number] & "'"

This should find the first record in the recordset with the Word:  Serial Number
...in the Log Field.

If not then please post some sample data to eliminate the need for us to guess.
Sound fair?
;-)

Thanks

JeffCoachman

... or for your situation:

    rstData.findfirst "Log=" & "'" & value & "'"

In other words try this:

    rstData.findfirst "Log=" & "'" & [Serial Number] & "'"

Here is boilerplate code for an unbound combo box record selector.  If Serial Number is a Text field, use the String syntax:

Private Sub cboSelect_AfterUpdate()
'Written by Helen Feddema 29-Jun-2009
'Last modified 11-Oct-2009
 
On Error GoTo ErrorHandler
 
   Dim strSearch As String
 
   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me![cboSelect].Value _
      & Chr$(39)
 
   'For numeric IDs
   strSearch = "[______ID] = " & Me![cboSelect].Value
 
   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch
 
ErrorHandlerExit:
   Exit Sub
 
ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit
 
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

The combo box's row source has the ID field as the first (bound) column, and one or more other columns with info useful for selecting a record.