Syntax error

[PHisaw]

Please forgive me for starting a new post, but it seems problem has changed a
little and the other post was left unanswered. I've always received a lot of
help here and am hoping someone can help me figure this one out. I'm trying
to append some records to a table for a subform and am now stuck with a
syntax error. All fields are numbers except model and module which are text.
All three fields are populated with the information on the form, but I can't
determine what is causing the error.

I've spent a considerable amount of time searching posts and changing sql
but it still throws error. If anyone can tell me what I'm doing wrong, I
would greatly appreciate it.

Thanks in advance,
Phisaw

Private Sub Command24_Click()

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()
Debug.Print strWhere

strSQL = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, Module,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.Module, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.Module = tSparePartsTemplate.Module) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE (((tSparePartsMainForm.QuoteNumber)= " &
Forms!fsparepartsmainform!QuoteNumber & _
"AND ((tSparePartsTemplate.Model)= '" & Forms!fsparepartsmainform!Model &
"', " & _
"AND ((tSparePartsTemplate.Module)= '" & Forms!fsparepartsmainform!Module &
"'); "

db.Execute strSQL, dbFailOnError

db.Close
Set db = Nothing

End Sub


it throws the Run-time error 3075
Syntax error (missing operator) in query expression
'(((tsparepartsmainform.quotenumber)= 25AND
((tsparepartstemplate.model)='111' AND
((tsparepartstemplate.module)='a');'

 

[RonaldoOneNil]

Is it as simple as putting a space before the AND so that it reads 25 AND
instead of 25AND

 

[PHisaw]

Thanks, but I tried this and it still gives the same error. I've tried
putting single quotes, double quotes, etc and still get an error.

 

[Douglas J. Steele]

You've got an incorrect comma in the second line of the WHERE clause:

"WHERE (((tSparePartsMainForm.QuoteNumber)= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND ((tSparePartsTemplate.Model)= '" & Forms!fsparepartsmainform!Model &
"', " & _
" AND ((tSparePartsTemplate.Module)= '" & Forms!fsparepartsmainform!Module &
"'); "

 

[PHisaw]

Thank you for replying. This is what I have now and it gives error 3075
"syntax error in query expression".

Private Sub Command24_Click()

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()
Debug.Print strWhere

strSQL = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, Module,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.Module, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.Module = tSparePartsTemplate.Module) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE (((tSparePartsMainForm.QuoteNumber)= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND ((tSparePartsTemplate.Model)= '" & Forms!fsparepartsmainform!Model &
"' " & _
" AND ((tSparePartsTemplate.Module)= '" & Forms!fsparepartsmainform!Module &
"'); "

db.Execute strSQL, dbFailOnError

db.Close
Set db = Nothing

End Sub

 

[Douglas J. Steele]

Sorry: didn't look closely at the parentheses

"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model &
"' " & _
" AND tSparePartsTemplate.Module= '" & Forms!fsparepartsmainform!Module &
"'' "

 

[PHisaw]

Thank you, thank you, thank you. Once again, you've solved my problem. Will
you please explain why I needed to remove the parentheses? I've searched
other posts that had them so I never thought to remove them.

One more tiny thing to complete this project - can you please tell me what
would be the best way to refresh my subform to have it populate with data
appended with command button?

Thanks again for your help.
Phisaw

 

[PHisaw]

I have the requery working.
Thanks again.

Sorry: didn't look closely at the parentheses

"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model &
"' " & _
" AND tSparePartsTemplate.Module= '" & Forms!fsparepartsmainform!Module &
"'' "

 

[Douglas J. Steele]

Well, I removed them because I dislike the excessive use of parentheses
Access insists on. <g>

The actual problem was that your parentheses were unbalanced: you had more
left parentheses than right ones. You could just as easily have used

"WHERE (((tSparePartsMainForm.QuoteNumber)= " &
Forms!fsparepartsmainform!QuoteNumber & _
") AND ((tSparePartsTemplate.Model)= '" & Forms!fsparepartsmainform!Model &
"') " & _
" AND ((tSparePartsTemplate.Module)= '" & Forms!fsparepartsmainform!Module &
"')); "