My Order form has a combobox called cboOrderStatusID.
The underlying table has 3 fields OrderStatusID, OrderStatus and OrderStatusType.
If the Order Status entered is not in the list, the attached code allows the user to add a new item. However, I would also like them to enter the OrderStatusType (which can be "Completed", "Processing" or "Not Started" - ideally they should be able to select one of these without typing it in full - perhaps from a list box.
Normally, where other fields must be completed when a new item is added, I create a popup form to enter all the details. However, in this case it seems a bit cumbersome.
Is there another way?
Private Sub cboOrderStatusID_NotInList(NewData As String, Response As Integer)
On Error GoTo err_OrderStatusID_NotInList
Dim ctl As Control
Dim strSQL As String
' Return Control object that points to combo box.
Set ctl = Me!cboOrderStatusID
' Prompt user to verify they wish to add new value.
If MsgBox(NewData & " is not in the Order Status list. Would you like to add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to table.
NewData = CapitalizeFirst(NewData)
strSQL = " INSERT INTO tblOrderStatus ( OrderStatus ) SELECT '" & Proper(NewData) & "'"
ctl.Value = NewData
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
If Err = 2113 Then
Err = 0