Stuck with User Input Query/Form

[naiku]

I need some help with a query based form, at least I think it needs a query based form. I have 2 table's one is Suppliers Details (name, address etc), and the second is Product details (product code, price etc). These 2 tables are linked on the Suppliers Account ID (One to Many). I want to create a form where the user has a combo box listing all the product codes, they select the code they want and then the product description appears in the next field, there is then another combo box which list's the suppliers of the product code selected in the first combo box. The user then picks the supplier they want, enters the quantity of product they want and the price gets completed in another field.
The problem is that I can't get this to work, I thought I could create a Select query where the query asks for a product, then asks for the supplier and base the form on this information. This does not work for 2 reasons, the first is that if I pick a product which has 2 suppliers, I am not limited to selecting from these 2 suppliers instead I can enter any supplier that I want (because with my query it uses a message box for the user input, but I want to use a combo box) and the second is that when creating the form it again uses message boxes and I can't figure out how to turn this into a combo box visually on a form, at the moment when opening the form Access displays the message box asking for use input.
Sorry for the lengthy post, can someone point me in the right direction please.

 

[jaydeebe]

You need to look at parameter queries. Here are two very good tutorials that i think cover exactly what you want -

http://www.fontstuff.com/access/acctut01.htm

http://www.fontstuff.com/access/acctut08.htm

Only dead fish (and shit) go with the flow!

 

[naiku]

Okay I tried the parameter queries, but they don't do exactly what I needed (unless I have made a mistake somewhere). If I understand correctly the parameter query will allow me to create the data I need, but then display it as a query result.

I want to create a form made up of several combo boxes, for example:

Product Supplier
ABC123 123ABC
ABC123 234DEF
DEF123 123ABC
GHI123 555GHI

Imagine the above as combo boxes, in the first combo box I have 3 values (ABC123, DEF123 and GHI123) the user selects one of these values (say ABC123) and they then move to the Supplier combo box where they will pick from either 123ABC or 234DEF (The only 2 values that will be displayed). But if they were to pick Product GHI123, the suppliers list would then update and only display 555GHI as this is the only supplier available for that product code.

I want this to be on a form, where the user only get's to see the form. Do I need to create a query for my Product combo box, then a seperate query for the Supplier combo box, whereby the data in the Supplier combo box is taken from the result's of the user's selection from the first query (Product, using the drop down box). Would this work on a single form, with data on the form coming from several queries?

 

[PHV]

A starting point.
RowSource of cboProduct:
SELECT ID,Product FROM tblProduct
In the AfterUpdate event procedure of cboProduct:
cboSupplier.RowSource = "SELECT S.ID,Supplier FROM tblSupplier S INNER JOIN tblSupplierProduct J ON J.SupplierID=S.ID WHERE J.ProductID=" & [cboProduct].Column(0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jaydeebe]

When a user selects a product this could trigger a query (use the after update event of the combo box) to populate the other combo box(or list box for multiple selects) with relevant suppliers.

Only dead fish (and shit) go with the flow!