Duplicates in unbound text boxes

  • Thread starter Thread starter Design by Sue
  • Start date Start date
D

Design by Sue

I have created a form for the user to enter a new part to the datebase. On
this form I have 6 fields, Suffix1, Suffix2, etc. The form and these fields
are unbound. The user can type any number in these fields as long as it is
not a match for one of the other 6 suffix fields. Is there an easier way
(there has to be) than creating a code for each field that goes like this
(this would go on the on exit event and each field would be customized for
it):

If Me.Suffix1 = Me.Suffix2 or
Me.Suffix 1 = Me.Suffix3 or
Me.Suffix1 = Me.Suffix4 or

etc through all of the possible choices with a "Then" MsgBox...

This would work but I would think there was an easier way.

Thanks
Sue
 
Untested, but the idea is to compare each box to all the others, so perhaps
something like this:

Function TestEm()
Dim strMsg As String

For i = 1 to 5
For j = i + 1 to 6
If Me("Suffix" & i).Value = Me.(Suffix" & j).Value Then
strMsg = strMsg & "Box " & i & " = " & "Box " & j & vbCrLf
End If
Next j
Next i

If strMsg <> vbNullString Then
MsgBox strMsg, vbExclamation, "Oops!"
End If
End Function
 
Back
Top