Recursive subforms not cooperating with DELETE/REQUERY...

[matthewslyman]

I have an unusual design and some very unusual issues with my code... I have forced Access to cooperate on everything except one issue - record deletion.

My form design involves a recursively nested form. In other words, the form, m_settings_menueditor_recursive has a single subform; m_settings_menueditor_recursive (both are viewed as datasheets - so the form is its own subdatasheet.) The Form_Open event modifies the form's recordset so that the root form only displays root nodes in the tree (Null parent), and the parent/child relationship on the subform is set so that nested forms only display the child nodes of their parent. This implements a tree structure, fully editable as a Microsoft Access datasheet, with open/close tree branch buttons in the form of open/close subdatasheet. In one table, "ui_modes_lkp", the menu elements (and submenu elements, and subsubmenu elements etc.) are specified (relationships are defined using the parent_mode field.

A sample of VBA code inside m_settings_menueditor_recursive:

Private Function AtRootLevel() As Boolean
    Select Case Me.Parent.Form.NAME
        Case "m_settings_menueditor":
            AtRootLevel = True
        Case "m_settings_menueditor_recursive":
            AtRootLevel = False
    End Select
End Function
 
Private Sub Form_Open(Cancel As Integer)
    pending_delete = False
    If AtRootLevel Then
        Me.RecordSource = "SELECT * FROM ui_modes_lkp WHERE parent_mode Is Null ORDER BY display_order;"
    Else
        Me.RecordSource = "SELECT * FROM ui_modes_lkp ORDER BY display_order;"
    End If
    Me.refresh_display_orders ' Make display orders increase from zero in steps of one.
    Me.Requery
    SetDefaults ' Set default values for fields, including display order
End Sub

There are database relationships (enforcing various kinds of referential integrity between the ui_modes_lkp table and itself); but I've tried removing these and it doesn't help. I also tried breaking the form/subform relationship and implementing that manually, but that led to more problems than it was worth.

In (fully updated) Access 2003/2007 the standard MS Access DELETE events just don't seem to work normally in my form. When I delete a record from the root form, it works fine and deletes the record, although it seems to only invoke Form_Delete and not BeforeDelConfirm or AfterDelConfirm (the delete operation succeeds even though the ...DelConfirm events are not invoked.)
HOWEVER - when I delete records from child subforms, they APPEAR to have been deleted (they disappear from the form) but I see a funny symbol in the record selector of the parent forms, all the way back to the root node... It's like a "no entry" road-sign but in black instead of red (a circle with a diagonal line through it - I have no clue what this means.). When I close the forms completely and reopen them, I see that the deleted child-mode record is back there again as though I never deleted it. Even when I disable all my VBA code, and there are no events listed on the form's Properties window, it doesn't work. It seems like only the Form_Delete event is invoked, and it seems something deep within the workings of MS Access interferes with the record deletion so that the other record deletion events are never invoked.

So to work around this problem, I tried manually implementing the DELETE function with a SQL query when the FORM_DELETE event is invoked, and cancel the event using

DoCmd.RunSQL "DELETE FROM ... WHERE ..."
DoCmd.CancelEvent

This leaves a row in the subdatasheet with "Deleted" in place of all the fields. I have attempted to shift the focus away and run DoCmd.Requery, and various VBA based Requery methods (all from within the relevant subform), but it just won't get rid of the "Deleted" record from the datasheet displayed... The VBA-based methods just give error messages saying "The record has been deleted."
This technique currently looks messy but at least it actually deletes the record...

Some of these methods even lead to problems when opening other branches of the tree, with the tree complaining that I am trying to do something I cannot do while in the middle of a transaction or something like that... Are these "Transactions" something to do with the Delete events that keep going amiss?

I know my design is a little weird in some respects but I'd be VERY grateful for anyone who can help me with this. If you can help me fix it before I do, I might be amenable to sharing my menu system with you (providing you don't share it around to other folks without my permission.)

