Find Record (Access 2000)

[JohnSimmonds]

How can I use a ListBox.Value as criteria for DoCmd.FindRecord?

[R. Hicks]

One way to do this is to use the BuildCriteria() Function that is in Access. Here is an example:

Private Sub cboYourComboName_AfterUpdate()
On Error GoTo ErrorHandler

If Not IsNull(cboYourComboName) Then
Me.RecordsetClone.FindFirst BuildCriteria("NameOfFieldInTable", dbText, cboYourComboName)
Me.Bookmark = Me.RecordsetClone.Bookmark
cboYourComboName = Null
Else
MsgBox "You Must Choose a Value from the ComboBox.", vbOKOnly, " No Item Selected ...."
End If

ExitHere:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume ExitHere
End Sub

You can find more information on the use of this function in Access Help files.

HTH
RDH

[charlotte]

One caveat on BuildCriteria is that it only handles a single field at a time. If you want two different criteria (i.e., Field1 = "OK" And Field2 > 3), you'll have to run BuildCriteria twice and join the results. I always forget about the function because by the time you type in all the pieces, you might as well have created the criteria string by hand. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

[R. Hicks]

You are absolutely right .....
I just thought I'd mention it due to many people not knowing that this function exists.

Happy Holidays,
RDH

[JohnSimmonds]

Works Great, Thanks and Merry Christmas

[R. Hicks]

Great .... You are Welcome .... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Make sure you read Charlotte's note about the limations of using this function.

Happy Holidays,
RDH