Loop through record set to change currency field based on average diff of 2 fields

[miniwheat51]

I'm not new to VBA per se, but I've not had to do something this complicated before and my knowledge of loops is not great. I've done research for weeks and am using this only as a last resort. I was able to find some code to help me but it's riddled with errors and I can't figure them out.

Background: In an Access db, I have a process to import several files that contain a dollar amount in the form of a text field. For some reason, the agency that creates these files has decided to add a third placeholder on some of the dollar amounts after the decimal, and the final format of this field needs to be in currency. When I convert this text field to currency, the total dollar amount is off by several dollars. I have a query that dumps all the records that contain a difference between the text field and the currency field onto a table (Calc_Diff) and a form that displays only these records. I have some VBA coded to a button that is supposed to find the average difference and add/subtract that average amount from each record where a difference exists, before dumping those adjusted records into a different table (All_Grouped). As I mentioned above, it's not working for various different reasons and any help would be appreciated. I'm also open to any other ideas, if there's a better way to go about it.
Thanks in advance!

 Private Sub Fix_Click()

Dim rsDiff As DAO.Recordset, rsAll As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim TotDiff As CurrencySet rsDiff = CurrentDb.OpenRecordset( _
        "SELECT * FROM [Calc_Diff]", _
        dbOpenSnapshot)
Set qdf = CurrentDb.CreateQueryDef("", _
        "SELECT * FROM [CopyOfAll_Grouped] " & _
        "WHERE [Invoice_Num]=[CurrentInvoice] and [Account_Name]=[CurrentAccount] and [L1L5]=[CurrentL1L5] " & _
        "ORDER BY [Invoice_Num] and [Account_Name] and [L1L5] DESC")
TotDiff = Nz(DLookup("SumOfDiff", "Total_Diff"), 0)

rsDiff.MoveLast
rsDiff.MoveFirst

Do Until rsDiff.EOF
    AvgDiff = TotDiff / rsDiff.RecordCount
    qdf!CurrentInvoice = rsDiff!Invoice_Num
    Set rsAll = qdf.OpenRecordset(dbOpenDynaset)
    Do Until rsAll.EOF
        TotAmt = IIf(rsDiff!Diff > 0, AvgDiff, rsAll!Amount_Cur)
        rsAll.Edit
        rsAll!Amount_Cur = rsAll!Amount_Cur - TotAmt
        rsAll.Update
        If AvgDiff = 0 Then
            Exit Do
        End If
        rsInv.MoveNext
            TotAmt = IIf(rsDiff!Diff < 0, AvgDiff, rsAll!Amount_Cur)
            rsAll.Edit
            rsAll!Amount_Cur = rsAll!Amount_Cur + TotAmt
            rsAll.Update
            If AvgDiff = 0 Then
                Exit Do
            End If
            rsAll.MoveNext
    Loop
    rsAll.Close
    Set rsAll = Nothing
    rsDiff.MoveNext
Loop
Set qdf = Nothing
rsDiff.Close
Set rsDiff = Nothing

End Sub

 

[miniwheat51]

First, thanks so much for your reply. I have posted this in other forums for several weeks and you're the first person I've heard from. I changed rs.Inv to rs.All as it was an error (some residual name left from where I got the code.)

However, my error seems to be occurring within the query. The error message that I get when I click the button is "Run-time error '3061': Too few parameters. Expected 3." It appears to be happening at this line of code:
Set rsAll = qdf.OpenRecordset(dbOpenDynaset)

The debugging tool is showing that rsAll = nothing in this line.

 

[miniwheat51]

Not sure what you mean? It was my understanding the query was being created within that recordset.

 

[miniwheat51]

So, when I wrote out the fields I needed in each SQL statement I made some changes to what I believed to be errors. I'm now getting an error message that states "qdf!Invoice_Num does not exist" in the first loop.

 Set rsDiff = CurrentDb.OpenRecordset( _
        "SELECT Invoice_Num, Amount, Amount_Cur, Diff, Account_Name, L1L5 FROM [Calc_Diff]", _
        dbOpenSnapshot)
        
Set qdf = CurrentDb.CreateQueryDef("", _
        "SELECT Account_Name, Amount, Amount_Cur, Invoice_Num, L1L5 FROM [CopyOfAll_Grouped] " & _
        "WHERE [Invoice_Num]=rsDiff![Invoice_Num] and [Account_Name]=rsDiff![Account_Name] and [L1L5]=rsDiff![L1L5] " & _
        "ORDER BY [Invoice_Num] and [Account_Name] and [L1L5] DESC")
        
TotDiff = Nz(DLookup("SumOfDiff", "Total_Diff"), 0)
 rsDiff.MoveLast
rsDiff.MoveFirst
 Do Until rsDiff.EOF
    AvgDiff = TotDiff / rsDiff.RecordCount
    qdf!Invoice_Num = rsDiff!Invoice_Num
    Set rsAll = qdf.OpenRecordset(dbOpenDynaset)
    Do Until rsAll.EOF
        TotAmt = IIf(rsDiff!Diff > 0, AvgDiff, rsAll!Amount_Cur)
        rsAll.Edit
        rsAll!Amount_Cur = rsAll!Amount_Cur - TotAmt
        rsAll.Update
        If AvgDiff = 0 Then
            Exit Do
        End If
        rsAll.MoveNext
            TotAmt = IIf(rsDiff!Diff < 0, AvgDiff, rsAll!Amount_Cur)
            rsAll.Edit
            rsAll!Amount_Cur = rsAll!Amount_Cur + TotAmt
            rsAll.Update
            If AvgDiff = 0 Then
                Exit Do
            End If
            rsAll.MoveNext
    Loop
    rsAll.Close
    Set rsAll = Nothing
    rsDiff.MoveNext
Loop
Set qdf = Nothing
rsDiff.Close
Set rsDiff = Nothing

Sorry for the confusion, I really don't know what I'm doing with this.

 

[miniwheat51]

Calc_Diff and CopyOfAll_Grouped are tables and don't contain SQL, to my knowledge.

As for the purpose of the rsAll.MoveNext occurrence, the code is supposed to determine if the difference between [Amount] and [Amount_Cur] is less than 0. If it is, then make the TotAmt the AvgDiff (the average difference); if it's not than leave the total amount as the [Amount_Cur] (the currency version of [Amount]). It's then supposed to go on and add the TotAmt (if it is the average difference) to Amount_Cur. Since I have this condition set if the difference is both greater than or less than 0, my hope was that adding a condition to do nothing where the difference is equal to 0 would work, but I can see it's a bit rough.

I'm also not married to this way of solving the problem. If there's a better way to do it, I'm open to hear suggestions.