?
Solved

Delete a record from a table through a subform in Access

Posted on 2014-09-05
9
Medium Priority
?
1,801 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 85
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 85
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 38

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

801 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