How to Initialize a field

[Daryl G.]

I have A table ZipCityStateCounty which contains a list of every Zip Code
and it's corresponding City County and State. The Key is the ZipCode

I have another Table Addresses, which contains the fields Address1,
Address2,City,State,Zip

I have built a lookup from the Address.Zip field to the
ZipCityStateCounty.Zipcode field, limited to the list which is working as
expected.

I have built a Query in the City and State fields on the Address field which
returns the City and State respectively where
Address.Zip=ZipCityStateCounty.Zipcode

This works great for the first record, however when I enter a second record
(just directly into a table, no forms developed yet) Access returns the City
and State values that were valid from the first record. This value appears
to be retained as I can enter an unlimited number for records and still only
return the values from the first record I enter.

The purpose of this is to Allow the Address field to have a list box that
will select the City or State from the master Zipcode table, but allow for
an override. (Some Zip Codes are valid for more than one city, and although
it gets delivered, mailing with the wrong city is not professional looking.)
That is why I want to store State and City information on the Address even
though it is redundant.

Please help me figure out how to initialize the query so that it reads the
Address.Zip field from the current Record.

Thanks,

Daryl

 

[Arvin Meyer]

This works great for the first record, however when I enter a second record
(just directly into a table, no forms developed yet) Access returns the City
and State values that were valid from the first record. This value appears
to be retained as I can enter an unlimited number for records and still only
return the values from the first record I enter.

And therein lies your problem. You cannot run code from a table so you can
tell the combo box to requery with each new record. If you use a continuous
or datasheet form, you'll have a different problem. You will requery the
rowsource and change the underlying data to return only the one record.
Every other record in the form will show blank because they are no longer
part of the same rowsource.

In order to do want you want, you need to use a single form view and requery
the rowsource in the after update event of the zipcode control.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access