Prevent duplicate number (integer) entry before update

[waqasi]

I am allocating a unique number to a patient and want to prevent duplicate entry before update

[Seth Schrock]

You would want this in the form's BeforeUpdate event:

If DCount(Expr:="*", Domain:="your form's record source", Criteria:="UniqueNumberField=" & Me.UniqueNumberField) <> 0 Then
     Cancel = True
     MsgBox "Number already exists"
End If

[waqasi]

Thank you very much it is working. I was using bit more complex code and giving error message type missmatched, code is as follow

Private Sub hospitalno_BeforeUpdate(Cancel As Integer)
 
    Dim SID As Long
    Dim stLinkCriteria As Long
    Dim rsc As DAO.Recordset
 
    Set rsc = Me.RecordsetClone
 
    SID = Me.hospitalno.Value
    stLinkCriteria = "[hospitalno]=" & "'" & SID & "'"
 
    'Check patientdetails table for duplicate Hospital Number
    If DCount("hospitalno", "tablepatientdetails", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Hospital Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Hospital Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
 
    Set rsc = Nothing
End Sub

[Seth Schrock]

Glad it helped. If you would select my answer as the best answer, I would appreciate it.

In future questions, it would be helpful for those of us who are trying to help you if you would give what you have tried along with any error messages (exact number and text) and the line of code that is selected when you hit debug on the error. Also, when you are posting code (VBA or SQL), please place your code in the code brackets. The button that looks like <CODE/> ( to the right of the bold, italics, underline buttons and to the left of the undo and redo buttons) will place the code tags in your post for you.

[zmbd]

waqasi:

A few things to mention:

(-) Seth was kind enough to provide you with a coded answer; however, Bytes is not a code nor homework service. Please read the FAQ and posting guidelines before re-posting your question.

(-) Post #3: As Seth pointed out, but let me emphasize, that posting comments such as "it doesn't work" or "there's an error" does not help us help you. Instead we need the specifics as to what you expected and what happened. In the case an error, you must post the EXACT title, error message, and the error number. We must have all three parts as this help us to determine if the error is from the OS or the program.

(-) We also ask that you do the basic trouble shooting on your system. We can not perform such as our systems simply are not yours.
You should have both options set.
You should compile your code.

(-) I have also deleted a post that directly asked for code which is not normally allowed. Please refer the site FAQ and the forum sticky posting guidelines.