Error On VBA code

captainlove

captainlove
Local time
Today, 14:32
Joined
Apr 18, 2008
Messages
39
I get an error after the group by statement in my VBA code saying expected: line number or label or statement or end of statement

My VBA is greyed out, after the group by clause. I suspect the error is before that. I have spent six hours on this. Can anyone out there help

Sql = "INSERT INTO tblTempHist (qcpID, revision_date,revision,Accepted, UserID,revID ) SELECT tblHistory.qcpID, " & _
"tblHistory.revision_date, tblHistory.revision, IIf(IsNull([UserID]),False,True) AS Accepted,qryHistLinkUser.userid, tblHistory.revid " & _
"FROM (tblQcp INNER JOIN tblHistory ON tblQcp.qcpID = tblHistory.qcpID) LEFT JOIN " & _
"qryHistLinkUser ON tblHistory.revid = qryHistLinkUser.REVID WHERE tblQcp.qcpID = " & Me.txtQcp & "AND qryHistLinkUser.UserId = " & Me.txtUser
"GROUP BY tblHistory.qcpID, " &_
"tblHistory.revision,qryHistLinkUser.userid, tblHistory.revision_date, IIf(IsNull([UserID]),False,True), tblHistory.revid "
DoCmd.RunSQL Sql
:mad:
 
I get an error after the group by statement in my VBA code saying expected: line number or label or statement or end of statement
Maybe try to copy and paste this:

"GROUP BY tblHistory.qcpID, " & _
"tblHistory.revision, qryHistLinkUser.userid, tblHistory.revision_date, IIf(IsNull([UserID]),False,True), tblHistory.revid"



Also, on the IIF statement, do you want those conditional values to be strings or booleans?? (i.e. - 0/-1's, or "False", "True"??) Have to put quote around strings...
 
This line has two glaring problems:

"qryHistLinkUser ON tblHistory.revid = qryHistLinkUser.REVID WHERE tblQcp.qcpID = " & Me.txtQcp & "AND qryHistLinkUser.UserId = " & Me.txtUser
"GROUP BY tblHistory.qcpID, " &_


"qryHistLinkUser ON tblHistory.revid = qryHistLinkUser.REVID WHERE tblQcp.qcpID = " & Me.txtQcp & "AND qryHistLinkUser.UserId = " & Me.txtUser &
" GROUP BY tblHistory.qcpID, " &_
^needs space
 
"qryHistLinkUser ON tblHistory.revid = qryHistLinkUser.REVID WHERE tblQcp.qcpID = " & Me.txtQcp & "AND qryHistLinkUser.UserId = " & Me.txtUser &
" GROUP BY tblHistory.qcpID, " &_
^needs space
Bob's answer = Adam's answer - explanation. :D
 
error on vba code

hello bob,

here is the database

the form is frmqcplist1, to get to that, its the first button on the switchboard.

Once in form, select a user/qcp combination and click display. I am trying to make the revision and revision date specific to combination

thanks a lot, maybe u can steer this stormy ship

Captain
 

Attachments

clove01.png


clove02.png
 
Hello bob,

Thanks for your response. I get the first error. The second error " & me.txtUser&. Should that read "& Me.txtUser &_

Thank you Bob, you the best
 
No, it needs to be

Me.txtUser & _ (with a space between the & and the _ as well)
 

Users who are viewing this thread

Back
Top Bottom