.findfirst vba access2010 unbound form runtime error

Question

I have the following VBA code to populate unbound fields in an unbound form in Access 2010 :

Private Sub Combo0_AfterUpdate()

Dim D As Database
Dim rsExp As Recordset
Dim Criteria As String

Set D = CurrentDb
Set rsExp = D.OpenRecordset("ExpAsset", DB_OPEN_DYNASET)

Criteria = "[serial_number]=" & [Combo0]

rsExp.FindFirst Criteria

Me!Name = rsExp("User")
Me!Type = rsExp("Type")
Me!MODEL = rsExp("Model")
Me!Notes = rsExp("Notes")
Me!Department = rsExp("Department")
Me!Status = rsExp("Status")

rsExp.Close

End Sub

I am getting error on this line rsExp.FindFirst Criteria runtime error 3077 Syntax error (Missing operator) in expression.

I have changed the code to Criteria = "[serial_number]=" & Str([Combo0]) but then i get runtime error 13 type mismatch.

HansUp HansUp · Accepted Answer · 2013-04-04T23:58:23

Give yourself an opportunity to examine the criteria you're asking .FindFirst to use. It may not be what you expect.

Dim strCriteria As String
'strCriteria = "[serial_number]=" & Me.Combo0
' since serial_number is text, enclose the Combo0 value in quotes
' when you build strCriteria
strCriteria = "[serial_number]='" & Me.Combo0 & "'"
Debug.Print strCriteria
' or MsgBox strCriteria if you prefer

You can see the output from Debug.Print in the Immediate window. Go there with Ctrl+g

If [serial_number] is text data type, and Debug.Print gives you something like either of these, you have a problem.

[serial_number]=A0123
[serial_number]=

If that doesn't lead you to the solution tell us the data type of [serial_number] and the value of Me.Combo0 when you encounter the error.

.findfirst vba access2010 unbound form runtime error

1 Answers