Check for Duplicates in Unbound Text Boxes

[Adam McReynolds]

Any suggestions on the best way to check for duplicates in unbound text boxes on a form? I have 10 text boxes that I want to check. Currently I have a code in the After Update even of each but I am getting 50/50 results. I have this code in each of the 10 text boxes w/ each different for that text box:

If Me.txt_rid1 = (Me.txt_rid2 Or Me.txt_rid3 Or Me.txt_rid4 Or Me.txt_rid5 Or Me.txt_rid6 Or Me.txt_rid7 Or Me.txt_rid8 Or Me.txt_rid9 Or Me.txt_rid10) Then
MsgBox "Duplicate Record ID Entered. Please Fix"
Cancel = True
Exit Sub
End If

Thanks.

 

[jdraw]

Could you please give is some more context about what you are trying to do? There may be some options, but until we know WHAT these text boxes and values mean and why there should be no duplicates, it's difficult to offer any focused suggestions.

Sometimes you start with a list of values, and let the user choose1, then remove it form the list, and repeat as necessary.
Avoids using same value more than once.

 

[MarkK]

Here's a programmatic approach ...

Sub txt2_AfterUpdate()
   CheckForDupes Me.txt2    [COLOR="Green"]'pass the control to the dupe checker[/COLOR]
End Sub

Sub txt3_AfterUpdate()
   CheckForDupes Me.txt3
End Sub

Private Sub CheckForDupes(ctrl As TextBox)
   Dim i As Integer
   Dim txt As TextBox
   For i = 1 To 10                      [COLOR="Green"]'loop thru controls[/COLOR]
       Set txt = Me.Controls("txt" & i) [COLOR="Green"]'set object reference[/COLOR]
       If Not txt Is ctrl Then         [COLOR="Green"] 'don't compare control to itself[/COLOR]
          If txt = ctrl Then            [COLOR="Green"]'if we have equality then...[/COLOR]
              ctrl = Null               [COLOR="Green"]'...blank the dupe value...[/COLOR]
              ctrl.SetFocus             [COLOR="Green"]'...focus the control...[/COLOR]
              MsgBox "No Dupes"         [COLOR="Green"]'...educate user...[/COLOR]
              Exit For                  [COLOR="Green"]'...abondon search[/COLOR]
          End If
       End If
   Next
End Sub

...but it's still sort of brute-force. I'd pursue what jdraw is talking about, like, what are you trying to do? It'd be way easier, for instance, if you could list those values a datasheet based on a table, because then the table index can prohibit dupes on entry, and you can deal with the data in a recordset, and so on...

The main principles worth demonstrating in this code are,
1) use subroutines
2) pass in objects as parameters

 

[Solo712]

Any suggestions on the best way to check for duplicates in unbound text boxes on a form? I have 10 text boxes that I want to check. Currently I have a code in the After Update even of each but I am getting 50/50 results. I have this code in each of the 10 text boxes w/ each different for that text box:

If Me.txt_rid1 = (Me.txt_rid2 Or Me.txt_rid3 Or Me.txt_rid4 Or Me.txt_rid5 Or Me.txt_rid6 Or Me.txt_rid7 Or Me.txt_rid8 Or Me.txt_rid9 Or Me.txt_rid10) Then
MsgBox "Duplicate Record ID Entered. Please Fix"
Cancel = True
Exit Sub
End If

Thanks.

You would be much better off setting check boxes in an option group. The option group allows only one item to be selected, so you don't need to check anything. After you select the option you assign a value from a single text box to the selected item.

http://msdn.microsoft.com/en-us/library/office/bb214235(v=office.12).aspx

Best,
Jiri

 

[Adam McReynolds]

Could you please give is some more context about what you are trying to do? There may be some options, but until we know WHAT these text boxes and values mean and why there should be no duplicates, it's difficult to offer any focused suggestions.

Sometimes you start with a list of values, and let the user choose1, then remove it form the list, and repeat as necessary.
Avoids using same value more than once.

Thanks for the reply. The user is entering record id's into unbound text boxes and I do not want them to enter the same record id as it when the submission is made my SQL update code is executed and that would leave one record not updated and the one in there twice updated as the last SQL to run which could then be incorrect information. So if they can't enter duplicates then that will eliminate that problem. Thanks for the help.

 

[jdraw]

Having users key anything into a text box is subject to typos etc.

I still don't fully understand your issue, but will make a few comments for you to consider.

If you have a unique index on your table, then a user can not enter a duplicate and will get a warning msg.

If you select the record ids and put them or the associated Name as the rowsource of a combobox, then let the user highlight an entry and do whatever -- no data entry involved.

If there are only a few options, then make an option group and let the user select an option, and based on the chosen option do whatever behind the scenes ---no data entry.

If there is more to this, then please tell us in plain English what it is you are trying to achieve. Once we understand then options will be forthcoming.

Good luck.