Solved

Deleting records based on data matches in two different tables

Posted on 2014-11-11
2
124 Views
Last Modified: 2014-11-12
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!
0
Comment
Question by:Megin
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40436599
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
 

Author Closing Comment

by:Megin
ID: 40437967
It works!

Thank you!!!!!!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now