I've struck a problem with deleting records in a form.
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.
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).
Microsoft Access can't delete 1 record(s) in the delete query due to key violations and 0 records(s) due to lock violations.
Didn't you already delete the record with DoCmd.RunCommand acCmdDeleteRecord?
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.
Maybe it's because you're creating orphan records. Try putting the acCmdDeleteRecord last and see what happens.
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?
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.