Combo list generated by query.

[Kenny Kepler]

Hello,

I have a combo-box control on a form for entering the product ID of a
product to an order. I want only the product ID's belonging to the customer
who is placing the order to be available in the list. I have created
customer profiles that store all the customers' details and the products
they use. The profile is comprised of a Customer form and a Cust_Prod
subform (customer products). The Customer form source table has fields for
all the basic details (ID, company name, address details, etc.) and is
linked to the Products table via a middle table, Cust_has_Prods (customer
has products) which has a multiple-field primary key of the cutomer's ID and
the product's ID. I then created a query to use as the source of the
subform Cust_Prod and using the form I can add products to the customer's
profile. This works great.

But I am struggling get the filtered list of products based on the
customer's ID of the current record in the Order form. I can see now that
the customer's table would not have duplicates of the customer's id and the
orders table would and that is where I am stumped.

Alll help is super extremely appreciated.

TIA,

Kenny

 

[Jeff Boyce]

Kenny

If you need a way to list only those products a customer is "entitled" to
order, you need a table that lists one row for each valid CustomerID X
ProductID. Then, in your ordering form, first select a customer from a
combo box, then requery the Product combo, using the value in the
cboCustomer control as a criterion in the query that fills the cboProduct.

 

[Kenny Kepler]

Hi Jeff,

I kind of figured that much out, if I am getting what you are saying. I
already have a table that lists one row for CustomerId X ProductID; the
Cust_has_Prod table. How do I make a query that will use the value in the
cboCustomer control as a criterion in a query to fill the cboProduct. I
have tried a few different and convoluted queries already but I just can't
seem to get it right. I have worked out that I need to select the Prod_ID
field from the Cust_has_Prod table, but how do I say:

WHERE Orders.Cust_ID = Cust_has_Prod.Cust_ID

in a way that works. That WHERE clause won't work because the Orders table
and Cust_has_Prod table will both have many duplicate records for Cust_ID.
I need a way of telling it to say something more like:

WHERE cboCustomerID of current record's value = Cust_has_Prod.Cust_ID

I just can't seem to work out how.

Please help.

Kenny

 

[Jeff Boyce]

Kenny

Open a new query in design mode.

Select your trelCust_has_Proc table, the ID field, and the CustID field.
Add the Product table, joined by ProductID - select the ProductName.

In the criterion row under CustID, add in something like the following
(actual syntax may vary):

Forms!frmYourFormName!cboCustomer

(where cboCustomer is the name of the combo box you use to select a
customer -- the first field needs to be the CustID.)

 

[Kenny Kepler]

Hi Jeff,

I have tried that and it results in some pretty queer behaviour. I have to
run now. Please check this post in about 6 hours +. I will send a detail of
exactly what is happening. I really need the help on this one.

Thanks,

Kenny

 

[Jeff Boyce]

Kenny

Re-post to the 'group at large. It sounds like you may need more eyes on
this...