find duplicates from string and display msg box

[Jerry Stoner]

I need to compare a string taken from 5 fields, compare for duplicates and display a message box if duplicate is found. Also some of these fields may be null but the combination of all 5 fields should always be a unique value (combination of text and numbers). Any help would be much appreciated.

 

[The_Doc_Man]

I have to try to read between the lines here. Are you talking about trying to define a compound key with five member fields and some of the keys could be null? (That's what it sounds like.)

As long as the text fields are fairly short you can do this with something simple.

Suppose the table is called tblMain and the five fields are stA, stB, stC, stD, and stE. The form's text boxes are tbA, tbB, tbC, tbD, and tbE.

I don't know where you would want to put this, but here is a way to do this...

Dim loRecs As Long
Dim stQry As String

stQry = "[stA]=""" & Nz(tbA,"") & """ and [stB]= """ & Nz(tbB,"") & """ and [stC] = """ & Nz(tbC,"") & """ and [stD] = """ & Nz(tbD,"") & """ and [stE] = """ & Nz(tbE,"") & """"

loRecs = DCount( "[stA]", "tblMain", stQry )

After this statement, loRecs contains either 0 or the number of records for which all five fields match the ones on the form.

If loRecs > 0 then

MsgBox "Duplicates found: " & CStr(loRecs), vbOKOnly, "Cannot save this record"

End If

What else you do kind of depends on where you do it. But in a BeforeUpdate routine, you can do a Cancel on the update event.

 

[Jerry Stoner]

You did a great job reading between the lines, that is exactly what I want to do. Will try it out in the morning and let you know how it goes. Thanks alot.

 

[Jerry Stoner]

OK I get a data type mismatch when loRecs is called?

Private Sub txtFifth_BeforeUpdate(Cancel As Integer)
Dim loRecs As Long
Dim stQry As String

stQry = "[First]=""" & Nz(txtFirst, "") & """ and [Second]= """ & Nz(txtSecond, "") & """ and [Third] = """ & Nz(txtThird, "") & """ and [Fourth] = """ & Nz(txtFourth, "") & """ and [Fifth] = """ & Nz(txtFifth, "") & """"

loRecs = DCount("[First]", "tblPartNumber", stQry)

'After this statement, loRecs contains either 0 or the number of records for which all five fields match the ones on the form.

If loRecs > 0 Then

MsgBox "Duplicates found: " & CStr(loRecs), vbOKOnly, "Cannot save this record"