Prevent duplicates on Access form with multiple fields to enter serial numbers.

[Erica Weems]

I have an Access form where the user will enter 20 serial numbers in text fields using a bar code reader. How can I check the form to make sure the same serial number hasn't been scanned more than once before the data is saved. Thanks in advance.

Here is the code i've tried so far.

Dim Ctrl As Control
    For Each Ctrl In Me.Controls
    If Ctrl.Tag = "dcheck" Then
        If Ctrl.Name <> ActiveControl.Name And _
        Ctrl.Text = ActiveControl.Text Then
        MsgBox ActiveControl.Text & " is a duplicate of Box " & Ctrl.Name
        Dups = True
    End If
End If
Next Ctrl
 

Erica

[MMcCarthy]

I think you are on the right track. I'm guessing your problem is in the use of ActiveControl.Name as the focus will move from control to control during the check.

Try this ...

Dim Ctrl As Control
Dim ctlName As String
 
    ctlName = ActiveControl.Name
    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "dcheck" Then
            If Ctrl.Name <> ctlName And _
                Ctrl.Text = Control(ctlName).Text Then
                MsgBox Control(ctlName).Text & " is a duplicate of Box " & Ctrl.Name
                Dups = True
            End If
        End If
    Next Ctrl
 

[ADezii]

Why not avoid ActiveControl altogether? After all 20 Serial Numbers are entered into the Form, poll the Text Boxes, then list any Duplication, pin-pointing the Text Boxes involved. After rethinking, this may be a little messy, but only you cab decide whether or not you wish to use this approach. The only problem with this Logic is that Duplicates will be listed twice, as illustrated in the Sample Output, but if you want to take this route, I'm sure that can be corrected.

Dim ctl_1 As Control
Dim ctl_2 As Control
 
For Each ctl_1 In Me.Controls
  If ctl_1.Tag = "dcheck" And Not IsNull(ctl_1) Then
    For Each ctl_2 In Me.Controls
      If ctl_2.Tag = "dcheck" And Not IsNull(ctl_2) Then
        If ctl_1.Name <> ctl_2.Name And ctl_1 = ctl_2 Then
          Debug.Print ctl_1.Name & " and " & ctl_2.Name & " are Duplicates!"
        End If
      End If
    Next
  End If
Next

Sample OUTPUT (notice duplication):

Text2 and Text6 are Duplicates!
Text6 and Text2 are Duplicates!
Text11 and Text12 are Duplicates!
Text12 and Text11 are Duplicates!

[Erica Weems]

Thanks for your response. I ended up using the following code that worked.

Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "dcheck" Then
            If Ctrl.Name <> ActiveControl.Name And _
                Ctrl.Value = ActiveControl.Value Then
                MsgBox ActiveControl.Value & " has already been entered." '& Ctrl.Name
                Dups = 1
            End If
          End If
        Next Ctrl