Query Criteria Issue

[DavidMcArthur]

Hi All,

I have a big database which we use for Purchase Order Numbers and Job Reference Numbers.

When we go to issue a purchase order I have to choose the product from a list but the problem is it will show ALL items. Is there a way I can choose the Supplier First and have the products to that supplier filtered?

I know there will be a few more questions to be answered on this...



Its one of the last bits I need to get right.

[Minty]

You need cascading combo boxes. Have a look here for a sample and code
http://www.baldyweb.com/CascadingCombo.htm

[DavidMcArthur]

Yes! this looks like it. I cant get it to work on my database though.... Here is the relationship which links into the photo I posted earlier.

[Minty]

What is the query used for the product combobox , you need to add the SupplierCmbo as a criteria in your Product combo.
If you post up the names of your forms, and the SQl of the query we may be able to help.

[orange]

A few comments re design of tables and relationships for consideration:
-suggest no spaces in field names
-PurchaseOrder table should have a PK
- would include AgreedTo or pricePaid field in table Order_Line
If you don't include a Price in Order_Line, and you depend on Price in Product, you will get an error in any historic info. That is, when you change a Product price, the new value will be used for existing Orders/OrderLines which will not(necessarily) be the Price at the time of the Purchase.

[DavidMcArthur]

Hi Orange,

The point you made about price is a good one. I struggled with this so what I did was create a new product everytime the price changes and then untick "Active" which is a check box. This will keep all historical data but hide it so it doesn't get confusing.

[DavidMcArthur]

Here are my form names. I removed all query data because its giving an error

[Minty]

Is there a new question or is this solved ?
I'm a little confused.

[DavidMcArthur]

Not been resolved.

[Minty]

So where are you with it.
What have you tried - what code / SQL query is being used , and what errors are you getting?

[orange]

As Minty said earlier, when you need to Select a Product from a Supplier, you would normally use a form. And on the form would be 2 comboboxes.
The first combo would be a list of Suppliers - and you would select one.
The second combo contains a list of Products-BUT -only those Products supplied by the Supplier you selected in the first combo.
This approach is known as Cascading Combos.

Post your current status. We can't help you unless you provide feedback to our questions.

Some links to help:
http://www.fmsinc.com/microsoftacces...cascading.html

These 2 from DataPig are older, but lead you through the entire set up.
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

Good luck