Compare fields on form for dups

[Razor1]

I have a form with 8 fields that I need to compare to see if there is a duplicate entry in any of the fields after entry in each field.
I am not sure on the best way to approach this problem. I know I can put code in each field to check the other fields but I was looking for a way to do it without specfic code in each field. Thanks in advance for any ideas on how best to accomplish this.

Razor1

 

[lameid]

Sounds like you may want to loop through the Controls collection and then based on the ControlType, compare the value to the other values...

You might use a naming convetion so the related controls begin the same way and use the left function to test for matches.

You could also do this by making all the comparisons...

In any case you would perform your test on the form's event rather than the control. The afterupdate event should work. Try a test with a few controls before going through all permutations of comparisons.

 

[kcmass]

How about if you walked all the controls in a loop building a concatinated string which would include each succeeding control's value. Just before you add a control's data to the concatination, test for its existence in the concatinated string with the instr() function. As soon as instr() returns a value greater than 0 you know you have a dupe. If you need to know each dupe then stuff it's name and value in an array as you are processing it.

Regards

Kevin

 

[TheAceMan1]

How are ya Razor1 . . .

The following is a global function you can use to test for duplicate data in fields. In a module in the modules window, copy/paste the following:

[blue]Public Function DupExist(frm As Form, tblName As String, fldName As String) As Boolean
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String, DL As String
   
   Set db = CurrentDb
   SQL = "SELECT [" & fldName & "] " & _
         "FROM [" & tblName & "] " & _
         "WHERE [" & fldName & "] = '" & frm(fldName) & "';"
   Debug.Print SQL
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then [green]'Dups Exist![/green]
      DL = vbNewLine & vbNewLine
      
      MsgBox "You've Entered Duplicate Data!" & DL & _
             "Duplicate data is not allowed!" & DL & _
             "Go back and try again! . . .", _
             vbInformation + vbOKOnly, _
             "Duplicates Detected! . . ."
      DupExist = True
   End If
   
   Set rst = Nothing
   Set db = Nothing

End Function[/blue]

Next ... in the [blue]Before Update[/blue] event of each control in question, copy/paste the following line ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Cancel = DupExist(Me, "[purple][b]TableName[/b][/purple]", "[purple][b]FieldName[/b][/purple]")[/blue]

Thats it. Give it a whirl and let us know.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]