Comboboxes and record deletion

[Chuckles]

I have two unbound combo boxes on one form and would like to select an
item in the first combo box which then brings up a list associated with
that item in the second combo box That part is all set up. however I
can't seem to figure out when i press a delete cmd button how to remove
that record from a table. I tried to write an sql statement but because
I'm new to all of this I can't seem to get those tricky parenthesis in
the right spots. Code would be greatly appreciated. Thanks for any help
in advance.

[Br]

Chuckles wrote:

I have two unbound combo boxes on one form and would like to select an
item in the first combo box which then brings up a list associated
with that item in the second combo box That part is all set up.
however I can't seem to figure out when i press a delete cmd button
how to remove that record from a table. I tried to write an sql
statement but because I'm new to all of this I can't seem to get
those tricky parenthesis in the right spots. Code would be greatly
appreciated. Thanks for any help in advance.

Docmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblMyTable WHERE [lngMyID] = " &
Me![cboMyCombBoxBoundToID]
Docmd.SetWarnings True
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Chuckles]

Thanks Bradley for the response. It still doesn't seem to be working
Here's the code. What am I a missing?

Private Sub Command24_Click()
On Error GoTo Err_Command24_Click

Dim MsgStr As String
Dim TitleStr As String

If IsNull(Me.Combo45) Then
MsgBox "No job codes selected...", vbExclamation
Exit Sub
End If

MsgStr = "Are You Sure You Want To Delete This Record?"
TitleStr = "Confirm Delete Record"
If MsgBox(MsgStr, vbYesNo, TitleStr) = vbNo Then
Exit Sub
End If

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM [Employee/Jobcodes] WHERE
[empnum] = " & [Me.Combo45.Value]
DoCmd.SetWarnings False

DoCmd.SetWarnings True
MsgBox "Removed successfully...", vbInformation
DoCmd.SetWarnings True

Me.JobCodeCombo = Null
Me.JobCodeCombo.Requery
Me.JobCodeCombo = Me.JobCodeCombo.ItemData(0)
Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub

[Chuckles]

Sorry tried it again heres my sql statement:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM [Employee/Jobcodes] WHERE
[empnum] = " & Me![Combo45] And [JobCode] = "& me![jobcodecombo]"
DoCmd.SetWarnings True

I get a type mismatch error? Can't figure it out.

[Br]

Chuckles wrote:

Thanks Bradley for the response. It still doesn't seem to be working
Here's the code. What am I a missing? <>
DoCmd.RunSQL "DELETE * FROM [Employee/Jobcodes] WHERE
[empnum] = " & [Me.Combo45.Value]

Get this code line by itself and manually plug an ID into it, run it,
and see if the record is deleted.

Is [empnum] is actually a number and not text?

Is the ID the bound column in your combobox?

ps. Get into a good habit early :) ... use an exclamation, eg.
Me![Combo45], when refering to objects (use dot for
methods/properties).

<>
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Chuckles]

Ok I commented everything out except this line of code after the
command button. I am still getting a type mismatch error. Thank you so
much for all of your help.

DoCmd.RunSQL "DELETE * FROM [Employee/Jobcodes] WHERE [empnum] = " &
Me![Combo45] And [JobCode] = "& me![jobcodecombo]"

[Chuckles]

oh and by the way neither combobox is bound because they look up data
from queries. As for empnum, it is text, as well as the jobcode it is
also text.

[Roger Spencelayh]

<os*****************@news-server.bigpond.net.au>
<11********************@g47g2000cwa.googlegroups.c om>
<qU*****************@news-server.bigpond.net.au>
<11*********************@o13g2000cwo.googlegroups. com>
Lines: 23
NNTP-Posting-Date: 07 Oct 2005 21:07:17 GMT
NNTP-Posting-Host: 81.187.1.21
X-Trace: 1128719237 news.aaisp.net.uk 38045 81.187.1.21
X-Complaints-To: aa*************@gradwell.net
Xref: number1.nntp.dca.giganews.com comp.databases.ms-access:836997

In article <11*********************@o13g2000cwo.googlegroups. com>,
Chuckles wrote:

DoCmd.RunSQL "DELETE * FROM [Employee/Jobcodes] WHERE [empnum] = " &
Me![Combo45] And [JobCode] = "& me![jobcodecombo]"

As a test, instead of using Combo box values, use actual values. E.g.:

DoCmd.RunSQL "DELETE * FROM [Employee/Jobcodes] WHERE [empnum] = '1'
And [JobCode] = '2';"

Assuming you have an empnum of 1 and a jobcode of 2 this should work.

Now replace the 1 and 2 with you combo box values:

DoCmd.RunSQL "DELETE * FROM [Employee/Jobcodes] WHERE [empnum] = '" &
Me![Combo45] & "' And [JobCode] = '" & me![jobcodecombo] & "';"

Hope that helps.

--
Roger

[Br]

Chuckles wrote:

oh and by the way neither combobox is bound because they look up data
from queries. As for empnum, it is text, as well as the jobcode it is
also text.

Ok. If they are text you must put quotes around them in your SQL
statement.

Oh, and you are missing a few & etc...

DoCmd.RunSQL "DELETE * FROM [Employee/Jobcodes] WHERE [empnum] = " &
Me![Combo45] And [JobCode] = "& me![jobcodecombo]"

Should be...

DoCmd.RunSQL "DELETE * FROM [Employee/Jobcodes] WHERE [empnum] = '" &
Me![Combo45] & "' And [JobCode] = '" & me![jobcodecombo] & "';"
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response