Solved

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

Posted on 2007-12-05
7
306 Views
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.
0
Comment
Question by:ouestque
  • 4
  • 3
7 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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#]
0
 
LVL 119

Expert Comment

by:Rey Obrero
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#]
0
 

Author Comment

by:ouestque
ID: 20414279
what is the char(34) function for in you answer?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 119

Expert Comment

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

Author Comment

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

Accepted Solution

by:
Rey Obrero 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#]
0
 

Author Comment

by:ouestque
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. :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you 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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

943 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now