Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cannot delete records (2003)

    Hello everyone,

    long time no see. I have some kind of "easy" problem I suppose but I just can't figure it out.
    Look at the attached test database and the frmSchools. When I use the built-in delete function of Acces it says "Are you sure to delete..." Yes I am, but when I restart the database or the form the record is still there.
    I'M assuming that Access is only deleting the relationships in the joinSchools... table but I don't know how to tell Access to delete the actual record in tblSchools AND all relationships.

    Cheers Dennis

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cannot delete records (2003)

    It's a bug in Access that it pretends to delete the record while it doesn't. The cause it the outer join between tblSchools and joinSchoolsParticipation in the record source of the form.

    If you want to delete a school, create a form based on tblSchools only. If you want to delete the corresponding records in joinSchoolParticipation as well, set a relationship between tblSchools and joinSchoolsParticipation with enforced referential integrity and cascading deletions. Deleting a school will then also delete related participation records.

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

    Re: Cannot delete records (2003)

    You don't have any relationships defined in the database that I can see, so I'm not sure what you expect to happen in a delete. In any case, you don't need the joinSchoolsParticipation in the query behind the parent form because the subforms are linked through the ID field in the parent table and it is that extra table that is causing part of your delete problems. If you create relationships between the parent table and the join tables and enforce referential integrity with cascading deletes, the join records will be deleted. Otherwise, you have to write code to delete those records yourself.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot delete records (2003)

    Sucks that you can't answer several answers with only one [img]/forums/images/smilies/wink.gif[/img]

    Thanks to both of you guys, now I know what those global relationships are for. I was always happy with setting up relationships in the form's themselves...

    A relationship with referential integrety+deletes between joinSchoolsParticipation / tblSchools / joinSchoolsUNMember did the trick. I also set the form source to only tblSchools (I had to go with the more complex version because I wasn't using global relationships apparently).

    Thanks for your quick response - what would I be w/o this board.

    Dennis

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot delete records (2003)

    As always... Another problem following the first problem:

    As I'm trying to open the form limiting it to some special values I have to include the other table into the form source:

    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmSchools"
    stLinkCriteria = "[Participated] = True AND [Year] =" & Forms![frmYear]![cboreg] & ""
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Maybe there is another way of providing a link for the user to "Open only Schools which have participated", but this is what I have always worked with. When basing the form only on tblSchools, deleting with referential integrity works. When basing it on tblSchools.* and joinSchoolsParticipation.Year + .Participated deleting doesn't work.

    Hope you can help me out (again)

    Dennis

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cannot delete records (2003)

    You can base the form on tblSchools, and use

    stLinkCriteria = "ID In (SELECT ID FROM joinSchoolsParticipation WHERE Participation = True And Year = " & Forms!frmYear!cboreg & ")"

  7. #7
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot delete records (2003)

    I wish I could come up with those solutions, as easy as they seem.

    Yes Hans that did the trick. Thanks

Posting Permissions

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