Graceful/Efficient Not In List Event

[Guest]

I have a form that has a unbound combo box control to select a Project with a
tab control to hold the Project information on. I'm looking to use the combo
boxes Not In List event to allow the user to enter new projects and find
existing projects.

With the following code, I'm able to get the project added, but then the
user has to reselect the project from the list. I think it should be easier
than that.

I've tried several different approaches, like adding the project and
requerying the combo box and setting the value to the NewData. But I always
end up with either an error that says I must save the existing value before
entering a new value, or I'm able to get the combo box to accept the value,
but the tab control doesn't reflect a blank record anymore.

Does anyone have any thoughts, ideas, or possibly a code chunk they are
willing to share?

Thanks in advance.

Mark

Private Sub cmbProjectID_NotInList(NewData As String, Response As Integer)
'add new project to the projects tables
On Error GoTo Err_Handler

Dim strMsg As String
Dim strSQL As String
Dim lngNewRecordID As Long

'prompt to add record
strMsg = "The project " & NewData & " does not exist in the projects
table." _
& vbCrLf & vbCrLf & "Do you wish to add this project?"

If MsgBox(strMsg, vbYesNo, "Add A New Project") = vbYes Then

'insert the new record into the table
strSQL = "INSERT INTO tPM_Projects (Project) VALUES('" & Trim(NewData) &
"')"
DoCmd.RunSQL strSQL, False

cmbProjectID.Value = DLookup("[ProjectID]", "tPM_Projects",
"[Project]='" & Trim(NewData) & "'")

Me.Requery

Else

cmbProjectID.Value = Null

End If

Response = 0

Exit_Routine:

Exit Sub

Err_Handler:

strSQL = "The following error occurred adding the new project to the
table." _
& vbCrLf & vbCrLf & Err.Number & " " & Err.Description

MsgBox strSQL, vbCritical, "Error Adding Project"

Resume Exit_Routine

End Sub

 

[Douglas J. Steele]

You must set Response to acDataErrAdded if you've added the value.

Check http://www.mvps.org/access/forms/frm0015.htm at "The Access Web" for a
working example.