Forms/Subforms

[GSchafer1]

I have a combo box on my Main Form with several items on the list, lets say, A,B,C,D. When someone selects "A", I want Asubform to come up on the main form. Bsubform when "B" is selected, Csubform when "C" is selected, so on, and so forth. Your help would be greatly appreciated.

 

[Rob.Mills]

You could create a sub in the After Update event of the combobox. What I would do would work easiest if you name the subform control to match it's selection in the combobox.

Then the sub would be

Dim ctl as Control

For Each ctl in Forms!FormName.Controls

If Typeof ctl is Subform then

If ctl.Name=me!combobox.value then

ctl.visible=true

else

ctl.visible=false

end if

end if

next ctl

 

[GSchafer1]

I'm still not sure what your saying. I'm still pretty new. The form is called Player, the combo box is called Sport and its list is sports. Baseball, Basketball, etc. Since describing each sport has so many other options (combo boxes) I've elected to use a subform for each sport to provide the specific information. When the sport is selected in the combo box on the Player form I need to call the appropriate subform. The subforms are titled Baseball, etc. I hope that explains it!

 

[Rob.Mills]

I understand what you're trying to accomplish. If someone selects Baseball in the combobox then the Baseball subform will show up. And the other subforms will be invisible, yes?

If so, the routine I gave you will work. Since you're pretty new, and maybe don't have any experience with VBA I'll describe the procedure a little more.

In the properties window of the combobox you can can find AfterUpdate on the events tab. Click on the build button at the end of that line. I might ask you what you want to create. Choose Event Procedure. This will automatically create something like this in the VBA editor:

Private Sub Combo1_AfterUpdate()

End Sub

What I want you to do is insert the following code in between those lines. Everything that is green is not necessary to copy because it's comments. Everything in red you will need to change to reflect your personal database.

'This line will create a variable that can point to any kind of control on a form or report.
Dim ctl as Control

'This line will tell the program to cycle through every single control on your form
For Each ctl in Forms!FormName(Enclose in brackets if there are spaces).Controls

'This will test to see if the control it's at for the moment is a subform. If it is it will run more tests. If not it skips it.
If Typeof ctl is Subform then

'Now if the subform name is the same as the selection in the combobox then it will make it visible. If it isn't then it will be hidden.
If ctl.Name=me!ComboboxName.value then

ctl.visible=true

else

ctl.visible=false

end if

end if

'This line will tell it to move on to the next control in the list and run all the same tests again
Next ctl



Try this out and let me know how it goes.

 

[GSchafer1]

Thanks

That was a great explanation. Thanks for the help!

 

[GSchafer1]

Not Yet!

Rob,
I've enter the code according to your instructions. Whenever I select any sport on the combo box the subforms all go away, and no matter what I select next they won't come back. By the way, you do understand exactly what I'm trying to do. Also, I've doubled checked everything.

 

[Rob.Mills]

Have you double-checked that the values in the combobox match exactly the name of the corresponding subform? You might have matched the value up to the source object. That won't cause a problem but it's not going to make the sub work. Remember a subform has basically two parts to it. The subform container and the form inside the container. This function is setup to run on the name of the container.

 

[GSchafer1]

Try This

This is in the combo box on the form Customers1

Private Sub cboSport_AfterUpdate()
Dim ctl As Control
For Each ctl In Forms!Customers1.Controls
If TypeOf ctl Is SubForm Then
If ctl.Name = Me!cboSport.Value Then
ctl.Visible = True
Else
ctl.Visible = False
End If
End If
Next ctl
End Sub

It's still doing the same thing. Sorry Rob, I feel like an idiot but I can't fix it.

 

[Rob.Mills]

It's got me a little bewildered too. I've gotten this same code to work.

You can send me the db. robert.mills@zcsterling.com

Don't compress it. They haven't given me access to zip software at work.