Syntax Error in VBA code

[Kirby]

The VBA code for an access form has a problem with the syntax - Error number 3144. When I go to debug, the line highlights is CurrentDb.Execute (sqlStr). Can someone please tell me where the problem is.


Private Sub AuftragAbgleichen()
Dim sqlStr As String, sAuftragAlphanum As String
Dim ortId As Long

ortId = cmb_Orte.Value

If Not IsNull(edAuftragAlphanum.Value) Then _
sAuftragAlphanum = edAuftragAlphanum.Value
If Len(sAuftragAlphanum) = 0 Then sAuftragAlphanum = cmb_Auftragsnummer2.Value

sqlStr = "UPDATE AUFTRAEGE SET BAUORT_NR = " & ortId & ", BAUSTRASSE ='" _
& txt_LieferStrasse & "', BAUBEZEICHNUNG = '" & txt_BauherrName & "' , AUFTRAG='" & sAuftragAlphanum & _
"', EstPanels = '" & TxtEstPanels & "', Estm2 = '" & TxtEstM2 & "', EstPrice = '" & TxtEstPrice & _
"', Variations = '" & txtVariations & "', startdate = '" & txtStartDate & "',ordernumber = '" & _
txtOrderNumber & "',finishdate = '" & txtFinishDate & "',invoiced = '" & txtInvoiced & "',full = " & boxFull.Value


sqlStr = sqlStr & " WHERE AUFTRAGS_NR =" & cmb_Auftragsnummer2.Value

CurrentDb.Execute (sqlStr)
End Sub

Kirby O'Rourke

 

[jdraw]

Try




CurrentDb.Execute sqlStr, dbFailOnError


You might also try

Debug.Print sqlstr and see what the rendered SQL looks like.

 

[CJ_London]

It looks like all your fields are presumed to be text in the sql but for example

EstPrice = '" & TxtEstPrice & "'

is this a number? if so, you need to remove the single quotations

Similarly

finishdate = '" & txtFinishDate & "'

probably needs to be

finishdate = #" & txtFinishDate & "#

It is also possible that by using & _ you have missed a space along the way

if you insert

Debug.Print sqlStr

before your execute command you will see the finished string in the immediate window

Copy this to the query sql window and try to run it, you will get more information as to why it is not working

 

[Kirby]

I tried using CurrentDb.Execute sqlStr, dbFailOnError and then I got an error saying "Invalid use of Null".
I think the problem could be something to do with the type of data as CJ London pointed out - especially the last item which is a tickbox. I will try a few things and see if it helps.

Kirby

 

[jdraw]

Did you try the Debug.Print?

 

[CJ_London]

Invalid use of Null

This says one (or more) of the fields have not been completed so are null. The solution is to wrap them in the nz function e.g.

EstPrice = '" & nz(TxtEstPrice) & "'

Though you still need to check for correct data type

 

[MarkK]

The most simple and reliable way to do this is using a DAO.QueryDef, which handles all your delimiters and your datatypes for you.

Private Sub Test1974612904()
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef("", _
        "UPDATE tTestData " & _
        "SET [Name] = p0, " & _
            "[Value] = p1 " & _
        "WHERE ID = p2;")
    With qdf
        .Parameters(0) = "This is the name"
        .Parameters(1) = "Value goes here"
        .Parameters(2) = 29
        .Execute dbFailOnError
        .Close
    End With
End Sub

Note that anything not recognized as a valid field in Jet SQL is presumed to be a parameter, therefore you can just throw in text like "p0" and "p1" as your parameter names. Also, parameters will be created and indexed in their order of appearance. Later in your code, you can assign values to parameters by index or by name, and then call the execute method of the QueryDef. If you want reliability, simplicity and readability, I recommend this method, but it will require a few additional lines of code.

Private Sub Test142974612934867()
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef("", _
        "UPDATE AUFTRAEGE SET BAUORT_NR = p0, BAUSTRASSE = p1, BAUBEZEICHNUNG = p2, " & _
        "AUFTRAG = p3, EstPanels = p4, Estm2 = p5, EstPrice = p6, Variations = p7, " & _
        "startdate = p8, ordernumber = p9, finishdate = p10, invoiced = p11, full = p12 " & _
        "WHERE AUFTRAGS_NR = p13;")
    With qdf
        .Parameters("p0") = ortId
        .Parameters("p1") = txt_LieferStrasse
        .Parameters("p2") = txt_BauherrName
        .Parameters("p3") = sAuftragAlphanum
        .Parameters("p4") = TxtEstPanels
        '...
        .Execute dbFailOnError
        .Close
    End With

End Sub

 

[Kirby]

OK, I tried lagbolt's solution and I ended up with the following:

Private Sub AuftragAbgleichen()
Dim qdf As DAO.QueryDef
Dim sqlStr As String, sAuftragAlphanum As String
Dim ortId As Long

ortId = Nz(cmb_Orte.Value)

Set qdf = CurrentDb.CreateQueryDef("", _
"UPDATE AUFTRAEGE SET BAUORT_NR = p0, BAUSTRASSE = p1, BAUBEZEICHNUNG = p2, " & _
"AUFTRAG = p3, EstPanels = p4, Estm2 = p5, EstPrice = p6, Variations = p7, " & _
"startdate = p8, ordernumber = p9, finishdate = p10, invoiced = p11, full = p12 " & _
"WHERE AUFTRAGS_NR = p13;")
With qdf
.Parameters("p0") = ortId
.Parameters("p1") = txt_LieferStrasse
.Parameters("p2") = txt_BauherrName
.Parameters("p3") = sAuftragAlphanum
.Parameters("p4") = TxtEstPanels
.Parameters("p5") = TxtEstM2
.Parameters("p6") = TxtEstPrice
.Parameters("p7") = txtVariations
.Parameters("p8") = txtStartDate
.Parameters("p9") = txtOrderNumber
.Parameters("p10") = txtFinishDate
.Parameters("p11") = txtInvoiced
.Parameters("p12") = boxFull.Value
.Parameters("p13") = cmb_Auftragsnummer2.Value

.Execute dbFailOnError
.Close
End With

End Sub


Everything looks like it is working now. Thank you all for your help.

Kirby O'Rourke