Access Form to check for duplicate values in a table during data entry

Something like this

Private Sub txtLotNum_BeforeUpdate (Cancel As Integer)

   If Nz (DLookup("[Lot#]","WO","[Lot#] =" & Chr(34) & Me.txtLotNum & Chr(34)),"") >"" Then
      MsgBox "Warning - This LOT# already exists"
      '  other action here
   End If

End Sub

mx

you need to make sure to add an undo command with the code above otherwise it will still update/create the record!

in the code under the "'other action here" is where it needs to be
i believe its just
docmd.undo


"The LOT# field is a text field with indexing enable but will allow duplicate entries." you could use the dlookup as suggested. your other option is to change the "indexing" so that it will not allow duplicates just in that column you can leave the others alone. i believe access will have a message popup if the user tries to input a duplicate record.

hope this helps

i think now it might be just

me.undo

test both

"conagraman:

This is why I put  

'  other action here

to imply that the message might instead  be a Yes/No ... asking user what action to take, etc.  The main part was how to check for the dup.

And you might note this comment in the Q:

"Duplicate entries are ok in the LOT# field but I just want the user to be alerted of the issue."

So ... apparently No Dups is not the desired setting ...

mx

Lets amp this up:

   If Nz (DLookup("[Lot#]","WO","[Lot#] =" & Chr(34) & Me.txtLotNum & Chr(34)),"") >"" Then
       If MsgBox ("This LOT# currently exists.  Would you like to continue (or whatever)?", 52,"LOT# check ...") = VbNo Then
             Me.txtLotNum.Undo
             Cancel = True
        End If
  End If

End Sub

and notice I have given the text box for LOT# a Name of txtLotNum

mx

nice!

So I added the Event to the Before Update for LOT#.  I tested the form and added a duplicate LOT# in the form and I never got prompted.  Below is the code I ended up using.  I am not sure if the "#" sign in my field is throwing the code off?

Private Sub LOT__BeforeUpdate(Cancel As Integer)

   If Nz(DLookup("[Lot#]", "WO", "[Lot#] =" & Chr(34) & Me.LOT_ & Chr(34)), "") > "" Then
      MsgBox "Warning - This LOT# already exists"
      '  other action here
   End If

End Sub

I missed the renaming of LOT# in the form field to txtLotNum.  I just made that change but I still do not receive a popup.  Below is the code I have in my form:

Private Sub txtLotNum_BeforeUpdate(Cancel As Integer)
 If Nz(DLookup("[Lot#]", "WO", "[Lot#] =" & Chr(34) & Me.txtLotNum & Chr(34)), "") > "" Then
       If MsgBox("This LOT# currently exists.  Would you like to continue (or whatever)?", 52, "LOT# check ...") = vbNo Then
             Me.txtLotNum.Undo
             Cancel = True
        End If
  End If
End Sub

Any other ideas?

Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

NOTE:  If you have an A2007 ACCDB, please convert to A2003 MDB.
mx