Using Loop to cycle through Records & updating fields: calling procdures to update calucations

[QA_Compliance_Advisor]

I have been trying to update calculated fields and apply this to all records rather than going through each record and manually "hitting" Calculate button for that specific record.

so i ahve used varies methods to cycle through records the current is as follows:

 Public Sub LoopRecExample()
On Error GoTo Error_Handler
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Income/Expense") 'open the recordset for use (table, Query, SQL Statement)
    
    With rs
        If .RecordCount <> 0 Then 'Ensure that there are actually records to work with
            'The next 2 line will determine the number of returned records
            rs.MoveLast 'This is required otherwise you may not get the right count
            rs.MoveFirst

            
            Do While Not .EOF
                'Do something with the recordset/Your Code Goes Here
                Debug.Print rs.Fields("amount")
                Call TaxCalculation
                .MoveNext
            Loop
        End If
    End With
 
    rs.Close 'Close the recordset

Error_Handler_Exit:
    On Error Resume Next
    'Cleanup after ourselves
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: LoopRecExample" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit

the debug.print works it does display the correct figures, but the call function/subroutine didn't work. I am clearly missing something with the loop communicating with the subroutine (TaxCalculation).

Can anyone either help or focus me to some reading material would be greatly appreciated.

[ranman256]

This code has already been written. Its called an update query.
zero vb code needed.

[QA_Compliance_Advisor]

I know the loop code works, however, when trying to call a subroutine the loop doesn't carry out the subroutine calculations. However, thinking about now that have written my issue, It wouldn't surprise me that the calculations are based on a form layout and not a recordset layout.

[knarfreppep]

Public Sub gsubInsertTax()
dim rsRecordset as Recordset
Set rsRecordset = CodeDb.OpenRecordset("SELECT lngRecordID, sngAmount FROM tblIncome", dbOpenSnapshot)
DoCmd.SetWarnings False
While NOT rsRecordset.EOF
DoCmd.RunSQL "UPDATE tblIncome SET sngTax = gfct_sngTax(" & rsRecordset!sngAmount & ") WHERE lngRecordID = " & rsRecordset!lngRecordID
rsRecordset.MoveNext
Wend
DoCmd.SetWarnings False
Set rsRecordset = Nothing
End Sub

Public Function gfct_sngTax (ByVal sngAmount as Single) as Single
gfct_sngTax = sngAmount * .35
End Function


To do what I think you might be trying to do, you would need to pass your recordset to your TaxCalculation sub/function as a ByRef argument. But that's all too hard in my view.

[knarfreppep]

And, as alluded to by another of your interlocutors, a more efficient solution is -

Public Sub gsubInsertTax()
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblIncome SET sngTax = sngAmount * .35"
DoCmd.SetWarnings True
End Sub

[QA_Compliance_Advisor]

Thanks Knarfreppep, you have given me a few things to think about.