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

Posted on 2007-12-05
Medium Priority
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?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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 2000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

589 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