Delete query from two tables

[SoggyCashew]

Hello, I want to run a delete query from a button on my form. The form is a
continious form and each row has a text box "txtEmployeeID" that shows the
UserID. I want to be able to delete the information pertaining to the userID
from two tables and the "UserID"
1) tblUser -Field: UserID (pk)
2) tblInput-Field: UserID

How would I write the SQL in my button to exicute this. Thanks!

 

[Douglas J. Steele]

You either need a two queries (one for each table), or else set up a
relationship with Cascade Delete enabled between the two tables, so that
deleting from tblUser will also delete from tblInput.

 

[SoggyCashew]

Douglas, I create a select query that gives me the answer I want and then
when I change it to a delete query it asks me what table I want to delete
from. Why?
--
Thanks,
Chad

You either need a two queries (one for each table), or else set up a
relationship with Cascade Delete enabled between the two tables, so that
deleting from tblUser will also delete from tblInput.

 

[John Spencer]

It would help if you posted your query.

Hint: In design view; View: SQL

As a guess your select query displays fields from more than one table.
So when you convert it to a Delete query, the database does not know
whether you want to delete from table one or table two.

Also, please post which version of Access you are using.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Douglas J. Steele]

Perhaps you misinterpretted my second suggestion. When you set up the
relationship with Cascade Delete enabled between the two tables, your delete
would only reference the parent table (tblUser).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas, I create a select query that gives me the answer I want and then
when I change it to a delete query it asks me what table I want to delete
from. Why?

 

[SoggyCashew]

Well, I got it worked out using VBA in a button. Thanks for the sugestions!

Private Sub cmdDeleteInputAndUser_Click()
Dim strMsg As String

DoCmd.SetWarnings False '--------------------------Set all Warnings to false
or turns them off

strMsg = "Are you sure you want to delete this employee and all thier
attendance entries?" & Chr(13) & _
"" & Chr(13) & _
"" & _
"NOTE: ****THIS CAN NOT BE UNDONE****" & _
"All data including the employee will be deleted if (Yes) is
selected!!!"

If MsgBox(strMsg, vbCritical + vbYesNo) = vbYes Then

'//-----------Runs delete query
DoCmd.RunSQL "DELETE * FROM tblInput WHERE UserID =" & Me.txtEmployeeID
DoCmd.RunSQL "DELETE * FROM tblUser WHERE UserID =" & Me.txtEmployeeID
DoCmd.SetWarnings True '----------------Sets Warnings back to true
Me.Requery

Else
DoCmd.SetWarnings True '----------------Sets Warnings back to true if no
was selected
End If

End Sub

 

[Douglas J. Steele]

A couple of comments.

Using the Execute method of the Database object means you don't have to
worry about the SetWarnings settings, plus you'll get a trappable error if
something goes wrong.

Since you're doing two related operations, you may want to use a Transaction
to ensure that either both run or neither do.

Even if you don't change your code, you need to move where you set the
warnings back on. You're always turning them off, but only turning them back
on if you don't do the deletions!

 

[SoggyCashew]

Douglas, what do you mean? Im seting the warning back after runing the
deleting and if I dont delete I set the warning back OR am i missing
something? Thanks!
--
Thanks,
Chad

A couple of comments.

Using the Execute method of the Database object means you don't have to
worry about the SetWarnings settings, plus you'll get a trappable error if
something goes wrong.

Since you're doing two related operations, you may want to use a Transaction
to ensure that either both run or neither do.

Even if you don't change your code, you need to move where you set the
warnings back on. You're always turning them off, but only turning them back
on if you don't do the deletions!

 

[John Spencer]

Your code looks good, however I would just setWarnings just before and just
after the delete queries AND I would insert error handling code just in case
that would turn warning back on

Private Sub cmdDeleteInputAndUser_Click()
Dim strMsg As String
On Error goto Proc_Error

strMsg = "Are you sure you want to delete this employee and all thier
attendance entries?" & Chr(13) & _
"" & Chr(13) & _
"" & _
"NOTE: ****THIS CAN NOT BE UNDONE****" & _
"All data including the employee will be deleted if (Yes) is
selected!!!"

If MsgBox(strMsg, vbCritical + vbYesNo) = vbYes Then

'//-----------Run delete queries
DoCmd.SetWarnings False '------Set all Warnings to false
DoCmd.RunSQL "DELETE * FROM tblInput WHERE UserID =" & Me.txtEmployeeID
DoCmd.RunSQL "DELETE * FROM tblUser WHERE UserID =" & Me.txtEmployeeID
DoCmd.SetWarnings True '----------------Sets Warnings back to true
Me.Requery

End If
Exit Sub

Proc_Error:
DoCmd.SetWarnings True
End Sub

As Douglas said, using the execute method would be better.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

Sorry, I missed the fact that you set it back in both legs of the If
statement. That's an unusual thing to do...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas, what do you mean? Im seting the warning back after runing the
deleting and if I dont delete I set the warning back OR am i missing
something? Thanks!

 

[SoggyCashew]

I just thought as a precaution it would be better if I set it back both
cases. Thanks to both of you for the help!