Show certain records only on subform

[JayGee1969]

I have a database in which I have a customer order form containing the customers details in the parent form and the products ordered in the subform. As each customer orders from a different set of products I want only to see the products associated with that customer when I click on the subform. I have created a query that will filter out those products for each customer, but when I open up the customer order form it still shows all the products in the products table in the subform. As I select the costumer from a combo box in the main form I want it to automatically apply the same choice to the list of products in the relevant combo on the subform.



Please give your answer in very simple terms because I have no programming knowledge. Thanks,

[orange]

Research Form/Subform and linkChildFields/linkMasterFields
To start
see http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

There is a tutorial (free video) at http://www.youtube.com/watch?v=YZKN_-P6wck

[John_G]

Hi -

It sounds like you did not link the subform to the main form correctly. Go to the properties for the main form control containing the subform. There are two properties there - Link Child fields and Link Master Fields. Set those to the fields that relate the subform records to the main form record - in your case Order_ID I think.

It's not clear from your post though what you main form contains - is it customer or customer order? (Usually it would be the latter)

"the relevant combo on the subform."

Can you explain that a bit? What data is the combo box on the subform supposed to show?

John

[JayGee1969]

The main form and the subform are linked already by the Order ID field and the Customer ID field. The source for the main form is OrdersQuery and the source for the subform is a OrderDetailsQuery.

I have a ProductsQuery which enables me to sort which products are ordered by which customer. I would like it so that only the products ordered by ie Customer A show in the subform when Customer A is selected in the main form. I tried using ProductsQuesry as one of the 'ShowTable's in the OrderDetaisl Query, but this does not seem to work.

[orange]

I suggest you watch the short tutorial here http://www.youtube.com/watch?v=YZKN_-P6wck

[JayGee1969]

Thanks. I have already watched that but it does not help. My forms are already linked by the OrderID and CustomerID fields which means that the relevant order will come up for the right customer, but it does not help me to filter out the right set of products as I still see the full set of products regardless of which Customer is selected.

[orange]

Please post a jpg of your tables and relationships.

You seem to have related Customer and Order, but you may not have related Order to OrderItems and OrderItems to Products --just a guess.

Here's a data model that has customers, Orders and Products that may help
http://www.databaseanswers.org/data_...ucts/index.htm

Also:
These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation. These videos follow a Customer, Order, and Items theme.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

[John_G]

Hi -

I'm a bit confused here, so just to clarify - what is the record source for the main form, and what is the record source for the subform?

Is your objective to list all the products a customer has ordered, without breaking it down by individual orders?

John

[JayGee1969]

I am pretty sure all relationships are in place, but please tell me if you think I have missed something.

[JayGee1969]

The source for the main form is OrdersQuery and the source for the subform is a OrderDetailsQuery. The company that I am making this for produces different products for different customers so they want to be able to create an order by selecting from a list of products, but rather than having to scroll through their whole database of products they want simply to pick form the list of products that are relevant to the customer for whom each order is created.
I have added a screen print of the order form with subform - when I select a customer in the top box I then want to be able to see only the products related to that customer in the Product field.

[orange]

I want only to see the products associated with that customer when I click on the subform.

Since you have a Link between Customers and Products, you could try a new form

With Customers in Mainform and Products in Linked subform. No need to involve Order nor Order details if all you want is Customers and related Products.

[John_G]

Hi -

It's pretty close - but the relationship between customers and products should not be there. The actual relationship between Customers and Products is many-to-many, so the one you have is actually incorrect.

Take CustomerID out of Products - it should not be there.

It's my own opinion, but when I look at an ER diagram, if there are two (or more) different routes to get from Table A to Table B, there is a mistake somewhere (not counting lookup tables).

If you need to find all the products related to (i.e. ordered by) a particular customer, then you yould need a 4-table query: Customers - Orders - Order Details - Products, but you would not necessarily need fields from all of them.

There would be lots of duplicates of course, so you could make it a Totals query, and group by CustomerID & ProductID (in the very simplest layout)

But your structure is fine for what you need, except as I noted.

Just as an aside - some people may say that UnitPrice and ProductName should not be included in OrderDetails; IMO its OK to keep them there, because they record the prices and names that were valid at the time of the order, and proces change all the time. Not 100% normalized, I admit, but the simplest.

HTH

John

[orange]

Good point John_G. I first question the link between Customers and Products, then went back to post #1 and saw OP only wanted Customer and Product Info.

I should have seen the M:M right away , but didn't.

[John_G]

"...when I select a customer in the top box I then want to be able to see only the products related to that customer in the Product field..."

Now I see what you want to do - quite reasonable if your list of products is extensive.

But your definition of "relevant" is a problem. What happens if a customer wants to order a product they have never ordered before? Because they never ordered it, it won't appear in the dropdown, so that won't work.

A solution might be to have another table linking customers to product categories (that relationship is many-to-many), so that the combo box will list all products in categories the customer is interested in - whether they ordered them yet or not. You could just requery the combo for whatever customer you were working with.

John

[JayGee1969]

I'm not sure that the Customer-Product relationship is actually many to many. My client makes bespoke products for each of his customers, so each customer only orders their own set of products. ie Customer A will not order any of the products ordered by Customer B and vice versa. To my mind this makes it a one to many relationshiop, or am I misunderstanding this here?

Also, if a customer wants to order a new product they have never ordered before then the client will add that product to the products table before completing the order for that product. As they add a product to the products table they also specify for which customer it is to be made. This way the product will then appear int he drop down in the order form.