Make Fields on form visible only if certain field has less than a certain value

I'm trying to build a form that will track information for utility payments for a college campus.

On the form I have the fields for account 1 and percentage 1 visible and accounts 2 and 3 and percentages 2 and 3 are not visible.  I want to makes account 2 visible if percentage 1 is less than 100% and account and percentage 3 visible if 1 + 2 is less than 100%.

this is the code I have and it isn't working:
Private Sub NCSANum1Percent__Exit(Cancel As Integer)

    If NCSANum1Percent < 100 Then
        NCSANum2.Visible = True
        NCSANum2Percent.Visible = True
        DoCmd.GoToControl "ncsanum2"
    End If
           
End Sub

Private Sub NCSANum2Percent_Exit(Cancel As Integer)

    If (NCSANum1Percent + NCSANum2Percent) <> 1 Then
        NCSANum3.Visible = True
        NCSANum3Percent.Visible = True
        DoCmd.GoToControl "ncsanum3"
    Else
        NCSANum2.Visible = False And NCSANum2Percent.Visible = False
    End If
       
End Sub
dcphilliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
in what way is this code 'not working'?  Have you set a breakpoint in the NCSANum1Percent__Exit routine, and then single stepped through the code?

rather than using

DoCmd.GoToControl "ncsanum2"


try:

ncsanum2.SetFocus

AW
Arthur_WoodCommented:
also, this line:

NCSANum2.Visible = False And NCSANum2Percent.Visible = False

should be TWO lines:

NCSANum2.Visible = False
NCSANum2Percent.Visible = False

as you had it coded, it evaluates the TRUTH of the line (AND is a LOGICAL operator, and does not allow you to execute both parts as if they were separate statement  - the line is NOT  do part1 and then do part 2)

AW
dcphilliAuthor Commented:
if I put in .25 as the percentage for ncsanum1percent, I want it to then make ncsanum2 and ncsanum2 percent visible, and it doesn't.  It just goes to a new record.

I have made the changes suggested and it still doesn't work.

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

kenspencerCommented:
Hi,
I don't think you should have the ELSE clause in the second subroutine -- if items 1 + 2 add up to 100% you would still want to see the second item fields.

Ken
dcphilliAuthor Commented:
I commented that section out, and added me.refresh and it still doesn't make num2 and percent2 visible.

Private Sub NCSANum1Percent__Exit(Cancel As Integer)

    If NCSANum1Percent < 100 Then
        NCSANum2.Visible = True
        NCSANum2Percent.Visible = True
        Me.Refresh
        NCSANum2.SetFocus
        'DoCmd.GoToControl "ncsanum2"
    End If
           
End Sub

Private Sub NCSANum2Percent_Exit(Cancel As Integer)

    If (NCSANum1Percent + NCSANum2Percent) <> 1 Then
        NCSANum3.Visible = True
        NCSANum3Percent.Visible = True
        NCSANum3.SetFocus
        'DoCmd.GoToControl "ncsanum3"
    'Else
        'NCSANum2.Visible = False
        'NCSANum2Percent.Visible = False
    End If
       
End Sub
broesiCommented:
Just a quick guess:

you are trying to hide the controls in the ON_Exit-event of NCSANum2Percent. That means, it is only run when you move the cursor out of this field.
If you want to hide the other field generally, try putting your code in the Current-Event of the form!

Another problem that might arise: if your form is a continouus form, your code will lead to ALL additional fields of all visible records being hidden/showed...

HTH,


broesi
rcdickersonCommented:
It seems like in your first if statement, one-hundred percent is '100', but in your second if statement, one-hundred percent is '1'. Are the percentages expressed as decimals (like .54) or as whole numbers (like 54)?

Make sure you are actually entering the branch of code that sets visibilities before you do anything else. This may seem kind of basic, but try puting a

Debug.Print "Test"

in right after your if statement , open the immediate window in your VB editor (view->immediate window or Cntl+G), and try running it. If you see 'Test' displayed after you run the code, then you know you made it to the correct branch. Otherwise, check your program logic to make sure everything is working out the way you want it to.
dcphilliAuthor Commented:
I got it to work by changing it to an after_update event instead of an exit event.

Thanks for your help anyway.

Denise
broesiCommented:
dcphilli,

great! But please note that your fields will not be hidden/shown correctly while moving from record to record!
You would have to use the OnCurrent-event to accomplish this!

broesi
dcphilliAuthor Commented:
okay, give me a little more on this.  I saw that when I scrolled through the records that 2 and 3 didn't show up.

can you help me some more?  I'll give you the points if you tell me how to fix that.

broesiCommented:
as said in my last post: move your code to the OnCurrent-event of the form. This event is fired everytime the form moves to another record.

broesi

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.