Full Version: Problem Deleting Record In Form
UtterAccess Forums > Microsoft® Access > Access Forms
SydneyGeek
I've struck a problem with deleting records in a form.
It will probably help if I describe the layout a bit first:
Split database, accdb format. All users have their own front end, connected to a common mdb back end.
Main Booking form opens, browsing all records and by default goes to last record.
Booking form has two subforms, and several calculated fields that call those subforms to display summary information. Standard table fields as well.
So far, so good... except possibly for the 'browse all records' bit.
In the back end, the main table is related to both of the tables that drive the subforms. Referential integrity enforced, cascading updates and deletes.
In the front end I don't get the same picture. Cascading updates and deletes are not selected, or editable.
Deleting records just doesn't work. The button's Click event (without the error handing code) looks like this:
CODE
    Dim lngInvNo As Long
    Dim strSQL As String, _
        strSQL1 As String, _
        strSQL2 As String
    
    If Me.NewRecord Then
        Me.Undo
    Else
        lngInvNo = Me.Invoice_No
        strSQL = "DELETE * FROM Invoices WHERE [Invoice No]=" & lngInvNo
        strSQL1 = "DELETE * FROM [Student Bookings] WHERE [Invoice No]=" & lngInvNo
        strSQL2 = "DELETE * FROM [Voucher Purchases] WHERE VInvNo=" & lngInvNo
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.RunSQL strSQL1
        DoCmd.RunSQL strSQL2
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    End If

The record apparently disappears from the form, but is not deleted (which is why I added the three SQL statements).
Turning off the warnings shows that the code proceeds OK until the final DELETE (which removes the record behind the main form).
Then I get the message:
Microsoft Access can't delete 1 record(s) in the delete query due to key violations and 0 records(s) due to lock violations.
What is going on here?
Denis
projecttoday
Didn't you already delete the record with DoCmd.RunCommand acCmdDeleteRecord?
SydneyGeek
That's what surprised me -- no.
've solved the problem by tracing at table level. Turns out that the main table got corrupted and was rebuilt, with the original archived using an Old suffix.
The relationships to the old table still remained, and were causing the lock problems. I have now deleted the old relationships.
The removal works now, but I still need to run the SQL calls so the acCmdDeleteRecord still isn't behaving as I would expect.
Denis
projecttoday
Maybe it's because you're creating orphan records. Try putting the acCmdDeleteRecord last and see what happens.
SydneyGeek
I shouldn't be creating orphans if the cascading deletes are operating. The database has been split for a long time and the front end has been redeveloped since. Is that why the relationships are out of sync?
If so, is there a way to get the front end to recognise the back end relationships correctly?
The potential orphan issue is why I decided to delete the child records first. When that still didn't work, I tried to delete directly from the table and that's where I got the clue about the old table.
The delete now works, in the order shown originally, but I'm going to play with a few more options to see if I can understand what happened.
By the way, I am not getting notifications when I get an answer to my posts. Where in my profile do I set that option?
Denis
projecttoday
You should also be aware that each query doesn't necessarily complete execution before the next one starts.
Yes I think there is an option for automatic replies.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.