Error when populating listbox

[KevCB226]

Hi

I've created a few listboxes before, which have all worked fine, but this one however keeps throwing up an error message.

Run-time error '3464': Data type mismatch in criteria expression

Can someone help with this, here is my code, and the highlighted section it debugs.

Dim db As Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim EmpID As Integer

Me.List35.RowSource = ""
EmpID = [Forms]![frmWork_Placement]![Emp_ID]

Set db = CurrentDb()
sSQL = " SELECT Object_ID, Emp_ID, Name1, Name2, Name3, Name4, Name5" & _
       " FROM dbo_tblEmpContacts WHERE Emp_ID = '" & EmpID & "'"

[COLOR="Red"]Set rst = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)[/COLOR]

    Me.List35.AddItem rst![Name1]
    Me.List35.AddItem rst![Name2]
    Me.List35.AddItem rst![Name3]
    Me.List35.AddItem rst![Name4]
    Me.List35.AddItem rst![Name5]

rst.Close
Set rst = Nothing

Thanks

 

[nateobot]

If your Emp_ID is a numeric datatype (number, decimal, integer, etc...) you will get that error. Don't put the apostrophes around it and things should be fine.

 

[KevCB226]

Thanks for that reply, it worked great

I now have another problem though. Unfortunately some of the records that I'm placing in the listbox don't contain anything, so are null values.

What I would like to do is create an If statement to say that If there are no records then display a MsgBox to tell the user that there is nothing to select, else display only the records that are not null.

I can't figure this out, I have tried using EOF in the If statement and setting it to true, but I keep getting the 'Invalide use of Null' error.

Dim db As Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim EmpID As Integer

Me.List35.RowSource = ""
EmpID = [Forms]![frmWork_Placement]![Emp_ID]

Set db = CurrentDb()
sSQL = " SELECT Object_ID, Emp_ID, Name1, Name2, Name3, Name4, Name5" & _
       " FROM dbo_tblEmpContacts WHERE Emp_ID = " & EmpID & ""

Set rst = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

    If rst.EOF = True Then
    MsgBox "No Employer Contacts.  Please go back and enter a contact", vbOKOnly
    Else
    Me.List35.AddItem rst![Name1]
    Me.List35.AddItem rst![Name2]
    Me.List35.AddItem rst![Name3]
    Me.List35.AddItem rst![Name4]
    Me.List35.AddItem rst![Name5]
    End If

rst.Close
Set rst = Nothing

Thanks