How to simplify the select case (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

How about this:

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

Reply | Quote