Moving through a Recordset
On a monthly basis I want to create an "Actual vs Budget" report.
I have created a table called tblBudgetActual that contains budgetary revenue amounts. The primary key is composed of 3 fields [Territory], [Year], and [Month]. This file also contains a field for Actual revenue which will be updated by the routine below, just before the Budget vs Actual Report is created.
A query is created (qryActual) that gathers the Territory, OrderDate, and Revenue amount for each order.
This routine starts by entering the Year into a Form. The 1st thing is that all Actual Revenue values from tblBudgetActual are reset to 0 for each record (row) in the specified year.
Now for the problem. I am attempting to step through qryActual from the beginning (it is ordered By Territory and By OrderDate - ascending) and add the specific revenue to the appropriate record in tblActualBudget. For example, there is a record in qryActual for Territory = "B001", OrderDate = 2/23/04, Revenue = $12,670; this needs to get added to tblBudgetActual where Territory = "B001", Year = "2004", and Month = "02".
How do I migrate through qryActual? I have been trying to use the following routine unsuccessfully (I have left out the number to String conversions for simplicity)
Dim db As DAO.Database, rcdBA As DAO.Recordset, rcdA As DAO.Recordset
Set db = CurrentDb()
Set rcdBA = db.OpenRecordset("tblBudgetActual")
Set rcdA = db.OpenRecordset("qryActual")
' --> Reset rcdBA Actual Revenue values to 0 goes here (no problem)
'Move current Order revenue values into rcdBA; the variable strMo is extracted from rcdA!OrderDate into "01", "02", etc.
Do Until rcdA.EOF
rcdBA.FindFirst(rcdBA!Territory=rcdA!Territory And rcdBA!Month=strMo)
Do While rcdBA!Territory=rcdA!Territory And rcdBA!Month=strMo
rcdBA.Edit
rcdBA!Revenue = rcdBA!Revenue + rcdA!Revenue
rcdBA.Update
rcdA.MoveNext
Loop
Loop
From what I can tell the main problem seems to be with the "Find" statement. How do I jump in to the correct record in rcdBA? Thanks for any help.
|
