Using the lookup field...

[jbfraleigh]

Let's say that I have two tables:

Table Numbers:

ID VALUE
-- ------
1 123
2 456
3 789

Table Letters

ID VALUE NUM_LOOKUP
-- ------ -------------
1 ABC 1
2 DEF 3

Now, I'd like to use the lookup feature so that NUM_LOOKUP is a combo that displays the data in VALUE based on the relationship between ID in the two tables.

So, when you open the Letters table, you see the above. If you click in the NUM_LOOKUP field, you get a box that displays "123" for the first record and "789" for the second record.

I'm very close -- Here's what I have:

I tried the following:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [ID],[VALUE] FROM [Numbers] AS [NUM_LOOKUP];
Bound Column: 1
Column Count: 2

Problem: This selects ALL values of ID from [Numbers].

I tried adding the following to my row source:
WHERE [Letters].[NUM_LOOKUP]=[Numbers].[ID]

This just gives me the "enter parameter" dialog box for [NUM_LOOKUP] and [ID].

Any help?

Thanks!

 

[neileg]

You need to do this in a query, or more elegantly in a form. You can't do this at table level as far as I know.

 

[Pat Hartman]

1. Value is the name of a property and so is a poor choice as a column name. You will have trouble with this if you need to reference it in VBA.
2. I would recommend NEVER using the lookup facility. It is intended for novices who don't know any better and who will not be writing queries or VBA. Always use a query that joins the two tables if you want to see the lookup value along with the ID value.
3. You can't do what you are asking in a table in any event. In order to make "cascading combos" work you need an event in which you can fire .requery code. You don't have any events available in a table.
4. ID is also a poor choice as a column name since it is non-descriptive.