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

Help on Updating Combo Box

P: 29
Hi -

I have a combo box in a form from a table called 'client names'. I would like users to be able to select name and also add a new name to the combo box, which would also add to the table 'clients name'.

What I have now, is user only able to select a name from combo box but not add unless they exit form and add to table.

Can't seem to figure this one out.

Thanks!
Dec 5 '06 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,615
Unfortunately, this is not possible in a ComboBox.
A ListBox can have an item entered which is not in the current list but I don't think you can trigger an addition to any table that way.
What I think you need is a form structure for adding your record. Possibly selected (filtered) by your ComboBox entry.
Dec 5 '06 #2

ADezii
Expert 5K+
P: 8,669
Hi -

I have a combo box in a form from a table called 'client names'. I would like users to be able to select name and also add a new name to the combo box, which would also add to the table 'clients name'.

What I have now, is user only able to select a name from combo box but not add unless they exit form and add to table.

Can't seem to figure this one out.

Thanks!
'It is a rather unorthodox approach, but if you are still interested this will
'definately work. Crerate a Command Button next to the Combo Box and
'duplicate this code in the Click() Event:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddNameToComboBox_Click()
  2. On Error GoTo Err_cmdAddNameToComboBox_Click
  3.  
  4. Dim strNameToAdd As String
  5.  
  6. strNameToAdd = InputBox$("Name to Add", "Add Name To Combo Box")
  7. If Len(strNameToAdd) > 0 Then
  8.   Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  9.   Set MyDB = CurrentDb()
  10.   Set MyRS = MyDB.OpenRecordset("Client Names", dbOpenDynaset)
  11.     MyRS.AddNew
  12.       MyRS![Name] = strNameToAdd
  13.     MyRS.Update
  14.     MyRS.Close
  15. End If
  16.  
  17. Me![cboName].Requery
  18.  
  19. Exit_cmdAddNameToComboBox_Click:
  20.     Exit Sub
  21.  
  22. Err_cmdAddNameToComboBox_Click:
  23.     MsgBox Err.Description, vbExclamation, "Error in cmdAddNameToComboBox()"
  24.     Resume Exit_cmdAddNameToComboBox_Click
  25. End Sub
NOTE: Should you need any further clarification, please let me know...
Dec 5 '06 #3

P: 51
1. Create a cmdAdd button next to the combo box.
2. Make a AddAClient Form (Pop up form) so that user can add a new client. At the bottom of the form, create a cmdSave button and a cmdCancel button. Whent he user click on the cmdAdd button, it should open the form AddAClient Form. After the user enter the data, when the user click on the cmdSave button, it should save to your Client table. Make sure to put code on all your cmd button.
3. In the combox box, After Update command, but code comboboxName.Requery.
It should update your combo box.
good luck!
Dec 5 '06 #4

P: 29
'It is a rather unorthodox approach, but if you are still interested this will
'definately work. Crerate a Command Button next to the Combo Box and
'duplicate this code in the Click() Event:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddNameToComboBox_Click()
  2. On Error GoTo Err_cmdAddNameToComboBox_Click
  3.  
  4. Dim strNameToAdd As String
  5.  
  6. strNameToAdd = InputBox$("Name to Add", "Add Name To Combo Box")
  7. If Len(strNameToAdd) > 0 Then
  8.   Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  9.   Set MyDB = CurrentDb()
  10.   Set MyRS = MyDB.OpenRecordset("Client Names", dbOpenDynaset)
  11.     MyRS.AddNew
  12.       MyRS![Name] = strNameToAdd
  13.     MyRS.Update
  14.     MyRS.Close
  15. End If
  16.  
  17. Me![cboName].Requery
  18.  
  19. Exit_cmdAddNameToComboBox_Click:
  20.     Exit Sub
  21.  
  22. Err_cmdAddNameToComboBox_Click:
  23.     MsgBox Err.Description, vbExclamation, "Error in cmdAddNameToComboBox()"
  24.     Resume Exit_cmdAddNameToComboBox_Click
  25. End Sub
NOTE: Should you need any further clarification, please let me know...
Thanks for your reply. Being new to Access, I'm not sure what the code means. So when I did type in new name it gives me an error message that says "item not found in this collection". Did not add to Combo box or table.
Dec 5 '06 #5

MSeda
Expert 100+
P: 159
Combo boxes also have an "on not in list" event that the above suggested technique can be used in.
Dec 5 '06 #6

NeoPa
Expert Mod 15k+
P: 31,615
Combo boxes also have an "on not in list" event that the above suggested technique can be used in.
That's interesting. I didn't know that.
Dec 6 '06 #7

P: 29
Thanks for the suggestions, will give them all a try and see which works best for the form.
Dec 6 '06 #8

Post your reply

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