Filter Records from Combo Box not finding any records?

[TammyT]

Simple idea: use a combo box on the form header to list all records in a table; select the record you want, & it displays the rest of the data for that record in the form detail. Should be easy, right? Why do I always get stuck on the obvious ones!!!!!

Okay - here is the code as I used it from Microsoft's own Filter Records example, modified for my field names:


Private Sub cboItemChoice_AfterUpdate()
' Go to Tools > References and insure that
' there is a reference to
' Microsoft DOA x.x Object Library
' where x.x is the latest version you have

Dim rst As DAO.Recordset
Dim varBookmark As Variant

If Not IsNull(cboItemChoice) Then
Set rst = Me.RecordsetClone

'Find the record that matches the control
With rst
varBookmark = Me.Bookmark
.FindFirst "[ItemID] = " & Chr(34) & cboItemChoice & Chr(34)

If .NoMatch Then
MsgBox "Record not found"
Me.Bookmark = varBookmark
Else
Me.Bookmark = .Bookmark
End If

.Close
End With
Set rst = Nothing
End If

End Sub


Private Sub Form_Current()

cboItemChoice.Value = ItemID

End Sub


When I try selecting a record in the combo box, it gives me a run-time error: 3201, no current record, & the debug is pointing to:

varBookmark = Me.Bookmark

Also - I know it matters, but I never get it right. The Item ID field is numeric - do I have enough " & ' in this line: .FindFirst "[ItemID] = " & Chr(34) & cboItemChoice & Chr(34) ?

Thanks, all!

 

[PHV]

Why not simply this ?
Private Sub cboItemChoice_AfterUpdate()
Dim rst As DAO.Recordset
If Not IsNull(cboItemChoice) Then
Set rst = Me.RecordsetClone
'Find the record that matches the control
With rst
.FindFirst "[ItemID]=" & cboItemChoice
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
.Close
End With
Set rst = Nothing
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TammyT]

That didn't pull anything into the form.

Next idea? !

(code - gotta love it!)