Open a Form with Combo Box NotInList Event

[Guest]

I would like to open a data entry Form with Combo Box NotInList Event.
When the user types a value that doesn't exist in the table, a message
appears, asking if the user wants to create this new entry.
If the user selects no, the dialog box closes, and entry can be performed to
the combo box.
If the user answers yes, the value that is typed in the combo box, and which
does not exist in the database should appear as the default value. Then the
user is prompted to save the value, and exit the data entry form. The combo
box should be updated with the new value.
The combo box needs to be requeried to accept the value.
Here is my code, but it is not working correctly:

Private Sub Cost_Center_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim rst As Recordset
Dim Db As Database




strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Company") Then
Response = acDataErrDisplay

Else

DoCmd.OpenForm "frmCostCenter", , , , acFormAdd
Cost_Center.Requery
DoEvents
End If

End Sub

 

[Brian Bastl]

John,
This might get you closer:

Private Sub Cost_Center_NotInList(NewData As String, Response As Integer)
Dim strMsg As String

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Company") Then
Response = acDataErrContinue
Me.Undo

Else

DoCmd.OpenForm "frmCostCenter", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
End If

End Sub

'****************

frmCostCenter load event:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.MyCostCenterControl = Me.OpenArgs
End If
End Sub

HTH,
Brian