- Joined
- Aug 24, 2015
- Messages
- 1
- Reaction score
- 0
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!
Code:
Private Sub Fix_Click()
Dim rsDiff As DAO.Recordset, rsAll As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim TotDiff As Currency
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
DoUntil rsDiff.EOF
AvgDiff = TotDiff / rsDiff.RecordCount
qdf!CurrentInvoice = rsDiff!Invoice_Num
Set rsAll = qdf.OpenRecordset(dbOpenDynaset)
DoUntil 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.Close
Set rsAll =Nothing
rsDiff.MoveNext
Loop
Set qdf =Nothing
rsDiff.Close
Set rsDiff =Nothing
End Sub
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!
Code:
Private Sub Fix_Click()
Dim rsDiff As DAO.Recordset, rsAll As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim TotDiff As Currency
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
DoUntil rsDiff.EOF
AvgDiff = TotDiff / rsDiff.RecordCount
qdf!CurrentInvoice = rsDiff!Invoice_Num
Set rsAll = qdf.OpenRecordset(dbOpenDynaset)
DoUntil 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
LooprsAll!Amount_Cur = rsAll!Amount_Cur + TotAmt
rsAll.Update
If AvgDiff =0 Then
Exit Do
End If
rsAll.MoveNext
rsAll.Close
Set rsAll =Nothing
rsDiff.MoveNext
Loop
Set qdf =Nothing
rsDiff.Close
Set rsDiff =Nothing
End Sub