Add new record function of my city combo box not working

[Xine]

Working: citycombo "AfterUpdate" auto populates the Area, Community and Province fieilds
Working: citycombo "Not on list" event allows you to update tbl CityArea
Not Working: "Not on List" is to apply the new City, Area, Community and Province to the form ----instead I get "Please try again" message (re: If IsNull(result))

Can anyone see the error in the code below?

--------------------

Private Sub citycombo_AfterUpdate()

Me.Area = Me.citycombo.Column(2)
Me.Community = Me.citycombo.Column(3)
Me.OrgProv = Me.citycombo.Column(4)

If Me.citycombo = "" Then
Me.Area = ""
Me.Community = ""
Me.OrgProv = ""
End If



End Sub

Private Sub citycombo_NotInList(NewData As String, Response As Integer)

Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13) ' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new city.
Msg = "'" & NewData & "' is not in the list of cities." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the CityArea form in data entry
' mode as a dialog form, passing the city name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in CityArea form's Form_Load event
' procedure.
DoCmd.OpenForm "frmCityArea", , , , acAdd, acDialog, NewData
End If

' Look for the city the user created in the Org_Don form.
Result = DLookup("[City]", "tbl CityArea", _
"[OrgCity]='" & NewData & "'")

If IsNull(Result) Then
' If the city was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the customer was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If

End Sub

[rpeare]

in the ON EXIT property of the form frmCityArea issue REFRESH (this may not be needed on some versions of access and whether or not you're using bound forms) command and requery the combo box on the original form (i.e. forms!frm_main!comboName.requery)

[Xine]

I changed a field name (in orange below) and it's ALMOST working.
Now, the "Not In List" event triggers, I select "yes" from the message box and enter new data into the linked form (frmCityArea), BUT then "Not in List" message box pops up again and when I choose "no", that's when the new info I've entered populates my form.

Any suggestions as to why this is happening?


Private Sub citycombo_AfterUpdate()

Me.Area = Me.citycombo.Column(2)

Me.Community = Me.citycombo.Column(3)

Me.OrgProv = Me.citycombo.Column(4)

If Me.citycombo = "" Then

Me.Area = ""
Me.Community = ""
Me.OrgProv = ""

End If

End Sub


Private Sub citycombo_NotInList(NewData As String, Response As Integer)

Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13) ' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new city.
Msg = "'" & NewData & "' is not in the list of cities." & CR & CR
Msg = Msg & "Do you want to add it?"

If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then

' If the user chose Yes, start the CityArea form in data entry
' mode as a dialog form, passing the city name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in CityArea form's Form_Load event
' procedure.

DoCmd.OpenForm "frmCityArea", , , , acAdd, acDialog, NewData

End If

' Look for the city the user created in the form.
Result = DLookup("[City]", "tbl CityArea", _
"[City]='" & NewData & "'")

If IsNull(Result) Then

' If the city was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"

Else

' If the customer was created, set the Response argument to
' indicate that new data is being added.

Response = acDataErrAdded

End If

End Sub