Run-time error 3134, Syntax error INSERT INTO (1 Viewer)

AccessNewbie73 · Dec 8, 2009

Hello everyone,

I'm venturing into my first attempts at sql and am getting a "Run-time error '3134': Syntax error in INSERT INTO statement. Here's the code below. Any suggestions??

Private Sub cmdSaveLabRequest_Click()

Dim strCriteria As String
Dim strSQL As String

If Me.unbOrderedBy = "" Then

MsgBox (" Ordered By field cannot be blank")

Else
If Me.cmbMinistry = "Select Ministry" Then

MsgBox (" Select Ministry")

Else

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO tblOSHWALabRequests (Candidate, ApplicantID, Req, StartDate, DOB, Position, PL-Dept, Ministry, DateLabsOrdered, OrderedBy, AllNewHireLabs, QuantiferonGold, RubeollaAntibody, RubellaAntibody, MumpsTiter, HepBSurfAntigen, UrineDrug, LeadLabTests, BloodLead, ZincProtoporphyrin, LeadCBCDiff, OncLabTests, OncCBCDiff, CMBP, SGPT, DCProviderLabTests, DCCBCDiff, MiscLabTests, VaricellaAntibody, UAHCG,) Values (cmbCandidate, unbApplicantID, unbReq, unbStartDate, unbDOB, unbPosition, unbPLDept, cmbMinistry, unbDateOrdered, unbOrderedBy, unbAllLabs, unbAllTests1, unbAllTests2, unbAllTests3, unbAllTests4, unbAllTests5, unbAllTests6, unbPBTests, unbPBTest1, unbPBTest2, unbPBTest3, OncTests, OncTest1, OncTest2, OncTest3, OncTest4, unbDCProvidersTests, unbDCProvidersTest1, unbMiscTests, unbMiscTest1, unbMiscTest2)"

End If

End If

Me.Refresh

DoCmd.SetWarnings True

End Sub

JANR · Dec 8, 2009

Point 1

All SQL has to be on one string, for long SQL statements breake it up in pieces to be more readeble, if your code is as you posted it. Then add a linecontinue at the end of the individuelle lines.

Code:

....[FONT=Arial]OrderedBy, [COLOR=red]& [/COLOR][COLOR=red]_[/COLOR][/FONT]

A better way is to build it up gradually:

Code:

Dim strSQL As String
 
strSQL = ""
strSQL = strSQL & " INSERT INTO....."
strSQL = strSQL & " (candidate, [FONT=Arial]ApplicantID...."[/FONT]
[FONT=Arial].....[/FONT]
[FONT=Arial]....[/FONT]
 
DoCmd.RunSQL strSQL

Point 2:
In your values bit make sure to wrap textfields in " " , DateFields #SomeDate#

Point 3:
Why are you using what looks like unbound form, when binding it to a table/Query is so much easier.

Sidenote, your syntax error is probably this bit:

Code:

...[FONT=Arial]VaricellaAntibody, UAHCG[COLOR=red][B],[/B][/COLOR]) Values.... [/FONT]

Remove the extra comma, but I don't think it will run as you expect, do a Debug.Print of the SQL before you execute is also a good idea.

Just my 5 cents

JR

AccessNewbie73 · Dec 8, 2009

Thanks for your guidance on this. Makes sense to break it into smaller pieces and I tried to add the "&_" followed by a new line - hope I got the syntax right. Again, thanks for your help as I dip my feet in the SQL water!

Do you see any obvious problems with what I've got below?

Is the "Do.cmd.RunSQL strSQL" right at the end?

Dim strCriteria As String
Dim strSQL As String

strSQL = ""
strSQL = strSQL & "INSERT INTO tblOSHWALabRequests"
strSQL = strSQL & "(Candidate, ApplicantID, Req, StartDate, DOB, Position, PL-Dept, & _ Ministry, DateLabsOrdered, OrderedBy, AllNewHireLabs, QuantiferonGold, RubeollaAntibody, & _

