Update Multiple Fields using VBA SQL

[jsdba]

I have some vba/sql syntax (compile) error that i just cant figure out.
I don't want to write 10 update statement, i rather write one long one. Can someone tell me where i went wrong. Much appreciated.

    db.Execute ("UPDATE tblProposal SET _
                "ProjectAddress='" & me.ProjectAddress & "', _
                "AddressModifier='" & me.AddressModifier & "', _
                "BoroughID=" & me.BoroughID & ", _
                "ClientID=" & me.ClientID & ", _
                "ClientContactID=" & me.ClientContactID & ", _
                "ClientEmail='" & me.ClientEmail & "', _
                "ClientStreet='" & me.ClientStreet & "', _
                "ClientCity='" & me.ClientCity & "', _
                "ClientState='" & me.ClientState & "', _
                "ClientZip='" & me.ClientZip & "', _
                "ProposalDescription='" & me.ProposalDescription & "', _
                "RetainerRequested=" & me.RetainerRequested & ", _
                "RetainerPercentRequested=" & me.RetainerPercentRequested & ", _
                "RetainerAmountRequested=" & me.RetainerAmountRequested & ", _
                "PdfCreateDate='" & me.PdfCreateDate & ", _
                "WHERE ProjectGroupID=" & me.ProjectGroupID)

 

[arnelgp]

   db.Execute ("UPDATE tblProposal SET _
                "ProjectAddress='" & me.ProjectAddress & "'," &  _
                "AddressModifier='" & me.AddressModifier & "'," &  _
                "BoroughID=" & me.BoroughID & "," &  _
                "ClientID=" & me.ClientID & "," &  _
                "ClientContactID=" & me.ClientContactID & "," &  _
                "ClientEmail='" & me.ClientEmail & "'," &  _
                "ClientStreet='" & me.ClientStreet & "'," &  _
                "ClientCity='" & me.ClientCity & "'," &  _
                "ClientState='" & me.ClientState & "'," &  _
                "ClientZip='" & me.ClientZip & "'," &  _
                "ProposalDescription='" & me.ProposalDescription & "'," &  _
                "RetainerRequested=" & me.RetainerRequested & "," &  _
                "RetainerPercentRequested=" & me.RetainerPercentRequested & "," &  _
                "RetainerAmountRequested=" & me.RetainerAmountRequested & "," &  _
                "PdfCreateDate=#" & Format(me.PdfCreateDate,"mm/dd/yyyy") & "# " &  _
                "WHERE ProjectGroupID=" & me.ProjectGroupID)

 

[jsdba]

   db.Execute ("UPDATE tblProposal SET _
                "ProjectAddress='" & me.ProjectAddress & "'," &  _
                "AddressModifier='" & me.AddressModifier & "'," &  _
                "BoroughID=" & me.BoroughID & "," &  _
                "ClientID=" & me.ClientID & "," &  _
                "ClientContactID=" & me.ClientContactID & "," &  _
                "ClientEmail='" & me.ClientEmail & "'," &  _
                "ClientStreet='" & me.ClientStreet & "'," &  _
                "ClientCity='" & me.ClientCity & "'," &  _
                "ClientState='" & me.ClientState & "'," &  _
                "ClientZip='" & me.ClientZip & "'," &  _
                "ProposalDescription='" & me.ProposalDescription & "'," &  _
                "RetainerRequested=" & me.RetainerRequested & "," &  _
                "RetainerPercentRequested=" & me.RetainerPercentRequested & "," &  _
                "RetainerAmountRequested=" & me.RetainerAmountRequested & "," &  _
                "PdfCreateDate=#" & Format(me.PdfCreateDate,"mm/dd/yyyy") & "# " &  _
                "WHERE ProjectGroupID=" & me.ProjectGroupID)

Copied and paste but still no luck!

 

[jsdba]

I think i got it!

    db.Execute ("UPDATE tblProposal SET[B][COLOR="Red"] " &[/COLOR][/B] _
                "ProjectAddress='" & Me.ProjectAddress & "'," & _
                "AddressModifier='" & Me.AddressModifier & "'," & _
                "BoroughID=" & Me.BoroughID & "," & _
                "ClientID=" & Me.ClientID & "," & _
                "ClientContactID=" & Me.ClientContactID & "," & _
                "ClientEmail='" & Me.ClientEmail & "'," & _
                "ClientStreet='" & Me.ClientStreet & "'," & _
                "ClientCity='" & Me.ClientCity & "'," & _
                "ClientState='" & Me.ClientState & "'," & _
                "ClientZip='" & Me.ClientZip & "'," & _
                "ProposalDescription='" & Me.ProposalDescription & "'," & _
                "RetainerRequested=" & Me.RetainerRequested & "," & _
                "RetainerPercentRequested=" & Me.RetainerPercentRequested & "," & _
                "RetainerAmountRequested=" & Me.RetainerAmountRequested & "," & _
                "PdfCreateDate=#" & Format(Me.PdfCreateDate, "mm/dd/yyyy") & "# " & _
                "WHERE ProjectGroupID=" & Me.ProjectGroupID)

 

[jsdba]

Sorry guys i have another VBA SQL error i need some help with

db.Execute ("UPDATE tblFeeService InnerJoin tblFeePaymentStage ON tblFeeService.FeeID = tblFeePaymentStage.FeeID SET " & _
            "InvoiceNum='" & IncrementalInvoiceNum & "'" & _
            "WHERE tblFeePaymentStage.QuantityCompleted > 0 " & _
            "AND tblFeeService.ClientID=" & Me.OriginalClientID & _
            "AND tblFeeService.ProjectNum='" & Me.ProjectNum & "';")

SQL in Query Builder (Verified Working)

UPDATE tblFeeService INNER JOIN tblFeePaymentStage ON tblFeeService.FeeID = tblFeePaymentStage.FeeID SET tblFeePaymentStage.InvoiceNum = "1001"
WHERE (((tblFeePaymentStage.QuantityCompleted)>0) AND ((tblFeeService.ClientID)=274) AND ((tblFeeService.ProjectNum)="160001"));

Runtime error Syntax error in UPDATE statement.

 

[Minty]

db.Execute ("UPDATE tblFeeService InnerJoin tblFeePaymentStage ON tblFeeService.FeeID = tblFeePaymentStage.FeeID SET " & _
            "InvoiceNum='" & IncrementalInvoiceNum & "[COLOR="Red"]' "[/COLOR] & _
            "WHERE tblFeePaymentStage.QuantityCompleted > 0 " & _
            "AND tblFeeService.ClientID=" & Me.OriginalClientID [COLOR="Red"]& " "[/COLOR] & _
            "AND tblFeeService.ProjectNum='" & Me.ProjectNum & "';")

Rather than db.execute and a huge string, build the string first (strSql or similar) then use

Debug.Print strSql

db.execute strSql

In the immediate window you will then see exactly what you are passing as as query clause.

 

[arnelgp]

try re-arranging your table in your update statement, on sql view:

db.Execute ("UPDATE tblFeePaymentStage Inner Join tblFeeService ON tblFeePaymentStage.FeeID=tblFeeService.FeeID SET " & _
            "tblFeePaymentStage.InvoiceNum='" & [IncrementalInvoiceNum] & "'" & _
            "WHERE tblFeePaymentStage.QuantityCompleted > 0 " & _
            "AND tblFeeService.ClientID=" & Me.OriginalClientID & _
            "AND tblFeeService.ProjectNum='" & Me.ProjectNum & "';")

 

[jsdba]

Thanks guys. Debug.Print strSql was helpful. i had "InnerJoin" and not "Inner Join" + some missing quotes Minty pointed out.