Using a join table to autofill data on a form

[Jim_H]

I had classes 20+ years ago on databases, so I understand the basic principles of tables, queries and normalizing. But other than that, I haven't built a database in 20+ years, so please bear with me as I brush off the rust. I now have a project to build a database and I need some help getting over a hump.

This particular problem deals with creating a system to autofill city, state and zip on a contact form. I already have the data and, based on advice I found in a couple other forums, I've created the following tables:

tbl_States
------------
State_ID (PK)
State

tbl_CityState
------------
City_ID (PK)
City
State (FK)

tbl_Zips
------------
Zip_ID (PK)
Zip Code



tbl_CityZip
------------
ZipID (FK)
CityID (FK)

tbl_Persons
------------
Person_ID (PK)
First Name
Last Name
ZipID (FK - tbl_CityZip.ZipID)
City
State

I have created a Contact form which uses a combo box to look up the zip code from tbl_CityZip, and it brings in the zip code from tbl_Zips just fine (displaying the zip, but storing tbl_CityZip.ZipID). What I can't figure out is how to have it automatically display the related city and state in respective text boxes. Any assistance and instructions would be appreciated. Thank you!

[Welshgasman]

I would bring in the other data into the combo source.
Then when the zip code is selected, in the AfterUpdate event of the combo, set your txt controls from the relevant columns.
I would not use the names though, but the data IDs if you are hell bent on storing them, which I would not be. You can show what you want.

[Jim_H]

No, I don't want to store the city and state names in tbl_Persons, I just want the tbl_CityZip ID to be stored. But I do want the city and state names to be displayed on the Contact form so that when the user views a record in a readable format, the city name and state name are displayed as text. That's the problem I'm having right now, though, is figuring out how to have that happen.

How do I have the zip combo box bring in the other data? And how do I setup the AfterUpdate event display the text of the city and state, but store the data IDs?

[Welshgasman]

You select that extra data in the query for the combo.
Then in the afterupdate event just set your controls

From ChatGPT.

Private Sub cmbEmployee_AfterUpdate()
    ' Set the control values based on ComboBox columns
    Me.txtFirstName = Me.cmbEmployee.Column(1) ' Column index starts at 0
    Me.txtLastName = Me.cmbEmployee.Column(2)
End Sub

[mike60smart]

Hi
Are the ZipCodes from USA or UK List ?

[Welshgasman]

Hi
Are the ZipCodes from USA or UK List ?

We do not have zipcodes?
I would also expect an address, as a zipcode can cover a fair area.

[mike60smart]

Hi
You can use the following as the Row Source for a Combobox

[davegri]

You might find this useful:
Auto enter city and state when zipcode entered

[June7]

Combobox RowSource includes columns for ZipID, City, State, ZipCode. Expressions in textboxes reference combobox columns by index. Index begins with 0 so second column is index 1:

=[cbxZip].Column(1)

This does not save those textboxes data to table.

[Jim_H]

First off, my apologies for not replying sooner, had to be away from the project for a couple days. Secondly, thank you all for offering assistance!

@June7 - your solution worked, thank you! I was tearing my hair out trying to get the event code to work.

New problem:
I have a small set of Person's test date entered for testing, and each Person has a number assigned in the ZipID column. By way of simplified example, let's say the data looks like this:

tbl_Persons
Name .................. ZipID (FK)
Bob Smith .................... 1
Mary Jones ................... 2
Jane White ................... 3
Frank Harold ................ 4

tbl_Zip
ZipID (PK)............... Zip Code
1 ............................. 20401
2 ............................. 20402
3 ............................. 20403
4 ............................. 20404
5 ............................. 20405

When I open the Contact form, all the zip codes are off by one ZipID

Name ................. Expected ................. Displayed
Bob Smith ............. 20401 .................... 20402
Mary Jones ............ 20402 .................... 20403
Jane White ............ 20403 .................... 20404
Frank Harold .......... 20404 .................... 20405

Any thoughts?

[madpiet]

Why are you even doing that with the ZipCode? I'd just store it as is unless you have other attributes in the ZipCode lookup table that would aggregate the person records somehow.

[Welshgasman]

First off, my apologies for not replying sooner, had to be away from the project for a couple days. Secondly, thank you all for offering assistance!

@June7 - your solution worked, thank you! I was tearing my hair out trying to get the event code to work.

New problem:
I have a small set of Person's test date entered for testing, and each Person has a number assigned in the ZipID column. By way of simplified example, let's say the data looks like this:

tbl_Persons
Name .................. ZipID (FK)
Bob Smith .................... 1
Mary Jones ................... 2
Jane White ................... 3
Frank Harold ................ 4

tbl_Zip
ZipID (PK)............... Zip Code
1 ............................. 20401
2 ............................. 20402
3 ............................. 20403
4 ............................. 20404
5 ............................. 20405

When I open the Contact form, all the zip codes are off by one ZipID

Name ................. Expected ................. Displayed
Bob Smith ............. 20401 .................... 20402
Mary Jones ............ 20402 .................... 20403
Jane White ............ 20403 .................... 20404
Frank Harold .......... 20404 .................... 20405

Any thoughts?

Well, you must be bringing the data in incorrectly?

What does the actual record source look like?

[Welshgasman]

Why are you even doing that with the ZipCode? I'd just store it as is unless you have other attributes in the ZipCode lookup table that would aggregate the person records somehow.

Probably trying to save disk space?

[mike60smart]

Hi
Can you upload the database with no confidential data so we can see what is happening?

[madpiet]

smaller than an enormous CHAR(5) column?? Talk about wasting time, but I guess it's a free country