Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

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!
1 Solution
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 ##
MeginAuthor Commented:
It works!

Thank you!!!!!!

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now