Solved

Delete a record from a table through a subform in Access

Posted on 2014-09-05
9
1,374 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Megin
Comment Utility
Ignore that. I see all the errors.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

11 Experts available now in Live!

Get 1:1 Help Now