Apend Query SQL to VBA module

[Thanks, Chad]

Hello, I have an apend query i wanted to use in a module and I cant get it to
work. I tried the code below but gives me an error "Query input must contain
at least on table or query" Thanks!


Public Function RunYearlyReview()
Dim db As DAO.Database
Dim strSql As String

strSql = "INSERT INTO tblYearlyReview ( DateOfHire, FirstName, LastName,
Status, CurrentMonth )" & _
"SELECT tblUser.DateOfHire, tblUser.FirstName, tblUser.LastName,
tblUser.Status, Month([DateOfHire]) AS CurrentMonth" & _
"WHERE (((tblUser.Status)=False) AND
((Month([DateOfHire]))=Month(Now())));"

DoCmd.RunSQL strSql

End Function

 

[Douglas J. Steele]

You appear to be missing the name of the table from which you're selecting:

strSql = "INSERT INTO tblYearlyReview ( DateOfHire, FirstName, LastName,
Status, CurrentMonth )" & _
"SELECT tblUser.DateOfHire, tblUser.FirstName, tblUser.LastName,
tblUser.Status, Month([DateOfHire]) AS CurrentMonth" & _
"FROM tblUser " & _
"WHERE (((tblUser.Status)=False) AND
((Month([DateOfHire]))=Month(Now())));"

 

[boblarson]

Your other part of the statement (the SELECT part) needs a FROM clause:

strSql = "INSERT INTO tblYearlyReview ( DateOfHire, FirstName, LastName,
Status, CurrentMonth )" & _
"SELECT tblUser.DateOfHire, tblUser.FirstName, tblUser.LastName,
tblUser.Status, Month([DateOfHire]) AS CurrentMonth" & _

FROM xxxxxxxxxxxxxxxxx

"WHERE (((tblUser.Status)=False) AND
((Month([DateOfHire]))=Month(Now())));"

--
Bob Larson

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Thanks, Chad]

I have added the from and am still gettin the same error?

strSql = "INSERT INTO tblYearlyReview ( DateOfHire, FirstName, LastName,
Status, CurrentMonth )" & _
"SELECT tblUser.DateOfHire, tblUser.FirstName, tblUser.LastName,
tblUser.Status, Month([DateOfHire]) AS CurrentMonth" & _
"FROM tblUser" & _
"WHERE (((tblUser.Status)=False) AND
((Month([DateOfHire]))=Month(Now())));"

 

[Douglas J. Steele]

You're missing spaces between the various lines you're concatenating
together.

strSql = "INSERT INTO tblYearlyReview ( DateOfHire, FirstName, LastName,
Status, CurrentMonth ) " & _
"SELECT tblUser.DateOfHire, tblUser.FirstName, tblUser.LastName,
tblUser.Status, Month([DateOfHire]) AS CurrentMonth " & _
"FROM tblUser " & _
"WHERE (((tblUser.Status)=False) AND
((Month([DateOfHire]))=Month(Now())));"

 

[Thanks, Chad]

Oh OK I see what your talking about! It works but how do I get rid of the
notification that its about to append rows? DoCmd.setwarnings = false then
true?

Thanks,
Chad

You're missing spaces between the various lines you're concatenating
together.

strSql = "INSERT INTO tblYearlyReview ( DateOfHire, FirstName, LastName,
Status, CurrentMonth ) " & _
"SELECT tblUser.DateOfHire, tblUser.FirstName, tblUser.LastName,
tblUser.Status, Month([DateOfHire]) AS CurrentMonth " & _
"FROM tblUser " & _
"WHERE (((tblUser.Status)=False) AND
((Month([DateOfHire]))=Month(Now())));"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have added the from and am still gettin the same error?

strSql = "INSERT INTO tblYearlyReview ( DateOfHire, FirstName, LastName,
Status, CurrentMonth )" & _
"SELECT tblUser.DateOfHire, tblUser.FirstName,
tblUser.LastName,
tblUser.Status, Month([DateOfHire]) AS CurrentMonth" & _
"FROM tblUser" & _
"WHERE (((tblUser.Status)=False) AND
((Month([DateOfHire]))=Month(Now())));"

 

[boblarson]

Use

CurrentDb.Execute strSql, dbFailOnError

instead
--
Bob Larson

Tutorials at http://www.btabdevelopment.com

__________________________________