"Duplicate declaration in current scope" error in Access VBA

Question

I'm having an issue with a VBA program I made. I want to create a program which inputs 50,000 records throughout a table (which is the Employee Table in my case), and every time I try to run it, it states an error that says "Compile Error: Duplicate declaration in current scope."

My code is as follows:

Option Compare Database
Option Explicit

Sub arrayData1()

'This subroutine will pump in 50 k records for the first two columns of EMPLOYEE table.
'Also takes in sample names, attempts to clean the data beofre its entered in the table.
'Declare variable by using keyword DIM
Dim EmployeeFNames() As Variant  'implies array. array is always declared variant datatype.
Dim EmployeeLNames() As Variant
Dim EmployeeType() As Variant
Dim num As Integer, dbs As Database, InsertRecord As Variant, num1 As Long
Dim EmployeeID As Long, EmployeeFName As String, EmployeeLName As String, EmployeeType As String, EmployeeWages As Long

'assign value to variables
Set dbs = CurrentDb() 'assign current db(Stage 2 Project)
EmployeeID = 0 'initialise value.

    For num1 = 0 To 50000
    EmployeeID = EmployeeID + 1 'increment by 1.
    EmployeeWages = EmployeeWages + 1
    ' array is populated with names.
    EmployeeFNames = Array("Peter", "Mary", "Frances", "Paul", "Ian", "Ron", "Nathan", "Jesse", "John", "David")
    EmployeeLNames = Array("Jacobs", "Smith", "Zane", "Key", "Doe", "Patel", "Chalmers", "Simpson", "Flanders", "Skinner")
    EmployeeTypes = Array("Groundskeeper", "Housekeeper", "Concierge", "Front Desk", "Chef", "F&B", "Maintenance", "Accounts", "IT", "Manager")

    'Equation for random generation
    'INT (upperbound - lowerbound +1) * Rnd + lowerbound) ' upper & lower bound are index values of array
    num = Int((9 - 0 + 1) * Rnd + 0) ' equation generates at random a number between 0 & 9.
    EmployeeFName = EmployeeFNames(num) ' name is picked at random from array based on random number.
    EmployeeLName = EmployeeLNames(num)
    EmployeeType = EmployeeTypes(num)

    ' Use SQL INSERT statement to insert record in EPLOYEE table.
    InsertRecord = "INSERT INTO EMPLOYEE(EmployeeID, EmployeeFName, EmployeeLName, EmployeeType, EmployeeWages) VALUES(" _
                   & "'" & EmployeeID & "'" & "," & "'" & EmployeeFName & "'" & "," & "'" & EmployeeLName & "'" & "," & "'" & EmployeeType & "'" & "," & "'" & EmployeeWages & "'" & ")"

    dbs.Execute InsertRecord
    Debug.Print EmployeeID; EmployeeFName; EmployeeLName; EmployeeType; EmployeeWages
    Next

End Sub

I would appreciate any fixes to this problem and any suggestions towards my code.

Answer 1

You have tried to declare (Dim) the variable EmployeeType as an array of Variant and then later you try to declare it (again) as String.

You'll need to use two different names for those two variables.