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
  • 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?
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 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