Right now I have a subform with a combobox that pulls it's data from a table. I want the user to either select an existing item or type in a new item and have a macro create the new table row. What I have right now works in the sense that it prompts the user if they want to creat a new item and the new item is created (and I can see it in the combobox list), but I'm still getting an error saying that the item does not exist in the table forcing the user to manually select the newly created list item they just typed in.
edit: It appears that the new item doesn't always show up automatically and requires the form to be refreshed, so now I need to figure out how to get it to consistently appear right away without a refresh.
Code:
Private Sub MaterialCostCode_NotInList(NewData As String, Response As Integer)
Dim rst As DAO.Recordset
'Update value list with user input.
On Error GoTo ErrHandler
Dim bytUpdate As Byte
bytUpdate = MsgBox("Do you want to add " & _
Me![MaterialCostCode].Text & " to the list?", _
vbYesNo, "Cost code does not exist!")
'Add user input
If bytUpdate = vbYes Then
Set rst = CurrentDb.OpenRecordset("MaterialCostCode", dbOpenTable)
rst.AddNew
rst!Material = Forms![Material]![MaterialId]
rst!CostCode = Me![MaterialCostCode].Text
rst.Update
rst.Close
Set rst = Nothing
ElseIf bytUpdate = vbNo Then
Response = acDataErrContinue
Me![MaterialCostCode].Undo
End If
Exit Sub
ErrHandler:
MsgBox Err.Number & ": " & Err.Description, _
vbOKOnly, "Error"
End Sub
edit: It appears that the new item doesn't always show up automatically and requires the form to be refreshed, so now I need to figure out how to get it to consistently appear right away without a refresh.