Create new record in a drop down box.

[kcddoorman]

I have a main order table that gets some of its information from several look up tables. A create a look up relationship between the two tables and that allows me to choose a record. I can create another form that allow me edit the individual lookup tables so I can enter new clients, job, employees, etc.
I'd like to add a some functionality to the drop down boxes. Can you have a "New" record in the drop down box that lets you enter data? The way I can do it now is have a button next to the drop down box that opens the corresponding add/edit/delete form.

Thank You,
James

[Jim Doherty]

I have a main order table that gets some of its information from several look up tables. A create a look up relationship between the two tables and that allows me to choose a record. I can create another form that allow me edit the individual lookup tables so I can enter new clients, job, employees, etc.
I'd like to add a some functionality to the drop down boxes. Can you have a "New" record in the drop down box that lets you enter data? The way I can do it now is have a button next to the drop down box that opens the corresponding add/edit/delete form.

Thank You,
James

Look at the NotInList event for combobox (dropdowns) The idea being that if something is NOT in the list then the user is asked if they wish to add there and then. You must set the LimitToList property of the combobox to YES to make this work.

The following example procedure gives you an idea of the code that needs to be in 'NotInList' event procedure for the combobox.

Obviously amend the displayed detail of this to suit your purposes...

 
Private Sub cboComboBox_NotInList(NewData As String, 
Response As Integer)
	On Error GoTo cboComboBox_NotInList_Err
	Dim intAnswer As Integer
	Dim strSQL As String
	intAnswer = MsgBox("The value " & Chr(34) & NewData & _
		Chr(34) & " is not currently listed." & vbCrLf & _
		"Would you like to add it to the list now?" _
		, vbQuestion + vbYesNo, "Add New lookup value")
	If intAnswer = vbYes Then
		strSQL = "INSERT INTO MyTable([MyFieldName]) " & _
				 "VALUES ('" & NewData & "');"
		DoCmd.SetWarnings False
		DoCmd.RunSQL strSQL
		DoCmd.SetWarnings True
		MsgBox "The new value has been added to the list." _
			, vbInformation, "Value Added"
		Response = acDataErrAdded
	Else
		MsgBox "Please choose a value from the existing list." _
			, vbInformation, "Lookup Value Selection Required"
		Response = acDataErrContinue
	End If
cboComboBox_NotInList_Exit:
	Exit Sub
cboComboBox_NotInList_Err:
	MsgBox Err.Description, vbCritical, "Error"
	Resume cboComboBox_NotInList_Exit
End Sub

Regards

Jim :)

[ADezii]

I have a main order table that gets some of its information from several look up tables. A create a look up relationship between the two tables and that allows me to choose a record. I can create another form that allow me edit the individual lookup tables so I can enter new clients, job, employees, etc.
I'd like to add a some functionality to the drop down boxes. Can you have a "New" record in the drop down box that lets you enter data? The way I can do it now is have a button next to the drop down box that opens the corresponding add/edit/delete form.

Thank You,
James

What you describe can actually be implemented, and as a matter of fact, I actually like the idea under certain circumstances. Let's assume your Combo Box lists all the Last Names in an Employees Table in Ascending Order. In this Combo Box you would also like a 'Add New Employee' Option. Follow these 2 simple steps based on the assumptions that I have given you:

1. Create a simple UNION Query in order to enable this functionality. Set the Row Source of the Combo Box equal to the SQL Statement.
   [CODE=sql]
   SELECT [Employees].[LastName] FROM Employees
   UNION
   Select '<Add New Employee>' From Employees Order By [Employees].[LastName];
   [/CODE]
2. Write code in the AfterUpdate() Event of the Combo Box.
   [CODE=vb]
   Private Sub cboEmployee_Aft erUpdate()
   If Me![cboNew] = "<Add New Employee>" Then
   'Open Form for entering New Employee
   Else
   'Do whatever you like
   End If
   End Sub
   [/CODE]

NOTE: <Add New Employee> is written in this fashion in order to place it as the Top Option in the Combo Box.

[kcddoorman]

Very nice, Thanks a lot!
james

[kcddoorman]

If I were to try the second method shown above using an If/Then statement to open a form to add new records how would I get that data into the field once it is filled out. I tried a afterupdate event that requeries the table but it would be better if the data popped right into the combo box.

[ADezii]

If I were to try the second method shown above using an If/Then statement to open a form to add new records how would I get that data into the field once it is filled out. I tried a afterupdate event that requeries the table but it would be better if the data popped right into the combo box.

1. Add the data to the Table via a Form.
2. Requery the Combo Box as in:
   [CODE=vb]Forms!<Your Form>!<Your Combo Box>.Requery[/CODE]