Opening a subform based on the option selected in a main form

[jinalpatel]

I have three option buttons (1 option group): construction projects, non construction projects and aquisition projects.

I have created a form with more than 35 data entry fields for each of this options.ex.frmConstructionProjects,frmNonconstruct ionProj, frmAquisitionProjects

When user selects an option fromm the options group it should open the respective subform.

Private Sub optProject_Click()
If Me.optProject.Value = 1 Then
DoCmd.OpenForm "frmNonConstructionProj
Else
If Me.optProject.Value = 2 Then
DoCmd.OpenForm "frmConstructionProjects
Else
If Me.optProject.Value = 3 Then
DoCmd.OpenForm "frmAquisitionProjects"
End If
End If
End If
End Sub
 

It opens a new form rather than putting it like a subform under the option group object. How to make it look like a subform which only contains a data respective to the selected option?

Thanks !!

[ChipR]

It sounds like you want to use a Tab control, and put the appropriate data on each tab page.

[jinalpatel]

Thats one way!!
here is some change in req.
I want all those option buttons on main tab. When user selects one of the option the respective subform (or form)should be opened on 11th tab.
The goal here is :Not giving user the access to all the three kinds of prjects type's data entry fields!!
thanks much!!

[ChipR]

You could have 3 subforms and show/hide them. Or, if the format of the subform is the same, you could just dynamically set the source for a single subform.
Actually, it doesn't seem like you need a subform, just a bunch of controls (data entry fields) that you can hide/unhide.

[jinalpatel]

Where to write the code for show/hide the subform and how?
Is it under the click event of the 11th tab (page)?
something like

If Me.optProject = 1 Then
Show "frmConstructionProjects"
End If
 

[ChipR]

I'd probably keep it in the option group's After_Update. Then you won't change it needlessly. You can do:

mySubform.Visible = True

[jinalpatel]

thanks for the help!!

[ChipR]

You probably want to set one form to Visible = True, and the other two forms to Visible = False in the AfterUpdate code that you already have. Are there 3 values for your optProjects, because you need a case for each of them.

None of the subforms are going to be visible if the user isn't viewing the tab that they are on anyway, but if you really want to hide them you can watch for the tab control's Change() event, test the value of the tab control to see what # tab is now being viewed, and hide/show things accordingly.

[jinalpatel]

Thanks ChipR for your help!! I appreciate it.

[jinalpatel]

Here is the code if anybody is looking at this thread for help themselves!!

Private Sub optProjects_AfterUpdate()
If Me.optProjects.Value = 1 Then
frmConstructionProjects.Visible = True
frmNonConstructionProj.Visible = False
frmAquisitionProjects.Visible = False
End If
 
If Me.optProjects.Value = 2 Then
frmNonConstructionProj.Visible = True
frmConstructionProjects.Visible = False
frmAquisitionProjects.Visible = False
End If
 
If Me.optProjects.Value = 3 Then
frmAquisitionProjects.Visible = True
frmConstructionProjects.Visible = False
frmNonConstructionProj.Visible = False
End If
 
End Sub

OR

Private Sub optProjects_AfterUpdate()
Select Case Me.optProjects
 
Case 1
 
frmConstructionProjects.Visible = True
frmNonConstructionProj.Visible = False
frmAquisitionProjects.Visible = False
 
 
Case 2
 
frmNonConstructionProj.Visible = True
frmConstructionProjects.Visible = False
frmAquisitionProjects.Visible = False
 
Case 3
 
 
frmAquisitionProjects.Visible = True
frmConstructionProjects.Visible = False
frmNonConstructionProj.Visible = False
 
Case Else
frmAquisitionProjects.Visible = False
frmConstructionProjects.Visible = False
frmNonConstructionProj.Visible = False
End Select
 
End Sub
 

I know it is easy but still!!!

[jinalpatel]

I am sorry but I am too impatient..I have one more question regarding the same issue.
When we are going through records using record navigation, it does not change the respective form on 11th tab although it does show (and change it while we are going through records) the option selected previously..
why is that and how to fix it?

[ChipR]

It sounds like you have one option button and subform for each type of project, so they will both need to be updated in the Form's Current() event.

[ChipR]

What I mean is, in the Current event, check the project type, then set the optProjects value. Doing this in code probably will not trigger the option group's after update event, but you can call it yourself.

[jinalpatel]

No I have a optionGroup not a single option button. This group has three options and user selects one of them and be able to see the respective form on the 11th tab.
It works very well when we are entering new record. But does not update the form according to the option selected for the previous data.
Sorry I didn't get the part that says to do something with form's current event.
Can u give a little more hint on that, please?

[ChipR]

I'm not clear on what is supposed to happen when a user moves to another record. Does the subform shown depend on the the option selected only, or on some attribute of the record?

[jinalpatel]

Ya it only depends on the option selected.
The showing of a subform on the 11th tab does not rely on any other attributes but the option button..

[ChipR]

@jinalpatel
I'm very confused by this. What does not change, what does, what should?

[jinalpatel]

Ok
When the user navigates through records, the records are changed on each and every tab. (normal behaviour)
Here, on the first tab it changes the option automatically but does not change the respective form based on that option on 11th tab.

I think this is the issue of refreshing the data each time user navigates through the records. I need to somehow find the way so that the access refreshes itself and show the respective record on each and every tab.

For example,
On main tab I have three options animals, birds and reptiles. On 11th tab I have data entry filds for their qualities
If I am navigating through the records, and my first record has option selected as Animal, it should show the qualities for animals on 11 th tab.(this should make sense)

But here it shows option selected as animals and qualities (on 11th tab) of birds
(because last time when I left access I entered record which has option selected as birds)
Does this make sense?

If not then it is ok..I need to figure it out somehow!!!!

Thanks!!

[ChipR]

Just a few more questions, I promise :-]

@jinalpatel
How is this changed automatically? I was under the impression from your other post that the option group and hence the subform were only selected by the user.

[ChipR]

Do you have a default set for the option group when the form first opens? Try putting this in the Form's Current event:

   optProjects_AfterUpdate

[jinalpatel]

I think my hard work to explain you worked and it is working with just a single line of code.Perfect!!! Many many thanks to you for helping out..
I just wrote

Private Sub Form_Current()
optProjects_AfterUpdate
End Sub
 

[ChipR]

I'm glad you got it working :)