Runtime Error '13': Type Mismatch

[Pookiemeister]

I'm not sure if this should be posted in the MS Access forum or Excel. I'm using the Excel userform on the front end and trying to retrieve the data on the back end.

I would like to create an autopopulated userform. If the user types in an employee ID, then the first & last name as well as that person's email address is automatically filled in on the form based on the data entered in MS Access. This is what I have so far.

USERFORM CONTROLS
5 Textboxes
1. txtEmpNum 'Employee ID
2. txtFN 'First Name
3. txtLN 'Last Name
4. txtEmail

Private Sub txtEmpNum_Change()
    FindRecordInAccess
End Sub

Sub FindRecordInAccess()

'Path
    Dim strPath As String
   
    'Provider
    Dim strProv As String
   
    'Connection String
    Dim strConn As String
   
    'Connection
    Dim Conn As New Connection
   
    'RecordSet
    Dim reqry As New Recordset
   
    'SQL Query
    Dim strQry As String
   
    strPath = "C:\Users\UO83254\OneDrive\Documents\Personnel.accdb"
    strProv = "Microsoft.ACE.OLEDB.12.0;"
    strConn = "Provider=" & strProv & "Data Source=" & strPath
   
    'Connection Open
    Conn.Open strConn
   
   
If Len(frmPersonnelEntry.txtEmpNum.Text) > 7 Then
        Dim defaultSql As String
        Dim strSQL As String
        
        defaultSql = "SELECT * FROM Personnel"
        
        strSQL = defaultSql & " WHERE " & [Employee #] & " LIKE '*" & frmPersonnelEntry.txtEmpNum.Text & "*'"[B] ERROR OCCURS HERE[/B]
       
        MsgBox "Record Found: "
        Debug.Print strQry
    Else
        MsgBox "Record NOT Found"
    End If

   
End Sub

 

[Alex Blakenburg]

Isn't "[Employee #]" just a field name in the database and needs to be passed through as a string ?

Untested but try"

        strSQL = defaultSql & " WHERE [Employee #] LIKE '*" & frmPersonnelEntry.txtEmpNum.Text & "*'"

 

[Pookiemeister]

@Alex Blakenburg
Yes, that is correct [Employee #] is the field name. Thank you for the quick response.

 

[Alex Blakenburg]

So did the change I made fix it ?