Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Unanswered: Deleting record in Form does not delete record from underlying table

    Oooooo, I can't figure this out.

    Here's the basics:

    Access 2000 database, being worked on in Access 2002.

    I have a Main Form (frmClientDataEntry) which contains a tab control.

    The record source for the Main Form is a SQL statement left joining a primary table (tblClient) with a child table (tlbProgramData) on the appropriate key fields.

    On two of the pages of the tab control are subforms with simple query recordsources left joining tblClient to tblPhone and to tblEmployment respectively.

    In the relationships window all relationships are set to cascade update/delete.

    BUT.

    If I delete a client record using the Main Form's select/delete ribbon, the record and its child records in the subforms *appear* to get deleted (and I get a warning that I am about to delete the record)...yet it is not: when I re-open the form, there it is. And when I open the underlying tables, the parent and child records are still there.

    If I *update* an exisiting client record, or add a *new* client record using the form, all changes/additions to parent and child take affect and are not undone by closing/reopening the form.

    If I delete a *child* record on one of tab control's Phone or Employment subforms, that takes affect and is not undone by closing/reopening the form.

    It is only in attempting to delete a parent record using the form that I am encountering this problem.

    Any theories out there?

    There was a similar post relating to access 2007 and deletions from a subform not taking affect in this same way...but I've checked many of the settings suggested by users responding to that post and everything seems to be set correctly.

    Thanks for any advice....

    Regards,
    Shelley

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why is the frmClientDataEntry form's record source linked to a child table? The child records should be displayed in your subform(s), so there should be no reason to link it. If you find you don't need that link, remove the child table from the underlying query and try it again.

    Strange that you get no error message about the deletion not working though :/
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2009
    Posts
    5
    Hello, StarTrekker, and thank you for responding.

    Actually, now that I think about it, the relationship between tblClient and tblProgramData is 1:1---they are split into two tables for neatness sake because Program Data has a lot of fields, but there is only one set of these fields per client. So I guess it's not a parent/child relationship, but rather a co-parent relationship.

    So maybe the way I've joined them in the form is the problem. I'll try a couple different things and post back.

    Thanks!

    --Shelley

  4. #4
    Join Date
    Jan 2009
    Posts
    5
    Hello again...

    So, I realized that in the relationships window the relationship between tblClient and tblProgramData was set as 1:8 instead of 1:1, because I'd set the keys incorrectly. I fixed that and re-established the relationship as 1:1, then re-set my form data source to be just tblClient.

    What happened then is that if I was only able to enter some client data and not get to any of the program data fields for that client, then the whole record would disappear from the form the next time it was opened (it would still appear in the underlying table, though).

    So, I changed the join in the form's data source to be a left join on tblClient and tblProgramData, and that problem was fixed----AND, the original delete problem was fixed, too! All parts of all records show up when I want them to, and all are deleted when deleted.

    So, I think that's that...for now...unless I find I've inadvertently created additional problems that haven't shown up yet.

    Thanks, Star Trekker for your assistance!

    Regards,
    Shelley

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome, glad to help.

    I suspected that it would put you on the right track

    With 1:1 relationships, I just merge the tables together... it's so much easier to deal with.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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