I have frmJobs with subform frmJobParts - data comes from table Jobs and JobParts.
I am trying to copy multiple records in table JobParts for a previous job (the value comes from [frmJobs]![txtPrevJobNo]) and append them to the JobParts table, but with an updated job number (value comes from[frmJobs]![txtjobNo]).
I tried to create an Append query without much success, so I thought I would try and do it as an SQL statement, but I am gettg a syntax error when I run the SQL statement.
Can anyone point me at the syntax error please?
I am trying to copy multiple records in table JobParts for a previous job (the value comes from [frmJobs]![txtPrevJobNo]) and append them to the JobParts table, but with an updated job number (value comes from[frmJobs]![txtjobNo]).
I tried to create an Append query without much success, so I thought I would try and do it as an SQL statement, but I am gettg a syntax error when I run the SQL statement.
Can anyone point me at the syntax error please?
Code:
Dim StrSQL As String
Dim IntJobNo As Integer
IntJobNo = [Forms]![frmJobs]![txtJobNo]
StrSQL = "INSERT INTO JobParts (JobNo, PartNo, PaperColour, PaperGSM, PaperType, FrontInk1, FrontInk2, FrontInk3, FrontInk4, RevInk1, RevInk2," & _
"RevInk3, RevInk4 SELECT (intJobNo, JobParts.PartNo, JobParts.PaperColour, JobParts.PaperGSM, JobParts.PaperType, JobParts.FrontInk1," & _
"JobParts.FrontInk2, JobParts.FrontInk3, JobParts.FrontInk4, JobParts.RevInk1, JobParts.RevInk2, JobParts.RevInk3, JobParts.RevInk4)" & _
"FROM JobParts WHERE (((JobParts.JobNo)=[frmJobs]![txtPrevJobNo]))"
DoCmd.RunSQL (StrSQL)