Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2128
  • Last Modified:

Delete a record from a table through a subform in Access

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
Megin
Asked:
Megin
  • 5
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
MeginAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Independent Software Vendors: 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!

 
MeginAuthor Commented:
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
 
MeginAuthor Commented:
Ignore that. I see all the errors.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
MeginAuthor Commented:
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
 
PatHartmanCommented:
Yes.  All you are doing is putting the SQL into a variable.  Scott's example shows how to execute it.
0
 
MeginAuthor Commented:
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

Independent Software Vendors: 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!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now