Combo boxes that delete

[Chuckles]

Hi all,
I have two unbound combo boxes on a form that I wuould like to use to
delete a record from a table. The two combo boxes find data from two
other tables through queries. When clicking on a command button to
delete the records it gives me an error of type mismatch. Here is the
code I am using. Any help would be grateful

DoCmd.SetWarnings False
Dim JobCode As String
Dim sempnum As String
Dim sstrsql As String
Dim rst As Recordset
sJobCode = Me![JobCodeCombo]
sempnum = Me![Combo45]

strsql = "DELETE FROM [Employee/Jobcodes] WHERE [empnum] =
" & sempnum And " [JobCode] = " & sJobCode

DoCmd.RunSQL strsql

[Douglas J. Steele]

What data type are empnum and JobCode? If either is text, the value being
passed must be in quotes. For example, if empnum is numeric and JobCode is
text, you'd need:

strsql = "DELETE FROM [Employee/Jobcodes] WHERE [empnum] =
" & sempnum And " [JobCode] = " & Chr$(34) & sJobCode & Chr$(34)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Chuckles" <tm*****@new.rr.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Hi all,
I have two unbound combo boxes on a form that I wuould like to use to
delete a record from a table. The two combo boxes find data from two
other tables through queries. When clicking on a command button to
delete the records it gives me an error of type mismatch. Here is the
code I am using. Any help would be grateful

DoCmd.SetWarnings False
Dim JobCode As String
Dim sempnum As String
Dim sstrsql As String
Dim rst As Recordset
sJobCode = Me![JobCodeCombo]
sempnum = Me![Combo45]

strsql = "DELETE FROM [Employee/Jobcodes] WHERE [empnum] =
" & sempnum And " [JobCode] = " & sJobCode

DoCmd.RunSQL strsql

[Chuckles]

Thanks for the reply
They are both text so I tried this

strsql = "DELETE FROM [Employee/Jobcodes] WHERE [empnum] = " &
"sempnum" And " [JobCode] = " & "sJobCode"

I also set a breakpoint. empnum shows the correct string from sempnum
but jobcode shows jobcode = "" maybe the breakpoint didn't make it all
the way down the string? Still getting the type mismatch

[Douglas J. Steele]

Reread my suggestion. You aren't putting quotes around the variables: you're
passing the name of the variable the way you're doing it.

You can use

strsql = "DELETE FROM [Employee/Jobcodes] WHERE [empnum] = " & Chr$(34) &
sempnum & Chr$(34) & " And [JobCode] = " & Chr$(34) & sJobCode & Chr$(34)

or you can use

strsql = "DELETE FROM [Employee/Jobcodes] WHERE [empnum] = """ & sempnum &
""" And [JobCode] = """ & sJobCode & """"

(that's 3 double quotes before and after sempnum, 3 double quotes before
sJobCode and 4 after)

or you can use

strsql = "DELETE FROM [Employee/Jobcodes] WHERE [empnum] = '" & sempnum &
"' And [JobCode] = '" & sJobCode & "'"

Exagerated for clarity, this last one is

strsql = "DELETE FROM [Employee/Jobcodes] WHERE [empnum] = ' " & sempnum &
" ' And [JobCode] = ' " & sJobCode & " ' "

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Chuckles" <tm*****@new.rr.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Thanks for the reply
They are both text so I tried this

strsql = "DELETE FROM [Employee/Jobcodes] WHERE [empnum] = " &
"sempnum" And " [JobCode] = " & "sJobCode"

I also set a breakpoint. empnum shows the correct string from sempnum
but jobcode shows jobcode = "" maybe the breakpoint didn't make it all
the way down the string? Still getting the type mismatch

[Chuckles]

Being that I am pretty new to sql statements I was shocked and amazed
when that worked. Thank you so much Doug. You rock!!!