View data from related table in Forms

[HLCruz via AccessMonster.com]

I have consistently had a hard time understanding the way Access uses related
data in forms. My current problem is that I have a table for zip codes that
I would like to use to popluate various forms . This is so that I only need
to store a zip code in my client information table and so that my users only
need to enter a zip code to see a city, state and county. However it appears
that I cannot do this by adding the table to my forms' query because this
makes my query unable to update. I have asked this question before and most
replies suggest I use DLookUp, but since I still don't understand how Access
uses relationships, I have been unable to troubleshoot this when it doesn't
work.

If there's anyone who could explain this or knows of a good online resource I
would really appreciate it!

Thank You!

 

[Jeff Boyce]

In your form, add a combo box that looks at the source table for zip codes.
Don't add the zip code table to the query underlying the form.

By the way, in my vicinity, I have a zip code that spans two cities. How
will your application handle that condition?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[HLCruz via AccessMonster.com]

Thanks Jeff, is there a way that I can set my combo box up so that it will
populate based on the relationship between my information table & my zips
table? I don't want my users to be able to select a city or state, I want
them to enter a zip code only.

I do have a couple known instances where a zip code spans two cities, however,
both are for suburbs of a large metro. Our postal service delivers the mail
fine, and since this is the main purpose of our database, we haven't run in
to any problems. Do you experience problems with this?

In your form, add a combo box that looks at the source table for zip codes.
Don't add the zip code table to the query underlying the form.

By the way, in my vicinity, I have a zip code that spans two cities. How
will your application handle that condition?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have consistently had a hard time understanding the way Access uses
related

[quoted text clipped - 20 lines]

Thank You!

 

[Jeff Boyce]

What is it that you are trying to "populate"? If you have a zip code, don't
you already have (via your zips table) the city/state/ info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff, is there a way that I can set my combo box up so that it will
populate based on the relationship between my information table & my zips
table? I don't want my users to be able to select a city or state, I want
them to enter a zip code only.

I do have a couple known instances where a zip code spans two cities,
however,
both are for suburbs of a large metro. Our postal service delivers the
mail
fine, and since this is the main purpose of our database, we haven't run
in
to any problems. Do you experience problems with this?

In your form, add a combo box that looks at the source table for zip
codes.
Don't add the zip code table to the query underlying the form.

By the way, in my vicinity, I have a zip code that spans two cities. How
will your application handle that condition?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have consistently had a hard time understanding the way Access uses
related

[quoted text clipped - 20 lines]

Thank You!

 

[HLCruz via AccessMonster.com]

Yes, the trouble I seem to be having is getting that city and state data to
appear on my forms. Ideally I would like for the city and state to "appear"
after the Zip code field on my form is updated, for a new record. On
existing records, I would like for the form to show the city and state based
on the existing zip code in that field.

In my mind this seems like the most simple example of using related data ...
but I can't seem to get the data to appear on the form.

What is it that you are trying to "populate"? If you have a zip code, don't
you already have (via your zips table) the city/state/ info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff, is there a way that I can set my combo box up so that it will
populate based on the relationship between my information table & my zips

[quoted text clipped - 26 lines]

 

[Jeff Boyce]

Wait... "for a new record" implies that you want to (redundantly) store the
same information your zip table already holds.

If you only wish to SEE the city/state data for the zip you've selected on
your form, you can use something like:
Me!txtCity = Me!cboSelectZip.Column(1)
Me!txtState = Me!cboSelectZip.Column(2)
in the AfterUpdate event of the SelectZip combo box, where the second and
third columns of data in the combo box are the values for city and state
(the "Column()" property is zero-based).

But you should NOT be trying to add the city/state values to new records.

You can display the city/state info for existing records if you add
something like:
Call cboSelectZip_AfterUpdate()
to your OnCurrent event for the form.

Note: names of controls have been changed to protect the innocent ... use
your controls' names.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes, the trouble I seem to be having is getting that city and state data
to
appear on my forms. Ideally I would like for the city and state to
"appear"
after the Zip code field on my form is updated, for a new record. On
existing records, I would like for the form to show the city and state
based
on the existing zip code in that field.

In my mind this seems like the most simple example of using related data
...
but I can't seem to get the data to appear on the form.

What is it that you are trying to "populate"? If you have a zip code,
don't
you already have (via your zips table) the city/state/ info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff, is there a way that I can set my combo box up so that it
will
populate based on the relationship between my information table & my
zips

[quoted text clipped - 26 lines]

Thank You!