RubellaAntibody, MumpsTiter, HepBSurfAntigen, UrineDrug, LeadLabTests, BloodLead, & _
ZincProtoporphyrin, LeadCBCDiff, OncLabTests, OncCBCDiff, CMBP, SGPT, & _
DCProviderLabTests, DCCBCDiff, MiscLabTests, VaricellaAntibody, UAHCG)"
strSQL = strSQL & "Values"
strSQL = strSQL & "(cmbCandidate, unbApplicantID, unbReq, #unbStartDate#, #unbDOB#, & _
unbPosition, unbPLDept, cmbMinistry, #unbDateOrdered#, unbOrderedBy, unbAllLabs, & _
unbAllTests1, unbAllTests2, unbAllTests3, unbAllTests4, unbAllTests5, unbAllTests6, & _
unbPBTests, unbPBTest1, unbPBTest2, unbPBTest3, OncTests, OncTest1, OncTest2, & _
OncTest3, OncTest4, unbDCProvidersTests, unbDCProvidersTest1, unbMiscTests, & _
unbMiscTest1, unbMiscTest2)"

DoCmd.RunSQL strSQL

JANR · Dec 9, 2009

Here is a better and cleaner SQL string:

Code:

Dim strSQL As String
strSQL = ""
strSQL = strSQL & " INSERT INTO tblOSHWALabRequests"
strSQL = strSQL & " (Candidate, ApplicantID, Req, StartDate, DOB, Position, PL-Dept, [COLOR=red][B][[/B][/COLOR]& _ Ministry[COLOR=red][B]][/B][/COLOR], DateLabsOrdered, OrderedBy, AllNewHireLabs, QuantiferonGold, RubeollaAntibody,"
StrSQL = strSQL & " RubellaAntibody, MumpsTiter, HepBSurfAntigen, UrineDrug, LeadLabTests, BloodLead, ZincProtoporphyrin, LeadCBCDiff, OncLabTests, OncCBCDiff, CMBP, SGPT,"
strSQL = strSQL & " DCProviderLabTests, DCCBCDiff, MiscLabTests, VaricellaAntibody, UAHCG)"
strSQL = strSQL & " Values"
strSQL = strSQL & " (cmbCandidate, unbApplicantID, unbReq, #unbStartDate#, #unbDOB#, unbPosition, unbPLDept, cmbMinistry, #unbDateOrdered#, unbOrderedBy, unbAllLabs,"
strSQL = strSQL & " unbAllTests1, unbAllTests2, unbAllTests3, unbAllTests4, unbAllTests5, unbAllTests6, unbPBTests, unbPBTest1, unbPBTest2, unbPBTest3, OncTests, OncTest1, OncTest2,"
strSQL = strSQL & " OncTest3, OncTest4, unbDCProvidersTests, unbDCProvidersTest1, unbMiscTests, unbMiscTest1, unbMiscTest2)"
Debug.Print strSQL
'Currentdb.Extecute strSQL, dbFailOnError ' if strSQL looks ok, then uncomment line.

Note the brackets [], a fieldname like &_Ministry in a table is asking for trouble!!!

Also are all your fields in the target table numbers exept a few dates? If some are text you must wrap them in quotes. I also would use Execute methode instead of Docmd.RunSql, then you don't have to turn Warings on and off.

But you still haven't given an answer on why you think this is the best way to accomplice you goal. I still think a bound form is the way to go.

JR

FYI Where are the values coming from? If they are fields in a form you have to refrence the properly, like Me!cmbCandidate and so on. If this is a Form Module.

missinglinq · Dec 9, 2009

Also note that

If Me.unbOrderedBy = "" Then

is not going to tell you if the field is blank. A blank field in Access, including fields that have had data and subsequently had the data deleted is Null. To check for Nulls

If Nz(Me.unbOrderedBy, "") = "" Then

gemma-the-husky · Dec 9, 2009

but you cant set the values out as you have - if they are variables they need to be formatted differently.

This is crazy - if you are new to access, use bound forms until you understand what is going on.

JANR · Dec 9, 2009

gemma-the-husky said:
but you cant set the values out as you have - if they are variables they need to be formatted differently.

This is crazy - if you are new to access, use bound forms until you understand what is going on.

Well spoken, large Insert statements should be avoided it to easy to get the order or syntax wrong. One wrong character and you be debugging for ever.

Just to give you some idee, in your value fields you must wrap every field correctly.

Code:

Every textfield must use this wrapper:  '" & Me!textfield & "' 
 
Numberfield must use this wapper:  " & Me!NumberField & "
 
Datefields must be wrapped like this: #" & Me!DateField & "#

If you do not use US date format mm/dd/yyyy, then you have to Formate the datefields before you send it to the table.

JR

