Apend Query SQL to VBA module

  • Thread starter Thread starter Thanks, Chad
  • Start date Start date
T

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
 
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())));"
 
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

__________________________________
 
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())));"
 
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())));"
 
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

Douglas J. Steele said:
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!)


Thanks said:
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())));"
Click to expand...
Click to expand...
 
Back
Top