mdstalla
asked on
Access VBA for Delete Record on multiple Tables
I have a button on an Access Form. What I would like to do is, upon clicking this button, the current record gets deleted. The issue I am having is that this record exists in multiple Tables. For example, each of my records is given a unique identifier 'InterviewID.' I have 4 records: 100, 101, 102 and 103. Currently, I am working on record 102—and I'd like to delete this record within all Tables. For example:
Table1
Primary Key: InterviewID [102]
Table2
Primary Key: InterviewID [102]
Table3
Primary Key: InterviewID [102]
I've been using the following code—but it only seems to delete the record for Table1 (InterviewID still exists in Table2 and Tabl3).
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
End If
Does anyone know how to modify this code so that it deletes the current record in all tables?
Table1
Primary Key: InterviewID [102]
Table2
Primary Key: InterviewID [102]
Table3
Primary Key: InterviewID [102]
I've been using the following code—but it only seems to delete the record for Table1 (InterviewID still exists in Table2 and Tabl3).
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
End If
Does anyone know how to modify this code so that it deletes the current record in all tables?
Setting up relations for tables as gustav mentioned is one approach. Just be careful with that; you can end up deleting a whole lot of data you don't mean to very quickly as that will work not only through a form but also through query and table views.
The other method is to issue an actual SQL delete statement (either via query or directly - DELETE * FROM <table> WHERE <condition>) on each table that you want to delete from. There are cases where you may want to delete from some, but not all that you may have setup relation ships with depending on the circumstances.
Some view the second as somewhat of a "protection" because you need to go through the form to get rid of the data. Relations are really the right way to go, but more often than not, it leads to people deleting a lot of things they don't intend to.
Jim.
The other method is to issue an actual SQL delete statement (either via query or directly - DELETE * FROM <table> WHERE <condition>) on each table that you want to delete from. There are cases where you may want to delete from some, but not all that you may have setup relation ships with depending on the circumstances.
Some view the second as somewhat of a "protection" because you need to go through the form to get rid of the data. Relations are really the right way to go, but more often than not, it leads to people deleting a lot of things they don't intend to.
Jim.
revising your codes, without setting relationships with your tables
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
'DoCmd.RunCommand acCmdDeleteRecord
CurrentDb.Execute "delete * from table1 where interviewid=" & Me.txtInterviewID
CurrentDb.Execute "delete * from table2 where interviewid=" & Me.txtInterviewID
CurrentDb.Execute "delete * from table3 where interviewid=" & Me.txtInterviewID
End If
End If
just change "txtInterviewID" with the actual name of the control for InterviewID
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
'DoCmd.RunCommand acCmdDeleteRecord
CurrentDb.Execute "delete * from table1 where interviewid=" & Me.txtInterviewID
CurrentDb.Execute "delete * from table2 where interviewid=" & Me.txtInterviewID
CurrentDb.Execute "delete * from table3 where interviewid=" & Me.txtInterviewID
End If
End If
just change "txtInterviewID" with the actual name of the control for InterviewID
That will make the record appear as #Deleted on the form. Not very nice.
Whatever, the issue smells:
> The issue I am having is that this record exists in multiple Tables.
It sounds like you should study some basic material on relational databases and normalisation. Just bing/google on the topic.
/gustav
Whatever, the issue smells:
> The issue I am having is that this record exists in multiple Tables.
It sounds like you should study some basic material on relational databases and normalisation. Just bing/google on the topic.
/gustav
not if you do a requery after deletion
Then you waste time during the requery, and you loose the current record and has to relocate the recordset after the requery.
/gustav
/gustav
ASKER
I used Rey's code but got an error:
Runtime Error 3075
Syntax Error (Missing Operator) in query expression InterviewID=".
Any clue what the hold-up could be?
If I click 'End' the record appears to delete as desired.
Runtime Error 3075
Syntax Error (Missing Operator) in query expression InterviewID=".
Any clue what the hold-up could be?
If I click 'End' the record appears to delete as desired.
post the actual codes that you used
is the field InterviewID Text or Number data type ?
ASKER
Rey:
Here's the actual code I used. InterviewID is a Number for each Table. You should note that this error only appears to be related with the last table "TblAgentNos"
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
'DoCmd.RunCommand acCmdDeleteRecord
CurrentDb.Execute "delete * from table1 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table2 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table3 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table4 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table5 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table6 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table7 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table8 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table9 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from tblInterviewMain where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from tblAgentNos where interviewid=" & Me.InterviewID
End If
End If
Here's the actual code I used. InterviewID is a Number for each Table. You should note that this error only appears to be related with the last table "TblAgentNos"
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
'DoCmd.RunCommand acCmdDeleteRecord
CurrentDb.Execute "delete * from table1 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table2 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table3 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table4 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table5 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table6 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table7 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table8 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from table9 where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from tblInterviewMain where interviewid=" & Me.InterviewID
CurrentDb.Execute "delete * from tblAgentNos where interviewid=" & Me.InterviewID
End If
End If
Then in your table tblAgentNos you have no field named interviewid.
But having the same info in 11 tables is very weird. It needs a redesign.
/gustav
But having the same info in 11 tables is very weird. It needs a redesign.
/gustav
run a compact and repair then
check your table tblAgentNos by running this query
select * from blAgentNos where interviewid= 102
change 102 with the interviewID that you deleted from the other tables.
post back the result
check your table tblAgentNos by running this query
select * from blAgentNos where interviewid= 102
change 102 with the interviewID that you deleted from the other tables.
post back the result
ASKER
Here's what my code looks like based on your instructions (let me know if I did this wrong):
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
'DoCmd.RunCommand acCmdDeleteRecord
CurrentDb.Execute " select * from TblAgentNos where interviewid= 28"
End If
End If
I'm getting an error message that says:
Run Time Error 3065
Cannot execute a select query
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
'DoCmd.RunCommand acCmdDeleteRecord
CurrentDb.Execute " select * from TblAgentNos where interviewid= 28"
End If
End If
I'm getting an error message that says:
Run Time Error 3065
Cannot execute a select query
what I meant is you create a new query in design view using TblAgentNos
Field * | interviewid
Table TblAgentNos
Criteria 28
Field * | interviewid
Table TblAgentNos
Criteria 28
ASKER
Oh, Okay-- sorry.
I did what you said using the Criteria 34. I've attached the Query with its returns:
accessspreadsheet.xlsx
I did what you said using the Criteria 34. I've attached the Query with its returns:
accessspreadsheet.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You're the Man Rey! That worked perfectly--thanks for all of your help!
yeah that did it, but you have to rethink the design of your table..
see this link Data Normalization
see this link Data Normalization
ASKER
Really? Is my design technique really that messed up? I ask that question inquisitively… as a Rookie Database Designer, not as a cocky-bastard.
It would seem to me that if I have X-number of Tables, each of which contains Fields with information that needed to be associated with the same, appropriate, respective Record… there would have to be some 'common denominator….' a common Field in which each of these Tables could be related/connected.
For each Table, the first Field I created (The Unique Identifier--The Key) was "Interview ID." By creating this… I was able to create Relationships… saying "Interview ID in Table1 is the same as Interview ID in Table2.
Why is this erroneous?
What would be a more prudent way of linking Tables?
It would seem to me that if I have X-number of Tables, each of which contains Fields with information that needed to be associated with the same, appropriate, respective Record… there would have to be some 'common denominator….' a common Field in which each of these Tables could be related/connected.
For each Table, the first Field I created (The Unique Identifier--The Key) was "Interview ID." By creating this… I was able to create Relationships… saying "Interview ID in Table1 is the same as Interview ID in Table2.
Why is this erroneous?
What would be a more prudent way of linking Tables?
There may not be. We can't tell without knowing the schema.
Again, if relations are properly set up, this would be a much easier and faster method:
https://www.experts-exchange.com/questions/28575598/Access-VBA-for-Delete-Record-on-multiple-Tables.html?anchorAnswerId=40484655#a40484655
/gustav
Again, if relations are properly set up, this would be a much easier and faster method:
https://www.experts-exchange.com/questions/28575598/Access-VBA-for-Delete-Record-on-multiple-Tables.html?anchorAnswerId=40484655#a40484655
/gustav
Set up relations between table1 - table2 and table1 - table2. For each relation set referential integrity to Delete.
DO make a backup first.
/gustav