Retain value of variables after module closes 1

[dbanker]

I am making a report that displays employee's hours and want a field in the details that also displays each employee's hours to date. I think I solved it by writing code in a module. This module is called from the On Print event of the details section of the report. Here is some of the code in the module:
Option Compare Database
Public Gbl_Run_Sum As Double
Public Gbl_RunSum1, Gbl_RunSum2, Gbl_RunSum3, Gbl_RunSum4 As Double


Public Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Me.EmployeeID = 1 Then
Gbl_Run_Sum1 = Gbl_Run_Sum1 + Me.HoursWorked
Gbl_Run_Sum = Gbl_Run_Sum1
End If
If Me.EmployeeID = 2 Then
Gbl_Run_Sum2 = Gbl_Run_Sum2 + Me.HoursWorked
Gbl_Run_Sum = Gbl_Run_Sum2
End If
If Me.EmployeeID = 3 Then
Gbl_Run_Sum3 = Gbl_Run_Sum3 + Me.HoursWorked
Gbl_Run_Sum = Gbl_Run_Sum3
End If
If Me.EmployeeID = 4 Then
Gbl_Run_Sum4 = Gbl_Run_Sum4 + Me.HoursWorked
Gbl_Run_Sum = Gbl_Run_Sum4
End If
Me.HoursTotal = Gbl_Run_Sum

End Sub

The problem I have is as it is run the values saved in the variables aren't retained as the program continues. I'm sure it's because the sub ends after each detail and the values are reset. Is there some way to retain the values in the variables after the sub ends until I'm ready for it to reset?

 

[randy700]

Why not store the values in a table?


Randy

 

[dhookom]

First, do you realize only 2 of your variables are dim'd as Double and the remainder as [red]variants[/red]?

Public Gbl_Run_Sum As Double
Public [red]Gbl_RunSum1, Gbl_RunSum2, Gbl_RunSum3[/red], Gbl_RunSum4 As Double

Why are you doing any of this with code? You can set the Running Sum property of a text box.

Duane
Hook'D on Access
MS Access MVP

 

[dbanker]

Putting the data in a table might work, not sure if it would be more work than just setting the variables to hold values after the sub ends.

Tried using the running sum properties but the report is grouped on the DayWorked to show who worked each day and I need the hours they worked to date for the specified time frame to show up beside the hours for each day they work. There is no way to set the running sum to cumulate each employee's hrs in the details section with the records grouped on the DayWorked.

I thought putting "As Double" at the end of the line of variables designated all of the ones in the line to be Dimmed the same. If it doesn't I will fix it.

The easiest way for me would be to get the variables to carry the values, but if someone can make it happen an easier way please show me.

 

[dhookom]

If you want a running sum over groups, I would probably calculate this in the report's record source query.

Unless VBA has changed, you should use:

Public Gbl_Run_Sum As Double
Public Gbl_RunSum1 As Double, Gbl_RunSum2 As Double
Public Gbl_RunSum3 As Double, Gbl_RunSum4 As Double

Duane
Hook'D on Access
MS Access MVP

 

[SeeThru]

I think the variables may need to be database level (declared as public in a main database module) or be saved into a temporary table, as suggested.

Be aware that with a temporary table, if the DB is shared other users could overwrite them while you are still using them.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[dbanker]

DHookem

Could you give me an example of how to calulate the results in the record source query?

 

[fneily]

faq701-5268

 

[dhookom]

dbanker,
If fneily's suggestion doesn't work, come back with some specifications of what you are attempting to accomplish and with what. We only see some code with no context.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

how to calulate the results in the record source query
Lots of "running sum" queries here:
forum701

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PaulBricker]

The Static declaration was designed to retain the value of the variable after the procedure ends but you probably have to use it inside the Sub and not globally (although I'm not 100% sure of that, you might be able to declare it in the called procedure). I have used Static in Access reports before, but unless you are careful you can end up with unwanted (incorrect) results. Some Access report events fire more than once per report formatting and can cause values to bloat. I would suggest what the people above have suggested. Use a query, table or you might look at using grouping and the running sum property for the fields.

Paul

 

[2Grandpa]

You can use the runing sum by employee by adding a grouping by employee. Put the employee name and sum fields in the title section and hide the detail section.

 

[dbanker]

I had previously researched fneily's suggestion earlier when dhookem suggested using a field in the source query to calculate the running sum. I thought it would be perfect for what I was trying to do but when I tried running the code I got a "Run time error 3061 Too few parameters. Expected 2" on the following line of code: Set rst = db.OpenRecordset("qryTest", dbOpenDynaset)
If that problem can be explained/solved it would probably fix my dilemma.

 

[dbanker]

I just took a much closer look at the code from the example fneily suggested and it looks like it will only total for one idName and value. If I'm wrong let me know. I have at the moment 12 different idNames I have to total hrs for and that will vary according to the number of employees we have. We need to enter a period start date and end date. The report and source query then needs to pull up each day and list each person that worked and their hrs worked, and total hrs to date.
My report should look like this:
Name Hours Total Hours This Period
Monday Oct 27
Debbie Brown 8 8
Darnell Banks 8.5 8.5
Tim Johnson 7 7
Tuesday Oct 28
Debbie Brown 8 16
Wednesday
Debbie Brown 8 24
Darnell Banks 8 16.5
etc
The records are grouped on the day worked so trying to use a running sum field in the group header or footer of the report doesn't produce the needed data, even if you group on last name too.

 

[dhookom]

It would help tremendously if you provided your actual table and field names.

I would use a simple query with a subquery like:

SELECT TableA.*, 
(SELECT Sum(HoursWorked) 
 FROM TableA AliasA
 WHERE AliasA.EmployeeID = TableA.EmployeeID
   AND AliasA.WorkDate <=TableA.WorkDate)
FROM TableA;

Duane
Hook'D on Access
MS Access MVP

 

[dbanker]

Mr Hookom

That did the trick thank you sooooo much. Iv'e been pulling my hair out for the last few days trying to get over this hump. Thanks again and thanks to everyone that gave suggestions.