choosing which subform based on option button

[Guest]

I have a single database to cover inventory where there are 3 types of inventory and different detail questions to be asked for each, depending on what the type is. I'd like to create a main inventory form for the general info, w/ an option button for each type of item (pieces, custom, factory). Then I'd like the form to display one of three possible sub-forms within it's frame depending on what type is selected. Am I:

a. completely crazy?
b. trying to make this far more complex than necessary?
c. close but not quite there yet?

Thanks to anyone who can help me!

-beejsnyder

 

[Chris]

It's none of the three

What I have done on a similar occasion is to create a tab
control, set the "tab style" property to "None", and
insert a subform on each page of the tab control. Then
the option button controls which tab has focus
(using .Setfocus) and, therefore, which subform is
visible.

HTH

Chris

-----Original Message-----
I have a single database to cover inventory where there

are 3 types of inventory and different detail questions
to be asked for each, depending on what the type is. I'd
like to create a main inventory form for the general
info, w/ an option button for each type of item (pieces,
custom, factory). Then I'd like the form to display one
of three possible sub-forms within it's frame depending

 

[Allen Browne]

In the AfterUpdate event procedure of your option group, set the
SourceObject property of your subform control:

Select Case Me.MyGroup.Value
Case 1
Me.[fsubGeneric].SourceObject = "Form1"
Case 2
Me.[fsubGeneric].SourceObject = "Form2"
Case 3
Me.[fsubGeneric].SourceObject = "Form99"
Case Else
Msgbox "Huh?
End Select

If the three types have similar fields, it would be a better structure to
use just one table, with an extra field to indicate the type of entry
(pieces, custom, or factory).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a single database to cover inventory where there are 3 types of

inventory and different detail questions to be asked for each, depending on
what the type is. I'd like to create a main inventory form for the general
info, w/ an option button for each type of item (pieces, custom, factory).
Then I'd like the form to display one of three possible sub-forms within
it's frame depending on what type is selected. Am I:

 

[John Vinson]

I have a single database to cover inventory where there are 3 types of inventory and different detail questions to be asked for each, depending on what the type is. I'd like to create a main inventory form for the general info, w/ an option button for each type of item (pieces, custom, factory). Then I'd like the form to display one of three possible sub-forms within it's frame depending on what type is selected. Am I:

a. completely crazy?
b. trying to make this far more complex than necessary?
c. close but not quite there yet?

It's quite doable, if a bit complex. What you could do is change the
SourceObject property of the Subform control in the AfterUpdate event
of the option group. If 1, 2, 3 are the values of the three options
the code might be

Private Sub optItemType_AfterUpdate()
Select Case optItemType
Case 1
Me!subMySubform.SourceObject = "sbfrmPieces"
Case 2
Me!subMySubform.SourceObject = "sbfrmCustom"
Case 3
Me!subMySubform.SourceObject = "sbfrmFactory"
End Select
End Sub

Here subMySubform is the Name property of the subform control;
sbfrmPieces and so on are the names of the forms which you want to
display in that control.

 

[tina]

hi beej. Allen and John's solution is very handy and easy to code. one
advantage of the solution is that, when you open your main form, it will
take less time to load one unbound subform control, as opposed to 3 bound
subform controls. fyi, it also works with a combo box, if you ever need to
replace the option group with something that takes up less space on your
main form.

I have a single database to cover inventory where there are 3 types of

inventory and different detail questions to be asked for each, depending on
what the type is. I'd like to create a main inventory form for the general
info, w/ an option button for each type of item (pieces, custom, factory).
Then I'd like the form to display one of three possible sub-forms within
it's frame depending on what type is selected. Am I:

 

[Guest]

Thanks to everyone for the help -- I think I'm almost there (in that it's not giving me error messages anymore)

The question I'm left with is how to set up the form to know where to put the appropriate subform? I have created the three sub-forms and have set up the option group w/ the values and the event procedure for after update. But how to I tell the original form "display SubformCustom here because option 1 was selected?"

Thanks!

 

[Guest]

I feel like I should clarify my question here.

I have an Option Group called "Department" and bound to a field in the table called "Dept.". Within the Option Group I have three radio buttons, for Custom, Factory and Gemstones. I have subforms created with the same three names. I have a subform frame called "DeptSubform". And, finally, I have the following code in the AfterUpdate event for the Option Group called "Department":

Private Sub OptItemType_Enter()
Select Case OptItemType
Case 1
Me!DeptSubform.SourceObject = "Custom"
Case 2
Me!DeptSubform.SourceObject = "Factory"
Case 3
Me!DeptSubform.SourceObject = "Gemstones"
Case Else
MsgBox "What Department?"
End Select
End Sub

