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

    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
    Provided Answers: 15
    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
    out on a limb
    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
    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"
    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