Main form with different subforms (1 Viewer)

Lanser

Registered User.
Local time
Today, 08:44
Joined
Apr 5, 2010
Messages
60
Hi,
I have googled but can't find a solution.
I have a main form for each product that would like to contain 2 subforms one containing targets for each product and one for results.
The Target subform will be fixed targets and the results subform will be for editing/adding results..
FrmMain
frmTarg_672
frmRes_672

My problem is that depending on the product group the subforms will have different layouts and fields 7 or 8 different layouts.

Can I set which pair of subforms to use based on the product group? Or use the one subform and hide/show fields based on product group (which I think will make the form design incredibly messy)

1648138574426.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:44
Joined
May 21, 2018
Messages
8,463
I am a little concerned that you have different types of data, which could mean the table structure is not correct. But I will assume you table is OK and that you just have some different fields for the different groups.
Using VBA code to show/hide/Move fields is not that hard and could be done. If you are more comfortable in having seperate subforms that may require less code and you could have very different appearances. Configuration management may be more difficult this way.

In the latter cases You would use the On Current event of the main form. Then have a select case statement to determine the group. In this code you set the source object (the form inside the subform control).
Something like
Code:
Private Sub Form_Current
  selectCase Me.ProductGroup
    case "SomeGroupName"
         me.subformControlName.SourceObject = "SomeSubFormForGroup1"
    case  "SomeOtherGroup"
           me.subformControlName.SourceObject = "SomeSubFormForGroup2"
     case
  end select

If you only have slight changes but a lot of subforms I would make one form and use code to modify. Again you would use the On Current and a select case. But you would have code to show, hide, move, change colors, blah....
If you have a lot of formatting differences but few subforms than build multiple subforms.
 

Lanser

Registered User.
Local time
Today, 08:44
Joined
Apr 5, 2010
Messages
60
Hi, thanks for the fast response.
I basically have four tables for products, results, targets and groups.
Results and targets have 17 matching fields which are used in a variety of combinations across the groups hence the messyness when I tried to do it on one subform.

John
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:44
Joined
May 21, 2018
Messages
8,463
Results and targets have 17 matching fields which are used in a variety of combinations across the groups hence
If you are saying you have 17 common fields in the two tables, that sounds as if your database may not be properly structured. Usually should not have the same data in two places. However, that is not always the case. I could have Suppliers and Customers. In both table could have first name, last name, mI, address,... but I could hold a lot of other information unique information to a supplier and unique to a customer. I am guessing that is a similar case with Targets and results.

If Targets and Results have 17 fields that are the same but only few different fields then consider a single table

Code:
tblResults_Targets
  ResultTargetType  - Either "Result" or "Target"
 common fields
  unique Target fields
  unique Result fields

I am not sure what is more correct. In purist database design you are probably correct with the two tables. For ease of use you may be better with a single table.
 

Lanser

Registered User.
Local time
Today, 08:44
Joined
Apr 5, 2010
Messages
60
I probably used the wrong word they are not really common fields One product will have one fixed Target but many variable results is why I split the tables
i.e the targ_Ph field will always be 3.2 but the res_Ph field will have a different result for each batch of that product tested.

regards
John
 

Users who are viewing this thread

Top Bottom