Display subform depending on field value

[rjf3123]

OK, my next adventure is this:

On my main form, I have a field (FeePercent) which can contain one of three possibilities; 0%, 40% or 50%.

I have three subforms defined, one for each of the 3 percentages from this field.

When I open the main form, I would like to know if it is possible to have the appropriate subform come up with the main form depending on the value of the FeePercent field.

i.e. if FeePercent = 40%, bring up the 40% subform.

Thanks! -
R. Fisher

 

[mark curtis]

Set all 3 sub forms to be invisible:

On_Current

if me.FeePercent.value = 50% then subfrm50%.visible=True
Elseif me.FeePercent.value = 40% then subfrm40%.visible=True
Else
me.subfrm0%.visible=True
End if

 

[rjf3123]

Mark -
Getting closer (I think).

All three subforms (0%Subform, 40%Subform & 50% Subform) are on the main form with "visible"=No for all three.

On all 3 subforms, in the "On Current" I copied your "IF" statement as it appears - making the neceesary changes to reflect the actual field name and subform name:

if me.FeeElection.value = 50% then 50%subform.visible=True Elseif me.FeeElection.value = 40% then 40%subform.visible=True Else 0%subform.visible=True End if

When I bring up the form, I get the message:

"Microsoft Access can't find the macro 'if me.' "

Is this "If" statement supposed to be a macro or does this have something to do with the "me." after the "IFs"?

Thanks -
R. Fisher

 

[mvos]

Can I suggest an alternative way.

What is the datatype of the percentage field, is it text?

If so then

Select Case Me.FeeElection
Case "40%"
Me.SubFormControl.SourceObject = "40%Subform"
Case "20%"
Me.SubFormControl.SourceObject = "20%Subform"
Case Else
Me.SubFormControl.SourceObject = "0%Subform"
End Select

Replace 'SubFormControl' with the name of the actual subform control

 

[rjf3123]

FeeElection field is a number - percentage. Don't want to change it to a text field 'cause I need to make calculations against it elsewhere.

R. Fisher

 

[mvos]

OK just change the

Case "40%"

to

Case .4

 

[rjf3123]

Ok, I know I have to build an event for this, never used vb side of access before.

So I have the 3 subforms (SF0, SF40, SF50) on the main form called ClientTracking. Each of the subforms has "visible" set to "no".

There is a field on the ClientTracking form from the ClientTracking table called "FeeElection" which is a number, 0%, 40% or 50%.

On the ClientTracking form (parent form) itself, in properties for "On Current", I defined an [event procedure]. the event procedure reads:

Private Sub Form_Current()
Select Case Me.FeeElection
Case 0.5
Me.SubFormControl.SourceObject = "SF50"
Case 0.4
Me.SubFormControl.SourceObject = "SF40"
Case Else
Me.SubFormControl.SourceObject = "SF0"
End Select
End Sub

When I pull the ClientTracking form up, I get a message:

Compile Error: Method or data member not found

with .SubFormControl highlighted.

You said to "Replace 'SubFormControl' with the name of the actual subform control" But I am not sure what that means.

I know it should be pretty basic, but beyond the point-n-click side of Access, I am very new to this.

Thanks Again!
R. Fisher

 

[mvos]

Right click over the control that holds the subform on the main form and open the properties window. Select the All tab, the first entry (Name) contains the name of the subform control.

That's what you should be using.

 

[RichMorrison]

you wrote
<<
Me.SubFormControl.SourceObject = "SF50"
>>

Try this instead:
Me![SubFormControl].SourceObject = "SF50"

Access thinks Me.Something is a *method*

RichM

 

[rjf3123]

I will get this, swear to "you know who"...

Ok, each of the 3 subforms are named:

SF0, SF40 and SF50 respectively.

I change the code to:

Private Sub Form_Current()
Select Case Me.FeeElection
Case 0.5
Me.SF50.SourceObject = "SF50"
Case 0.4
Me.SF40.SourceObject = "SF40"
Case Else
Me.SF0.SourceObject = "SF0"
End Select
End Sub

As I cycle through the records, the records where the FeeElection is 0% show the SF0 subform, but the records where the FeeElection is either 40% or 50% do not change the subform to the proper one. In other words, the SF0 (0% subform) stays on the screen regardless.

R. Fisher

 

[mvos]

LOL

Put this line just before the Select Case line

Msgbox Me.FeeElection

and check that the result matches the numbers in your case statements

 

[rjf3123]

Put the line in ...

when I bring up the form (first record happens to be 0% Fee Election, I get a little msg box that has 0, the 40%s say 0.4 and the 50%'s say 0.5

R. Fisher

 

[rjf3123]

Msgbox = Poor Man's debugger???

 

[mvos]

True but it proves the point

I've just re-read your previous post

Private Sub Form_Current()
Select Case Me.FeeElection
Case 0.5
Me.SF50.SourceObject = "SF50"
Case 0.4
Me.SF40.SourceObject = "SF40"
Case Else
Me.SF0.SourceObject = "SF0"
End Select
End Sub

The Me.ControlName should be the same for each one. You've put in the various subform names.

You need to put in the name of the control, it may be something like Child1

 

[rjf3123]

Folks -
Thanks all for the assist with this. Trial and error with these options finally gave me the results I was looking for.

The following is the "solution" I have working. Not sure if it is conventional or not, but at least it works and brings up the correct subform based on the FeeElection field.

Wanted to pass this back to let you know what I ended up with and again, THANK YOU!

Private Sub Form_Current()
Select Case Me.FeeElection
Case 0.5
SF0.Visible = False
SF40.Visible = False
SF50.Visible = True
Case 0.4
SF0.Visible = False
SF50.Visible = False
SF40.Visible = True
Case Else
SF40.Visible = False
SF50.Visible = False
SF0.Visible = True
End Select
End Sub

R. Fisher