Ludique
asked on
Not In List - Add Item with 2 data fields
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?
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) & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
ctl.Value = NewData
DoCmd.SetWarnings True
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
exit_cboOrderStatusID_NotInList:
Exit Sub
err_OrderStatusID_NotInList:
If Err = 2113 Then
Err = 0
Resume Next
Else
MsgBox Str(Err)
MsgBox Err.Description
Resume exit_cboOrderStatusID_NotInList
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER