Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Unanswered: Code for an error message when duplicate values are entered

    Hi guys, I'm currently creating a simple form and the form is linked to one table. I'm looking for a code that displays an error message when duplicate values are entered in my 'parcel-id' field, if two of the same parcels numbers are entered in I would like for the error message to show right when they enter in the value not when they try to save a record, I dont want to use the index yes(no duplicates) for this reason. Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Code:
    If Not IsNull("parcel-Id", "<Table>", "parcel-Id = " & Me.parcel-Id.Value) Then MsgBox "Parcel " & Me.parcel-Id.Value & " already exists."    ' Air code (not tested)
    Where <Table> is the name of the table against which you want to check and parcel-Id is also the name of the control on the form (for the expressions using Me.parcel-Id.Value).
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so place some code behind the control containg parcel-id's on change/lost focus/before update events
    that check to see if the value int he control already exists in the relevant table
    it could be a dlookup, it could be a docmd.r u nsql (remove the spaces) and take appropriate action.. display an error message, change the background, whatever

    make that code a call to a common function and you cna use it in whatever events you see fit.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2009
    Posts
    39
    I was able to do this using the following code



    Private Sub ParcelID_BeforeUpdate(Cancel As Integer)
    Dim sID As String

    sID = Nz(ParcelID.Value, "")

    If sID <> "" Then
    If Nz(DLookup("parcelid", "conference record 2011", "parcelid = '" & sID & "'"), "") <> "" Then
    MsgBox "This parcel number has already been used"
    Else
    MsgBox "This parcel number has already been used"
    End If
    End If
    End Sub


    For some reason the code you gave me got an error at IsNull but I was able to make it work with my code. 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
  •