Thank you NeoPa and Stewart for your responses. I think this may reveal a flaw in my understanding of how forms can /should connect. Let me give a specific example and see if that helps to shed some more light on it.
I have two forms “frmAddSites” and “sfrmAddr1” that are linked in a form/subform relationship:
“frmAddSites” has a control source, tblSites.
The table, tblSites, includes:
SiteID – primary key, autonumber
SiteName – text
Addr1LKUP – number, foreign key to an address table called “tluAddr1”.
(and a few other descriptive fields)
The table, tluAddr1, includes the fields:
Addr1ID – primary key, autonumber
Addr1 – text
ZipCodeLKUP – number, foreign key to a zipcode table tluZipCode.
The table, tluZipCode, holds the city and state information for each zip code:
Zipcode – primary key
PostalCityLKUP – number, foreign key to tluPostalCity
StateLKUP – number, foreign key to tluState
“sfrmAddr1” has a control source, qryAddr1.
The query, qryAddr1, holds all of the address information from tluAddr1 and associated lookup tables (see above):
Addr1ID
Addr1
PostalCity
State
ZipCode
“frmAddSites” has the following controls:
txtSiteID - text box for SiteID
txtSiteNm - text box for SiteName
cboAddr1 - combo box for Addr1LKUP.
The row source for the cboAddr1 is qryAddr1 (bound column is Addr1ID, visible column is Addr1)
“sfrmAddr1” has the following controls:
txtAddr1ID - text box for Addr1ID
txtCity - text box for PostalCity
txtState - text box for State
txtZipCode - text box for ZipCode
The two forms are linked by
Master Field Addr1LKUP
Child Field Addr1ID
The functionality I am hoping for is to be able to choose an address from the cboAddr1 on the main form (frmAddSites) and have the subform (sfrmAddr1) automatically display the associated City, State and ZipCode.
The code Me.Requery in the AfterUpdate Event of cboAddr1 requeries the entire form, but also jumps to the first record.
The code Me.sfrmAddr1.Requery does not seem to do anything.
If I change the selection in cboAddr1 on a record, then move to another record, and go back to the record where I changed the combo box selection, the correct record on the subform is displayed.
Does this make more sense?
Is this inherently a problematic way to link a form and subform?
Is there a way to make the subform display the correct record when the linked field changes in the main form?
Thanks again,
Bridget