Variable already exist error (MS Access)

[ladybanks]

I am trying to load data from two separate tables into a third table. Log the load time into another table. However anytime i run my scripts in Access, the data doesnt load and i get variable exist error. I need help.

Below is my code:
Dim dbs As Database
Dim qdf As QueryDef
Dim sql As String
Dim mytimer As Date
Dim rcvd_date As Date
Dim mydate As Date
Dim icount As Integer
Dim load_index As Integer


Set dbs = CurrentDb
'dbs.Execute "drop mydate;"
dbs.Execute "SELECT Max(loadDate) into mydate FROM load_scheduler;"

'10/7/1999 4:45:00 PM
If DateValue(mydate) = #10/7/1999# Then
dbs.Execute "INSERT INTO comptrackdata ( [Date Received From Customer], [Customer Company/Department], [External Customer], [Customer Contact], [Contact phone], ContactEmail, Complaint, [Specific Product/Service], Investigation, [First response to customer by receiver of comment], [Date Completed or moved to CAPA] )SELECT [Date Received], companyname, 1, [customer name], workphone, emailname, Question, catalognumber, Response, Notes, [date closed]FROM Contacts;"
icount = dbs.RecordsAffected
dbs.Execute "select max([date received]) into rcvd_date from contacts;"
dbs.Execute "insert into load_scheduler values(rcvd_date, now(), 1);"

ElseIf DateValue(mydate) > #10/7/1999# Then
dbs.Execute "select max([date received]) into rcvd_date from contacts;"
If rcvd_date > mydate Then
dbs.Execute "INSERT INTO comptrackdata ( [Date Received From Customer], [Customer Company/Department], [External Customer], [Customer Contact], [Contact phone], ContactEmail, Complaint, [Specific Product/Service], Investigation, [First response to customer by receiver of comment], [Date Completed or moved to CAPA] )SELECT [Date Received], companyname, 1, [customer name], workphone, emailname, Question, catalognumber, Response, Notes, [date closed]FROM Contacts where [Date Received] >= mydate;"
dbs.Execute "INSERT into load_scheduler values(rcvd_date, now(), 1);"
ElseIf rcvd_date < mydate Then
dbs.Execute "INSERT INTO comptrackdata ( [Date Received From Customer], [Customer Company/Department], [External Customer], [Customer Contact], [Contact phone], ContactEmail, Complaint, [Specific Product/Service], Investigation, [First response to customer by receiver of comment], [Date Completed or moved to CAPA] )SELECT [Date Received], companyname, 1, [customer name], workphone, emailname, Question, catalognumber, Response, Notes, [date closed]FROM Contacts where [Date Received] <= mydate;"
dbs.Execute "INSERT into load_scheduler values(rcvd_date, now(), 1);"
ElseIf rcvd_date = mydate Then
End

End If
End If



End Sub

[ADezii]

I am trying to load data from two separate tables into a third table. Log the load time into another table. However anytime i run my scripts in Access, the data doesnt load and i get variable exist error. I need help.

Below is my code:
Dim dbs As Database
Dim qdf As QueryDef
Dim sql As String
Dim mytimer As Date
Dim rcvd_date As Date
Dim mydate As Date
Dim icount As Integer
Dim load_index As Integer


Set dbs = CurrentDb
'dbs.Execute "drop mydate;"
dbs.Execute "SELECT Max(loadDate) into mydate FROM load_scheduler;"

'10/7/1999 4:45:00 PM
If DateValue(mydate) = #10/7/1999# Then
dbs.Execute "INSERT INTO comptrackdata ( [Date Received From Customer], [Customer Company/Department], [External Customer], [Customer Contact], [Contact phone], ContactEmail, Complaint, [Specific Product/Service], Investigation, [First response to customer by receiver of comment], [Date Completed or moved to CAPA] )SELECT [Date Received], companyname, 1, [customer name], workphone, emailname, Question, catalognumber, Response, Notes, [date closed]FROM Contacts;"
icount = dbs.RecordsAffected
dbs.Execute "select max([date received]) into rcvd_date from contacts;"
dbs.Execute "insert into load_scheduler values(rcvd_date, now(), 1);"

ElseIf DateValue(mydate) > #10/7/1999# Then
dbs.Execute "select max([date received]) into rcvd_date from contacts;"
If rcvd_date > mydate Then
dbs.Execute "INSERT INTO comptrackdata ( [Date Received From Customer], [Customer Company/Department], [External Customer], [Customer Contact], [Contact phone], ContactEmail, Complaint, [Specific Product/Service], Investigation, [First response to customer by receiver of comment], [Date Completed or moved to CAPA] )SELECT [Date Received], companyname, 1, [customer name], workphone, emailname, Question, catalognumber, Response, Notes, [date closed]FROM Contacts where [Date Received] >= mydate;"
dbs.Execute "INSERT into load_scheduler values(rcvd_date, now(), 1);"
ElseIf rcvd_date < mydate Then
dbs.Execute "INSERT INTO comptrackdata ( [Date Received From Customer], [Customer Company/Department], [External Customer], [Customer Contact], [Contact phone], ContactEmail, Complaint, [Specific Product/Service], Investigation, [First response to customer by receiver of comment], [Date Completed or moved to CAPA] )SELECT [Date Received], companyname, 1, [customer name], workphone, emailname, Question, catalognumber, Response, Notes, [date closed]FROM Contacts where [Date Received] <= mydate;"
dbs.Execute "INSERT into load_scheduler values(rcvd_date, now(), 1);"
ElseIf rcvd_date = mydate Then
End

End If
End If



End Sub

It appears to me that mydate refers to both a Variable and a Table which makes things a little ambiguous. Change the Name of the Variable to something different.

Dim mydate As Date
dbs.Execute "SELECT Max(loadDate) into mydate FROM load_scheduler;"