Lookup to show on form

[Elizabeth]

I have a table with zip code, state, city, and county. The ZipID is used as
a lookup in another table. How do I get all four columns to show on the
form? I am increddibly new at this and teaching myself as I go. Please be
specific.

 

[Jeff Boyce]

Elizabeth

It sounds like you've discovered (re-discovered) one of the many reasons
folks here in the newsgroup recommend against using the Lookup data type in
table definitions.

Access tables store data, Access forms (and reports) display data. Don't
fall into the trap of trying to use Access tables as if they were
spreadsheets -- they aren't.

The way you'll find most folks here handle "lookup" is to store the foreign
key value in your main table, then use a combobox in a form to "look up"
values. That way, you can see "all four columns" when you make your choice.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Elizabeth]

I'm sorry. I used the wrong terminology. The ZipID is a combobox, not a
lookup. When I look at it in the table, I see all 4 columns and everything
looks correct. However, when I switch to the form, it does not give me the
combobox (right word?) to choose from. I have another field that I had setup
originally as a combobox that shows correctly on the form and the drop down
list is there to choose from. I designed the form before I switched the Zip
to a combobox and I cannot figure out how to correct it without having to
start the form over from scratch.
--
Thanks for any help you can give!

Elizabeth

Elizabeth

It sounds like you've discovered (re-discovered) one of the many reasons
folks here in the newsgroup recommend against using the Lookup data type in
table definitions.

Access tables store data, Access forms (and reports) display data. Don't
fall into the trap of trying to use Access tables as if they were
spreadsheets -- they aren't.

The way you'll find most folks here handle "lookup" is to store the foreign
key value in your main table, then use a combobox in a form to "look up"
values. That way, you can see "all four columns" when you make your choice.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

In this case, I suspect that if you open that table in design view, you'll
see that the ZipID field is defined as a "Lookup" data type, which displays
as a combobox when you open the table.

To reduce confusion (yours and anyone elses who might have to understand
this), consider changing the datatype to the type appropriate to the
underlying looked-up table ID. Then, in your form, convert the textbox
control to a combobox, "feed" it from the underlying (Zip) table, and bind
it to the ZipID field in your "main table".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm sorry. I used the wrong terminology. The ZipID is a combobox, not a
lookup. When I look at it in the table, I see all 4 columns and
everything
looks correct. However, when I switch to the form, it does not give me
the
combobox (right word?) to choose from. I have another field that I had
setup
originally as a combobox that shows correctly on the form and the drop
down
list is there to choose from. I designed the form before I switched the
Zip
to a combobox and I cannot figure out how to correct it without having to
start the form over from scratch.

 

[Elizabeth]

How do I go about converting the textbox control? I do not see anything in
the properties that will allow me to select combobox. I started a new form
and now have the drop down, but I still can't see all four columns. I am
afraid that this will confuse anyone entering information.

 

[Jeff Boyce]

Elizabeth

A combobox displays what you tell it to display. What's the SQL of the row
source for your combobox?

And how many columns have you told Access to display for it? (these are
properties of the combobox)

When you say "I still can't see all four columns", do you mean when you drop
the list down, or after making your selection?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Elizabeth]

This is where I keep getting lost because I've never done anything like this
before. I don't know what the SQL is or where to find it. Would that be the
"SELECT [Zips_US].[Zip], [Zips_US].[City], [Zips_US].[St], [Zips_US].[County]
FROM [Zips_US] ORDER BY [Zip];" that is listed as Row Source under the lookup
tab of the table?
I also can't find where to set the number of columns to display. I have the
column count as 4 in the lookup tab. When I go to the drop down list, all 4
columns appear. It is after I have made the selection that I am having
trouble with.

 

[Steve]

Hello Elizabeth,

When you say "the form" I assume you have something like a customer table
that records each customer's address and his ZipID (from a Zip table). And
now you want to have a form that displays a customer's name and his city,
county, state and zipcode. BTW, your tables seem to be set up correctly. To
get the form you want, first create a query that includes both the customer
table and the Zip table. Pull down into the query grid all the customer
fields from the customer table you want then pull down city, county, state
and zipcode from the Zip table. Now use the form wizard to create the form
you want.

Steve
(e-mail address removed)