Standard Update Module
This module reads through each record in a dataset and updates a single field.
Program Code
Option Compare Database
Option Explicit
' ***********************************************************************
' Update Percentages for Distribution
' ***********************************************************************
Public Function UpdateAccountDivisionPercentages()
Dim db As DAO.Database
Dim recIn As DAO.Recordset
Dim dblPercentDistribution As Double
' ***********************************************************************
' Open the Files
' ***********************************************************************
Set db = CurrentDb()
Set recIn = db.OpenRecordset("qryListAccountDivisionDistribution")
' ***********************************************************************
' Loop Through Each Record And Calculate The Percentage Distribution
' ***********************************************************************
Do
' ***********************************************************************
' Update the Percentage
' ***********************************************************************
If recIn![TotalSell] = 0 Then
dblPercentDistribution = 0
Else
dblPercentDistribution = Round((recIn!DivisionalSell / recIn!TotalSell), 6)
End If
Update_Record:
recIn.Edit
recIn![SellPercent] = dblPercentDistribution
recIn.Update
Skip_Record:
recIn.MoveNext
Loop Until recIn.EOF
recIn.Close
Set recIn = Nothing
Set db = Nothing
End Function