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

Allow Duplicate Entries in a Combo Box

P: 10
have a project in which I am entering Serial Numbers and Date codes into a Combo box. Serial numbers are all different. However, they could each have the same Date Code. Each Serial Number has a corresponding Date Code which I then write to a table using VBA. When I try to enter a duplicate date code, it automatically finds the duplicate and will not allow me to add it again to that combo box. How do I configure that combo box so I can enter the same date code multiple times?

I use a Command button to Add the data to both combo boxes which also adds the values to 2 listboxes.

Expand|Select|Wrap|Line Numbers
  1. ' 9-26-2008
  2. ' Modified: 1-19-2009 , Combined two buttons
  3. Dim frm As Form
  4. ' SN
  5. Dim ctl As Control
  6. Dim ctl1 As Control
  7. ' DC
  8. Dim ctl2 As Control
  9. Dim ctl3 As Control
  10.  
  11. On Error GoTo cmdAddSNErr
  12.  
  13. Set frm = Forms!frmPrimary_User_Interface '!frmPrimary_User_Interface.Form
  14. ' SN
  15. Set ctl = frm!lstSN
  16. Set ctl1 = frm!cboOrigSerialNum
  17. ' DC
  18. Set ctl2 = frm!lstDC
  19. Set ctl3 = frm!cboOrigDateCodes
  20.  
  21. 'intSNDCct = ctl.ListCount
  22. ' 2-14-2009
  23. ' Did the user enter the Quantity Rejected?
  24. If txtOrigQtyRej.Value <> "" Then
  25.     ' Yes
  26.     ' Did the user enter both a SN & DC
  27.     If ctl1.Value <> "" And ctl3.Value <> "" Then
  28.         ' Yes
  29.         ' Data entry for all of the serial numbers in this rejection notice
  30.         If intQtyRej <> ctl.ListCount And intQtyRej <> ctl2.ListCount Then             ' Make sure user doesn't add more serial numbers than Quantity Rejected
  31.  
  32.             'Me.lstSN.SetFocus
  33.             ' SN
  34.             ctl.AddItem ctl1.Value
  35.             ctl1.Value = ""
  36.  
  37.             'ctl1.Value = ""    ' Clear for next entry
  38.             ' DC
  39.  
  40.             ctl2.AddItem ctl3.Value
  41.  
  42.             ctl3.Value = ""                  ' Clear for next entry
  43.  
  44.             lblSNCount.Caption = "SN: " & ctl.ListCount & "/" & ctl2.ListCount & " of REJ: " & intQtyRej
  45.             intSNDCct = ctl.ListCount
  46.         Else                                                ' User exceeded Quantity Rejected
  47.  
  48.             MsgBox "Serial Numbers or Date Codes entered Exceeds Quantity Rejected value [" & intQtyRej & "]" & vbCrLf & "This last Serial Number or Date Code is ignored!", vbExclamation, "Serial Number(s) entry"
  49.             Exit Sub
  50.         End If
  51.     Else ' No
  52.             MsgBox "You must enter a Serial Number and a Date Code or enter '0' if none exists!" & vbCrLf & "Please make sure you enter both before attempting to add!", vbExclamation, "SN/DC entry: Missing Data"
  53.             cboOrigSerialNum.Value = ""
  54.             cboOrigDateCodes.Value = ""
  55.             cboOrigSerialNum.SetFocus
  56.             Exit Sub
  57.     End If
  58. Else ' No
  59.         MsgBox "You must enter the Quantity Rejected before you can add Serial Numbers and Date Codes!" & vbCrLf & "Please the Quantity Rejected!", vbExclamation, "SN/DC entry: Missing Data"
  60.         txtOrigQtyRej.SetFocus
  61.         Exit Sub
  62. End If
  63.  
  64. Set frm = Nothing
  65. ' SN
  66. Set ctl = Nothing
  67. Set ctl1 = Nothing
  68. ' DC
  69. Set ctl2 = Nothing
  70. Set ctl3 = Nothing
  71.  
  72. ExitcmdAddSN:
  73.     On Error GoTo 0
  74.     Exit Sub
  75.  
  76. cmdAddSNErr:
  77.     Select Case err.Number
  78.         Case Else
  79.             MsgBox err.Description & strErrSect, vbCritical, "Error " & err.Number & " in cmdAddSN"
  80.     End Select
  81.     Resume ExitcmdAddSN
Mar 16 '09 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 1,287
What's the control source for your combo box?
Mar 16 '09 #2

NeoPa
Expert Mod 15k+
P: 31,494
It sounds like you may have designed the table with a unique index on the date field.

If this isn't the case then please try to explain your problem more clearly with relevant error messages where appropriate.
Mar 17 '09 #3

P: 10
I resolved the issue by creating a table to place those values in. We also went with just text boxes. If the user needs to view what they entered, I have a button that will load it into a ListView control. I found a great Function for populating a ListView Control. All you have to do is call it and send an SQL Statement to it. Real slick.

Thanks for your reply.
Mar 19 '09 #4

P: 10
My project has many tables. I did not initially design the table structure. They use a Primary Key and Foreign Key. I split the database for Multi User usage.
Mar 19 '09 #5

Post your reply

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