Results 1 to 12 of 12

Thread: [RESOLVED] Access Force two text boxes to always equal 100 percent

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2014
    Posts
    7

    Resolved [RESOLVED] Access Force two text boxes to always equal 100 percent

    I'm new to the vbforums and excited to see a lot of activity here.

    I'm not sure how to even ask the question correctly but I have a tbl that has two columns, GroupA and GroupB. I want the total for both A and B to always equal 100 percent. And to force the other box to change it's number to equal 100 percent when the other boxes number is changed within the form. Which of course is changing the numbers in the tbl. There is only one record being saved for each box.

    When GroupA's number is changed to 48% then GroupB's numbers have to change to 52%. But if i wanted to change GroupB's number in stead (and first) it would look at GroupB's number and force GroupA's number to change to equal 100%.

    I've looked everywhere and can't find anything to help or guide me in a direction. My VBA skills are very Noob for sure.

    Thanks guys for any help at all.
    Gary
    Last edited by glwilson; Jun 25th, 2014 at 11:29 AM.

  2. #2
    Hyperactive Member
    Join Date
    Oct 2010
    Location
    Indiana
    Posts
    457

    Re: Access Force two text boxes to always equal 100 percent

    You can do it in the textbox's events (you'll have to do it for both textboxes where you enter "GroupA's number" and "GroupB's number")
    Assuming GroupA's txtbox is named textbox1, and GroupB's is named textbox2, the following code will go in the "After Update" event of textbox1:

    Code:
        If IsNumeric(Me.textbox1.Value) Then
            Me.textbox2.Value = 100 - CInt(Me.textbox1.Value)
        End If
    Now, this code just makes sure there is a numerical value entered into the textbox - you could do some additional checking to make sure they enter a percentage between 1 & 100, as well as other things. It is also assuming that you are entering whole numbers (100 = 100%), otherwise, if you are actually typing %, it will require a different code...

    This code will also work for Textbox2, you will just have to swap textbox1 & textbox2 in the code and put it in the After Update event of textbox2.
    Last edited by nO_OnE; Jun 27th, 2014 at 10:08 AM. Reason: Clicked wrong button....

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2014
    Posts
    7

    Re: Access Force two text boxes to always equal 100 percent

    Thank you so much for the response "nO_OnE".

    I tried the code and it does change the amount in the second text box but under 50% the second text box shows 10% in the other text box. Anything over 50% it shows up -90% in the other text box. I changed the 100 to 0.1 as the text boxes are already formated to percentages.

    Here my code but i'll keep playing with it. Thank you again.


    Code:
    Private Sub LADay10s_AfterUpdate()
     If IsNumeric(Me.LADay10s.Value) Then
            Me.LANight10s.Value = 0.1 - CInt(Me.LADay10s.Value)
        End If
    End Sub
    
    Private Sub LANight10s_AfterUpdate()
     If IsNumeric(Me.LANight10s.Value) Then
            Me.LADay10s.Value = 0.1 - CInt(Me.LANight10s.Value)
        End If
    End Sub

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Access Force two text boxes to always equal 100 percent

    Wouldn't it be 1, not .1?


    Edit: And you wouldn't then convert the other box's value to an integer, either, right?
    Last edited by vbfbryce; Jun 28th, 2014 at 06:11 AM.

  5. #5

    Thread Starter
    New Member
    Join Date
    Jun 2014
    Posts
    7

    Re: Access Force two text boxes to always equal 100 percent

    Sorry for not posting back sooner.

    When I use "1" it gives an absolute "0" or "100" as the result. So I'm thinking the code works bit not as desired. It needs to equal 100, not display 100 in the other txt box.

    Thank you anyway guys. Something will turn up.
    Cheers. :-)

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Access Force two text boxes to always equal 100 percent

    assuming that the value in the textbox is any number less than 100
    the second textbox must = 100 less the value in the first textbox
    if you want to avoid issues with % in textbox use val

    code should be
    Code:
    Private Sub LADay10s_AfterUpdate()
     If IsNumeric(Me.LADay10s.Value) Then
            Me.LANight10s.Value = 100 - al(Me.LADay10s.Value)
        End If
    End Sub
    
    Private Sub LANight10s_AfterUpdate()
     If IsNumeric(Me.LANight10s.Value) Then
            Me.LADay10s.Value = 100 - val(Me.LANight10s.Value)
        End If
    End Sub
    is there any code in other events for either textbox? code in other event may cause unexpected results for those of us that can not see it
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Jun 2014
    Posts
    7

    Re: Access Force two text boxes to always equal 100 percent

    Thank you much Westconn1.

    The code works pretty good but with a 99 in the front of the opposite textbox. So if I type in 25 in "LADay10s" textbox, then "LANights10s" becomes 9975. Instead of just 75. And vise versa. It's consistent but strange.

    Thank you really for the help. I'll keep playing with it.

  8. #8

    Thread Starter
    New Member
    Join Date
    Jun 2014
    Posts
    7

    Re: Access Force two text boxes to always equal 100 percent

    Well i think i found it. I'm formatting the textbox as "Percent" when i take that back to "Standard" it's fine. I'd like it to show the % char if possible. Any ideas on that?

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Access Force two text boxes to always equal 100 percent

    try like
    Me.LADay10s.Value = 100 - val(Me.LANight10s.Value) & "%"
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    New Member
    Join Date
    Jun 2014
    Posts
    7

    Re: Access Force two text boxes to always equal 100 percent

    Seems that it breaks the code in the adjacent textbox. It returns a zero in the Nights textbox when you enter a value into the Days textbox. I even tried double quotes and single around the %.

    Thank you again Westconn1 for the help.

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Access Force two text boxes to always equal 100 percent

    put a label at end of textbox for % character?

    i tested. added a userform with 2 textboxes
    Code:
    Private Sub textbox1_AfterUpdate()
     If IsNumeric(TextBox1.Value) Then
            TextBox2.Value = 100 - Val(TextBox1.Value) & "%"
            TextBox1.Value = TextBox1.Value & "%"
        End If
    End Sub
    
    Private Sub textbox2_AfterUpdate()
     If IsNumeric(TextBox2.Value) Then
            TextBox1.Value = 100 - Val(TextBox2.Value) & "%"
            TextBox2.Value = TextBox2.Value & "%"
        End If
    End Sub
    all worked fine, just type the numbers in text box, % added to both
    if you type the % it will fail on isnumeric, unless you change like
    Code:
    Private Sub textbox1_AfterUpdate()
    pos = InStr(TextBox1, "%")
     If IsNumeric(Left(TextBox1.Value, Len(TextBox1) + CBool(pos))) Then
            TextBox2.Value = 100 - Val(TextBox1.Value) & "%"
            If pos = 0 Then TextBox1.Value = TextBox1.Value & "%"
        End If
    End Sub
    change textbox names to suit
    this code is tested to work correctly whether or not user types %
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    New Member
    Join Date
    Jun 2014
    Posts
    7

    Re: Access Force two text boxes to always equal 100 percent

    So cool. You really helped me out here. Works great!

    You're awesome!

    Thank you so much Westconn1. Sweet!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width