[puppydogbuddy]

It seems to me that your delete code was deleting only one side of an enforced one o many relationship. You ether need to turn on cascade delete for that relationship or simulate a cascade in your code by deleting the related records in the primary and foreign key tables.

[matthewslyman]

Thank you for your suggestion... Actually I already have "Cascade Delete" switched on, along with all the other Cascade and Referential Integrity options, for that relationship... I have tried without and tried deleting the relationship altogether (relying only on the Form/Subform key relationship as defined in the Form properties box) but to no avail.
It's not the cascade delete that's the problem - since I'm deleting something on the "Many" side of the one-to-many relationship. That should be allowed by referential integrity under any circumstances, right?

[puppydogbuddy]

In order for the cascade delete to work, you must have referential integrity enforced between all tables involved. In absence of a referential integrity enforced cascade delete, you would delete records on the many side first and then delete the records on the "one" side. See the link below for details.

http://www.databasedev.co.uk/delete_query.html

[matthewslyman]

Only one database table is involved - "ui_modes_lkp". I have added it twice to the relationships diagram, with a relationship between "mode_id" on one and "parent_mode" on the other. I understand that if there is referential integrity WITHOUT cascade deletion, you must first delete the records on the "many" side and then the ones you want to delete on the "one" side (I think this is what you are saying.) I've tried various combinations of options on the relationship and none of them improve matters - so I suspect it's something to do with the referential integrity implemented in the relationship between FORM and SUBFORM... It might be a quirk of MS' implementation of forms.

I have no problem hand-coding DELETE queries, or indeed, constructing them in the Access worksheet/design view.

I've simplified the design now, removing almost all of my event-invoked code, and still I'm getting these errors... I thought the form might be corrupted, so I created a new blank one and copied the controls and code into it - which makes no difference at all...

Still stuck, and beginning to think this might be a problem deep within Access... I might have to resort to using VBA code to close the form and re-open it (with all subdatasheets closed as a result.)

[matthewslyman]

Naturally, before adding the table twice in the relationships diagram, I amended the indexing/ keys as appropriate. "mode_id" is primary key, "parent_mode" is INDEXED, with duplicates being acceptable. The relationships diagram shows a one-to-many relationship as it should.

[puppydogbuddy]

Ok, what I would do from a debugging point of view is to change my delete query back to a select query and verify that the correct records (both the primary and cascaded records) appear in the query. If the query is missing records, tweak the select query until the correct records show up, then go to the toolbar and change the query type back to a delete query.

Let me know what happened.

[matthewslyman]

I've had the DELETE query running in a DoCmd.RunSQL statement for some time now... There's nothing wrong with it. Except that it puts big fat "Deleted" signs in all the fields, and leaves the deleted record in place (with junk displayed), and messes up the indexing somehow, and prevents Requery from working. The query is technically perfect, apart from that.

I have this code (and some other stuff) inside the Form_Delete event subroutine:

    DoCmd.CancelEvent
    DoCmd.SetWarnings WarningsOff
    DoCmd.RunSQL "DELETE FROM ui_modes_lkp WHERE mode_id=" & Chr$(34) & Me.mode_id_Box.value & Chr$(34) ' A hack to make this silly thing work...  It seems like something is interrupting/ circumventing all the other delete Events
    DoCmd.SetWarnings WarningsOn

