Help on Updating Combo Box

[rgsw]

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!

[NeoPa]

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.

[ADezii]

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:

Private Sub cmdAddNameToComboBox_Click()
On Error GoTo Err_cmdAddNameToComboBox_Click
 
Dim strNameToAdd As String
 
strNameToAdd = InputBox$("Name to Add", "Add Name To Combo Box")
If Len(strNameToAdd) > 0 Then
  Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  Set MyDB = CurrentDb()
  Set MyRS = MyDB.OpenRecordset("Client Names", dbOpenDynaset)
    MyRS.AddNew
      MyRS![Name] = strNameToAdd
    MyRS.Update
    MyRS.Close
End If
 
Me![cboName].Requery
 
Exit_cmdAddNameToComboBox_Click:
    Exit Sub
 
Err_cmdAddNameToComboBox_Click:
    MsgBox Err.Description, vbExclamation, "Error in cmdAddNameToComboBox()"
    Resume Exit_cmdAddNameToComboBox_Click
End Sub

NOTE: Should you need any further clarification, please let me know...

[elainenguyen]

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!

[rgsw]

'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:

Private Sub cmdAddNameToComboBox_Click()
On Error GoTo Err_cmdAddNameToComboBox_Click
 
Dim strNameToAdd As String
 
strNameToAdd = InputBox$("Name to Add", "Add Name To Combo Box")
If Len(strNameToAdd) > 0 Then
  Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  Set MyDB = CurrentDb()
  Set MyRS = MyDB.OpenRecordset("Client Names", dbOpenDynaset)
    MyRS.AddNew
      MyRS![Name] = strNameToAdd
    MyRS.Update
    MyRS.Close
End If
 
Me![cboName].Requery
 
Exit_cmdAddNameToComboBox_Click:
    Exit Sub
 
Err_cmdAddNameToComboBox_Click:
    MsgBox Err.Description, vbExclamation, "Error in cmdAddNameToComboBox()"
    Resume Exit_cmdAddNameToComboBox_Click
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.

[MSeda]

Combo boxes also have an "on not in list" event that the above suggested technique can be used in.

[NeoPa]

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.

[rgsw]

Thanks for the suggestions, will give them all a try and see which works best for the form.