Modify Lookup

[Guest]

I am using Win XP Pro and Office 2003 Pro, MS ACCESS

I have a lookup field, ProductID, in a Transaction Table that pulls
ProductName from Products Table with the following Row Source Statement:
SELECT DISTINCTROW Products.* FROM Products ORDER BY Products.ProductName;

I need to modify this to include ProductDescription and ProductNumber from
the Products Table in the lookup pull down list.

How should I modify the Row Source Statement to get this done?

Thanks for your help.

 

[Guest]

You want something like this:

SELECT ProductName, ProductDescription, ProductNumber FROM tblProducts
GROUP BY ProductName, ProductDescription, ProductNumber ORDER BY ProductName;

Sharkbyte

 

[Guest]

I appreciate your response. I tried your suggestion in the table and in the
subform design, but did not get results.

 

[Dirk Goldgar]

I appreciate your response. I tried your suggestion in the table and
in the subform design, but did not get results.

I don't know that the query needs the GROUP BY clause, but shep, you do
also need to modify the properties of the combo box on the form (and in
the table, if you want it to work that way there), to set Column Count
to 3. You may also need to set the Column Widths property to get the
desired width for each column.

Be aware of two things:

1. The combo box is still only going to show the data from one column
when it is not in its dropped-down state.

2. Once the control on the form is created, changing the properties in
the table won't affect it (for the most part), so you'll have to make
them on the form as well.

 

[Guest]

Thank you that helps. I am getting closer to resolving it.

 

[Guest]

I finally got it to work. Dirk you were right on about setting the # of
columns and columns width.

Thanks again to both of you.