Deleting records based on data matches in two different tables

I have a subform in a form in Access and I need to delete item from it based on two other pieces of information in the form.

The problem I am running into is that one of these pieces of information is in a secondary table.

This should clear it up further:

Table1: ActID, STOid
Table2 (the one I need to delete from): tableid, ActID, ActDate

The form provides the ActDate and the STOid.

I want to delete based on the ActDate and STOid selected in the form.

This is what I have so far, but I know it isn't correct. I am not sure how to make the connection with table1.

Private Sub btn_DeleteAll_Click()
Dim msgRes As VbMsgBoxResult
Dim strsql As String

msgRes = MsgBox("Are you sure you want to reset your report?", vbOKCancel, "Reset Report")
If msgRes = vbCancel Then
Exit Sub

strsql = "Delete from Act_SubTO_Date where [ActDate] = " & Forms("frm_Act_Enter").[Sub_frm_Act_enter_crntrpt].Form.[ActDate] AND [Activities].[STOid] = " & Forms("frm_Act_Enter").[Sub_frm_Act_enter_crntrpt].Form.[cmbsto]"



End Sub

Open in new window


Thank you, in advance!
MeginAsked:
Who is Participating?
 
chaauCommented:
You need to modify your query a bit:
strsql = "Delete Act_SubTO_Date.* from Act_SubTO_Date INNER JOIN [Activities] ON Act_SubTO_Date.ActID =  [Activities].ActID " &_
" where Act_SubTO_Date[ActDate] = #" & Forms("frm_Act_Enter").[Sub_frm_Act_enter_crntrpt].Form.[ActDate] &_
"# AND [Activities].[STOid] = " & Forms("frm_Act_Enter").[Sub_frm_Act_enter_crntrpt].Form.[cmbsto]

Open in new window

Basically, you need to join the tables first, and then delete. Also, Date literals need to be enclosed in ##
0
 
MeginAuthor Commented:
It works!

Thank you!!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.