I'm working around the problem now - I've decided to use VBA code to close the form and re-open it (by changing the SourceObject of the parent form's subform containing m_settings_menueditor.) This "solution" is imperfect because it closes down all the open subdatasheets. But it works, and doesn't seem to leave the indexes in a twist.

[NeoPa]

I'm not sure if this is going to help, but when you describe the record as being deleted in the subform, but marked with No Entry at parent level, I understand this to mean that the records are locked. I don't understand why the subform doesn't realise the delete has failed, but with the same record being in use in multiple forms, I cannot see an easy way to delete (or otherwise amend) any records.

Maybe exploring in the Recordset Type of the form may yield some results.

Welcome to Bytes!

[puppydogbuddy]

what I was suggesting was to create a select query using the Access query grid, run it and verify that the correct records to be deleted show up, then change the query type to delete.

[matthewslyman]

Thank you, Puppydogbuddy! (Oh my, that's a tongue-twister.) I know what you were suggesting. My delete query actually deletes the correct records, nothing more, nothing less.

NeoPa, I think you're on the right track here... Without the manual DELETE query, when I try to delete PARENT records without first deleting children, despite having cascade-delete selected, I get messages saying:
"Could not update. Currently locked by another session on this machine."


One would think - even with referential integrity - one could delete LEAF CHILD records without difficulty... But apparently not... As described, I get the no-entry symbol (which as you say probably indicates record locking problems that the subform doesn't recognize before changing the display.) And then I start getting the following message when I try to do anything:
"Operation not supported in transactions."

I tried altering the Recordset Type field in the form design/ parameters from "Dynaset" to "Dynaset (inconsistent updates). The Record Locks setting was originally set to "No Locks", I'm now experimenting with changing this to "Edited Record."

While each subdatasheet looks at the same overall recordset, because of the way the recursion/ filtering works, no record appears in more than one subdatasheet (or, in more than one location in the tree.) I thought that would be enough to avoid this problem, but apparently not...

[NeoPa]

To be honest I struggle to comprehend your setup fully. The other side of a web page can be a bit difficult to comprehend things quite as involved as this.

I'm particularly struggling with the idea that you see the No Entry sign in higher level forms, yet, at the same time, these records are only ever shown at a single level.

As the first thing to consider is nearly always the fundamental design, this could make further help, shall we say, complicated. We'll do what we can of course.

[matthewslyman]

NeoPa, so long as you understand the principles of:

• Recursion/ self-join
• Subforms
• The other stuff (e.g. VBA, record locking issues...) that you obviously do understand,

you should be able to understand my code and database relationships. As for the no-entry sign, have you looked at the graphic near the top of this page?

If you will honour my copyright to the menu system, I'll send you a copy so you can see what's going on. And if you can fix this issue in a tidy manner, I'll give you a free license to my menu system.

[matthewslyman]

If you think you can do it, send me your email address... I think there's some way to email people through Bytes... I can see an Inbox link on my profile. Let me know if you can't do that and I'll post my address.

[NeoPa]

It's a kind offer Matthew, but I can't imagine I'd want to replace my own menu system at this stage. I can however, promise discretion. I never feel it appropriate to share others' work unless it's clear they are happy to (furthermore I have no-one who might be interested in Access databases - no matter how much I try to enthuse them. For myself, I have more than I know what to do with).

At this stage I cannot promise a lot of time for this. I am quite active across the site and this takes up what free time I have available (and more sometimes). Having said that, if you can produce a copy of the database with as little as possible in it other than required to illustrate the problem (and compact it and Zip it up), I can PM you my address (on the understanding that it's not to be shared) and I will have a quick look through it for anything that I can find that might be causing the problem. Site emailing is limited and doesn't support sending files.

At this stage, my gut feeling is that it won't be easily found, but it may well be something obvious that I simply haven't seen due to the difficulties of understanding something remotely. Let me know if this sounds acceptable to you and I will PM you my email address.

[matthewslyman]

I've created the stripped-down version, and since you don't want the menu system I've taken that out. So no problems... If you can fix it, you are welcome to document your work (and profit from that documentation) however you see fit, on the RECURSION/ SELF-JOIN side of things anyway...

Re: No customers interested in Access databases... I'm not surprised. I avoid Access data-stores like the plague, especially after the number of times they've crashed in-situ (beware of "compact & repair.") I've developed this menu system for:

• System development purposes (rapid application development/ system design modification)
• LAN-based user-interface
• Prototype for future user-interfaces

Go ahead and PM me your address and I'll send it to you. It's 142KB zipped, so that shouldn't bust your mailbox!

[NeoPa]

I've PMed the address. Feel free to send it on when you're ready.

I'll look at it over the weekend and see if there's anything I can see. You've probably done a lot of what I'll do anyway (you sound as if you've been around the block a few times), but it can't help to put some fresh eyes to it and see what turns up.

[NeoPa]

I've received the email, but it's in 2007 format (I presume in code fully available mode).

I should say I only work up as far as Access 2003 as I feel Access 2007 is ... well, let's just say I'm no fan.

If you can get me a 2003 (or earlier) MDB file, I'll look at it for you.

[NeoPa]

Matthew,

Please use this thread for ordinary correspondence. See previous post (#18) for why you've not heard more from me on the technical issue.

[matthewslyman]

Thought I sent you a .mdb file version... About two mins after you asked for it... It must have gone astray - I'll send it again.

[matthewslyman]

... And here's me thinking you'd given up!
;-)

[NeoPa]

Still no promises Matthew, but I'll have a look when I'm home and see what I notice.

[NeoPa]

OK Matthew, I have the A2003 version. What do I need to do to see the behaviour you're seeing?

PS It's admirably curt. Nothing extraneous as far as I can see. I hope I will be able to help.

[matthewslyman]

I presume you can open the .mdb file (let me know if not.) Just open the .mdb file. Look in the default form. Try selecting a record in a subdatasheet and deleting it, or try selecting multiple records and deleting them. You should start to see anomalies.

[NeoPa]

I can open the file (thank you).

When I do it though I don't notice any anomalies :S Everything works as I imagine you intended it to. Records, or even groups of records, delete ok. You did check that this version shows the same symptoms (I'm sure you did but I must ask)?

The only other thing I noticed was that the records all showed as in record layout, rather than in the design of the sub-form as I expected. I don't know where this takes you, but I hope it's some help.

[matthewslyman]

OK. The big anomaly I was talking about was... You try deleting records. It appears to succeed. But then you close the form and reopen it again. And the records are there again. Delete doesn't work on subdatasheet records in Access 2007, on this .mdb or .accdb... I didn't try Access 2003 on this yet.

-->>> Have you tried closing the form and reopening it, and double-checking that the records you deleted do not mysteriously re-appear as I described?

Do you not see the "No Entry" lookalike sign?

[matthewslyman]

I presume you've opened the subdatasheets up... Which is the point... As mentioned, it works fine on mine for the main records, but not for the ones in the subdatasheets. Be sure to click on the "plus" signs on the left and try deleting the records in leaf nodes of the tree... Sorry about the stupid instructions if this is obvious to you.

[NeoPa]

I closed the whole application and they were still gone when I returned.

I haven't seen any of the no-entry signs. I don't think this is a problem with your code or design. More likely an Access configuration issue (possibly).

[NeoPa]

@matthewslyman
Yes. This was clear from your earlier instructins, which I followed quite carefully.

No need for apologies. Everything needs to be checked and double-checked when anomalies occur.

[matthewslyman]

:-O

I just tested the .mdb in Access 2003 on my Windows XP virtual-PC... I was quite shocked to find that it worked! Who would have guessed that? MS Access 2007 breaks edits on subdatasheets, inexplicably... No wonder you don't like it... As ever, woe betide MS' early adopters...

"The only other thing I noticed was that the records all showed as in record layout, rather than in the design of the sub-form as I expected." - I don't understand this remark...

[matthewslyman]

...Sifting through Access configuration options...

[NeoPa]

@matthewslyman
Looking at the design of [m_settings_menueditor_recursive], it doesn't show in record format, whereas when viewed within [m_settings_menueditor], it does.

I would describe record format as fields displaying sequentially across horizontally, rather than as specified in the design of the form.