Full Version: Combining Form And Subform Into Another Subform
UtterAccess Forums > Microsoft® Access > Access Forms
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.
`m working on an Orders database and I have a Products form, with a datasheet subform for ProductDetails. As an example, the Product might be Garage Door 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.
In the Orders form, the main form contains the supplier, customer, order date etc, and and OrderDetail datasheet subform is where the products need to be added - along with fields such as quantity, cost multiplier and net cost etc.
How do I combine the ProductID from the main Products form and the rest of the fields from the ProductDetails subform to appear in the OrderDetail subform. As an example, so it would list Product then ProductDetail, e.g. Garage Door | White Door 8 feet | ..... (then things like cost, quantity etc)
So far, what I`ve got is that the OrderDetail subform displays each record in ProductDetail. I also can`t seem to add any record to the former.
My relationships I think are set correctly.
The Parent and Child link fields on Orders is the OrderID field.
I`ve uploaded a screenshot of the two forms. I`ve been playing around with some settings so it`s far from finished (and am working on ID fields rather than Lookup combo boxes for now) but you can hopefully see what I`m trying to do.
Hope this makes some kind of sense!
Thanks in advance
theDBguy
Hi,
welcome2UA.gif
Not sure I follow what you want to do. Are you talking about using sub datasheets?
dhmlofi
The subforms are set to Datasheet view if that`s what you mean. Ok from now I`ll refer to my subforms and subdatasheets
ach product comes in many different sizes, so I want to be able to have one product record with several subsets for the different sizes. This is why I`m using a subdatasheet on the Product form.
The way the database was until now, each variation of the products was a separate record and there was no subdatasheet. Let`s say that for supplier A there was 50 products. When it came to the dropdown boxes on the Order subdatasheet, the list of products was very long (even though each order form is using only one supplier and so the order subdatasheet 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.
Hope that makes it a bit clearer. Thanks.
theDBguy
Hi,
My the term "subdatasheet," I was referring to the "+" sign next to each record in the datasheet.
Check out this link: Add a subdatasheet
Owasn't sure if that's what you were looking for, that's why I asked.
Just my 2 cents... 2cents.gif
dhmlofi
Ok I get what you mean. From a quick look at the page you gave me the link to it says it`s more for viewing rather than editing. As I`m working in the forms rather than the tables, I`m unsure if that`s where I need to go for a solution. If it is, can you explain how?
Cheers
theDBguy
Hi,
I am still not sure that this is where you want to go but to see the subdatasheets in subforms, try the following steps:
1. Create a blank form
2. Add an empty subform control on it
3. Go to the Source Object property of the subform control and select the table's name from the dropdown
Just my 2 cents... 2cents.gif
dhmlofi
Ok thanks, I`ll give it a go.
theDBguy
Hi,
Good luck!
dhmlofi
The more I think about it, the more I think this isn`t where I want to go. I`m pretty sure my client isn`t going to want to have to expand a table to enter data.
Is there another way to do it?
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.