Using Form Values within SQL - VBA 1

[cakoliver]

I'm having trouble using form values within the DoCmd.RunSQL SQL3 statement shown below. I'm guessing I need some specific combination of quotes in order for the form value in variable RequisitionID to be evaluated properly. It looks ok in debug mode but when the SQL3 runs it gives:
RunTime error 3075: Syntax error (missing operator) in query expression '4567AAAA'. The '4567AAAA' is the value of variable RequisitionID which is at the end of the SQL3 statement.
btw - It's Access 2000

Thanks in advance for any help you can provide...

Set db = CurrentDb()
Set rs = db.OpenRecordset("TblHighestUsedReqDetailNumber")
Let HighestReq = rs("HighestUsedReqDetailNo") + 1

Set r2 = db.OpenRecordset("BOW_BOWORKFILE__WS_ESTIMATE")
Let JobNumber = r2("Job_number")
Let ModifiedJob = Left(JobNumber, 2) & Mid(JobNumber, 4, 4) & Mid(JobNumber, 9, 2)
Let RequisitionID = Mid(JobNumber, 4, 4) & Forms!FdlgUpdateFiles!Phase

Let SQL1 = "UPDATE TblWorkReqDetail SET TblWorkReqDetail.reqlineno =" & HighestReq
Let SQL2 = "Update TblWorkReqDetail SET TblWorkReqDetail.job =" & ModifiedJob
Let SQL3 = "Update TblWorkReqDetail SET TblWorkReqDetail.requisitionno =" & RequisitionID
                                                      

DoCmd.RunSQL SQL1
DoCmd.RunSQL SQL2
DoCmd.RunSQL SQL3

 

[PHV]

You may try this:
SQL3 = "UPDATE TblWorkReqDetail SET requisitionno=[tt]'"[/tt] & RequisitionID & [tt]"'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cakoliver]

PH,
Thanks,that worked just fine. Don't know what I'd do without you folks...