Feeding multiple tables from a single form.

[Raymas]

Hi all,

I currently have a form which creates a parts list for products and it works great. It is basically a inputs one to many into a table between products and raw materials. I now wish to include on the same form (for simplisity) the ability to add patterns for each product similarly to the way the parts list is generated. So another one to many table linking products and pattern numbers.

At first I tried adding a subform for creating pattern numbers. But i ran into difficulty when focus went from the main form to the subform (it looked like it would try to move to a new record in the main form, when I wanted that record to stay the same). I am now wondering if I should have everything on the mainform, and maybe there is some way to feed multiple tables. It all seems a bit hairy atm. If you have any tips and comments please let me know.

Cheers,

Raymas

 

[Jeanette Cunningham]

It is much easier if a form for data entry is based on a single table, but not always possible.
I'm not sure how your tables are related.
If you have a main form based on the products table, you can put a tab control on it and put a different subform on each tab of the tab control, where each subform's table is related 1-to-many to the products table.

 

[Raymas]

Thanks for the input Jeanette! Yes my structure does already loosely follow your suggestion. I have found a solution, although it seems dodgy, it works nicely. I have made the subforms invisible, and linked them using vba to unbound controls on the main form. So the user does not directly use the subforms, and this seems to have solved the issues i was having when focus shifted to the subforms.

 

[Jeanette Cunningham]

Your solution isn't dodgy at all. That is pretty close to the way most of us do it with subforms on the pages of a tab control.

Here is a tip that can speed up your form - you can remove the source object from every hidden subform control. Only load the subform into its tab control when the subform is needed - usually only when the user needs to see and work with a particular subform.

If you have your subforms on a tab control, use the Change event of the tab control to load and unload the source objects from the subform controls.