DAO Update Problem - Urgent

[venkatraman_r]

This is my code...There is no error reported, but the table is not updated...someone pls help me.



Sub PmtUpdateCurrencyRate(CtrlNbr As Long, RptNbr As Long)
Dim rsCurRate As Recordset
Dim sSQL As String
Dim sUpdateSQL As String
Dim uCurCRate As Double ' store currency conversion rate
Dim uITotal As Double ' store Invoice Total
Dim lVendID As Long ' store vendor id
Dim dIDate As Date ' store Invoice Date

On Error GoTo errhandler

Set db = CurrentDb

sSQL = "SELECT DISTINCT TBL_INVHDR.ORDERNBR as InvHdrOrdNbr, " & _
"TBL_INVHDR.RELEASENBR as InvHdrRelNbr, " & _
"TBL_INVHDR.INVOICENBR as InvHdrInvNbr, " & _
"TBL_INVHDR.VENDORID as InvHdrVendorId, " & _
"TBL_INVHDR.INVTOTAL as InvHdrInvTotal, " & _
"TBL_INVHDR.INVDATE as InvHdrInvDte, " & _
"TBL_INVHDR.VENDOR as InvHdrVendor, " & _
"CurConvRate.Currency_Per_Rate as CCRate " & _
"FROM TBL_INVHDR, TBL_VENDORS, CurConvRate WHERE " & _
"TBL_INVHDR.PROC_AP = 0 AND TBL_INVHDR.DISTRIBUTION = 1 AND " & _
"TBL_INVHDR.CONTROLNBR = " & CtrlNbr & " AND " & _
"TBL_INVHDR.REPORTNBR = " & RptNbr & " AND " & _
"TBL_INVHDR.VendorId = TBL_VENDORS.VendorId AND " & _
"TBL_VENDORS.Currency_Code = CurConvRate.Currency_Code_Source and " & _
"format(CurConvRate.Currency_Rate_Effective_Date,'mm/dd/yyyy') = format(TBL_INVHDR.INVDATE,'mm/dd/yyyy') ;"

Set rsCurRate = db.OpenRecordset(sSQL, DB_OPEN_DYNASET)

With rsCurRate
If Not (.BOF And .EOF) Then
.MoveFirst
Do While Not .EOF
MsgBox .Fields("InvHdrVendorId")
uCurCRate = .Fields("CCRate")
uITotal = .Fields("InvHdrInvTotal") * .Fields("CCRate")
lVendID = .Fields("InvHdrVendorId")
dIDate = .Fields("InvHdrInvDte")
'sUpdateSQL = "UPDATE TBL_INVHDR SET Cur_Conv_Rate = " & uCurCRate & ", " & _
' "Invtotal_USD = " & uITotal & " " & _
' "WHERE VENDORID = " & lVendID & " " & _
' "AND format(INVDATE,'mm/dd/yyyy') = #" & Format(dIDate, "mm/dd/yyyy") & "# ;"
ws.BeginTrans
db.Execute "UPDATE TBL_INVHDR SET Cur_Conv_Rate = " & uCurCRate & ", " & _
"Invtotal_USD = " & uITotal & " " & _
"WHERE VENDORID = " & lVendID & " " & _
"AND INVDATE = #" & dIDate & "#;", dbFailOnError
ws.CommitTrans
.MoveNext
Loop
End If
End With

Exit Sub
errhandler:
MsgBox Err.DESCRIPTION
End Sub