Solved

Delete a record from a table through a subform in Access

Posted on 2014-09-05
9
1,673 Views
Last Modified: 2014-09-05
I have a subform in a main form and I want to be able to select items in it to delete from a table.

The table is based on a query and when I try to just delete the everything that shows up in that row of the column (sub form is in datasheet view), I get a message that I cannot delete the record because there are other related records in the database.

I need to delete records from a table called Act_SubTO_Date. That table has the following fields: ActSubDateid (PK), Actid (references id from a different table), and ActDate.

My subform is actually pulling information from that table, but the query is involved so that I can give the user more information. So, I want to select the row in the subform and use a button to delete the record in the Act_SubTO_Date table when ActSubDateid matches ActSubDateid in the subform. That should take care of deleting the record, right?

Well, I have tried using code I have used in the past for other things and it just isn't working. Now I am confused and I am sure there are, like, 200 things wrong with this.

So, here is what I have. Try not to laugh:

Private Sub btn_DeleteItemCrntRpt_Click()

Dim frm As Form
Dim ctl As Control
Dim db As DAO.Database
Dim strsql As String

Dim i As Variant

Set frm = Forms(frm_Act_Enter)
Set ctl = frm![Sub_frm_Act_enter_crntrpt]
Set db = CurrentDb

For Each i In ctl.ItemsSelected

strsql = "Delete from Act_SubTO-Date WHERE " & "[ActSubDateid] = [frm_Act_Enter].[Sub_frm_Act_enter_crntrpt].[ActSubDateid]"
 
Next i

ctl.Requery

End Sub

Open in new window


I can't thank you all enough for all the help you give me!
0
Comment
Question by:Megin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 84
ID: 40306390
You can enable Cascading Deletes on your tables, which will take care of downstream records.

Cascade Delete
Or you can delete those records yourself using standard SQL/VBA.
0
 

Author Comment

by:Megin
ID: 40306408
I don't need to cascade the delete. I don't want any records deleted from the other tables. I just want the record deleted from the Act_SubTO-Date table. I think that the 'related records' issue is due to the query also including information from my Activities table and my Activity Type table. I don't want anything from those tables to be deleted.
I just want take the ActSubDateid from the record I select in the subform and delete the record in the Act_SubTo_Date table where ActSubDateid matches the choice in the form.
0
 
LVL 84
ID: 40306421
Okay ...

strsql = "Delete from Act_SubTO-Date WHERE [ActSubDateid] = " & Forms("frm_Act_Enter").[Sub_frm_Act_enter_crntrpt].Form.[ActSubDateid]

If ActSubDateid is a Text field:

strsql = "Delete from Act_SubTO-Date WHERE [ActSubDateid] = '" & Forms("frm_Act_Enter").[Sub_frm_Act_enter_crntrpt].Form.[ActSubDateid] & "'"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Megin
ID: 40306447
I did that, but nothing is happening.

So, I deleted a bunch of the code that seemed like it didn't apply. Here is what I have:

Private Sub btn_DeleteItemCrntRpt_Click()


Dim strsql As String

strsql = strsql = "Delete from Act_SubTO-Date WHERE [ActSubDateid] = " & Forms("frm_Act_Enter").[Sub_frm_Act_enter_crntrpt].Form.[ActSubDateid]
 

End Sub

Open in new window


I click the button and nothing happens. No error messages and no deletions.
0
 

Author Comment

by:Megin
ID: 40306456
Ignore that. I see all the errors.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40306459
Sorry, I assumed you were executing the query, but I guess not. Do this:


Private Sub btn_DeleteItemCrntRpt_Click()
Dim strsql As String

strsql = "Delete from Act_SubTO-Date WHERE [ActSubDateid] = " & Forms("frm_Act_Enter").[Sub_frm_Act_enter_crntrpt].Form.[ActSubDateid]
 
Currentdb.Execute strsql

End Sub

Open in new window

0
 

Author Comment

by:Megin
ID: 40306475
Alright, I fixed the obvious errors (need more coffee and to not multi-task!)

Still nothing. No errors and no deletes. Am I missing something to actually activate the code?

Private Sub btn_DeleteItemCrntRpt_Click()


Dim strsql As String

strsql = "Delete from Act_SubTO_Date WHERE [ActSubDateid] = " & Forms("frm_Act_Enter").[Sub_frm_Act_enter_crntrpt].Form.[ActSubDateid]

 

End Sub

Open in new window

0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40306488
Yes.  All you are doing is putting the SQL into a variable.  Scott's example shows how to execute it.
0
 

Author Closing Comment

by:Megin
ID: 40306494
It works perfectly!

I feel like I need to apologize for not being on the ball for this one. Thank you for your patience!
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 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