ASherbuck · Dec 9, 2009

I'm going to throw out a tip as it's worked for me and I haven't seen it recommended on the boards.

When I'm having a hard time with sql I just use Access to build my query the way I need it. I can test it (Except for code variables but those are easy to substitute in code - it also helps me figure out if my variables are what's screwing things up)

If my access query works I just flip it over to sql view, copy & paste then fix the formatting to make the vba happy.

irunergoiam · Dec 9, 2009

JANR,

Thanks for simplifying the code. I see where I was going wrong, as I wasn't sure how to wrap the line in quotations for subsequent StrSQL = strSQL & " lines.

The good news is. I'm no longer getting errors. The not-so-good news is that it is not updating my table. The field names for the table are correct in the string as are the forms field names. What am I missing?

JANR · Dec 9, 2009

The good news is. I'm no longer getting errors. The not-so-good news is that it is not updating my table. The field names for the table are correct in the string as are the forms field names. What am I missing?

Hard to say without seeing your full code, could be as simpel as an uncomment RunCommand, like:

Code:

'Currentdb.Execute strSQL....

Any errors would also help, I'm not a psychic

JR

irunergoiam · Dec 9, 2009

What you lack in psychic abilities, you more than make up for in patience and good old-fashioned Access know-how!

No errors. The code you supplied worked brilliantly in getting rid of that! Here's the entire code from the On Click Event Procedure:

Private Sub cmdSaveLabRequest_Click()
If Me.unbOrderedBy = "" Then
MsgBox (" Ordered By field cannot be blank")

Else
If Me.cmbMinistry = "Select Ministry" Then
MsgBox (" Select Ministry")
Else
DoCmd.SetWarnings False
Dim StrSQL As String
Dim strCriteria As String
StrSQL = ""
StrSQL = StrSQL & " INSERT INTO tblOSHWALabRequests"
StrSQL = StrSQL & " (Candidate, ApplicantID, Req, StartDate, DOB, Position, PL-Dept, [& _ Ministry], DateLabsOrdered, OrderedBy, AllNewHireLabs, QuantiferonGold, RubeollaAntibody,"
StrSQL = StrSQL & " RubellaAntibody, MumpsTiter, HepBSurfAntigen, UrineDrug, LeadLabTests, BloodLead, ZincProtoporphyrin, LeadCBCDiff, OncLabTests, OncCBCDiff, CMBP, SGPT,"
StrSQL = StrSQL & " DCProviderLabTests, DCCBCDiff, MiscLabTests, VaricellaAntibody, UAHCG)"
StrSQL = StrSQL & " Values"
StrSQL = StrSQL & " (cmbCandidate, unbApplicantID, unbReq, #unbStartDate#, #unbDOB#, unbPosition, unbPLDept, cmbMinistry, #unbDateOrdered#, unbOrderedBy, unbAllLabs,"
StrSQL = StrSQL & " unbAllTests1, unbAllTests2, unbAllTests3, unbAllTests4, unbAllTests5, unbAllTests6, unbPBTests, unbPBTest1, unbPBTest2, unbPBTest3, OncTests, OncTest1, OncTest2,"
StrSQL = StrSQL & " OncTest3, OncTest4, unbDCProvidersTests, unbDCProvidersTest1, unbMiscTests, unbMiscTest1, unbMiscTest2)"
Debug.Print StrSQL
'Currentdb.Extecute strSQL, dbFailOnError ' if strSQL looks ok, then uncomment line.

End If

End If

Me.Refresh

DoCmd.SetWarnings True
End Sub

JANR · Dec 9, 2009

I am a psychic after all.

remove the ' before the currentdb.Execute line.

In your code this line should be green, meaning Access skips this line and treats it as a commentline.

Commentlines are useful to explane different things about your code, so when you revist it later it's easier to understand what the code is supose to do. Make a habbit of this and life is easier.

JR

irunergoiam · Dec 9, 2009

Comment lines make a lot of sense - cool feature. That said, I got rid of the tick mark so the line reads:

Currentdb.Extecute strSQL, dbFailOnError ' if strSQL looks ok, then uncomment line.

Back to getting Run-tim error 3134. So, I tried just

Currentdb.Extecute strSQL, dbFailOnError

Still getting error. Debuging takes me to the Currentdb.... line and highlights it in yellow. When I do the right-click and run to cursor I still get the same "Run-time error"

