Thanks all. I figured it out with a little reference reading.
The codes are:
for the Main form -
Code:
Private Sub City_NotInList(NewData As String, Response As Integer)
Dim strType As String, strWhere As String
'User has typed in a city name that does not exist
strType = NewData
'Set up the test predicate
strWhere = "[City] = """ & strType & """"
'Ask if they want to add this city
If MsgBox("City " & NewData & " is not found. " & "Do you want to add this City?", vbYesNo + vbQuestion + vbDefaultButton2, gstrAppTitle) = vbYes Then
'Yup, Open the product add form and pass it the new name
DoCmd.OpenForm "CityAdd", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData
'DoCmd.OpenForm "formname", , , "EmployeeID=" & Me.EmployeeID
'Verify that City really was added
If IsNull(DLookup("CityID", "CityTable", strWhere)) Then
'Nope
MsgBox "You failed a City that matched what you entered." & "Please try again.", vbInformation, gstrAppTitle
'Tell Access to continue - we trapped the error
Response = acDataErrContinue
Else
'City added OK - tell Access that Field gets requeried
Response = acDataErrAdded
End If
Else
'Do not want to add - let Access display normal error
Response = acDataErrDisplay
End If
End Sub
and for the CityAdd form it is -
Code:
Private Sub Form_Load()
Me.City = Me.OpenArgs
End Sub