CategoryID, name in combo box.. why does it "work"?

[gonzo]

Newbie, but not finding the answer _how_ this works.. Just can't seem
to get it.

given tables Object(*ID, Name, Desc) and widget(*wID, f1.. Object,
...fn) such that records in Object show up in widget as a drop-down
box.. So each record in widget references a single record in Object.

I have something that seems to work at the table level, and I don't
know why. I had to reference 2 columns (1st is ID, 2nd is Name) in
order to have Name (text) show up in the drop-down. Of course, I have
to set column1 to = 0 in order to see text, otherwise it's the ID
number that displays.

Is this the usual method? I'm using the Northwinds example db, and
this seems to be the only way I can get it to look right.

Hope that's clear enough to spell out what I'm trying to do and what I
did.. now why does it work..?

Thanks!

 

[Tom Wickerath]

Hi Gonzo,

From your description, it sounds like you have a table lookup defined. An
example of a table lookup can be found in the Northwind sample database, by
opening the Orders table. When you click into the fields labelled Customer or
Employee, you will see a drop down. You should be aware that lookup fields
are considered the creation of the evil one. See the 2nd commandment here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

Open the Northwind Orders table in design view. Select the CustomerID field
(captioned as Customer). Press the F6 button to switch to the lower window.
Select the Lookup tab. You should see the following:

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [CustomerID], [CompanyName] FROM Customers ORDER BY
[CompanyName];

The row source is a SELECT statement, a.k.a. a query. It is selecting two
fields from the Customers table: CustomerID and CompanyName. The resulting
recordset is ordered alphabetically by CustomerName. A combo box will display
only one field until one clicks on it to open it in dropped mode. The
CustomerID is not something that is all that useful to the user, so we
display the CompanyName instead. However, the CustomerID is a foreign key in
the Orders table, so we specify this field as the bound column. Selections
made using the combo box store the value in the bound column. Does that help?

Note: Name and Desc are considered reserved words. You would do best to
avoid using any reserved words for naming things in Access:

http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Wally Steadman]

I have read the 10 Commandments and the Lookup field thing. This is
referring to Lookup fields in TABLES only correct? I should be able to
create a lookup field on a form to limit the data input correct? just
curious.

Wally Steadman

Hi Gonzo,

From your description, it sounds like you have a table lookup defined. An
example of a table lookup can be found in the Northwind sample database,
by
opening the Orders table. When you click into the fields labelled Customer
or
Employee, you will see a drop down. You should be aware that lookup fields
are considered the creation of the evil one. See the 2nd commandment here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

Open the Northwind Orders table in design view. Select the CustomerID
field
(captioned as Customer). Press the F6 button to switch to the lower
window.
Select the Lookup tab. You should see the following:

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [CustomerID], [CompanyName] FROM Customers ORDER BY
[CompanyName];

The row source is a SELECT statement, a.k.a. a query. It is selecting two
fields from the Customers table: CustomerID and CompanyName. The resulting
recordset is ordered alphabetically by CustomerName. A combo box will
display
only one field until one clicks on it to open it in dropped mode. The
CustomerID is not something that is all that useful to the user, so we
display the CompanyName instead. However, the CustomerID is a foreign key
in
the Orders table, so we specify this field as the bound column. Selections
made using the combo box store the value in the bound column. Does that
help?

Note: Name and Desc are considered reserved words. You would do best to
avoid using any reserved words for naming things in Access:

http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Newbie, but not finding the answer _how_ this works.. Just can't seem
to get it.

given tables Object(*ID, Name, Desc) and widget(*wID, f1.. Object,
...fn) such that records in Object show up in widget as a drop-down
box.. So each record in widget references a single record in Object.

I have something that seems to work at the table level, and I don't
know why. I had to reference 2 columns (1st is ID, 2nd is Name) in
order to have Name (text) show up in the drop-down. Of course, I have
to set column1 to = 0 in order to see text, otherwise it's the ID
number that displays.

Is this the usual method? I'm using the Northwinds example db, and
this seems to be the only way I can get it to look right.

Hope that's clear enough to spell out what I'm trying to do and what I
did.. now why does it work..?

Thanks!

 

[gonzo]

Thanks Tom,

OK, I get the gist of what you're saying.. these things are not such a
great idea. So pardon me for being dense, then why is it included
within the sample DB..? What's a better alternative? It seems like
such a natural UI method.

Guess I'll be poking around for more examples... The desired
application is a simple inventory, maybe I'll cave and modify something
that already exists.

Thanks again!

 

[Tom Wickerath]

Hi Wally,

This is referring to Lookup fields in TABLES only correct? Yes.

I should be able to create a lookup field on a form to limit the data input correct?

Yes, this is commonly known as a combo box, when it is a control on a form.
It is not the same thing as a lookup field defined at the table level. As
long as your combo box does not include several hundred records, then you
should be okay. If you find that a combo box includes too many records (can
lead to slow performance if a network is involved) then you can typically use
the idea of synchronized combo boxes, or you can have the user enter a few
characters first, before populating the list.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________