By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,564 Members | 804 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,564 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

Expand|Select|Wrap|Line Numbers
  1. Dim Ctrl As Control
  2.     For Each Ctrl In Me.Controls
  3.     If Ctrl.Tag = "dcheck" Then
  4.         If Ctrl.Name <> ActiveControl.Name And _
  5.         Ctrl.Text = ActiveControl.Text Then
  6.         MsgBox ActiveControl.Text & " is a duplicate of Box " & Ctrl.Name
  7.         Dups = True
  8.     End If
  9. End If
  10. Next Ctrl
Sep 30 '10 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 10K+
P: 14,534
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 ...

Expand|Select|Wrap|Line Numbers
  1. Dim Ctrl As Control
  2. Dim ctlName As String
  4.     ctlName = ActiveControl.Name
  5.     For Each Ctrl In Me.Controls
  6.         If Ctrl.Tag = "dcheck" Then
  7.             If Ctrl.Name <> ctlName And _
  8.                 Ctrl.Text = Control(ctlName).Text Then
  9.                 MsgBox Control(ctlName).Text & " is a duplicate of Box " & Ctrl.Name
  10.                 Dups = True
  11.             End If
  12.         End If
  13.     Next Ctrl
Sep 30 '10 #2

Expert 5K+
P: 8,627
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.
Expand|Select|Wrap|Line Numbers
  1. Dim ctl_1 As Control
  2. Dim ctl_2 As Control
  4. For Each ctl_1 In Me.Controls
  5.   If ctl_1.Tag = "dcheck" And Not IsNull(ctl_1) Then
  6.     For Each ctl_2 In Me.Controls
  7.       If ctl_2.Tag = "dcheck" And Not IsNull(ctl_2) Then
  8.         If ctl_1.Name <> ctl_2.Name And ctl_1 = ctl_2 Then
  9.           Debug.Print ctl_1.Name & " and " & ctl_2.Name & " are Duplicates!"
  10.         End If
  11.       End If
  12.     Next
  13.   End If
  14. Next
Sample OUTPUT (notice duplication):
Expand|Select|Wrap|Line Numbers
  1. Text2 and Text6 are Duplicates!
  2. Text6 and Text2 are Duplicates!
  3. Text11 and Text12 are Duplicates!
  4. Text12 and Text11 are Duplicates!
Sep 30 '10 #3

P: n/a
Thanks for your response. I ended up using the following code that worked.

Expand|Select|Wrap|Line Numbers
  1. Dim Ctrl As Control
  2.     For Each Ctrl In Me.Controls
  3.         If Ctrl.Tag = "dcheck" Then
  4.             If Ctrl.Name <> ActiveControl.Name And _
  5.                 Ctrl.Value = ActiveControl.Value Then
  6.                 MsgBox ActiveControl.Value & " has already been entered." '& Ctrl.Name
  7.                 Dups = 1
  8.             End If
  9.           End If
  10.         Next Ctrl
Sep 30 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.