DAO Update - Error - Very 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

[MartinLiss]

You need to include .Edit and .Update commands.

[venkatraman_r]

Db, that is database doesn't have a edit or update property/method. Only the recordset has.
Infact the db.excute can be given withing workspace.begintrans and workspace.committrans. I tried that also. It didn't work.

I know I am making some mistake I am not able to trace it.

[MartinLiss]

It's hard to read your code since it's not formatted (indented). Have you used the debugger to step through the code line by line?

[venkatraman_r]

I am sorry for that har formattings....I tried using the debugger....The query string and all came out perfectly..still couldnt guess whats the problem.

[MartinLiss]

I believe you when you say that the query string and all came out perfectly, but did you step through the code line by line to see what is happening?

[venkatraman_r]

Yep.....
there was no clue abt the bug..

any more suggestions?

[MartinLiss]

Try substituting this for your db.Execute statement

db.Execute "UPDATE TBL_INVHDR SET Cur_Conv_Rate = '" & uCurCRate & "', " & _
"Invtotal_USD = '" & uITotal & "' " & _
"WHERE VENDORID = '" & lVendID & "' " & _
"AND INVDATE = #'" & dIDate & "'#;", dbFailOnError

Other than that I'm out of ideas.

[venkatraman_r]

Hi Martin,

Thats ok..thanks for ur help...

Will try this anyway.
Rgds
Venkat.