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

mnmorency
mnmorency used Ask the Experts™
on
I have an Access table called WO and a field within that table called LOT#.  The LOT# field is a text field with indexing enable but will allow duplicate entries.  I have built a data entry form called WO_Form and I would like a popup that alerts the user they have entered a duplicate entry into the LOT# field.  Duplicate entries are ok in the LOT# field but I just want the user to be alerted of the issue.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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!

Author

Commented:
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
Database Architect / Application Developer
Top Expert 2007
Commented:
1) did you rename your text box for LOT# as suggested to txtLotNum ?

You are showing an Underscore here:

Me.LOT_

what is that?

Please give the text box holding LOT# the Name of txtLotNum, then try this code below:

 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

Author

Commented:
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?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial