[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4025
  • Last Modified:

MS Access lookup values in query / form

Haven't used MS Access for a while and can't figure this out:

I have a products table that has two columns - "ProductID" (pk) and "Product"

I have an Orders table that contains a lookup column "Product(s)" that uses the rowsource "SELECT Products.ProductID, Products.Product FROM Products;" its Bound col 1, column count 2 and col widths 0;2.54cm

This all works fine.

I then have created a query that filters the order table by customerID.

This works fine - when I view the query I see required columns, including the "Product(s)" lookup column that shows a list of the product names.

As an example in the Product(s) field I will see "Design, Marketing, Brochure"

However, when I try to use this query in a form (continuous form view) it only shows the productID's in the lookup box not the Product(names) I see in the query?

i.e. it now shows "1,3,4"

How can I make it so that the "Product" column (product names) show and not the ProductID field?

Many thanks,

Sam
0
SamB
Asked:
SamB
  • 2
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
create a query, using the Orders table with a join in Products table field ProductID
choose all the fields that you need from the Orders table AND to show the Product description, include in your column the Product field from the Products table.


select Orders.*, Products.Product
From Orders Inner join Products on Orders.ProductID=Products.ProductID
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Check the control in the form.  It should be a combo and have the same settings that you set for the lookup.

There's not need to include the product table in the forms base query unless you don't want to use a combo control for some reason.

Jim.
0
 
SamBAuthor Commented:
Can't get either solution to work.

I've attached the demo database .....

You'll see the query shows the info I need but the form reverts back to ID's only ?

Many thanks,

Sam
Database.accdb
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Sam,

 Doesn't look like you tried either?

  The form currently has a text box, not a combo box and it's bound to "Product(s)" (which BTW is a bad field name - stay away from any type of special characters in field names).

 You either need to change the control to a combo box, in which case you will be able to set things just as you did for the lookup properties

 or

 you need to do what Ray suggested and include the products table and the description field in the forms underlying query.

If the intent is to add/edit with this form, then the combo box is probably the best, but you still can do it either way.   If you follow Ray's suggestion, you'll need two text controls; one for the product key and then one to display the description.

Jim.
0
 
SamBAuthor Commented:
Thanks Jim.

I had changed to a combobox but didn't work - on further investigation I had left the 'Column Count' property at '1' and not updated to '2'. Once did this your solution worked.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now