TallMan · Dec 9, 2009

looks like on the last line you have a 'Currentdb.Extecute instead you should have a CurrentDB.execute.

Not sure if this will help.

JANR · Dec 9, 2009

That's because you haven't read what you've been told be me and others!

I expected that this code woulden't run because you haven't wrapped your value field's like I told.

Every textfield must use this wrapper: '" & Me!textfield & "'

Numberfield must use this wapper: " & Me!NumberField & "

Datefields must be wrapped like this: #" & Me!DateField & "#

Also your logic int the if's are not clear, see Missinglinq's post.

Also note that
If Me.unbOrderedBy = "" Then
is not going to tell you if the field is blank. A blank field in Access, including fields that have had data and subsequently had the data deleted is Null. To check for Nulls
If Nz(Me.unbOrderedBy, "") = "" Then

Also by using Execute metode you don't need to set the warnings on/off, and you have unused variables like. If you don't use it, don't declare it is the rule.

Code:

Dim strCriteria As String

Here is a start for you of a cleaner code. I have started on some of your value field's on pure guesswork Numbers and dates, but if some are text you have to change it to the right wrapper.

Code:

Option Compare Database
Option Explicit
Private Sub cmdSaveLabRequest_Click()
If Nz(Me.unbOrderedBy, "") = "" Then
    MsgBox " Ordered By field cannot be blank"
    Exit Sub ' no point to go so exit sub.
Else
    If Me.cmbMinistry = "Select Ministry" Then ' dosen't make sence to me??
        MsgBox " Select Ministry"
    Else
        Dim StrSQL As String
        StrSQL = ""
        StrSQL = StrSQL & " INSERT INTO tblOSHWALabRequests"
        StrSQL = StrSQL & " (Candidate, ApplicantID, Req, StartDate, DOB, Position, PL-Dept, [& _ Ministry], DateLabsOrdered, OrderedBy, AllNewHireLabs, QuantiferonGold, RubeollaAntibody,"
        StrSQL = StrSQL & " RubellaAntibody, MumpsTiter, HepBSurfAntigen, UrineDrug, LeadLabTests, BloodLead, ZincProtoporphyrin, LeadCBCDiff, OncLabTests, OncCBCDiff, CMBP, SGPT,"
        StrSQL = StrSQL & " DCProviderLabTests, DCCBCDiff, MiscLabTests, VaricellaAntibody, UAHCG)"
        StrSQL = StrSQL & " Values"
        StrSQL = StrSQL & " (" & cmbCandidate & ", " & unbApplicantID & ", " & unbReq & ", #" & unbStartDate & "#, #" & unbDOB & "#, unbPosition, unbPLDept, cmbMinistry, #" & unbDateOrdered & "#, unbOrderedBy, unbAllLabs,"
        StrSQL = StrSQL & " unbAllTests1, unbAllTests2, unbAllTests3, unbAllTests4, unbAllTests5, unbAllTests6, unbPBTests, unbPBTest1, unbPBTest2, unbPBTest3, OncTests, OncTest1, OncTest2,"
        StrSQL = StrSQL & " OncTest3, OncTest4, unbDCProvidersTests, unbDCProvidersTest1, unbMiscTests, unbMiscTest1, unbMiscTest2)"
        'Debug.Print StrSQL 'uncomment during development
        CurrentDB.Execute StrSQL, dbFailOnError ' if strSQL looks ok, then uncomment line.
    End If
End If
Me.Refresh
End Sub

Also indenting all your code work, helps to understand it better.

JR

irunergoiam · Dec 9, 2009

TallMan,

JANR caught that one for me and I removed the tick mark. Back to getting the error.

irunergoiam · Dec 12, 2009

Thank for your guidance JANR and TallMan. A few tweaks and I got it...

