A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup to show on form



 
 
Thread Tools Display Modes
  #1  
Old June 25th, 2009, 09:55 PM posted to microsoft.public.access.tablesdbdesign
Elizabeth
external usenet poster
 
Posts: 208
Default Lookup to show on form

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.
--
Thanks for any help you can give!

Elizabeth
  #2  
Old June 25th, 2009, 10:30 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Lookup to show on form

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" wrote in message
...
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.
--
Thanks for any help you can give!

Elizabeth



  #3  
Old June 25th, 2009, 11:20 PM posted to microsoft.public.access.tablesdbdesign
Elizabeth
external usenet poster
 
Posts: 208
Default Lookup to show on form

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


"Jeff Boyce" wrote:

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" wrote in message
...
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.
--
Thanks for any help you can give!

Elizabeth




  #4  
Old June 26th, 2009, 04:17 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Lookup to show on form

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

"Elizabeth" wrote in message
...
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


"Jeff Boyce" wrote:

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" wrote in message
...
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.
--
Thanks for any help you can give!

Elizabeth






  #5  
Old June 26th, 2009, 05:19 PM posted to microsoft.public.access.tablesdbdesign
Elizabeth
external usenet poster
 
Posts: 208
Default Lookup to show on form

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.
--
Thanks for any help you can give!

Elizabeth


"Jeff Boyce" wrote:

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

"Elizabeth" wrote in message
...
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


"Jeff Boyce" wrote:

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" wrote in message
...
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.
--
Thanks for any help you can give!

Elizabeth






  #6  
Old June 26th, 2009, 06:47 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Lookup to show on form

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" wrote in message
...
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.
--
Thanks for any help you can give!

Elizabeth


"Jeff Boyce" wrote:

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

"Elizabeth" wrote in message
...
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


"Jeff Boyce" wrote:

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" wrote in message
...
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.
--
Thanks for any help you can give!

Elizabeth








  #7  
Old June 26th, 2009, 07:11 PM posted to microsoft.public.access.tablesdbdesign
Elizabeth
external usenet poster
 
Posts: 208
Default Lookup to show on form

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.
--
Thanks for any help you can give!

Elizabeth


"Jeff Boyce" wrote:

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" wrote in message
...
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.
--
Thanks for any help you can give!

Elizabeth


"Jeff Boyce" wrote:

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

"Elizabeth" wrote in message
...
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


"Jeff Boyce" wrote:

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" wrote in message
...
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.
--
Thanks for any help you can give!

Elizabeth









  #8  
Old June 26th, 2009, 07:44 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Lookup to show on form

see comments in-line below...

"Elizabeth" wrote in message
...
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?


Yes ... and this is in the TABLE! Putting it there is the root cause of all
the difficulty you're having!

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.


And again, trying to do this is the table is discouraging, no? Read some of
the other posts in this newsgroup on the subject and see WHY its
discouraged.

If you insist on doing it this way, you'll have to discover work-arounds.
Doing it the way suggested means no more work-arounds.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

--
Thanks for any help you can give!

Elizabeth



  #9  
Old June 26th, 2009, 07:45 PM posted to microsoft.public.access.tablesdbdesign
Steve[_72_]
external usenet poster
 
Posts: 190
Default Lookup to show on form

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





"Elizabeth" wrote in message
...

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.
--
Thanks for any help you can give!

Elizabeth



  #10  
Old June 30th, 2009, 03:18 PM posted to microsoft.public.access.tablesdbdesign
Elizabeth
external usenet poster
 
Posts: 208
Default Lookup to show on form

I have managed to move the combo box from the table to the form. It shows
all four columns in the drop down, but I still can't get them to display on
the form. The column count is set to 4 and the SQL is "SELECT
[Zips_US].[Zip], [Zips_US].[City], [Zips_US].[St], [Zips_US].[County] FROM
[Zips_US] ORDER BY [Zip];". Is it even possible to show all four fields on
the form or am I just wasting my time?
--
Thanks for any help you can give!

Elizabeth


"Jeff Boyce" wrote:

see comments in-line below...

"Elizabeth" wrote in message
...
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?


Yes ... and this is in the TABLE! Putting it there is the root cause of all
the difficulty you're having!

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.


And again, trying to do this is the table is discouraging, no? Read some of
the other posts in this newsgroup on the subject and see WHY its
discouraged.

If you insist on doing it this way, you'll have to discover work-arounds.
Doing it the way suggested means no more work-arounds.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

--
Thanks for any help you can give!

Elizabeth




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:02 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.