Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Curious VBA quirk? (2003)

    Someone in my Office asked me if a Label could change, depending on whether a Checkbox value was True or False. This was extremely easy to achieve when I tested it on a Database I have at home, but a funny thing happened when I went to implement it at work.

    At Home, the following code worked:
    If cbo_FormatID = 1 Then
    FormatLabel.Caption = "Disk"
    Else: FormatLabel.Caption = "Tape"
    End If

    At work, when I tried this code, I got an error message that I couldn't do an Else or End If without an IF????????????
    As a workaround, I created two IF statements:
    If cbx_FormatID = 0 Then FormatLabel.Caption = "Disk"
    If cbx_FormatID = 1 Then FormatLabel.Caption = "Tape"

    This works, but I'm just curious why Access didn't recognize it in the first format, which worked at home. Both PCs are Access 2003 on Windows XP.
    Would it be that one was a CBO and the other was a CheckBox? Or was it b/c at home it was an OnClick Event whereas at work, it was an OnCurrent Event?
    Any insight to satiate my curiosity would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Curious VBA quirk? (2003)

    Try this

    If cbx_FormatID Then
    FormatLabel.Caption = "Disk"
    Else
    FormatLabel.Caption = "Tape"
    End If

    Placing multiple instructions on a line separated by : is a remnant of older versions of Basic.

    Note: a check box has values True (-1) and False (0), not 1 and 0.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Curious VBA quirk? (2003)

    Oh Hans

    I'm so surprised that your code worked . . . NOT!
    As usual, thanks for the correct syntax.

    I'm still curious why the format (in the one at work) didn't work. Why is the Syntax incorrect? I thought it was just a simple If, Then, Else piece of code? In actual fact, I don't understand how your code works as it doesn't actually state a condition (true/false). I'd be interested in knowing how Access interprets something that doesn't have a condition (just nosey, I guess <img src=/S/wink.gif border=0 alt=wink width=15 height=15>)

    P.S. Access put in the colon as I typed the If Then Else sub.
    P.P.S. Sorry about that wee oversight, I had actually stated True & False, not 1 and 0, but I was just copying, pasting, and modifying the CBO Sub. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    WendellB, thanks for your reply. Sorry, but you said Home twice; could you please clarify?

    Thanks to both you guys. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Curious VBA quirk? (2003)

    <P ID="edit" class=small>(Edited by WendellB on 15-Jan-04 09:49. to clarify the meaning of home.)</P>There's a problem in your "home" code. Your Else: statement is being interpreted as a line label because of the colon. I suspect you didn't have that when you ran your code at home.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curious VBA quirk? (2003)

    For those who subscribe to the "why solve a problem when you can avoid it" mentality (e.g. "workarounds" in general), you could just use:
    <font face="Georgia">
    FormatLabel.Caption = IIf(cbx_FormatID, "Disk", "Tape")
    </font face=georgia>
    Seriously, I agree with the others -- I suspect if you look closely between what you did at home vs. work you'll find something different in the way you used the colons. Like Hans, I try to avoid multi-statement lines (except occasionally in the immediate mode) for readability reasons, primarily.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Curious VBA quirk? (2003)

    And, presumably, the first statement is always TRUE and the SECOND is always FALSE? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Interesting. Don't ya just love how many ways there are to do the same thing? It's not confusing at all to newbies like me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>!

    Anyway, thanks for satisfying my curiosity, and thanks for the tips.

    Tom, I'll try to remember that Syntax for a future occasion. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Curious VBA quirk? (2003)

    The original code works for me in Access 2002 too.

    The general form of an If statement is If condition Then, where condition is an expression that evaluates to True or False. The code If cbx_FormatID Then works because the value of a check box is either True or False, so you don't need to add = True or = False: the value is a valid condition by itself.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Curious VBA quirk? (2003)

    There is sometimes a problem in Access/VBA when implicitly evaluating the contents of a checkbox as a boolean. It can cause odd hangups and is recommended against in virtually everything I've read. You can force an evaluation of the checkbox control by using parens around it, which returns a true or false, or you can use the Me.Checkbox = True syntax safely.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Curious VBA quirk? (2003)

    I had that problem in Access 97 - Access would stay active, minimized, after trying to quit. I haven't seen happen it in Access 2002.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Curious VBA quirk? (2003)

    Trust me! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> It's always safest to err on the side of explicit comparisons when it comes to VBA ... at least in Access. You never know what the *next* version might bring. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Charlotte

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Curious VBA quirk? (2003)

    <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

  12. #12
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Curious VBA quirk? (2003)

    FWIW, the Access 2002 edition of the Developer's Handbook (in the "Application Optimization" chapter) says that code with explicit comparisons (e.g., If x = True Then) runs faster than implicit code (e.g., If x Then).

  13. #13
    Lounger
    Join Date
    Jan 2004
    Location
    Honolulu, Hawaii, USA
    Posts
    47
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Curious VBA quirk? (2003)

    For what it's worth...
    Am running WinXP Sp2 Beta. The original code works fine for me. The answer should be "Disk" if 1 else "Tape".
    Office Access 2003(11.5614.5606).
    If it is a Combo, why not use Select? Chances are the number of choices is going to grow.
    Select Case cbo_FormatID
    Case 1
    FormatLabel.Caption = "Disk"
    Case Else
    FormatLabel.Caption = "Tape"
    End Select

  14. #14
    Lounger
    Join Date
    Jan 2004
    Location
    Honolulu, Hawaii, USA
    Posts
    47
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Curious VBA quirk? (2003)

    Interesting. ADH95 (Test 8), ADH97 say the opposite. ADH2k doesn't mention the test explicitly. Instead Test 13 says "Use logical assignments when possible." But there is another overriding consideration. Test for the most commonly occurring case first. (test 20 ADH2k). If they are truly using an optimizing compiler, then the difference is likely to shrink. The best practice is to write the test so it makes the most sense! Debugging is the MOST expensive operation.

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Curious VBA quirk? (2003)

    Keep in mind that the engine changed radically between 97 and 2000, so some of the rules changed as well. Between 2000 and 2002, a few things may have become more evident, resulting in more emphatic instructions in the latter ADH. A Select Case, however, generally evaluates a value, so I don't see that the discussion applies to that. Did I miss something?
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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