Limiting a list box to a query, in a subform made of two tables

[potassium flower]

I am creating a restaurant meal ordering system. I want to limit the meals that can be ordered to only those that are in use (i.e. in the form holding meal details the 'in use' field is true). I can't create the right query to link to the list box though.

tblOrder
OrderID (key)
TableID (foreign key)
GuestsArrived
GuestsLeft
Notes

tblOrderDetail
OrderID (key)
ItemID (key)

tblItem
ItemID (key)
Name
ItemTypeID (foreign key)
NumberInStock
Re-OrderLevel
Cost
Notes
InUse

The main form is 'frmOrder' and the subform contains 'OrderID' and 'ItemID' from 'tblOrderDetail', 'Cost' and 'Notes' from 'tblItem'.

Using 'Item ID' (tblOrderDetail) and 'In Use' in a query produces a long list with multiple instances of each meal.

Using ''Name' and 'In Use' produces the correct query but when you link it to a list box in the form I mentioned and then select an option from that list box it puts that selection in every record in that order?

[MMcCarthy]

Try this as the recordsource for your subform and let me know the results ...

SELECT tblOrderDetail.OrderID, tblItem.ItemID, tblItem.Name
FROM tblOrderDetail INNER JOIN tblItem
ON tblOrderDetail.OrderID=tblItem.OrderID
WHERE tblItem.InUse = -1;
 

Mary

[MMcCarthy]

Sorry just re-read the title. Set the list box row source to ...

SELECT tblItem.ItemID, tblItem.Name
FROM tblItem
WHERE tblItem.InUse = -1;
 

Mary

[MMcCarthy]

Using ''Name' and 'In Use' produces the correct query but when you link it to a list box in the form I mentioned and then select an option from that list box it puts that selection in every record in that order?

Not sure what is going on here.

1. Why are you using a list box instead of a drop down box?
2. Is your subform recordsource the same as the previous answer I gave in Post #2?
3. Is the control source of the listbox set to the field in which you want to store the ItemID?

Mary

[potassium flower]

I think I mean a drop down box, I'm now using the control Access calls a combo box. Changing the control source to ItemID solves the problem about all the records showing the same entry that i mentioed in post #1, thanks. The code from post 2 works, but it shows the Item IDs in the drop down box, it would make more sense to the user if it showed the meal names?

[MMcCarthy]

I think I mean a drop down box, I'm now using the control Access calls a combo box. Changing the control source to ItemID solves the problem about all the records showing the same entry that i mentioed in post #1, thanks. The code from post 2 works, but it shows the Item IDs in the drop down box, it would make more sense to the user if it showed the meal names?

Open the properties of the combobox and go to the Column Widths property. Set to 0cm;5cm and you should now see the item name. Setting the first column width to 0 will hide it.

Mary

[potassium flower]

Thanks, you also needed to set the colum count to 2 before the list became visible, but now it works fine. Thank you :)

[MMcCarthy]

Thanks, you also needed to set the colum count to 2 before the list became visible, but now it works fine. Thank you :)

You're welcome.

Sorry I shouldn't have assumed the column count was set to 2. ;)

Mary