Display all products but enter qtys for some

[csteuer]

My main form has a orderID and a clientID.
My subform is linked using the orderID.

The tables are Orders, Clients, Products and joinOrdersProducts.

I want to display ALL the products for a clientID in a subform and I
want to enter quantities per product by orderID.

So what I want to see in the subform is this--

Order 1 for Client ABC:
Product1 0
Product2 0
Product3 5
Product4 2

Order 2 for Client XYZ:
Product7 2
Product8 0
Product9 3

I tried linking the subform using both the orderID and the clientID but
I lose the ability to add new records, it just displays the ones
already entered.

I also tried creating a continuous subform and using an unbound control
to enter the quantities, but how do you save a particular row's unbound
control? If you enter a number into the first unbound control it
appears in all of the rows.

I think I need an outer join to show all the products per client but I
can't figure out how to save the combination of orderID and productID
back to the joinOrdersProducts table.

Suggestions?

 

[Jonathan Parminter]

-----Original Message-----

My main form has a orderID and a clientID.
My subform is linked using the orderID.

The tables are Orders, Clients, Products and joinOrdersProducts.

I want to display ALL the products for a clientID in a subform and I
want to enter quantities per product by orderID.

So what I want to see in the subform is this--

Order 1 for Client ABC:
Product1 0
Product2 0
Product3 5
Product4 2

Order 2 for Client XYZ:
Product7 2
Product8 0
Product9 3

I tried linking the subform using both the orderID and the clientID but
I lose the ability to add new records, it just displays the ones
already entered.

I also tried creating a continuous subform and using an unbound control
to enter the quantities, but how do you save a particular row's unbound
control? If you enter a number into the first unbound control it
appears in all of the rows.

I think I need an outer join to show all the products per client but I
can't figure out how to save the combination of orderID and productID
back to the joinOrdersProducts table.

Suggestions?

Hi csteuer, you have not mentioned the fields in the
table, joinOrdersProducts. I will assume that as a minimum
this table has the fields: orderID productID quantity

the subform will have the table, joinOrdersProducts as its
recordsource. the subform can be any of the options single
form, continuous form or datasheet.

you may like to use a combobox for the product selection.

Just a thought... you may have the sample database,
northwind.mdb installed with ms access. You should take a
look round this for ideas.

Luck
Jonathan