Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to simplify the select case (Access 2000)

    I have 2 option groups on my form, called OptConnection and OptTables. The OptConnection is a choice from different sources. I must select an option from the OptConnection and then select a choice from the OptTables.I am importing or linking different tables. For example the first choice in the OptTables is products, the second is customers. I am getting confused with the Select case and the If statements.Is there a shorter way to make the double choise?
    Here is the abridged code i am working with:
    ==================================

    Select case Me![OptConnection] 'import from different databases

    case 1 ' import from the server

    If Me![OptTables] = 1 then ' import the tables products
    DoCmd.TransferDatabase acImport, "Microsoft Access", "C:bebe.mdb ", acTable, "products", "products"
    elseif Me![OptTables]= 2 then ' import the tables customers
    DoCmd.TransferDatabase acImport, "Microsoft Access", "C:bebe.mdb ", acTable, "customers","customers"

    etc etc
    end if

    case 2 ' import from the database warehouse
    If Me![OptTables] = 1 then ' import the tables products
    DoCmd.TransferDatabase acImport, "Microsoft Access", "C:MyDocumentswarehouse.mdb ", acTable, "products", "products"
    elseif Me![OptTables]= 2 then ' import the tables customers
    DoCmd.TransferDatabase acImport, "Microsoft Access", "C:MyDocumentswarehouse.mdb ", acTable, "customers","customers"


    etc etc

    ================================================== ===

    I will be grateful for any help

  2. #2
    Lounger
    Join Date
    Feb 2001
    Location
    TX, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to simplify the select case (Access 2000)

    try something like this:

    select case Me![OptConnection] & "_" & Me![OptTables]
    case "1_1"
    case "1_2"
    case "2_1"
    case "2_2"
    case else
    end select

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: How to simplify the select case (Access 2000)

    How about this:
    <pre>dim strConnection as string

    If me!OptConnection=1 then
    strConnection = "C:bebe.mdb"
    else if Me!optConnection=2 then
    strConnection="C:MyDocumentswarehouse.mdb"
    endif

    If Me![OptTables] = 1 then ' import the tables products
    DoCmd.TransferDatabase acImport, "Microsoft
    Access", strConnection,acTable, "products", "products"
    elseif Me![OptTables]= 2 then ' import the tables customers
    DoCmd.TransferDatabase acImport, "Microsoft
    Access", strConnection, acTable, "customers","customers"
    end if
    </pre>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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