By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,559 Members | 2,992 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,559 IT Pros & Developers. It's quick & easy.

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

P: 45
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?
Feb 19 '07 #1
Share this Question
Share on Google+
7 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this as the recordsource for your subform and let me know the results ...

Expand|Select|Wrap|Line Numbers
  1. SELECT tblOrderDetail.OrderID, tblItem.ItemID, tblItem.Name
  2. FROM tblOrderDetail INNER JOIN tblItem
  3. ON tblOrderDetail.OrderID=tblItem.OrderID
  4. WHERE tblItem.InUse = -1;
  5.  
Mary
Feb 20 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry just re-read the title. Set the list box row source to ...

Expand|Select|Wrap|Line Numbers
  1. SELECT tblItem.ItemID, tblItem.Name
  2. FROM tblItem
  3. WHERE tblItem.InUse = -1;
  4.  
Mary
Feb 20 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 20 '07 #4

P: 45
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?
Feb 20 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 20 '07 #6

P: 45
Thanks, you also needed to set the colum count to 2 before the list became visible, but now it works fine. Thank you :)
Feb 20 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 20 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.