My apologies if this is a duplicate post as I didn't see what I thought i posted a few hours ago.
=======================
As a follow on to my prior post on Zip codes, I am interested in advice on whether or not to put City and State in their respective fields in the "target" tables (Students, Contacts) that are looked up via the Zip code or to simply get that information from the Zip code table when needed via query. The alternate city (the focus of my prior post) will be a field in the target tables with a lookup to the ZipAltCity table created thanks to the code supplied.
In another database I created from scratch, I just kept the Zip code in the Client table and used a query joined to the Zip code table to display City and State and also had a separate field for alternate city.
In the target tables in this database there is already City and State information. On the Students table I created a query that indicated only 44 of 1150 City field values didn't match what was in the Zip code table. But if I abandon that field in favor of the City field in the Zip code table, then any objects that have that field would have to be changed. So, I think I'd prefer to have the Zip code lookup in a form actually update the City and State fields in the target tables.
My questions, therefore, are:
- Is this approach OK and if not what is a better approach?
- How do I best update City and State from the Zip lookup in a form? I did make a stab at setting the City default value to that of the combo box containing the Zip table city from Column 1 of the Zip field lookup but that did not work. I then guessed at a VBA statement (City = Me.CityFmLookup) for an After Update event on the cboZip field. This does update the City field but I have to Refresh All or advance to another record to see it reflected in the table. The attachment reflects this.
- I would like to flag as an error when the zip code for an alternate city doesn't match the primary zip code in the target record (eg., if the target record's zip is changed). If I have a macro that displays a message under this condition, how do I prevent exiting the record until the error is corrected?
As always, any comments would be appreciated.
Thanks