Finding Duplicate Values

[TimMoffy]

not sure if this should be under forms or queries.

I have a form where users enter a reference number into a textbox on a form and I want to check that the ref. number does not already exist. If it does then I would like to display a form with the message on it, telling them of the duplication – can display the form easily enough.

I have created a find duplicates query but that doesn’t really work as the reference number is not written away at that time and it also returns ALL duplicates it finds, not just for the number in the text box.



Whenever I run the query as well it shows to the user, which isn’t very elegant and I’d like to remain hidden. I would guess this means putting into a form, which is fine, and only displaying the form if the number of records returned is greater than zero, but how to trigger the query / form ever being dsplayed. normally I would do this sort of thing using an On Click command, but do not have such a thing here so would it be on After update in the reference number text box?

In all I’m totally stumped by this

So what I’m asking I guess is:
How to write the data away from the text box before the form data is saved so I can run the find duplicates query and limit that query criteria to the reference number text box. From looking at the query created by the wizard I think I need to save the data first?

How to query the data without it being visible to the user if there is nothing to show?

If there is data to display then I know how to run the form….. I think.

Hope this all makes sense

Thanks in advance for your help.

[Missinglinq]

This is usually done using the DCount() function rather than a full blown Query. The exact syntax depends on the Datatype of the Field being used.
Replacing Field1 with the actual name of your Field/Control, and Domain with the name of the Table or Query that holds the Field:

If Field1 is Text

Private Sub Field1_BeforeUpdate(Cancel As Integer)
If DCount("Field1", "Domain", "[Field1] = '" & Me.Field1 & "'") > 0 Then
 Resp = MsgBox("This Value Already Exists! Do You Wish to Add Anyway?", vbYesNo)
 If Resp = vbNo Then
  Cancel = True
 End If
End If
End Sub

0f Field1 is Numeric

Private Sub Field1_BeforeUpdate(Cancel As Integer)
If DCount("Field1", "Domain", "Field1 = " & Me.Field1) > 0 Then
 Resp = MsgBox("This Value Already Exists! Do You Wish to Add Anyway?", vbYesNo)
 If Resp = vbNo Then
  Cancel = True
 End If
End If
End Sub

Linq ;0)>

[TimMoffy]

Fantastic - worked first time many many thanks for your help - boss is well impressed!

[Missinglinq]

Glad we could help!

Linq ;0)>

[squatter]

it really amazing... thanks master...