Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2008
    Posts
    35

    Unanswered: Option group question

    I know this is probably basic for this forum, but I have a question to ask anyway. I've got an option group with 2 options, values of 1 and 2 respectively. How do I set up an if...then...else... statement in SQL? I need it to be IF optionvalue1 = true THEN open form1 ELSE open form2. The problem is I don't know SQL at all so I'm at a complete loss.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The problem is I don't know SQL at all so I'm at a complete loss.
    If you are creating databases, you had better get yourself up to speed on SQL at least a little.

    You just need to use an IIf function in a calculated field expression or criteria expression... wherever it is you want to use the if.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2008
    Posts
    35
    Yeah, it would be nice, except I didn't have a choice and I'm learning as I go.

    Anyway, I searched the "iif" statement you told me about, and it mostly seems to be used to show if a quantity if over x amount, display large. Am I able to write a command to open up a form in the "iff" format?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well to be able to help you, you're going to need to provide some details.

    What is it you are actually wanting to do? You want to open forms by opening queries?

    Hang on a sec.... are you confusing SQL with VBA? You want a command button to open a form based on your option group.... don't you!

    If so, in the On Click event procedure for your command button you would have something like this:

    Code:
    If optionvalue1 = True Then 
       DoCmd.OpenForm "form1"
    Else 
       DoCmd.OpenForm "form2"
    End If
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Oct 2008
    Posts
    35
    PERFECT! Thanks, it works! Sorry about the confusion. I thought all of Access was in SQL, I don't know the differences yet.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No problem

    Glad I could help.

    SQL = Structured Query Language. It is generally used to work with data, list records, copy records, delete records or change records... among other things.

    VBA = Visual Basic for Applications. It is generally used for automating, opening forms, saving records, previewing reports, running queries or running SQL.

    Just for your clarification
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Oct 2008
    Posts
    35
    Well, unfortunately, I thought all was running well. The two forms that I had in the option group were almost identical in content that it took me a little while to realize that the otion group just opens one form. It doesn't matter if the optionvalue is 1 or not, when I hit that Continue button it takes me to the option value 1 form. What did I do wrong? I copy and pasted your code and changed the "form1" and "form2" to their proper names for my database.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Double-post fixup.... read on.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Bah.

    Ummm.... You should have picked up the mistake I deliberately put in there

    If your options are valued 1 and 2 then they are BOTH true.

    Code:
    If optionvalue1 = 1 Then 
       DoCmd.OpenForm "form1"
    Else 
       DoCmd.OpenForm "form2"
    End If
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Oct 2008
    Posts
    35
    Now maybe I'm just paranoid, but in the code you said "If optionvalue1 = 1 Then" didn't you just get done telling me that optionvalue1 is always going to be equal to one?

    I tried it as "if optionvalue1 = 1 then" and "if optionvalue = 1 then", and it's still not working correctly unfortunately. I used the Command button wizard as well just so I didn't screw anything up. Any other ideas?

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I've got an option group with 2 options, values of 1 and 2 respectively. How do I set up an if...then...else... statement in SQL? I need it to be IF optionvalue1 = true THEN open form1 ELSE open form2.
    Code:
    If <whatever the name is of your option group> = 1 Then 
       DoCmd.OpenForm "form1"
    Else 
       DoCmd.OpenForm "form2"
    End If
    You have to find out what the name is, and possibly set the name, by selecting the option group and going into Properties and setting the Name property to something like optWhichForm, then substitute that name in the code such as:

    Code:
    If optWhichForm = 1 Then 
       DoCmd.OpenForm "form1"
    Else 
       DoCmd.OpenForm "form2"
    End If
    You would also have to substitute form1 and form2 for their real names.

    didn't you just get done telling me that optionvalue1 is always going to be equal to one?
    No, I told you that the values 1 and 2 are both evaluated as TRUE.

    Actually the code could be this:

    Code:
    If optWhichForm = 1 Then 
       DoCmd.OpenForm "form1"
    ElseIf optWhichForm = 2 Then
       DoCmd.OpenForm "form2"
    Else
       MsgBox "Cannot determine which form to open!"
    End If
    Last edited by StarTrekker; 10-22-08 at 10:31.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Oct 2008
    Posts
    35
    Sweet! You're a sly fox StarTrekker, but at least you were making me think. It's working now, however I never would have guessed that the end code would have been structured like that. Thank for all your help!

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome!

    I do try to make people think about it rather than just handing out the answer, but I think I accomplished both on this one!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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