Error "Duplicate declaration in current scope"

[skydivetom]

Experts:



In my VBA code, I'm coming across a fundamental error (i.e., "Duplicate declaration in current scope"); yet I have not been able to successfully resolve it.

Below is my code:

    'Declare variables
    Dim rs As DAO.Recordset, sTarget As String, sField As String
    Dim sCurrent As String, i As Integer
    Dim sSQL As String, sFieldAlias As String
       
    'Determine which version format
    Select Case Forms!F01_MainMenu!optVersionType
        
        'PK Version:
        Case 1
               
            Const conSQL As String = "SELECT [00_tbl_Master_PK].SOURCE_FILE, CASE_NUMBER, |FIELDS_HERE| FROM [00_tbl_Master_PK];"
            
            'Reset counter
            iCounter = 0
            
            'Delete all queries
            If Forms![F01_MainMenu].chkDeleteAllQry = True Then Call DeleteAllQueries
            
            Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables_PK Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)
       
        'String Version:
        Case 2
           
            Const conSQL As String = "SELECT [00_tbl_Master_String].SOURCE_FILE, CASE_NUMBER, |FIELDS_HERE| FROM [00_tbl_Master_String];"
            
            'Reset counter
            iCounter = 0
            
            'Delete all queries
            If Forms![F01_MainMenu].chkDeleteAllQry = True Then Call DeleteAllQueries
            
            Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables_String Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)
       
    End Select

By default, the option group's value is = 1. Presently, however, it does NOT matter which option I'm choosing... I always end up w/ the same error.

My question: How can the CONST reference be modified so that when I leave the CASE statement I pass the correct conSQL value into the remainder of the code?

Thanks,
Tom

[Micron]

Use a string variable. I don't see the sense in declaring a constant that isn't.

[Minty]

@Micron is correct.

Oddly this isn't the first time I've seen really weird use of the Const declaration type.
I wonder if there is a "guide" somewhere that has some dodgy practice in it that is influencing how newer users are writing things.

[ranman256]

You only need to declare once.

[orange]

Tom,
Here is some M$oft documentation on Const

[Micron]

From that link: "After a constant is declared, it cannot be modified or assigned a new value."
Each version posted has a different sql statement so I don't see Const is of any use in that code no matter how many times it's declared. If one must do things the hard way, then 2 constants would be required and given the proper scope for the task.

[orange]

Agree 100% with micron. When you change values it is no longer a Constant.

[skydivetom]

Thank you... that worked great... 'appreciate the info. Cheers!