M
MikeF
In my Companies form, there is a CityID field.
In that field, when I type a city that is not in the Cities table, a form
pops up that ...
- adds a new CityID to tblCities
- adds the city string to the City field in tblCities
- allows me to enter from lookup drop-down the StateID / CountryID /
MetroID fields from tblCities.
But when that popup form is closed, only the city name is there in the main
form.
It needs to immediately/automatically have the State / Country / Metro
fields filled in as well.
Have tried numerous "requery" routines, but can't seem to nail it.
Can anyone assist?
*** The city popup form is frmCities, based on tblCities.
*** The main form comes from a query - qryFrmCompany.
All fields from tblCities are in that query as well.
*** A copy of the NotInList routine from the frmCompany's CityID field is
below.
Thanx,
- Mike
Private Sub CityID_NotInList(NewData As String, Response As Integer)
' Get confirmation of whether new Cities to be added to list
Dim strMessage As String, strDocName As String, strLinkCriteria As String
Dim dbsSmr As Database
Dim rstCities As Recordset
strMessage = "Do you wish to add " & NewData & _
" to the list of Cities?"
If MsgBox(strMessage, vbOKCancel + vbQuestion) = vbOK Then
' Open recordset of the tblCities table and add the NewData value
Set dbsSmr = CurrentDb()
Set rstCities = dbsSmr.OpenRecordset("tblCities")
rstCities.AddNew
rstCities!City = NewData
rstCities.Update
Response = acDataErrAdded ' Requery the list
' Open form to add data etc. for new Cities.
' Store form name in variable
strDocName = "frmCities"
' Set link criterion to Cities field = new Cities name
strLinkCriteria = "City = '" & NewData & "'"
' Open the form at newly added record
DoCmd.OpenForm strDocName, , , strLinkCriteria
Else
' Return to the form and undo edit
Response = acDataErrContinue
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If
End Sub
In that field, when I type a city that is not in the Cities table, a form
pops up that ...
- adds a new CityID to tblCities
- adds the city string to the City field in tblCities
- allows me to enter from lookup drop-down the StateID / CountryID /
MetroID fields from tblCities.
But when that popup form is closed, only the city name is there in the main
form.
It needs to immediately/automatically have the State / Country / Metro
fields filled in as well.
Have tried numerous "requery" routines, but can't seem to nail it.
Can anyone assist?
*** The city popup form is frmCities, based on tblCities.
*** The main form comes from a query - qryFrmCompany.
All fields from tblCities are in that query as well.
*** A copy of the NotInList routine from the frmCompany's CityID field is
below.
Thanx,
- Mike
Private Sub CityID_NotInList(NewData As String, Response As Integer)
' Get confirmation of whether new Cities to be added to list
Dim strMessage As String, strDocName As String, strLinkCriteria As String
Dim dbsSmr As Database
Dim rstCities As Recordset
strMessage = "Do you wish to add " & NewData & _
" to the list of Cities?"
If MsgBox(strMessage, vbOKCancel + vbQuestion) = vbOK Then
' Open recordset of the tblCities table and add the NewData value
Set dbsSmr = CurrentDb()
Set rstCities = dbsSmr.OpenRecordset("tblCities")
rstCities.AddNew
rstCities!City = NewData
rstCities.Update
Response = acDataErrAdded ' Requery the list
' Open form to add data etc. for new Cities.
' Store form name in variable
strDocName = "frmCities"
' Set link criterion to Cities field = new Cities name
strLinkCriteria = "City = '" & NewData & "'"
' Open the form at newly added record
DoCmd.OpenForm strDocName, , , strLinkCriteria
Else
' Return to the form and undo edit
Response = acDataErrContinue
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If
End Sub