Duplicate declaration in current scope error in Access

Question

I'm writing a VBA code to submit records one by one in a recordset loop. Running into this error saying there's a "Duplicate declaration in current scope". I've run into this before when I've accidentally had duplicate variables, but this time I can't see why it's happening. I'm thinking it might have to do with the layout of the script and maybe that it's trying to repeatedly declare it? Maybe I could work around it by declaring the variables in a module? Not sure.

Private Sub submitButton_Click()

Const VmfgConnStr = "Connection string is here"

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("")

Dim sqlString As String
sqlString = "INSERT INTO dbo.TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, TRAINED_BY, STATUS, COMPETENCY, APPROVED, TYPE) " & _
"SELECT '" & rst![EMPLOYEE_ID] & "', '" & rst![DOCUMENT_ID] & "', '" & rst![LATEST_REV] & "', '" & dtTrained & "', '" & sprTrained & "', 'T', 'Not Verified', 'NO', 'Internal'"

Set objAD = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objAD.UserName)
strDisplayName = objUser.DisplayName

Dim dtTrainedMsg As String
Dim sprTrainedMsg As String

Dim rst As Recordset

dtTrained = InputBox("Enter date trained as 'mm/dd/yyyy':", "", Format(Date, "mm/dd/yyyy"))
Debug.Print dtTrained

If StrPtr(dtTrained) = 0 Then

Exit Sub

Else

sprTrained = InputBox("Trained By:", "", strDisplayName)
Debug.Print sprTrained

If StrPtr(sprTrained) = 0 Then

Exit Sub

Else

Dim ConfirmMsg, ConfirmStyle, ConfirmTitle, ConfirmResponse
ConfirmMsg = "Continue?"
ConfirmStyle = vbYesNo
ConfirmTitle = " "
ConfirmResponse = MsgBox(ConfirmMsg, ConfirmStyle, ConfirmTitle)

If ConfirmResponse = vbYes Then

recSelect = "SELECT EMPLOYEE_ALL.EMPLOYEE_ID, TRAINING_DOCS_ALL.DOCUMENT_ID, TRAINING_DOCS_ALL.LATEST_REV " & _
"FROM TRAINING_DOCS_ALL, EMPLOYEE_ALL " & _
"WHERE EMPLOYEE_ALL.SELECTED = -1 AND TRAINING_DOCS_ALL.SELECTED = -1"

Set rst = CurrentDb.OpenRecordset(recSelect)
rst.MoveFirst
Do Until rst.EOF
    Debug.Print rst![EMPLOYEE_NAME]; rst![DOCUMENT_ID]
    
    qdf.sql = sqlString
    
    qdf.ReturnsRecords = False
    
    qdf.Connect = VmfgConnStr
    
    qdf.Execute
    
    rst.MoveNext
Loop

CurrentDb.Execute "DELETE * FROM TRAINING_RECORDS"

CurrentDb.Execute "INSERT INTO TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, TRAINED_BY, STATUS) " & _
"SELECT * FROM uSysTRAINING_RECORDS " & _
"WHERE EMPLOYEE_ID = '" & EMPLOYEE_ID.Value & "'"

CurrentDb.Execute "DELETE FROM TRAINING_NEEDED " & _
"WHERE EMPLOYEE_ID LIKE '" & EMPLOYEE_ID.Value & "' AND DOCUMENT_ID LIKE '" & DOCUMENT_ID.Value & "'"

Else

End If

End If

End If

End Sub

Answer 1

Consider best practices in VBA and SQL generally and MS Access specifically:

VBA Tips

1. Use Option Explicit at the top of every module. In fact, set it as a global setting in IDE (Tools \ Options \ Require Variable Declaration) which declares line in all VBA projects going forward. This option will raise compile error if variables are used before its corresponding Dim call as many instances emerge in posted code.

   Unlike SQL with its lexical order that differs from logical order (i.e., first written clause, SELECT, is usually last clause run), VBA like many other languages runs code in order of how lines are written. Therefore, Dim must come before Set or = assignment .

