To add to dropdown list on a form without going to table

[lmschilling]

Could someone please tell me how to add to a dropdown box without going to the table to add a choice that's not in the list? I see there's a On Not In List event but I'm not sure how to write the code. Any help would be greatly appreciated.

Thanks,

Lynne

 

[Guest_imported]

Assuming I understand your question, you want to be able to add new entries to your combobox if they don't exist already and you don't want to enter them in the table.

An option I came up for my employees list:

If a name is entered that is not in the list, a message box will appear asking whether they want to enter a new employee. If yes, an employee form opens up and allows them to enter the new employee's name. When they close out this window, the combobox on the original form has been updated. Here's a sample of my code used in the NotInList event (my combobox name was ResearcherID. You would put in your own combobox name)

Private Sub ResearcherID_NotInList(NewData As String, Response As Integer)
On Error GoTo Researcher_NotInList_Err
'Add a new record to the Researcher table
'and requery the ResearcherID combo box
Dim NewResearcher As Integer, MsgTitle As String, MsgDialog As Integer
Const MB_YESNO = 4
Const MB_ICONEXCLAMATION = 48
Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7
'Make sure the user really wants to add it
MsgTitle = "Researcher is not in the list"
MsgDialog = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON1
NewResearcher = MsgBox("Do you want to add the new Researcher?", MsgDialog, MsgTitle)
If NewResearcher = IDNO Then
Response = DATA_ERRCONTINUE
' Display a customized message.
MsgBox "You have chosen NOT to enter a new Researcher. Please choose a name from the list."
Else
DoCmd.OpenForm "fm: Researchers", acNormal, , , acAdd, acDialog
Response = DATA_ERRADDED
End If
Researcher_Exit:
Exit Sub
Researcher_NotInList_Err:
MsgBox Err.Description
Resume Researcher_Exit
End Sub

Just replace the variable names and Messages with your own names. I hope this helps

 

[ShortTexan]

Some Guy's suggestion is excellent.
If you have a simple list that you are wanting to add to then your table should have an Index that is Unique (Yes (No Duplicates)) on the field you are adding too. The entry field is a Combo Box. I have included the code from one of my database's where I use this. Hope this helps.

Private Sub Manufacturer_NotInList(NewData As String, Response As Integer)
Dim rs As recordset, db As database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblManufacturer", dbOpenDynaset)
rs.AddNew
rs!Manufacturer = NewData
rs.Update
Response = acDataErrAdded
End Sub

 

[jfhewitt]

This is a pretty easy routine I found for my daughter...seems less complicated than most.

This routine will let you add data to a table on which a combo box is based, on the fly. For example, the "bates" table contains numbers and a name. To add a new name to the table while entering data on the form, this routine is attached to a command button (command5 below) which opens a dialog box for entry of the new name, then adds the new name to the existing table "bates" in the field "name". The combo box must be based upon the table "bates", and "name" must be selected as the listbox field. The same routine can be used to add data to any table in the selected database and recordset.

Private Sub Command5_Click()
Dim varName As Variant 'variable must be declared
varName = InputBox("Enter New Name&quot

Dim db As Database 'The "AddNew" function works with a recordset, so 'these
Dim rst As Recordset 'settings are required to make it work.
Set db = CurrentDb
Set rst = db.OpenRecordset("bates", dbOpenDynaset)
With rst
.AddNew
!name = varName
.Update
Me.cboList.Requery 'may not be essential sine this is a table not a query
End With 'probably not a bad idea to include just in case to use
End Sub 'a query rather than a table

 

[lmschilling]

Thanks to all for the excellent help with my question.