Dim StrSQL As String
Dim strCriteria As String
StrSQL = ""
StrSQL = StrSQL & " INSERT INTO tblOSHWALabRequests"
StrSQL = StrSQL & " (Candidate, ApplicantID, Req, StartDate, DOB, Position, [PL-Dept], Ministry, DateLabsOrdered, OrderedBy, AllNewHireLabs, "
StrSQL = StrSQL & " QuantiferonGold, RubeollaAntibody, RubellaAntibody, MumpsTiter, HepBSurfAntigen, UrineDrug, LeadLabTests, BloodLead, ZincProtoporphyrin, LeadCBCDiff, OncLabTests, OncCBCDiff,"
StrSQL = StrSQL & " CMBP, SGPT, Urinalysis, DCProviderLabTests, DCCBCDiff, MiscLabTests, VaricellaAntibody, UAHCG)"
StrSQL = StrSQL & " Values"
StrSQL = StrSQL & " ('" & cmbCandidate & "','" & unbApplicantID & "', " & unbReq & ", #" & unbStartDate & "#,#" & unbDOB & "#,'" & unbPosition & "','" & unbPLDept & "','" & cmbMinistry & "',#" & unbDateOrdered & "#,'" & unbOrderedBy & "'," & unbAllLabs & ","
StrSQL = StrSQL & " " & unbAllTests1 & ", " & unbAllTests2 & ", " & unbAllTests3 & ", " & unbAllTests4 & ", " & unbAllTests5 & ", " & unbAllTests6 & ", " & unbPBTests & ", " & unbPBTest1 & ", " & unbPBTest2 & ", " & unbPBTest3 & ", " & OncTests & ", " & OncTest1 & ", " & OncTest2 & ","
StrSQL = StrSQL & " " & OncTest3 & ", " & OncTest4 & ", " & unbDCProvidersTests & ", " & unbDCProvidersTest1 & ", " & unbMiscTests & ", " & unbMiscTest1 & ", " & unbMiscTest2 & ")"
DoCmd.RunSQL StrSQL

RainLover · Dec 12, 2009

Did I count the Fields and Controls right.

I got 30 Fields and 31 Controls.

Suggest you try just one insert. When that works add another and so on.

RainLover · Dec 12, 2009

I am not saying that this is correct, but this is how I would format the String.

Code:

Private Sub cmdSaveLabRequest_Click()
    Dim strSQL As String
 
    If Len(Me.unbOrderedBy) Then
    If Me.cmbMinistry = "Select Ministry" Then
        MsgBox " Select Ministry"
    Else
    strSQL = " INSERT INTO tblOSHWALabRequests " & _
             " (Candidate, ApplicantID, Req, StartDate, DOB, Position, PL-Dept, Ministry, DateLabsOrdered, OrderedBy, " & _
             " AllNewHireLabs, QuantiferonGold, RubeollaAntibody, RubellaAntibody, MumpsTiter, HepBSurfAntigen, " & _
             " UrineDrug, LeadLabTests, BloodLead, ZincProtoporphyrin, LeadCBCDiff, OncLabTests, OncCBCDiff, CMBP, SGPT, " & _
             " DCProviderLabTests, DCCBCDiff, MiscLabTests, VaricellaAntibody, UAHCG) " & _
             " Values " & _
             " (cmbCandidate, unbApplicantID, unbReq, #unbStartDate#, #unbDOB#, unbPosition, unbPLDept, cmbMinistry, " & _
             " #unbDateOrdered#, unbOrderedBy, unbAllLabs, unbAllTests1, unbAllTests2, unbAllTests3, unbAllTests4, " & _
             " unbAllTests5, unbAllTests6, unbPBTests, unbPBTest1, unbPBTest2, unbPBTest3, OncTests, OncTest1, OncTest2, " & _
             " OncTest3, OncTest4, unbDCProvidersTests, unbDCProvidersTest1, unbMiscTests, unbMiscTest1, unbMiscTest2)"
 
    CurrentDb.Execute strSQL, dbFailOnError
        Else
            MsgBox " Ordered By field cannot be blank"
        End If
    End If
Me.Refresh
End Sub

missinglinq · Dec 13, 2009

I have to agree with Dave here! A big part of the reason to use Access for database development is the speed with which it can be created, using bound forms. Several developers I know, experienced in Visual Basic database development and Access development, estimate that development using unbound forms takes twice as long as it does when using Access and bound forms.

If you insist on using unbound forms, you'd be far better off using a Visual Basic or C++ front end with a SQL Server or Oracle back end, because

You can create an EXE file which gives total protection to your code/design
You can distribute the db to PCs without a copy of Access being on board
Your data security if far, far better than anything you can do in Access

Run-time error 3134, Syntax error INSERT INTO (1 Viewer)

New member

Registered User.

New member

Registered User.

AWF VIP

Super Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

VIP From a land downunder

VIP From a land downunder

AWF VIP

Similar threads

Users who are viewing this thread