Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strange Delete Behavior (Access2K, Win2KPro)

    Tried searching first but couldn't find a good match...

    Although I got around this behaviour I'm curious about what is behind it: I have a db with a Form and Subform: the Form gathers basic Client information and one subform gathers Intake info and I have a button to go to the Discharge screen where the same Client Info form holds Discharge information. I want to be able to delete a given Discharge record but when I plop the Delete Record button from the control box onto the Subform, it does delete the Discharge record...and the other two as well! (Client Info and Intake Info). I checked for referential settings to see if Cascade Delete was ticked, etc. and may not have looked at the settings correctly as the options for setting referential integrety were grayed out (and not ticked). I went ahead and set up as SQL DELETE command instead to target the desired table and record, so I 'solved' the issue but don't understand what the problem was in the first place.

    Any ideas? BTW, I set up the form/subforms via the Subform Wizard and didn't do anything special to the source tables. Also, the subform for the Discharge info is based on a query rather than a table as I needed to install 2 calculation fields. The query itself is simply the Discharge table.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Strange Delete Behavior (Access2K, Win2KPro)

    <hr>I want to be able to delete a given Discharge record but when I plop the Delete Record button from the control box onto the Subform, it does delete the Discharge record...and the other two as well! (Client Info and Intake Info). <hr>
    I don't understand what you mean by "plopping the Delete Record button" onto the subform. I have a Command Button icon that invokes a wizard if I have "wizardry" turned on that will generate code to delete a record as one of the options. Are you by chance copying a button from the main form to the subform? Why not use the standard record-selector bar that all forms have as an option and allow the delete that way?
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Delete Behavior (Access2K, Win2KPro)

    By "plopping" I mean, 'using the Command Button icon and allowing the wizard to build the Delete Record code for the button' and doing this all on the subform. I don't want to use the drop-down option because the subform should only display data for a record selected in the Client Information main form & I don't want to confuse people by showing all the records in the Discharge table.

    Basically, you get the main Form/Subform: Client Information (with the record selecting drop-down) and the Intake subform. On the main form you have a button to go to the Discharge Information screen (which is Client Information main form/Discharge Info subform) and when you go to this one you can either create a record, edit an existing record or delete an existing record (that is, the Discharge Info related to the Client Info by an index).

    I happened to notice, in the post just below mine, that importing tables seems to effect how Referential Integrety is setup 'behind the scenes' and that might be the source of the odd behavior. I am indeed working with imported tables and Access seems to be acting as though Cascade Delete is in effect as deleting a record/ID on the Child form (Discharge Info) goes out and deletes the other records with the same Index number in the other two tables, which is not at all what I wanted, and, I guess, not what most people would expect...

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Strange Delete Behavior (Access2K, Win2KPro)

    I don't think this is a Referential Integrity issue, but i've been wrong once or twice before (today). If it were RI, you should see the Cascade Deletes going on, and they should link the three tables to the main table, and only kick in if you deleted a record in the main table.

    It seems to me the code that is generated by the wizard is probably the culprit. It would help if you can post the code behind the Click event for the command button. Also, are you using a continuous form for your subform, or does it display just one record at a time? There is presumably a linking field called ClientID in both the main table which displays Client Info, and the various tables used as data sources for the subforms. It's a little difficult to do, but a simple table description with the linking fields might help us to better understand your situation. Or you could try showing us the RI view as an attached image - but you will need to futz with it to get it smalled enough to be a reasonable attachment without loosing the basic resolution.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Delete Behavior (Access2K, Win2KPro)

    If it's the wizard code, I'd be slightly suprised as I have used the Delete Record code before without a problem, but I don't recall doing so on a subform (to delete the displayed record in the subform). Also, I haven't ever needed to set RI on tables so I might not have checked whether it was set correctly or not in the first place -- I fooled around with the Relationships window and nothing obvious showed in terms of Cascade Deletes being set. And I would *think* Cascade Deletes flow from a defined One to Many relationship from a main table to sub tables, not the other way around. But I try not to be naive about expecting logical behaviour from Access at times <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    As far as the code for the delete button, that's the canned docmd.menu item stuff that most people have probably already seen.

    Anyhow, I wrote my own code to accomplish the same thing just by polling a recordset for the ID and doing a DELETE * from the specific table, which not only does what I wanted but is easier for me to understand. I just didn't know if there was some know issue with placing a Delete Record button on a Subform and having records in other tables get deleted as well. Seeing as the need to delete a record on a subform is probably not unusual and the behaviour was, I was curious as to if anyone else had run across a similar situation. I may just have something weird going on in my db, but if I don't, this strikes me as an important 'gotcha' to watch out for... <img src=/S/duck.gif border=0 alt=duck width=23 height=23>

    If you want additional information, I'll be pleased to provide tomorrow as the db project is being done at home.

    sps

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Strange Delete Behavior (Access2K, Win2KPro)

    Hmm - I don't know of any issues with the delete code generated by the wizard for subforms, but we don't normally do that. We do it by using the record selector bar on the form, or we don't actually allow a delete and we use a boolean field to show that a record is deleted, and requery the subform.

    Maybe someone else can add more in this area - Hans? Charlotte? Mark L or the other Marks?
    Wendell

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Strange Delete Behavior (Access2K, Win2KPro)

    If you place a button on the subform to delete the subform record, the only thing that should be deleted is that current record and any child records to it where Cascading Deletes is set to true. It simply *can't* delete parent records unless you code is pointing at the record in the parent form, not the subform. Unless you post the code, it's impossible to tell why it isn't working. I delete subform records all the time without problems.
    Charlotte

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Delete Behavior (Access2K, Win2KPro)

    PROBLEM SOLVED

    Turns out I had, in fact, copied a Delete Record button recorded on the main form to the subform. I didn't notice it at first as it was pasted down at the lower right of the subform -- and the subform is fairly long. I guess the first Delete Record button 'overrode' the second one and I had the unexpected behavior of the main Costomer Info record deleted along with the info for Intake and Discharge on clicking the "new" Delete Record button on the subform. I deleted the copied button and now all works as expected.

    I know it sounds awful to copy controls from one form to another (esp, from a form to a subform) but in this case I wasn't expecting a problem as the wizard for Delete Record is so generic:

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    no matter where the button actually lives -- so I, in the tradition of being lazy, just thought it would be fine to cut and paste the button from the main form to the subform. Oh well! Live and learn...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •