Update table String (vba)

[JKDeveloper0718]

I have a form that Im processing batches of transactions and I have a button that posts the transaction. With that there is a subroutine that updates a table as follows:

Private Sub PostSecurityDeposit(Bnum As Integer)

Dim rsSD As Recordset
Set rsSD = CurrentDb.OpenRecordset("SELECT * FROM tblRCSecurityDepositData WHERE BatchNumber = " & Bnum)
While Not rsSD.EOF
If rsSD!TransCodeId = 5 Then
CurrentDb.Execute ("Update tblHousehold Set UASecurityDeposit = rsSD!NewSecDep where HouseholdId = " & rsSD!HouseholdId)
End If
rsSD.MoveNext
Wend
rsSD.Close
Set rsSD = Nothing
End Sub

So the CurrentDb.Execute ("Update tblHousehold Set UASecurityDeposit = rsSD!NewSecDep where HouseholdId = " & rsSD!HouseholdId) line is the update and it doesnt like this string is there something Im missing within this string that causing a problem. I print out the fields from the loop and they are fine.

 

[PHV]

CurrentDb.Execute ("Update tblHousehold Set UASecurityDeposit=[!]'" &[/!] rsSD!NewSecDep [!]& "'[/!] where HouseholdId=" & rsSD!HouseholdId)

If UASecurityDeposit is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Crowley16]

no, you can't do that, when you pass that string to execute, it will look for a UASecurityDeposit which has the value "rsSD!NewSecDep" not the value contained in rsSD!NewSecDep.

you need to do something like:

"update tblName set fldName = '" & rs!fldName & "' where..."

--------------------
Procrastinate Now!