Incomplete query clause

[Simonhtc4]

Hi Guys and Girls,
Im having trouble with some VBA i hope you can help me with:

in Summary, i have a Users_tbl (this is a list of all our employees), i have another table called ArchiveUsers_tbl (As you can imagine its a list of Employees that leave)
but i have to keep the data for upto 5yrs.
Due to the way the db is reports and queries i need have these seperate



I have already created a query for appending (AppendArchiveUsers_qry) and also Deleting(DeleteArchiveUsers_qry).

with these created i have also created a form with with a Combo box of all the employees names and a button with the following VBA code:

Private Sub Command5_Click()
Dim sAppendSql As String
Dim sDeleteSql As String

sAppendSql = "INSERT INTO ArchivedUsers_tbl.* " & _
"SELECT Users_tbl.* FROM Users_tbl " & _
"WHERE Users_tbl.UserID = " & _
Me.UserID

sDeleteSql = "DELETE Users_tbl.* " & _
"WHERE User_tbl.UserID =" & _
Me.UserID

CurrentDb.Execute sInsertSql(This is where the error is appearing)
CurrentDb.Execute sDeleteSql
End Sub


I keep getting an Error:
Run-time error '3078'
The Microsoft access database engine cannot find the input table or query". Make sure it exists and that its name is spelled correctly.

Can anyone help me as everything looks like its spelt correctly?

[Simonhtc4]

I have tried this again since and put the following VBA in:

how everim getting a different error msgbox now (Syntax error in query. incomplete query clause)


Private Sub Command5_Click()
Dim AppendArchiveUsers_qry As String
Dim DeleteArchiveUsers_qry As String

AppendArchiveUsers_qry = "INSERT INTO ArchivedUsers_tbl.* " & _
"SELECT Users_tbl.* FROM Users_tbl " & _
"WHERE Users_tbl.UserID = " & _
Me.UserID

DeleteArchiveUsers_qry = "DELETE Users_tbl.* " & _
"WHERE User_tbl.UserID =" & _
Me.UserID

CurrentDb.Execute AppendArchiveUsers_qry( this is where im getting the issue again)
CurrentDb.Execute DeleteArchiveUsers_qry
End Sub

[aytee111]

INSERT INTO ArchiveUsers_tbl SELECT....

[Simonhtc4]

INSERT INTO ArchiveUsers_tbl SELECT....

Thanks for the quick reply, where do i put this, my brain is exhausted today.

Simon

[aytee111]

Change your insert query string, there is a ".*" which you must remove.

To get a query working in VBA, always do it in query design first, then copy the SQL to VBA and make the required changes to the criteria. Unless you are a hotshot with SQL!

[Simonhtc4]

Change your insert query string, there is a ".*" which you must remove.

To get a query working in VBA, always do it in query design first, then copy the SQL to VBA and make the required changes to the criteria. Unless you are a hotshot with SQL!

I get a syntax error in INSERT INTO statement

[aytee111]

It would help if we could see what your SQL is.

Immediately prior to executing the SQL string, add this line: Debug.Print AppendArchiveUsers_qry. The string will be displayed in the immediate window, copy and paste it into a new query design and troubleshoot it there.