Solved

Delete a record from a table through a subform in Access

Posted on 2014-09-05
9
1,446 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
  • 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
 

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

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

22 Experts available now in Live!

Get 1:1 Help Now