Update multiple columns using docmd.sql "Update" command

[jgitaunjoroge]

Dear Friends,
Am trying to update multiple columns in a customer table but the statement am using is returning an error. Kindly check it for me.

DoCmd.RunSQL ("update customer set customer.paid=" & Me.Parent.txtPAID.Value,customer.balance = me.Parent.txtcontract.value+me.Parent.txtpaid.value)

Thanks,
Njoroge Joseph:banghead::banghead:

 

[Galaxiom]

What is the error message?

Write the SQL expression to a variable and see what it looks like.
That usually makes the problem obvious.

BTW It is generally more reliable to record the transactions and sum them to get the balance when required.

 

[jgitaunjoroge]

Hi Galaxiom,
The below statement is giving error. i don't know whether the syntax is correct or not.
DoCmd.RunSQL ("update customer set customer.paid=" & Me.Parent.txtPAID.Value,customer.balance = me.Parent.txtcontract.value+me.Parent.txtpaid.value)

The table name is customer
field names are : customer.paid, customer.balance
Please note that if i update just one column it works fine like below
DoCmd.RunSQL ("update customer set customer.paid =" & Me.Parent.txtPAID.Value)

Thanks,

 

[jdraw]

Further to Galaxiom's suggestion:

Dim a variable as String, then assign your SQL to that variable,
then print (Debug.Print tahat variable) to see what Access understands re your Update SQL. Correct any identified errors before applying/attempting the update directly.

How are you restricting the update to a specifc record/set of records?
See DebuggingTips for more info.

 

[Minty]

You need to do what has been suggest above but you are missing some quotes and appear to have a typo;

("update customer set customer.paid=" & Me.Parent.txtPAID.Value  [COLOR="Red"]&"[/COLOR],customer.balance = [COLOR="red"]"& ([/COLOR] me.Parent.txtcontract.value+me.Parent.txtpaid.[COLOR="red"]valu e )[/COLOR])

This assumes that your values are numbers not text.

It would be better and much easier for you to see what was happening if you did this (Air Code - untested);

Dim sSql As String
Dim nValue As Single
Dim nPaid As Single
Dim nBal as Single

nValue = me.Parent.txtcontract.Value
nPaid = me.Parent.txtpaid.Value  
nBal = nValue + nPaid

sSql = "Update customer set paid = " & nPaid & ", "
sSql = sSql & "balance = " & nBal & " ;"

Debug.Print sSql

DoCmd.RunSQL sSql

 

[jgitaunjoroge]

Friends,
Attached is the database.
Please check it for me.
Name of the form is customer. when i select Receipt and put some amount, it is supposed to update the balance, paid columns in main form and refresh. This is not happening.

 

[Solo712]

Hi Galaxiom,
The below statement is giving error. i don't know whether the syntax is correct or not.
DoCmd.RunSQL ("update customer set customer.paid=" & Me.Parent.txtPAID.Value,customer.balance = me.Parent.txtcontract.value+me.Parent.txtpaid.value)

The table name is customer
field names are : customer.paid, customer.balance
Please note that if i update just one column it works fine like below
DoCmd.RunSQL ("update customer set customer.paid =" & Me.Parent.txtPAID.Value)

Thanks,

You need to watch your quotes:

("update customer set customer.paid=" & Me.Parent.txtPAID.Value & ",customer.balance =" & me.Parent.txtcontract.value+me.Parent.txtpaid.value

Best,
Jiri

 

[Minty]

This is not happening.

Can you post up your code and assuming you have broken down the SQL statement show us where it isn't working ?