Show Hide Subforms

[Scott A]

I have a table which has three sub-tables, all using the
same primary key (they're truly sub-tables, the main table
holds the core data, while only one of the three subs will
include data, based on the type selected in the main
table).

I would like to build a form that presents fields for only
one of these three subforms (I have subforms built on each
of the 3 sub-tables), based on the selection made in an
option group.

I have a few questions before I get started, and am
looking for a little guidance:

1) is it best to program this type of behavior with the
OnLoad event?

2) Which is the best statement to use? If... Then...
Else or Select Case?

Thanks!

Scott A

 

[Michel Walsh]

Hi,


Use a sub-form that would change for each "type" of information to be
displayed.


With Main table:

pk, f1, f2, kindOfData ' field name


(where f1 and f2 are common data to all types, the core data, and kindOfData
is, as example, the name of the type), then, in the onCurrentEvent of the
main form,


Me.SubFormControlName.SourceObject= "frm" & Me.KindOfData


which would display the subform frmPlane, frmTruck, frmTrain, frmShip,
..... accordingly to the kindOfData value being "Plane", "Truck", "Train",
"Ship", ... The right subform being displayed, it is up to your program to
re-establish the links (main form-subform) if required. If you want to add
"OVNI", just have to create the form frmOvni, and use the KindOfData =
"OVNI". With a Select Case statement, or with a cascade of if -elseif, you
would have to touch the code of the main form. Using the object oriented
approach just proposed, you don't have to.


Sure, the form frmPlane, used as subform, is bound to what you call the
sub-table that is concerned about Planes, and so for frmTruck. As example,
the frmPlane may display a control call wingspan... but frmTruck won't have
such field... that creates no problem... since each (sub) form is
independent.... and only eventually refer to the "core" values of its Parent
form.



Since the subform changes for each record (the onCurrent event fire each
time a record is about to be displayed), it would be preferable that each
subform got the same size, and no border (to merge, graphically, with the
main form) so it appears to be one form, for the end user. To avoid
flickering, you may test:

If Me.SubFormControlName.SourceObject <> "frm" & Me.KindOfData Then
Me.SubFormControlName.SourceObject= "frm" & Me.KindOfData
End If


Hoping it may help,
Vanderghast Access MVP

 

[Michel Walsh]

Hi,


Alternatively, indeed, you can place the three subforms, in the main
form, and turn two invisible. In the onCurrent event, turn the desired on
visible, as appropriate:

Me.pk.SetFocus 'can't turn invisible if we are in the subform
that turns invisible
Select Case TypeOfData
Case "Plane"
frmTruck.Visible=False
frmShip.Visible=False
frmPlane.Visible=True
Case "Truck"
...
...


Among the immediate inconveniences, you may note it is slower to load
and to navigate through records... since all the three subforms are updated,
even if two of them are invisible... and it get worse more you add "type of
data"...


Hoping it may help,
Vanderghast, Access MVP