Loop through record set to change currency field based on average diff of 2 fields (1 Viewer)

[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

 

[Uncle Gizmo]

Well I've been through it and fixed all of the obvious mistakes. Make sure you use option compare an option explicit at the top of your module. I renamed:- "rsInv.MoveNext" to "rsAll.MoveNext" as it appeared to be an error but I don't really have a clue you'll have to tell me why its named that.

Option Compare Database
Option Explicit

Private Sub Command0_Click()

Dim rsDiff As DAO.Recordset
Dim rsAll As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim TotDiff As Currency
Dim AvgDiff As Integer
Dim TotAmt As Integer

Set 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
                            
                            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


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.

 

[Uncle Gizmo]

It might be an idea if you show your SQL statements

 

[miniwheat51]

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

 

[Uncle Gizmo]

Where your code says select *(all) from...

Show SQL statements build up without the "all" parameter and instead show the actual field names, it might then be possible to interpret what your code is doing and possibly spot errors.

 

[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.

 

[Uncle Gizmo]

What is the SQL of Calc_Diff and CopyOfAll_Grouped?

 

[Uncle Gizmo]

Regarding this occurrence of "rsAll.MoveNext"

                            rsAll.MoveNext
                            
                            TotAmt = IIf(rsDiff!Diff < 0, AvgDiff, rsAll!Amount_Cur)

Can you explain what it's purpose is?

 

[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.

 

[Uncle Gizmo]

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.

I haven't given any thought to another solution and there might well be a better way for you to go.

Reviewing your question I note this line:-

>>> When I convert this text field to currency, the total dollar amount is off by several dollars<<<

it doesn't really ring any bells with me I'm not 100% sure what you mean and if I was asked to make a suggestion as to a method, I couldn't because I don't understand your issue.

I suggest you re-post in another thread explaining clearly your issue; you may well be offered an alternative method.

Bear in mind I think a famous man was it Einstein? once said:- "If you can't explain it to a 6 year old then you don't understand it yourself" Maybe that would be a good approach for you to take.

 

[Uncle Gizmo]

As for the purpose of the rsAll.MoveNext occurrence, the code is supposed to determine...........

The issue is you've got two of them in there and I can't see why? I wondered if you knew why? In other words what's the reasoning behind having it twice? To my mind there should only be one.

 

[Uncle Gizmo]

It looks like you're using DLookup to calculate a value ("TotDiff") based on a different table ("Total_Diff") than the ones shown in your Code. Can you explain how the 3 tables are related? Why aren't you extracting this value from one of the tables that are used in the SQL statements? "Calc_Diff" and "CopyOfAll_Grouped"