Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding or editing (AccessXP SP1)

    Hi. I've set up the following code and what I want it to do is add a new record to a table for a student if the student is not in the table already. If the student record exists, I want it to update the existing record with new details. The code is as follows (I've added the word 'Fred' to help with debugging):

    ================================================== =============
    With rst
    If .RecordCount = 0 Then ' if there is no data to add or edit, do nothing
    Else
    With rstC
    .Index = "PrimaryKey" ' Set the index.
    varBookmark = .Bookmark
    .Seek "=", [Forms]![frmStudents]![StudentID]
    If Not .NoMatch Then ' if no match exists for student record
    .AddNew
    !StudentID = rst!StudentID
    !Term = rst!Term
    !Class = ": " & rst!TermID & rst!StreamID
    .Update
    Else
    ' .Index = "PrimaryKey" ' Set the index.
    ' .Seek "=", [Forms]![frmStudents]![StudentID]
    .Bookmark = varBookmark
    .Edit
    !StudentID = rst!StudentID
    !Term = rst!Term
    !Class = ": fred " & rst!TermID & rst!StreamID
    .Update
    End If
    End With
    End If
    End With
    ===============================================

    The problems are:
    1. It's not adding new records: it goes straight to edit/update, ignoring the new record branch but it acts like it's adding anyway because I get two records for the same student (and 'fred' appears in the record).
    2. Instead of edit/update, it adds. That is, it still ignores the new record branch, then goes to edit/update, but it acts like it's adding anyway because I get two records for the same student (and 'fred' appears in the record).
    3. If I make an edit for a different student to a different term, a new record is added for the correct term, 'fred' appears in the new record, but, and this is really strange, the previous record that had 'fred' in it, loses the word 'fred'.

    Aside from all those strange, back to front issues, what I want to do is written in the first paragraph.

    Can someone help please?
    Thanks
    Nick

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Adding or editing (AccessXP SP1)

    I think you have a Not that is not needed.

    If Not .NoMatch Then ' if no match exists for student record

    Surely If .NoMatch then there no match for exists for student record.
    Regards
    John



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

    Re: Adding or editing (AccessXP SP1)

    You have switched the AddNew and Edit parts, or in other words, you should have

    If .NoMatch Then

    instead of

    If Not .NoMatch Then

    For if .NoMatch is true, a match was not found, so you want to add a new record. If .Nomatch is false, a match was found, so you want to edit the found record.

    Moreover, I think you should omit the bookmark code. You set the bookmark variable to the bookmark of rstC before Seek, then if found, you move back to the original position.

    Note: I usually use FindFirst instead of Seek, since Seek does not work in linked tables.

Posting Permissions

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