• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

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

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.
0
ouestque
Asked:
ouestque
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:

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#]
0
 
Rey Obrero (Capricorn1)Commented:
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#]
0
 
ouestqueAuthor Commented:
what is the char(34) function for in you answer?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Rey Obrero (Capricorn1)Commented:
chr(34) is double quote- that is just to handle names with special character i.e., O'Brian
0
 
ouestqueAuthor Commented:
would it work if I put the double quote manually? i.e. put--->"" instead of chr(34)?
0
 
Rey Obrero (Capricorn1)Commented:
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#]
0
 
ouestqueAuthor Commented:
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. :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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