Open subform depending on choice in frame

[JonoB]

I have a main form with an option group that allows users to choose one of 5 options (although this may increase). The forms also has 5 hidden subforms and depending on which one of the 5 choices options are chosen, then one of five sub forms will be made visible.

In order to do this, I think that the code must make all subforms visible = false and then open up the correct subform, every time the option group is updated. I am pretty much there, by using the following code on the After Update event of the option group. This iterates through all subforms(with a trailing number between 1 and 5) and sets them visible = false. This works 100%

Dim i As Integer
For i = 1 To 5
Me("sfrmRSLpis" & i).Visible = False
Next i

However, now the part I cant get right - depending on the choice made in optiongroup, I want to set the relevant sfrm visible = true.

I have tried the following code, but it Access doesnt seem to like it and it cant seem to pick up the option value chosen in the option group:

Dim i As Integer
Dim f As Integer

Me.Framepis.Value = f

For i = 1 To 5
Me("sfrmRSLpis" & i).Visible = False
Next i

Me("sfrmRSLpis" & f).Visible = True


Any help much appreciated!

 

[sillysod]

the way i would do it is to set all 5 sfrms as visible = false and enabled = false in the form load option.

then i would use an IF statement like this in the afterupdate event
(just going to use 3 sfrms as an example.

'if option group selection is option 1 then set sfrm 1 as enabled and visible and sfrm 2 and 3 as disabled and invisible

if optiongroup.value = "1" then

sfrm1.enabled =true
sfrm2.enabled =false
sfrm3.enabled =false

sfrm1.visible = true
sfrm2.visible = false
sfrm3.visible = false

else if

'if option group selection is option 2 then set sfrm 2 as enabled and visible and sfrm 1 and 3 as disabled and invisible

optiongroup.value = "2" then

sfrm1.enabled =false
sfrm2.enabled =true
sfrm3.enabled =false

sfrm1.visible = false
sfrm2.visible = true
sfrm3.visible = false

else if

'if option group selection is option 3 then set sfrm 3 as enabled and visible and sfrm 1 and 2 as disabled and invisible

optiongroup.value = "3" then

sfrm1.enabled =false
sfrm2.enabled =false
sfrm3.enabled =true

sfrm1.visible = false
sfrm2.visible = false
sfrm3.visible = true

else if

optiongroup.value = "" then

sfrm1.enabled =false
sfrm2.enabled =false
sfrm3.enabled =false

sfrm1.visible = false
sfrm2.visible = false
sfrm3.visible = false

endif
endif
endif

im sure theres a neater way of doing it, maybe with a select case decision structure etc, but thats the way me and my basic visual basic would do it

 

[JonoB]

Yeah, I know that I could do that.....but what if I have 10 subforms (or more!). Way too much code !!!

I know that there must be an easier way.

 

[sillysod]

fair enough, i know i have a database at work that uses this for 9 buttons and 3 options (3 buttons show for each option) and its alot of code and a pain.

so howabout the select case structure. althought its still alot of code.

in afterupdate

'declare ingrOption as integer
dim ingrOption as integer
'set value of strOption
ingrOption = optiongroup.value

'clear form so theres no need to put the enable and visible values = false in for every case.

sfrm1.enabled =false
sfrm2.enabled =false
sfrm3.enabled =false

sfrm1.visible = false
sfrm2.visible = false
sfrm3.visible = false

select case strOption

Case 1

sfrm1.enabled =true

sfrm1.visible = true

case 2

sfrm2.enabled = true

sfrm2.visible = true

case 3

sfrm3.enabled = true

sfrm3.visible = true

case else

sfrm1.enabled =false
sfrm2.enabled =false
sfrm3.enabled =false

sfrm1.visible = false
sfrm2.visible = false
sfrm3.visible = false

 

[JonoB]

Doh! Too many late nights! Just had my "f" integer statement the wrong way around. This works perfectly and is nice and consise.

Dim i As Integer
Dim f As Integer

f = Me.Framepis.Value

For i = 1 To 5
Me("sfrmRSLpis" & i).Visible = False
Next i

Me("sfrmRSLpis" & f).Visible = True