Help with update query and unbound combobox

[Rick Wright]

Hi - I have created a form that will collect employee information. On this
form I have a field for City, State and Zipcode that will be stored in the
employee table. I also have a table called zipcodes that contains every
zipcode in the U.S. along with the City and State associated with that
zipcode. What I'm trying to do is when the user enters the zipcode the
record in the employee table will be updated with the correct City, State
and Zipcode that is in the zipcode table. I created an update query
"FindZipcode" with the parameter/criteria "Enter your zipcode". When I run
the query it asks for the zipcode, I enter 95662 and the query tells me that
it is updating 1 record. When the query closes I look in the employee table
and the correct City, State and zipcode are in the proper place. But, when I
try to create an unbound combobox with the wizard it tells me "No valid
fields can be found in 'FindZipcode'. Please select a new source." Any and
all help or suggestions are greatly appreciated.

 

[KARL DEWEY]

What I'm trying to do is when the user enters the zipcode the record in the
employee table will be updated with the correct City, State and Zipcode that
is in the zipcode table.
No need to do that as it is a waste of processing time and storage space.
Just join your zipcode table each time you want the city and state.

 

[Rick Wright]

Karl - Thank you for your response. What do you mean join the zipcode table
each time I want the city and state? Is that the same as joining the fields
in the relationship window which I have done, but I still can't get the city
and state to display in the form. - Rick

 

[Richard]

Hi Rick

What I'll do is create a combo with the zipcode table as underlying query,
and on update, populate the City and State field (unbound, just to show) and
save just the zipcode to the table. We don't need to save the city and state
field. For reports, just link the zipcode to the zipcode table and display
the city and state accordingly.

Hope this helps
Richard

 

[KARL DEWEY]

In your query join the tables on zipcode and pull fields from zipcode table
for city and state for the form.