By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,254 Members | 2,365 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

Variable already exist error (MS Access)

P: 17
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
Jan 31 '08 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. Dim mydate As Date
  2. dbs.Execute "SELECT Max(loadDate) into mydate FROM load_scheduler;"
Jan 31 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.