1 button to delete record from 3 tables

[andrewf10]

Hi

I have a database with a setup kind of like this.

Form1 for Table1, Form 2 for Table2 and Form 3 for Table3. There are no relationships set.

All 3 tables have an ID field, which 90% of the time, will have the same ID number on them.

What I'm trying to do is set up a button on Form1 which when clicked, will delete the current record on this form, then go to Tables 2 and 3 and delete the same record from them.

Can someone please advise me on the best way of doing this? Below is the code I've managed to get working...with 1 flaw.

If for whatever reason the current ID is not found on Tables 2 or 3, it goes and deletes the first record. I'd prefer the code to move onto the next table without deleting anything if it didnt find a matching ID.

Any help or advice would be hugely appreciated as I've used up my little bit of VBA knowhow getting this far!

Many thanks
Andrew




Private Sub DELETE_Click()
On Error GoTo Err_DELETE_Click

Dim resp As String
Dim currentrecord As String
Dim currentdescription As String

'Capture the current ID and Description for the message box'

currentrecord = Me.ID
currentdescription = Me.Description

resp = MsgBox("Are you sure you want to delete " & currentrecord & " - " & currentdescription & "?", vbYesNo, "Confirm deletion")

'If user confirms deletion, Table2 is opened on the current ID number and the deletion code is executed'

If resp = vbYes Then

DoCmd.OpenTable "Table2"
DoCmd.FindRecord Forms!Form1!ID

DoCmd.SetWarnings False 'System warning message is turned off
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 'Select record'
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 'Delete the selected record'
DoCmd.Close

'Table3 is opened on the current ID number and the deletion code is executed'

DoCmd.OpenTable "Table3"
DoCmd.FindRecord Forms!Form1!ID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 'Select record'
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 'Delete the selected record'
DoCmd.Close

'Form1, which has been open all the time, has its current ID number deleted'

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 'Select record'
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 'Delete the selected record'

'If the user cancels the deletion, the code is exited'

ElseIf resp = vbNo Then
Exit Sub
End If

Exit_DELETE_Click:
Exit Sub

Err_DELETE_Click:
MsgBox Err.Description
Resume Exit_DELETE_Click

End Sub

 

[dcx693]

The problem with the DoCmd.FindRecord method is that, it only moves the cursor to a record matching your search criteria if it finds that record. Otherwise, it just leaves the cursor at the first record. That's why it deletes the first record on the table if it doesn't find an ID match. It's just the wrong technique to use here.

Don't despair though, since there's an easy technique to use that will do what you want. I assume you're using Access 97, but let me know if you've got a different version. I've posted some code below that you can place behind a command button. I assume the button is called cmdDelete:

Private Sub cmdDelete_Click()
Dim rst As DAO.Recordset
Dim strSQL As String
 '- This assumes ID is a numeric field
 strSQL = "SELECT ID FROM Table2 WHERE ID=" & Forms!Form1!ID
 '- This takes only those records who have an ID equal
 '- to the one one Form1
 Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
 With rst
  '- Check if you're at the beginning of the recordset, and
  '- also at the end. This will only be true if no records
  '- were found.
  If Not (.BOF And .EOF) Then
  '- Place the recordset in edit mode
  .Edit
  '- Delete the current record in the recordset
  .Delete
  '- Update the recordset
  .Update
  End If
  .Close
 End With

 Set rst = Nothing
End Sub


If no matching records are found, the recordset will be empty, and the .EOF and .BOF conditions will be true, and nothing will happen. This code works for Table2, but you can adapt it to work for Tables3 and 1 with just a few edits.

 

[bjackson]

why not just use a delete query with criteria


DoCmd.Runsql("DELETE Table2.Id
FROM Table2
WHERE (((table2.Id)=" & me.id & "));")

 

[andrewf10]

Huge thanks to you both for your swift replies. I'm going to start testing both methods straightaway.

 

[saross]

Access 2k

dcx693: "I assume you're using Access 97, but let me know if you've got a different version. "

Your code has been really useful, only I use Access 2k so what is the difference? I've tried to run it and it queries the use of .update...