best way to write update query in vba

[tagteam]

I have googled and searched this forum and have found several different ways to run an update query in but i can get them to work quite correctly.



What is the cleanest way to run an update query in vba

I am trying :

Dim dbs As Database
Dim lngRowsAffected As Long
Dim lngRowsUpdated As Long
Dim UpdateBID As String
UpdateBID = Me.BillingInvoiceID
Set dbs = CurrentDb




lngRowsAffected = dbs.RecordsAffected


dbs.Execute "Update tbl_RissData Set tbl_RissData.BID = UpdateBID ;", dbFailOnError

lngRowsUpdated = dbs.RecordsAffected

[pbaldy]

You have to concatenate the variable into the string. If it's numeric:


dbs.Execute "Update tbl_RissData Set tbl_RissData.BID = " & UpdateBID, dbFailOnError

if text:


dbs.Execute "Update tbl_RissData Set tbl_RissData.BID = '" & UpdateBID & "'", dbFailOnError

[tagteam]

Ok, I have simplified the code to this

Dim dbs As Database
Dim UpdateBID As String
Dim CurrentRissID As Integer
Dim strSQL As String
UpdateBID = Me.BillingInvoiceID
CurrentRissID = Me.RISSID




Debug.Print "Update Bid is " & UpdateBID
Debug.Print "CurrentRissID is " & CurrentRissID


strSQL = "Update tbl_RissData Set tbl_RissData.BID = " & UpdateBID & " Where tbl_RissData.Id = " & CurrentRissID & ";"


Debug.Print "strSQL is " & strSQL


CurrentDb.Execute strSQL, dbFailOnError

My debug statements show
Update Bid is FRAL-081125
CurrentRissID is 1733
strSQL is Update tbl_RissData Set tbl_RissData.BID = FRAL-081125 Where tbl_RissData.Id = 1733;

it looks like everything is fine but I am still getting: Run-time error '3061'; Too few parameters. Expected 1.

[pbaldy]

Your value is text so you need the single quotes around it.

[tagteam]

Ok, great, I got it to work with

strSQL = "Update tbl_RissData Set tbl_RissData.BID = '" & UpdateBID & "' Where tbl_RissData.Id = " & CurrentRissID & ";"

For those learning like me it is Single Quote and then Double Quote

' " & UpdateBID & " '

[pbaldy]

Glad you got it working.