Search for duplicates in Table

[lost]

sorry for the basic question, but:

I have users entering a value on a form in a text box that is bounded
and called via a query. I need to check to see if there are any
duplicate values for that entry and bock it. I can't use the primary
key here, so I need some vb code to do the search the table.

I just need a place to start
Much thanks,

 

[Douglas J. Steele]

One way is to use DLookup, with the appropriate fields for the 3rd
parameter.

 

[lost]

One way is to use DLookup, with the appropriate fields for the 3rd
parameter.

Thanks for your suggestion. I am having a bit of a problem. I am
trying to check the txtserial entry in the "Assignment Table" to see
if there is another serial number.

I tried the following

serial_lostfocus

txtjunk.SetFocus
txtjunk.Value = DLookup("[random]", "Assignment Table",
"form![txtserial] = form![txtserial]")

As soon as I enter the second serial number, it gives my the first
cases random value, no matter if the serial is a duplicate or not.

Any thoughts ?

 

[John Vinson]

I tried the following

serial_lostfocus

txtjunk.SetFocus
txtjunk.Value = DLookup("[random]", "Assignment Table",
"form![txtserial] = form![txtserial]")

As soon as I enter the second serial number, it gives my the first
cases random value, no matter if the serial is a duplicate or not.

Well, a couple of issues here.

I'd STRONGLY suggest using Serial's BeforeUpdate event instead of
LostFocus. BeforeUpdate fires only when the user changes the value in
the control, and it can be cancelled; LostFocus fires whenever they
move out of the control, even if they're just tabbing through it.

Secondly, your search criterion will always be TRUE since it's
comparing the value in the txtserial textbox on the Form to the value
in the txtserial textbox on the Form... it's comparing the control to
itself, so it will naturally always be equal! Instead, compare the
value in the Table to the value in the textbox.

If you just want to warn the user if there's a duplicate, try code
like this. I'm assuming that the field [Serial] is bound to a textbox
named [txtserial], and that it's a Text field (leave off the ' and "'"
if it's numeric).

Private Sub txtserial_BeforeUpdate(Cancel as Integer)
If Me!Serial & "" = "" Then Exit Sub ' ignore blanks
If Not IsNull(DLookUp("[Serial]", "[Assignment Table]", _
"[Assignment Table].[Serial] = '" & Me!txtSerial & "'') Then
MsgBox "This serial number already exists!", vbOkOnly
<do something appropriate>
End If
End Sub