If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Syntax error
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');' |
#2
|
|||
|
|||
Syntax error
Is it as simple as putting a space before the AND so that it reads 25 AND
instead of 25AND "PHisaw" wrote: 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');' |
#3
|
|||
|
|||
Syntax error
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. "RonaldoOneNil" wrote: Is it as simple as putting a space before the AND so that it reads 25 AND instead of 25AND "PHisaw" wrote: 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');' |
#4
|
|||
|
|||
Syntax error
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 & "'); " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PHisaw" wrote in message ... 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');' |
#5
|
|||
|
|||
Syntax error
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" wrote: 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 & "'); " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PHisaw" wrote in message ... 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');' |
#6
|
|||
|
|||
Syntax error
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 & "'' " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PHisaw" wrote in message ... 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" wrote: 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 & "'); " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PHisaw" wrote in message ... 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');' |
#7
|
|||
|
|||
Syntax error
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 "Douglas J. Steele" wrote: 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 & "'' " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PHisaw" wrote in message ... 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" wrote: 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 & "'); " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PHisaw" wrote in message ... 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');' |
#8
|
|||
|
|||
Syntax error
I have the requery working.
Thanks again. "Douglas J. Steele" wrote: 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 & "'' " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PHisaw" wrote in message ... 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" wrote: 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 & "'); " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PHisaw" wrote in message ... 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');' |
#9
|
|||
|
|||
Syntax error
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 & "')); " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PHisaw" wrote in message ... 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 "Douglas J. Steele" wrote: 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 & "'' " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PHisaw" wrote in message ... 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" wrote: 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 & "'); " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PHisaw" wrote in message ... 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');' |
Thread Tools | |
Display Modes | |
|
|