NotInList events

[Steve Leferve]

Can someone tell what what objects I have to work with on the 'NotInList'
event? Basically I want to have a pop-up prompt the user if they want to add
the data they typed into the database.

Steve Lefevre

[fredg]

On Tue, 20 Apr 2004 17:45:14 -0400, Steve Leferve wrote:

Can someone tell what what objects I have to work with on the 'NotInList'
event? Basically I want to have a pop-up prompt the user if they want to add
the data they typed into the database.

Steve Lefevre

Steve,
There are several different ways to add data.
It depends on what else you need to do.
The following code in the [City] combo box NotInList event will update
the City field with a new city name. Adapt it to your own needs.
Watch out for word wrap on some of the longer lines.

On Error GoTo Err_City_NotInList
Dim IntResponse As Integer
' Prompt user to verify they wish to add new value.
IntResponse = MsgBox("You entered a City which is not in the list." &
vbNewLine & "Do you wish to add this City? Y/N ", vbQuestion + vbYesNo
+ vbDefaultButton2, "City Not Listed")
If IntResponse = vbYes Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in the NewData argument to City table
NewData = StrConv(NewData, vbProperCase)

CurrentDb.Execute " INSERT INTO tblCities(txtCity) SELECT " &
chr(34) & NewData & chr(34) & ";",dbFailOnError
Me!City = NewData
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
Me!City = Null
End If

Exit_City_NotInList:
Exit Sub

Err_City_NotInList:
If Err = 2113 Then
Err = 0
Resume Next
Else
MsgBox Str(Err) & " " & Err.Description
Resume Exit_City_NotInList
End If

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.