My goal is that I will click on the button called "Custom" and within the subform frame the subform called "custom" will be displayed, and the same cause-and-effect for the other two radio buttons and their corresponding subforms. But right now when I click on a radio button it DOES put the appropriate value in the table (yay!) but no subform is displayed. I feel VERY close but that I probably am misunderstanding some aspect of the event code.

Help? And thanks both in advance and after!

-beejsnyder

 

[tina]

Private Sub OptItemType_Enter()
did you copy the posted code directly from your form module? if so, this
code is running on the option group control's Enter event, not the
AfterUpdate event. if the bound field of the control is empty when you enter
the control, then of course the code will not assign a SourceObject.
if you're using the form for adding new records, AND reviewing/updating
existing records, you should have the code running on both the option group
control's AfterUpdate event, and the form's Current event. the posted code
itself looks fine to me, assuming you got all the "names" right. suggest you
put it in a separate procedure, such as
Private Sub isSource()
Select Case OptItemType
etc, etc, etc
End Sub
then call the procedure from the control AfterUpdate event and form Current
event, as
Private Sub OptItemType_AfterUpdate()
isSource
End Sub
and
Private Sub Form_Current()
isSource
End Sub

btw, you need to make sure the correct value is entered in the
ChildLinkFields property in the subform control (frame), when you set the
SourceObject.

hth

I feel like I should clarify my question here.

I have an Option Group called "Department" and bound to a field in the

table called "Dept.". Within the Option Group I have three radio buttons,
for Custom, Factory and Gemstones. I have subforms created with the same
three names. I have a subform frame called "DeptSubform". And, finally, I
have the following code in the AfterUpdate event for the Option Group called
"Department":

Private Sub OptItemType_Enter()
Select Case OptItemType
Case 1
Me!DeptSubform.SourceObject = "Custom"
Case 2
Me!DeptSubform.SourceObject = "Factory"
Case 3
Me!DeptSubform.SourceObject = "Gemstones"
Case Else
MsgBox "What Department?"
End Select
End Sub

My goal is that I will click on the button called "Custom" and within the

subform frame the subform called "custom" will be displayed, and the same
cause-and-effect for the other two radio buttons and their corresponding
subforms. But right now when I click on a radio button it DOES put the
appropriate value in the table (yay!) but no subform is displayed. I feel
VERY close but that I probably am misunderstanding some aspect of the event
code.

Help? And thanks both in advance and after!

-beejsnyder

put the appropriate subform? I have created the three sub-forms and have
set up the option group w/ the values and the event procedure for after
update. But how to I tell the original form "display SubformCustom here
because option 1 was selected?"

 

[Guest]

Tina:

First off, you're my hero. Seriously.

Secondly, I was looking at the ChildLinkFields property for the subform, but here's my question there. The subform and the main form are both from the same table, and I don't repeat any fields in the subform from the main form, so what would I put in these fields (ChildLink and Master, right?)? If there were two different tables involved I know I'd put the field that links them, but these are from one table so I'm confused. Thanks again for all your help!

-beejsnyder

 

[tina]

wouldn't that be heroine? <g>
i don't think i've ever used the same table as the RecordSource for both
objects in a form/subform. i'd guess you'll have to include the primary key
field in the RecordSource of the main form and the subforms that are used in
the main form. your LinkChildFields would be the primary key field, which in
this case is the same field used for LinkMasterFields. try it and see if it
works.

having said that, i really have to question your table design. if you are
selecting a specific inventory type, and then entering data in certain
fields that correlate to that type, AND you've got all those fields in one
table - *that table is not normalized!*
strongly recommend that you stop right where you are, forget forms for the
moment, and fix your table design before you go any further. trying to build
queries, forms and reports "on top of" a poor table design is nothing but an
unending hassle and headache, believe me.
if you'd like to post your current table's fields, including noting what
fields "go with" what specific "type", perhaps i (or someone else) can make
a table design recommendation to help you. the following format will be easy
to read:

tblMyTableName
AFieldName (primary key)
BFieldName
CFieldName (type)
DFieldName (type x)
EFieldName (type x)
FFieldName (type y)
GFieldName (type z)
HFieldName (type z)
etc, etc, etc

hth

Tina:

First off, you're my hero. Seriously.

Secondly, I was looking at the ChildLinkFields property for the subform,

but here's my question there. The subform and the main form are both from
the same table, and I don't repeat any fields in the subform from the main
form, so what would I put in these fields (ChildLink and Master, right?)?
If there were two different tables involved I know I'd put the field that
links them, but these are from one table so I'm confused. Thanks again for
all your help!