Results 1 to 3 of 3
  1. #1
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128

    Updating Field in Table vs. Using Related Table

    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:




    1. Is this approach OK and if not what is a better approach?
    2. 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.
    3. 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
    Attached Files Attached Files

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    A couple of comments on your data -

    Don't keep or refer to ZIP codes as numeric data. They aren't numeric - they are 5-character codes that just happen to LOOK like numbers. It's a lot easier to validate and search if you just use 5 characters. You don't need ZipPrefill in the ZipCodes table, and ZipLength and ZlpAltPrefill in ZipAltCities.

    Your Area_Codes field should not be numeric, especially if there is more than one code. Its probably a lot easier just to keep the data as a comma-separated list, e.g 912, 815,...

    I think I would keep your structure as you have it, with the full address in the Contacts table, and use the Zip Codes table for validation or verification. One big problem there is if you are given a wrong zip code, it might be very difficult to find out what the correct one is - larger centres have a lot more than one zip code in them.

    Using a combo box for the zip code list doesn't seem very practical, especially since you have it ordered by zip code and not city. How would you ever find the one you want, except to scroll the whole list? Combo boxes are not searchable. Besides which, there is a limit on the number of rows a combo box can have. It defaults to 1,000 and can be changed but more than 1,000 isn't practical. Better might be to filter the list based on the city/state entered and show the list of possibilities. Again, that wont work for larger places that have more than one zip code.

    Be careful about the concept of "unacceptable-cities" - if you added a city to that field for a zip code, but you used the (now invalid) city name in Contacts, now you have invalid data there with no easy way of finding it.

    So, for question 2 :
    How do I best update City and State from the Zip lookup in a form
    Don't. That assumes the zip is correct and has been entered correctly. You could check for a match and display a message is there isn't one.

    how do I prevent exiting the record until the error is corrected?
    Use the Before Update event for the form to cancel the update if an error is found.

  3. #3
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128
    John_G
    Thanks for your comments. Below are mine.

    1. "Don't keep or refer to ZIP codes as numeric data."
    I actually didn't - they are text fields. Sorry if my example indicated otherwise. And yes, I don't need ZipPrefill and ZipLength. I had just used them in padding out the short (less than 5 character) zip codes (in the Zip Code Excel file I downloaded and then imported to Access) with leading zeros to be a full 5 characters .

    2.
    Area_Codes field
    They were separated by commas in the Excel download file but looks like Access treated them as numbers while importing and I wasn't paying attention. I will put the commas back in.

    3. Use of Zip Codes
    My plan is to have the Zip code provide the City and State by referencing the appropriate lookup columns, not to validate based on the City. So when you select a Zip code from the lookup list it will fill in the City and State and the user doesn't have to key those in. I can easily provide a query by City showing Zip code if that proves to be needed.

    4. Cancelling updates.
    I'm very unfamiliar with Macro and even less so VBA. Are there any examples of cancelling code I could look at? Looks like "
    CancelEvent" in a macro would do this...?


    Thanks again!






Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-23-2016, 05:00 PM
  2. Replies: 1
    Last Post: 08-27-2014, 04:16 PM
  3. Replies: 2
    Last Post: 03-13-2013, 06:30 AM
  4. Replies: 7
    Last Post: 04-25-2012, 06:25 PM
  5. Replies: 3
    Last Post: 01-31-2011, 11:47 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums