Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Location
    Nottinghamshire, UK
    Posts
    14

    Unanswered: Prevent Duplication and go to Original...

    What I would like to do is prevent the user entering a duplicate record, undo the entry and go to the record already entered for that student.

    So when the user tries to enter a duplicate Student Number, I use the following code that informs the user of the duplicate. Within this code, I would then like to undo the new record, and automatically take the user to the original record for that student.

    Code:
    Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)
    
    If DCount("*", "tblStudentDetails", "strStudentNumber") > 0 Then
        Cancel = True
        MsgBox "Warning you are trying to enter duplicate information.", , "Duplicate Information"
    'Here I would like to undo the action and move the user to the original record
    End If
    
    End Sub
    I have attached an example.

    If you open the Student Job Outcomes form, and try to create a new record using the student number of AA11111, you will be informed of the duplicate. Here, I would like to undo the record and move to the original.

    Or if there are any better approaches, then please feel free to say.

    TIA
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Looks like you haven't made the student ID the Primary Key with no duplicates integrity set on the table. Doing this would bypass the need of your code. However, your "stuck" with your implementation, so when you get the error do something like
    StudentNo=(someothervalue), or populate the existing record with the first record.

  3. #3
    Join Date
    Jun 2004
    Location
    Nottinghamshire, UK
    Posts
    14
    poliarci

    Thanks for the reply.

    The Student Number is the Primary Key. However, I wanted to prevent the duplicate from being entered and just receiving the standard Access Error message.

    I have now solved the problem, thanks to a little help from another board and the solution can be found at:

    Preventing Duplicates from being entered

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Preventing input dulpicate primary Key values

    HI

    If the 'StudentNumber' is the key field than try this

    Code:
    Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)
    
    If DCount("StudentNumber", "tblStudentDetails", "strStudentNumber = '" & strStudentNumber & "'") > 0 Then
        Cancel = True
        MsgBox "Warning you are trying to enter duplicate information.", , "Duplicate Information"
    
        DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    
        Me.RecordsetClone.FindFirst "StudentNumber = '" & strStudentNumber & "'"
        Me.Bookmark = Me.RecordsetClone.Bookmark
    
    End If
    
    End Sub
    I have assummed that strStudentNumber is a field Text ?.
    If it is numeric you will not need apostrophies


    MTB

  5. #5
    Join Date
    Jun 2004
    Location
    Nottinghamshire, UK
    Posts
    14
    Mike

    Thanks for the alternative. Always nice to see a few variations.

Posting Permissions

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