Form and Sub-Form - Join Table

[dsajones]

Hi All. Grateful for any help here. I've attached a simple database where I have two main tables - Products and Packages. A package is a selection of products. I think the database structure is correctly normalised with a link table that joins a single package ID to multiple product IDs.

I've created a form using the form wizard to try and allow me to create a package and then in the sub-form, select the required products that make up that package. My problem is that the sub-form is just effectively giving me a read-only view of products that I have added to a package directly in table views. I can't find a way of making Product Name a drop down lookup of product names that I can select and save in the package.

Many thanks
David

 

[plog]

Probably has to do with using a query as the form's record source. You shouldn't do this. If you want to directly interact with data on a form, that form should be based off a table.

 

[dsajones]

Probably has to do with using a query as the form's record source. You shouldn't do this. If you want to directly interact with data on a form, that form should be based off a table.

OK, thanks. I'll have a play with that. The form was created using the form wizard so I sort of hoped it would end up giving me a form that could be fully interacted with!!

Thanks,
David

 

[JHB]

I think this is what you are looking for, database attached!

 

[dsajones]

I think this is what you are looking for, database attached!

That's exactly what I was trying to do I'll have a look at exactly where the differences are.

Thank you!!

Cheers,
David

 

[JHB]

You're welcome.

 

[dsajones]

Hi JHB. I've worked out the differences and got my form working as required now. Just tried to do something that I assumed would be very simple and it hasn't worked. I've had a price column to the products table and included that on the sub form. I've then added a text box to the main form so that it will show a total price for all the products in a particular package and included the following calculated expression using the expression builder - =sum([products subform].[form]![product price]). But whenever I load the form this field just displays #error. Any idea why that might be?

 

[dsajones]

I've made a small step forward where I read that there is an issue with a calculated control on a main form that references a control on the sub form. This recommended that you first create the calculated total in a control in the footer of the sub form, then on the main form have a control that references the control on the sub form. I've tried that and now I get #size! Instead of #error! Any ideas what's going on?

 

[JHB]

Post your database with the change.