Searching for duplicate records in mutliple fields of same table

[jspence]

I'm very new to vba coding and have a simple question:

My database requires users to scan serial numbers into multiple text boxes on a form that are written to a table. How do you search multiple fields in the same table for a duplicate record?

This is what I have so far:

If DCount("[Serial # 1]", "MAIN SERIAL TABLE_364", "[Serial # 1] = '" & Me![Serial # 1] & "'") > 0 Then
msgbox "Duplicate Serial Number"

This would work fine if I only had one field.

Please Help.
Thanks
Jason

 

[psemianonymous]

The quick and dirty solution, modify your DCount (shown here is for two fields; for each additional field you're going to have to have a total of N*N comparisons):

DCount("[ID]", "MAIN SERIAL TABLE_364", "([Serial # 1] = '" & Me![Serial # 1] & "'OR [Serial # 2] = '" & Me![Serial # 1] "') OR ([Serial #2] = '" & Me![Serial # 2] & "' OR [Serial # 2] = '" & Me![Serial # 2] & "')" )

DCount("[ID]", "MAIN SERIAL TABLE_364", "([Serial # 1] = '" & Me![Serial # 1] & "' OR [Serial # 2] = '" & Me![Serial # 1] "') OR ([Serial #2] = '" & Me![Serial # 2] & "' OR [Serial # 2] = '" & Me![Serial # 2] & "')" )

The structure will basically be this:
(field1 = (field1.value) or field2 = (field1.value) ... or fieldN = (field1.value)) OR
(repeat entire first part switching out (field1.value) for (field2.value)) OR
(repeat until at (fieldN.value))