Delete records in other tables

[Guest]

I wanted to set up a click command to delete a record that is comprised of 3
different tables.
The click command would be on form1 which is linked to table1
subforms within form1 are sbfrm2 which is linked to table2 and sbfrm3 linked
to table3. The three tables are related by three fields which would make a
unique record.
On form1 and sbfrm2 & 3 the user would create the id on each using three
comboboxes to choose the company, year and quarter. Those three attributes
will always be visible in the combobxs while the record is open to filter a
query on.
I need to be able to delete the related record in all tables when the user
decides to delete the current record.
I'm having trouble figuring out how to do this.
Can anyone help?

 

[Guest]

You need to turn on cascading deletes in your relationship definitions

 

[Guest]

I don't follow entirely, but you could create three separate delete queries,
each of which picks its criteria from the appropriate
[Forms]![OneOfYourForms]![AControlOnThatForm] (each would refer to a
different form/control that contains the primary key of the record to be
deleted) so that it deletes the correct record from the correct table. In the
Click event, the call for the queries would look like this:

Private Button1_Click()
DoCmd.SetWarnings False 'hides confirmation messages from user
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"
DoCmd.OpenQuery "Query3"
DoCmd.SetWarnings True
End Sub

If these are linked tables, you could simply turn on CascadeDelete for the
relationship, and then when you delete the "one" entry, the "many" are
automatically deleted.

 

[Guest]

that'll work! thanks brian!

I don't follow entirely, but you could create three separate delete queries,
each of which picks its criteria from the appropriate
[Forms]![OneOfYourForms]![AControlOnThatForm] (each would refer to a
different form/control that contains the primary key of the record to be
deleted) so that it deletes the correct record from the correct table. In the
Click event, the call for the queries would look like this:

Private Button1_Click()
DoCmd.SetWarnings False 'hides confirmation messages from user
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"
DoCmd.OpenQuery "Query3"
DoCmd.SetWarnings True
End Sub

If these are linked tables, you could simply turn on CascadeDelete for the
relationship, and then when you delete the "one" entry, the "many" are
automatically deleted.

I wanted to set up a click command to delete a record that is comprised of 3
different tables.
The click command would be on form1 which is linked to table1
subforms within form1 are sbfrm2 which is linked to table2 and sbfrm3 linked
to table3. The three tables are related by three fields which would make a
unique record.
On form1 and sbfrm2 & 3 the user would create the id on each using three
comboboxes to choose the company, year and quarter. Those three attributes
will always be visible in the combobxs while the record is open to filter a
query on.
I need to be able to delete the related record in all tables when the user
decides to delete the current record.
I'm having trouble figuring out how to do this.
Can anyone help?

 

[Tim Ferguson]

I wanted to set up a click command to delete a record that is
comprised of 3 different tables.

I am with Brian on this: cascading deletes can result in a massive loss
of data with no road back. In fact, what I generally do in this situation
is along the lines of:

' this is going to be used a lot
sqlWhere = "WHERE RecordID = " & format(me!recordID,"0")

' get an estimate of the likely damage
wRecordsAffected = DCount("*", "TableOne", sqlWhere) + _
DCount("*", "TableTwo", sqlWhere) + _
DCount("*", "TableThree", sqlWhere)

' give the user a chance to withdraw
If vbNo = MsgBox("You are about to delete " & wRecordsAffected & _
" lines of data. Are you sure?", vbYesNo, "Last Warning) Then
Exit Sub

End if

' change the question and make really sure
If vbYes = MsgBox("You are about to delete " & wRecordsAffected & _
" lines of data. Back out now?", vbYesNo, "Very Last Warning) Then
Exit Sub

End If

' okay then, can't say we didn't try
' execute is a much safer method than all that setwarnings stuff
db.Execute "DELETE TableOne " & sqlWhere, dbFailOnError
db.Execute "DELETE TableTwo " & sqlWhere, dbFailOnError
db.Execute "DELETE TableThree " & sqlWhere, dbFailOnError



Hope that helps


Tim F

 

[Guest]

Tim,
Thanks brah!
I'm using a combination of both actually.
I really appreciate your help!

Tim Teska