Linking Form and a Subform to another Subform

[dhmlofi]

I`ve been using Access for years and am usually pretty good at figuring out my problems or finding the answers on forums but this issue has me really stuck so I`d appreciate some help please.

I`m working on an Orders database. In the Products form, I`ve created a subform in datasheet view for ProductDetails. These details are for the variations of colour and size in the product along with their respective prices etc. As an example, the Product might be Garage Door A and the ProductDetails might be the likes of White Door 10 feet, White Door 8 feet, Red Door 11 feet, Red Door 12 feet (more complex than that but you get the picture). My client wants to be able to have it like this as there could be lots of variations of the one product.

This seems to all work fine.

The problem lies with my Orders form.

In the Orders form, the main form contains the supplier, customer, order date etc, and there is an OrderDetail subform in datasheet view where the products need to be added - along with fields such as quantity, cost multiplier and net cost etc.

My problem is that I can`t seem to get this subform to work. It worked fine in an earlier version of the database when the products were only on a form. As soon as I tried to split the products into products and product details I`ve run into problems.



How do I combine the Product from the main Products form and the rest of the fields from the ProductDetails subform to appear in the OrderDetail subform. I`d like it to list Product then ProductDetail, e.g. Garage Door | White Door 8 feet | ..... (then things like cost, quantity etc)

What I`ve found is that the OrderDetail subform displays each record in ProductDetail. And as my query has more than three forms with one to many relationships, I also can`t add a new record to the former.

My relationships I think are set correctly.

The Parent and Child link fields on Orders is the OrderID field.

I know this is confusing to describe so I`ve uploaded a diagram of what I`m trying to achieve.
Hope this makes some kind of sense!

Thanks

[burrina]

Pardon me, but it still sounds like you don't have the Master/Child relationships setup correctly. Without seeing your table structure or relationships it's a shot in the dark! If I understand correctly you have a order form with many products to choose from for that order, correct? Primary Key to Fake Key is the norm.On a one two many relationship. Some pics of your table structure and relationships and forms would be helpful.

[dhmlofi]

Thanks burrina. I`m attaching some images.

If it helps in explaining it, the way the database was until now, each variation of the products was a separate record and there was no subform. Let`s say that for supplier A there was 50 products. When it came to the dropdown boxes on the Order subform, the list of products was very long (even though each order form is using only one supplier and so the order subform is filtered to show only products from that one supplier). My client therefore wants to have the ability to separate the products and the sizes so that the dropdown lists are shorter. So Supplier A would have 5 products each with 10 sizes.

[dhmlofi]

and here`s the images of the forms...

[burrina]

Impressive screenshots and yes, I think your relationships appear to be in order. I am curious about this query though? If you run the query, can you do what you want? i.e. add/edit data? Does this end result look like you want it to? So, just to be clear, you have a customer that places an order and then you enter products on this subform for that order? You say you can't add records on this subform? Also what happens when you start using more than one supplier, how are you going to address this? Also, is the ONLY issue is that you need to filer the combobox better? This is accomplished of course via the query for that combo! Could you post a screenshot of the combo query and describe in detail what you want it to do?

[Rod]

Excuse me butting in, there is a mistake in your relationships: OrderDetail should be related to ProductDetail and not to Product.

[burrina]

I did not catch that, true! Maybe that will solve his problem!

[dhmlofi]

Thanks both of you. Do I need to link OrderDetailID to ProductDetailID or ProductID?

When I try the former it gives me a one-to-one relationship and when I try the latter it gives me a one-to-many but doesn`t indicate a 1 against OrderDetailID like I`d expect it to. I thought I`d have needed a many-to-many as I`ll have more than one orderdetailID with more than one productdetailID.

So I`m confused as to which it should be??

Thanks again

[burrina]

OrderDetailiD to ProdoctDetailID

[Rod]

To elaborate: remove ProductID from OrderDetail after deleting relationship with Products; add ProductDetailID as a foreign key; create 1:m relationship between ProductDetailID (PK on ProductDetail) and ProductDetail (FK on OrderDetail).

[dhmlofi]

Many thanks both of you again. I`ll give it a go and report back.

[dhmlofi]

Many thanks both of you again. I`ll give it a go and report back.

[dhmlofi]

I`d like to take this one step at a time as I know my query was also very mixed up. So can I please ask you to take a look at the screenshot and let me know if the relationships are now as they should be? Although I`m not seeing the 1 and infinity symbols on the new relationship, it does say it`s a one-to-many.

[burrina]

Looks good to me. However, what you speak of is when you check off Enforce Reverential Integrity! Sometimes this is not possible if you already have data in the tables.

[dhmlofi]

Hi again. Ok, thanks burrina for the Enforce Ref Int note, that solved that...appreciated. And thanks Rod for the detail on updating the Relationships...a huge help. I have been working on my query and am several steps ahead and actually thought I had it working! But I have come up on another hurdle that has me stumped.

I`m now able to add new records in the OrderDetails subform and have set up combo boxes to filter the product info. In the attached images you will see the message I get when I try to open the combo for ProductDetailID. I need it to pick the ProductID from the current subform record and from the parameter box, I can`t see how this is the wrong one I`m selecting. But whichever way I try, it`s not working.

As a note, Rod, you said to remove ProductID from OrderDetail which I did, but ended up having to put it back in as I need them to first select the product (productID) before selecting the customization of it (ProductDetailID).

Again, I`d appreciate your input. Thanks