Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Question Unanswered: Interesting challenge - feet or metres?

    Hi,

    I have a Combobox to give the option for a user to insert the area of his accommodation in either ft2 or m2.

    I have 2 text boxes - lets call them Text1 (for the area in ft2) and Text2 for the area in m2).

    If the user chooses to insert the area in ft2 (from the Combo) then the user cam insert, say 100, in Text1 (eg. the area of his accommodation is 100 ft2).
    So Text2 should automatically calculate the area in m2 and Lock the user from adjusting the value.

    But, if the user choose m2 from the Combo, then I want the opposite to happen eg. I want the user to insert the area in m2 in Text2, and Texty1 to automatically calculate the area in ft2 (and Lock the Text1 box).

    What kind of code do I put in the After UPdate field - and in which Box? Can I put an "if" statement in the After Update field?

    i think my explanation is too long - if nobody understands then i will expalin again??

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Obviously, it's the combo selection that will decide in which textbox the user can enter a value, so:
    Code:
    Private Sub Combo_Unit_AfterUpdate()
    
        Select Case Me.Combo_Unit.Value
            Case "ft2"
                Me.Text_ft2.Locked = False
                Me.Text_m2.Locked = True
                Me.Text_ft2.SetFocus
            Case "m2"
                Me.Text_m2.Locked = False
                Me.Text_ft2.Locked = True
                Me.Text_m2.SetFocus
        End Select
        
    End Sub
    Now, when the user enters a value in one of the text boxes (the only one he can type in as the other is locked), the value must be converted and send to the other textbox:
    Code:
    Private Sub Text_ft2_AfterUpdate()
    
        Me.Text_m2.Value = Me.Text_ft2.Value * 0.0929
        
    End Sub
    
    Private Sub Text_m2_AfterUpdate()
    
        Me.Text_ft2.Value = Me.Text_m2.Value * 10.7639
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    Hey, I think I am learning something about VBA. I think I understand 95% of the code you posted. hey, maybe it was simple code (?) but I understood it, at least.

    I was proud of myself for making a few twiddles and adjustments with the names of my Text Boxes (they were called "Floor Area (ft)" and I had trouble getting the code to work with spaces and brackets. And now the fields work perfectly - FANTASTIC - THANK YOU.

    As I tried to understand the code, i still had a couple of questions to how the code works??

    1. Case "ft2"
    I presume the words in quotation marks have to be exactly as in the ComboBox?

    2. Private Sub Text_ft2_AfterUpdate()
    Me.Text_m2.Value = Me.Text_ft2.Value * 0.0929
    End Sub

    What i dont fully understand is...if the user chooses "ft2" in the Combo, and the code takes the Focus to the Text_ft2 field, then why doesn't the "ft2" field calculate a value (based on the m2 field) as the above code tells it to do? I know I don't want it to do that but, if I was creating the code, i could not get the logic of why it works correctly? is it because the Focus code tells the "ft2" field to do "nothing" and just ignore the request to calculate from m2 to ft2?

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    1. The 'Select' keyword, specifies an object that is going to be tested, in this case 'Combo_Unit'.

    Each subsequent 'Case' statement, specifies the test condition for that object. So in the code you are using, the 'Select' statement then checks to see if the object being tested is equal to the String (a series of textual characters) "ft2".

    If the 'Select' statement finds that one of its 'Case' statements conditions ("ft2" in this case) is a match, it will execute the code found immediately after the 'Case' statement. In this case, 'Me.Text_ft2.Locked = False ... etc'


    So in short, they have to be exactly the same (not 100% sure if VBA is case sensitive or not, but I would assume so... If in doubt, and all that).


    2. 'Private Sub Text_ft2_AfterUpdate()' Tells the compiler that everything that follows this line is to be of 'Private' type (meaning that it can't be called from outside the module [honestly, don't worry about that]).

    That it is a 'Sub'routine... i.e., that it is a method that will need to be executed at a particular time (that it's not a function that will be called when needed).

    That it is to effect the object 'Text_ft2' (the name you gave it when you designed the form)

    And that it should run the code contained before the 'End Sub' statement, '_AfterUpdate()' - After an update has been made to the object (or control), which in this case is 'Text_ft2'.


    'Me.Text_m2.Value = Me.Text_ft2.Value * 0.0929' Tells your textbox, 'Text_m2' that it is to change its 'Value' (the bit you see on your screen), to be whatever is to the right of the '='.

    In this case that's 'Me.Text_ft2.Value * 0.0929', which takes whatever value has been input in the textbox, 'Text_ft2', i.e. it's value (again, the bit you see on the screen), and multiplies it (* = multiply) by '0.0929'.

    So the 'Value' of 'Text_m2' becomes the result of 'Text_ft2's 'Value' multiplied by '0.0929'


    You with me?


    The reason it doesn't update the 'Text_ft2' textbox with the value calculated from the 'm2' calculation is because:

    Private Sub Text_ft2_AfterUpdate()

    The AfterUpdate bit means the code will ONLY run, once the textbox in question has been updated. As the 'Text_m2' textbox has not been updated, its code:

    Code:
    Private Sub Text_m2_AfterUpdate()
    
        Me.Text_ft2.Value = Me.Text_m2.Value * 10.7639
        
    End Sub
    Will not run, as it hasn't been updated.


    Has that cleared up some stuff for you?
    Last edited by kez1304; 08-26-11 at 13:02.
    Looking for the perfect beer...

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Thanks kez1304! This explanation seems perfect.

    I see very few I could add to that (which does not mean that you cannot ask anyway!).

    For the Select Case structure, you can consider it as an alternative to an If...ElseIf... Else...End If structure which in this case would be:
    Code:
        If Me.Combo_Unit.Value = "ft2" Then
                Me.Text_ft2.Locked = False
                Me.Text_m2.Locked = True
                Me.Text_ft2.SetFocus
        ElseIf Me.Combo_Unit.Value = "m2" Then
                Me.Text_m2.Locked = False
                Me.Text_ft2.Locked = True
                Me.Text_m2.SetFocus
        End If
    When I need to compare something (a variable or the value of a control) to a set of possible values the Select Case structure seems more logical to me, but it's mainly a matter of taste.

    The comparison is not case sensitive (i.e. "A" = "a" is True) because, by default, Access inserts a line:
    Code:
    Option Compare Database
    at the beginning of every module. It is called a Directive and is located in the Declarations section of the module, before (and outside of) any procedure (Sub or Function). To make comparisons case sensitive in a module (i.e. "A" <> "a" is True) you change this directive to:
    Code:
    Option Compare Binary
    The procedures:
    Code:
    Combo_Unit_AfterUpdate()
    Text_ft2_AfterUpdate()
    Text_m2_AfterUpdate()
    are Event Handlers, which means that they are executed in response to an event. Controls in Access come with a set of pre-programmed events. You can consider these events as flags that are raised each time a specific event occurs (here after the value of the control is being updated -changed). You write an event handler that "catches" the signal (the flag is raised) and executes the code it contains. Here they are declared as Private because the events they handle cannot occur outside the form. Nothing prevents you from declaring them as Public, but it does not make a lot of sense in this case. Event programming is very usual in softwares such as Access and you can define your own events, to a certain extend.
    Have a nice day!

  6. #6
    Join Date
    Nov 2009
    Posts
    223
    kez1304,

    you have provided an excellently detailed response for me (a relative beginner) - thank you very much.

    i now understand why the code calculation does not run in one of the Text boxes. I guess, in theory, both Text Boxes could be considered as Updated because their Locked property was set to "True" or "False"? But i guess if both were Flase to begin with, then it only takes account of a "change" in "update"?

    Thanks again - it is much appreciated.

    Paul

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Only one of the Text boxes can be unlocked at a given time, so the user can only type something in this one.

    To be complete the form must perform an initialization routine that will set the initial state of the system (i.e. select one of the units when the form opens), like this:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Me.Combo_Unit.Value = "ft2" ' Arbitrarily decide that the form will start in square feet.
        Call Combo_Unit_AfterUpdate ' The AfterUpdate event does not occur when you change the value
                                    ' of a control using code: you must to explicitly call the handler.
        
    End Sub
    Have a nice day!

  8. #8
    Join Date
    Nov 2009
    Posts
    223
    Thanks for that, Sinndho.
    What would you attempt to do if you knew you would not fail?

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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