ms-accessvbatype-mismatchfindfirst

VBA: type mismatch in rs.findfirst

I have this code: 

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblJobs", dbOpenDynaset)
rs.FindFirst "EmpNo=" & "'cbEmpNo.Value'" & " And " & "JobNo=" & "'cbJobNo.Value'"

and I've been getting type mismatch (removing the ' doesn't seem to help, nor does adding [] around EmpNo & JobNo).

in tblJobs both EmpNo & JobNo are defined as Long Integer.

cbEmpNo & cbJobNo are ComboBoxes with no control source. Their row source is a SQL query pulling EmpNo / JobNo from tblJobs.

Solution

  • Examine the string built by that code. Here it is in the Immediate window:

    ? "EmpNo=" & "'cbEmpNo.Value'" & " And " & "JobNo=" & "'cbJobNo.Value'"
EmpNo='cbEmpNo.Value' And JobNo='cbJobNo.Value'

    So the code asks FindFirst to find the first row where EmpNo is equal to the string cbEmpNo.Value. But since EmpNo is Long Integer, that comparison triggers the type mismatch error. The same issue applies to JobNo.

    Build the string to include the values of those controls (cbEmpNo and cbJobNo) instead of their names, and don't include quotes around those values:

    Dim strFind As String
strFind = "EmpNo=" & Me.cbEmpNo.Value & " And JobNo=" & Me.cbJobNo.Value
Debug.Print strFind ' <- view this in Immediate window; Ctrl+g will take you there
rs.FindFirst strFind