Access:Using the fields in the current record as criteria to delete other records

Posted on 2007-12-05
Last Modified: 2013-11-27
I have a table (Name: "Main_Table" Columns: "Name", "SS#") Each record is distinct in this table
I have another table (Name "Main_table2" Columns: "Name", "SS#", "Hobbies") This table can have multiple records that are the same.

I have a subform (Recordsouce: "Main_Table" ---has 2 textboxes with control source: "Name", "SS#" ---The default view is set to "Continuous Forms" so that I can see all records in "Main_Table")

In the footer of this subform is a button titled "Delete". How do I make it so that when users select a record in the subform and click the button titled "Delete", my database will delete all records in "Main_Table2" where "Name" and "SS#" = the values in the current record.

i.e. "Main_Table"

Field           Name            SS#            
Row1         John               3244            
Row2         Jim                  2344     <--------------Currently selected record in the subform.
Row3       Aaron               1111            

i.e. "Main_Table2"

Field           Name            SS#             Hobbies
Row1         John               3244            Water skiing
Row2         Jim                  2344            Water Polo      
Row3       Aaron               1111            Basketball
Row4       Aaron               1111            Basketball
Row5       Aaron               1111            Basketball
Row6       Aaron               1111            Basketball
Row7         Jim                  2344            Water Polo  
Row8         Jim                  2344            Water Polo  
Row9         Jim                  2344            Water Polo  
Row10         John               3244            Water skiing
Row11        John               3244            Water skiing

When the delete button is pressed row2, 7,8 and 9 will be deleted because these records have the same name and SS# as the currently selected record.

I also need to know how to make the selected record(i.e. row2) be deleted when the "Delete" button is pressed.
Question by:ouestque
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
  • 4
  • 3
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20412637

currentdb.execute "delete * from Main_Table where [name]=" & chr(34) & me.[name] & chr(34) & " and [SS#]= '" & me.[SS#] &"'"

if SS# is number type

currentdb.execute "delete * from Main_Table where [name]=" & chr(34) & me.[name] & chr(34) & " and [SS#]= " & me.[SS#]
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20412646
sorry wrong table name

currentdb.execute "delete * from Main_Table2 where [name]=" & chr(34) & me.[name] & chr(34) & " and [SS#]= '" & me.[SS#] &"'"

if SS# is number type

currentdb.execute "delete * from Main_Table2 where [name]=" & chr(34) & me.[name] & chr(34) & " and [SS#]= " & me.[SS#]

Author Comment

ID: 20414279
what is the char(34) function for in you answer?
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!

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20414507
chr(34) is double quote- that is just to handle names with special character i.e., O'Brian

Author Comment

ID: 20414580
would it work if I put the double quote manually? i.e. put--->"" instead of chr(34)?
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 20414619
if you don't want the chr(34), just use

currentdb.execute "delete * from Main_Table2 where [name]='" &  me.[name]  & "' and [SS#]= '" & me.[SS#] &"'"

if SS# is number type

currentdb.execute "delete * from Main_Table2 where [name]='"  & me.[name] & "' and [SS#]= " & me.[SS#]

Author Comment

ID: 20416104
Ok. Thanks so much!!! I will give it a try.

I did not know if there was a significant difference between the chr(34) and "" or not. Nonetheless I am glad you posted it, because I have never seen it before. :)

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

623 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