Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    5

    Unhappy Unanswered: Combo Box linked to several forms

    Hi George, can you help me with this one. Trying to recall my VBA experience has been a little rough around the edges.

    I have a combo box on a switchboard form that has a table of 9 form names that I've linked to the combo box. The form names do not exactly match the actual forms that I'd like to open when that particular selection is made. I'd like to open the selected form in Edit mode in order to add or edit records.

    Table name: tblforms (only two columns, ID and formname)
    Combo box: Forms
    9 different Forms

    Thanks for the help!
    Last edited by AC2009; 07-22-09 at 11:58.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    George? How will the program know what form to open if the names in the table do not match actual form names? If they did, it would be relatively simple:

    DoCmd.OpenForm Me.ComboName,...
    Paul

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    My name isn't George either, can I help?

    There are many ways.


    AfterUpdate of the combo box, use a select statement:

    Code:
    Select Me.ComboName
       Case "My UserFriendly FormName That Shows In The Combo Box"
          Docmd.OpenForm "MyRealFormName"
       Case ...
       ...etc
    End Select
    ... bit longwinded, but it would work.

    -OR-

    Make the form names match the combo content. Simplest avenue, but it might make the combo a touch unfriendly.

    -OR-

    Add the REAL formname in a new field to the table of combo choices, include it in your combo box and use Docmd.OpenForm MyCombo.Column(1) or you could use a DLookup instead of including it in your combo.
    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

  4. #4
    Join Date
    Jul 2009
    Posts
    5

    Red face

    Sorry All, I had previously read some threads that George replied to. Anyway, if it is not too much of a problem, I have forgotten a lot of VBA since I haven't used it in over 7 years. I need help with the whole structure. I had several syntax errors.

    Thanks!
    Last edited by AC2009; 07-20-09 at 22:26.

  5. #5
    Join Date
    Jul 2009
    Posts
    5
    StarTrekker, thank you! Will try that and let you know how it worked out.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Please do
    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
    Jul 2009
    Posts
    5
    StarTrekker, I tried the longwinded code that you gave me, but it returned an error with the Me.comboName. It is telling me that I need a Select case statement. Do I need to end each case clause with End Select? Or, do I need to number each Case statement.. i.e Case 0, Case 1, etc.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and what is the name of the combo box you are using

    try replacing comboname in the code

    eg
    Select Me.MyComboBox
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    My bad. Missing the CASE keyword

    Here is a completed case statement, just not with enough options:

    Code:
    Select Case Me.ComboName
       Case "My UserFriendly FormName That Shows In The Combo Box"
          Docmd.OpenForm "MyRealFormName"
       Case "A Different Combo Option"
          Docmd.OpenForm "MyOtherFormName"
    End Select
    You just have to substitute the named bits, including ComboName.

    Sorry for the confusion.
    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
    Jul 2009
    Posts
    5
    StarTrekker, after a few tweaks it is working wonderfully! Thank you.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Great stuff!

    And you're welcome
    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
  •