Problems with LOOKUP Field!

HI All.

I need help with tthe LOOKUP Field for my tables. I have a form with a subreport/subform on it. My Subreport/subform is setup to be another table. This table is linked to the main form by a key.

I hope all understand untill now. This is fairly straightforward.

Now in my subform, I have specified on of the fields as a lookup. The problem is: I want the lookup expression to use a value in the current row to complete the lookup.

i.e.:
Table1 - A Table in my Access DB
1 | Text
1 | Text2
2 | Hello
3 | Bye
3 | Bye2

Table2 in my Subreport/Subform
3 | [Lookup] <- I want this lookup to get "Bye" and "Bye2" becuase the 3 in THIS ROW is my Key
^ The 3 gets automatically filled on a new record because it is the link so I have this info!

Basically I think I need to know how to find the 3 in my CURRENT ROW! How can I do this in the lookup field?

[The_Doc_Man]

Lookup is single-valued. It will not find two answers and return them both. This problem stems from the fact that your selection key is not unique, which for a table lookup, it must be.

I was hoping that wasn't the case. I don't think I like this MS Access.

[Pat Hartman]

DLookup() is a function. VB/VBA functions return a single value. You could write your own function and return an array. I'm not sure why you don't like Access for this. Please tell us what language you use that acts differently with integral functions.

Your over simplification makes it impossible to decern your actual situation. Are you certain that you need DLookup() at all? Are you sure that you don't want to create a query that joins the main table to the lookup table so that all values from the lookup table will be returned?

becuase the 3 in THIS ROW is my Key

- KEYS are unique so 3 can't be a key.