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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|