2. Related to above, place all Dim calls as top level lines before any application code. This reads better and avoids issues of variable declaration before use.

   Const VmfgConnStr = "Connection string is here"
   
   Dim rst As Recordset
   Dim qdf As QueryDef
   Dim recSelect As String, sqlString As String
   
   Dim dtTrainedMsg As String, dtTrained As String
   Dim sprTrainedMsg As String, sprTrained As String
   
   Dim ConfirmMsg As String, ConfirmStyleAs String 
   Dim ConfirmTitle As String, ConfirmResponse As String
   
   Dim objAd, objUser As Object
   Dim strDisplayName As String
   Dim Employee_ID_value As String, DOCUMENT_ID_value As String
   

3. Consistently indent your code with any blocks such as If, With, For, Do, etc. This facilitates readability and maintainability.

   If StrPtr(dtTrained) = 0 Then
       ...
   Else
       ...        
       If StrPtr(sprTrained) = 0 Then
           ...
       Else
           ...        
           If ConfirmResponse = vbYes Then        
               ...
               Do Until rst.EOF   ' REDUNDANT WITH insert-select
                   ...
               Loop                
               ...        
           Else                   ' REDUNDANT IF EMPTY BLOCK
   
           End If        
       End If       
   End If
   

SQL Tips

1. Use table aliases to avoid re-writing long identifiers.

2. Avoid looped queries and run insert-select if possible. (See below if parameterization is needed if dtTrained and sprTrained are VBA variables).

   INSERT INTO dbo.TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, 
                                    TRAINED_BY, STATUS, COMPETENCY, APPROVED, TYPE)
   SELECT e.EMPLOYEE_ID, t.DOCUMENT_ID, t.LATEST_REV, 'dtTrained', 'sprTrained', 
         'T', 'Not Verified', 'NO', 'Internal'
   FROM TRAINING_DOCS_ALL t, EMPLOYEE_ALL e
   WHERE e.SELECTED = -1 
     AND e.SELECTED = -1
   

3. Be careful of cross joins as above uses due to cartesian product that can have performance issues with large tables. (e.g., 1,000 rows X 1,000 rows = 1,000,000 result rows).

4. If wildcards (*) are not used, don't use LIKE operator which runs a different even slower query process. Instead use equality, =.

5. Avoid selecting all columns with SELECT * FROM (especially in append queries). See Why is SELECT * considered harmful?.

MS Access Tips

• Avoid scripting SQL strings inside VBA. Instead use stored queries with parameterization. Doing so you avoid messy concatenation and punctuation of quotes. This helps in code readability and maintainability, even efficiency as query compiler optimizes saved queries and not string queries run on the fly.

  Specifically, replace CurrentDb.Execute ... with the following set up.

  SQL (save as stored objects - no concatenation or line breaks)

  PARAMETERS [e_id_param] TEXT;
  INSERT INTO TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, 
                                DATE_TRAINED, TRAINED_BY, STATUS)
  SELECT EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, TRAINED_BY, STATUS
  FROM uSysTRAINING_RECORDS
  WHERE EMPLOYEE_ID = [e_id_param]
  

  PARAMETERS [e_id_param] TEXT, [doc_id_param] TEXT;
  DELETE FROM TRAINING_NEEDED 
  WHERE EMPLOYEE_ID = [e_id_param] AND DOCUMENT_ID = [doc_id_param]
  

  VBA (call QueryDefs, bind param(s), and execute - no SQL shows in code)

  Set qdef = CurrentDb.QueryDefs("mySavedAppendQuery")
  
  qdef![e_id_param] = EMPLOYEE_ID.Value
  qdef.Execute
  
  Set qdef = Nothing
  
  
  Set qdef = CurrentDb.QueryDefs("mySavedDeleteQuery")
  
  qdef![e_id_param] = EMPLOYEE_ID.Value
  qdef![doc_id_param] = DOCUMENT_ID.Value
  qdef.Execute
  
  Set qdef = Nothing