Hello
My Report is grouped by Work Order #.
Each Work Order # has many different products.
In the Report's footer I want to sum up the total number of each product.
So if Work Order # 1 has two hats and Work Order # 2 has three hats, in the report's footer, Total Hats should say 5. (Note: There are many work orders and about 20 products)
I don't know how to accomplish this effeciently.
Currently, I made a generic function to make these calculations but it is too slow since this function is run 20 times (for 20 products). This method is very ineffecient since I keep reproducing almost identical queries 20 times (each time I run the function).
Below is my function: Note: we identify our products with a MOE code.
There must be a better way to do this no?
****************************************************
Public Function MoeSum(MOENum As String) As Integer
Dim dbs As Database
Dim qd As DAO.QueryDef
Dim rcs As DAO.Recordset
Dim Total As Double
Set dbs = CurrentDb
Set qd = dbs.QueryDefs("DriversDaillyReceivingSheetMOESum")
qd![[Forms]![DailyDriversReceivingSheet]![Drivers]] = [Forms]! [DailyDriversReceivingSheet]![DRIVERS]
qd![[Forms]![DailyDriversReceivingSheet]![Date Scheduled]] = [Forms]![DailyDriversReceivingSheet]![DATE SCHEDULED]
qd![[Moe]] = MOENum ' THIS IS THE ONLY VARIABLE in the QUERY
Set rcs = qd.OpenRecordset
Total = 0
If rcs.EOF Then
MoeSum = 0
Exit Function
End If
rcs.MoveFirst
Do While Not rcs.EOF
Total = rcs![EXPECTED PACKAGES] + Total
rcs.MoveNext
Loop
MoeSum = Total
End Function
My Report is grouped by Work Order #.
Each Work Order # has many different products.
In the Report's footer I want to sum up the total number of each product.
So if Work Order # 1 has two hats and Work Order # 2 has three hats, in the report's footer, Total Hats should say 5. (Note: There are many work orders and about 20 products)
I don't know how to accomplish this effeciently.
Currently, I made a generic function to make these calculations but it is too slow since this function is run 20 times (for 20 products). This method is very ineffecient since I keep reproducing almost identical queries 20 times (each time I run the function).
Below is my function: Note: we identify our products with a MOE code.
There must be a better way to do this no?
****************************************************
Public Function MoeSum(MOENum As String) As Integer
Dim dbs As Database
Dim qd As DAO.QueryDef
Dim rcs As DAO.Recordset
Dim Total As Double
Set dbs = CurrentDb
Set qd = dbs.QueryDefs("DriversDaillyReceivingSheetMOESum")
qd![[Forms]![DailyDriversReceivingSheet]![Drivers]] = [Forms]! [DailyDriversReceivingSheet]![DRIVERS]
qd![[Forms]![DailyDriversReceivingSheet]![Date Scheduled]] = [Forms]![DailyDriversReceivingSheet]![DATE SCHEDULED]
qd![[Moe]] = MOENum ' THIS IS THE ONLY VARIABLE in the QUERY
Set rcs = qd.OpenRecordset
Total = 0
If rcs.EOF Then
MoeSum = 0
Exit Function
End If
rcs.MoveFirst
Do While Not rcs.EOF
Total = rcs![EXPECTED PACKAGES] + Total
rcs.MoveNext
Loop
MoeSum = Total
End Function
