Solved: Syntax error

[slamet Harto]

Hi there,

Can you help to fix this line, please?
As I have an errorr message "Syntax error in Update Statement"

Thanks for assistance,

[vba]msq = "Update TbVouch set CompNm='" & frmVoucherBB.txtCoyNm.Text & "',Route='" & frmVoucherBB.txtRoute.Text & "',Amount='" & frmVoucherBB.TxtAmt.Text & "'," & _
"Qty='" & frmVoucherBB.TxtBoxQty.Text & "',VoucherNo='" & frmVoucherBB.TxtBoxVno.Text & "',In-Out='" & frmVoucherBB.CBoInOut.Text & "'," & _
"Status='" & frmVoucherBB.CboInStat.Text & "'," & _
"VocStat='" & frmVoucherBB.CboStat.Text & "',Comment='" & frmVoucherBB.txtComments.Text & "' where Ltrim(TbVouch.VoucherRegno)='" & frmVoucherBB.lblVoucher.Text & "'"

Cn.Execute (msq)
MsgBox "Update Completed...!!!", vbInformation, "Thank you"[/vba]

[Bob Phillips]

[vba]

msq = "UPDATE TbVouch SET " & _
"CompNm='" & frmVoucherBB.txtCoyNm.Text & "', " & _
"Route='" & frmVoucherBB.txtRoute.Text & "', " & _
"Amount='" & frmVoucherBB.TxtAmt.Text & "', " & _
"Qty='" & frmVoucherBB.TxtBoxQty.Tex & "', " & _
"VoucherNo='" & frmVoucherBB.TxtBoxVno.Text & "', " & _
"In-Out='" & frmVoucherBB.CBoInOut.Text & "', " & _
"Status='" & frmVoucherBB.CboInStat.Text & "', " & _
"VocStat='" & frmVoucherBB.CboStat.Text & "', " & _
"Comment='" & frmVoucherBB.txtComments.Text & "' " & _
"WHERE Ltrim(TbVouch.VoucherRegno)='" & frmVoucherBB.lblVoucher.Text & "'"

cn.Execute (msq)
MsgBox "Update Completed...!!!", vbInformation, "Thank you"
[/vba]

[slamet Harto]

Hi Bob,

Glad to receiving your reply, however it doesn't work.

Best, Harto

[Bob Phillips]

Can you post a workbook then so we can see the actual details. Is it an Access DB, if so can you post that as well? What error are you getting now?

[Bob Phillips]

I just noticed a couple of errors

[vba]

msq = "UPDATE TbVouch SET " & _
"CompNm='" & frmVoucherBB.txtCoyNm.Text & "', " & _
"Route='" & frmVoucherBB.txtRoute.Text & "', " & _
"Amount='" & frmVoucherBB.txtAMt.Text & "', " & _
"Qty='" & frmVoucherBB.txtBoxQty.Text & "', " & _
"VoucherNo='" & frmVoucherBB.TxtBoxVno.Text & "', " & _
"In-Out='" & frmVoucherBB.CBOInOut.Text & "', " & _
"Status='" & frmVoucherBB.CBoINStat.Text & "', " & _
"VocStat='" & frmVoucherBB.CBOStat.Text & "', " & _
"Comment='" & frmVoucherBB.txtComments.Text & "' " & _
"WHERE Ltrim(TbVouch.VoucherRegno)='" & frmVoucherBB.lblVoucher.Caption & "'"
[/vba]

and there might be a problem with quantity and Amt if they are numeric fields in the database as you are enclosing them with quotes.

[slamet Harto]

Thanks bob
highly appreciated it
Regards, harto