Running sum over unbound textboxes

[apestaart]

In the detailsection of a report I calculate the values of anunbound textbox. (On Detail_format)
The unbound textbox is named : Tegoed.
I want to set the runningsum for this textbox via the properties of Tegoed. I choosed running sum over group. That does not work.
I tried to do it in an other way by defining in the "On open report Name" the code : reports!rptTegoed!Tegoed.runningSum = 1. Running this code gives me an error 2465 "Can't find the field 'Tegoed'"
What can I do to solve this problem?
Thanks in advance Apestaart

 

[rjoubert]

I'm not entirely clear on what you're attempting to do. Do you want this textbox to simply display 1 for the 1st record, 2 for the 2nd record, 3 for the 3rd record, etc.? If so, leave the Running Sum property set as you have it, and set the Control Source to =1.

 

[apestaart]

No I want the the running sum of the calculated values (VB Code) of the unbound textbox named "tegoed"
Apestaart

 

[rjoubert]

Use the same control source that you have for the tegoed field, and set the running sum property to "Over All." If you are using code to calculate the tegoed field value, you can always put that code into a public function, then call the function using the control source property of the tegoed field.

 

[apestaart]

Dear rjoubert,
Lets concentrate on the calculated field Tegoed by using code.
As you sugested I have build a public function Ftegoed in my main module. I have to call that function with 4 parameters.
In the sourcecontrol of the Tegoed field I added :
Ftegoed(par1,par2,par3,par4). These parameters belong to the same record as Tegoed.

Starting the report it is asking for: " Parametervalue 'Ftegoed(par1,par2,par3,par4)'".
What is going wrong?

Apestaart

 

[rjoubert]

In the control source property, you need the = (equal sign) before your function call.

 

[apestaart]

I defined the control source property as :

=NVakantiedagen ([Urencontract],[Snipper],[Vakantie],[Vakextra],[Geboortedatum])

I need 5 parameters. When I try to store the content I receive an error ; "The expression you entered contains invalid syntax".

Maybe I have put my function in a wrong place, where should it go?? In Module??(This I tried) or in the on format module?? (This I tried)
I never used Public functions before.
What is wrong?

 

[rjoubert]

Can you post the code for the function you created?

 

[apestaart]

Here is my code. It is part of reports

Public Function Nvakantiedagen(intUrencontract As Integer, intVakantie As Integer, IntSnipper As Integer, intVakextra As Integer, Geboortedatum As Date) As Integer 
Dim Leeftijd As Long
'Insert Calculate the ages of persons (still to do)
If intUrencontract > 0 Then
Nvakantiedagen = ((intUrencontract / 164.67) * 25 * 7.6) / 12 - Nz(intVakantie, 0) - Nz(IntSnipper, 0) + Nz(intVakextra, 0)

Else
Nvakantiedagen = 0
End If
End Function

 

[rjoubert]

Have you verified that the fields you are passing in match up to what the function is expecting? In other words, are your [Urencontract],[Snipper],[Vakantie],[Vakextra] fields all integers, and is your [Geboortedatum] field a date? You can change the control source to the following to be sure...

=NVakantiedagen (CInt([Urencontract]),CInt([Snipper]),CInt([Vakantie]),CInt([Vakextra]),CDate([Geboortedatum]))

Note: I don't see where you're using the date parameter in your function. If you don't need, why pass it in?

 

[apestaart]

Hallo rjoubert,
I checked all my dimensions of the parameters. Because there where some in Single and some in Integer I changed everything to Single.
I still can't store the function in the recordsource of the field Tegoed
Iam a little bit lost.
Regards Apestaart

 

[apestaart]

I have forgotten to say that I use the Geboortedatum to calculate the age of a person.
Depending of that age I gave a person extra vacation days. That I should add also to the formule in the function.
The dimension of Geboortedatum is date
I hope we can fix my problem.
Regards
.

 

[rjoubert]

Since you changed those integers to singles, try this...

= NVakantiedagen (CSng([Urencontract]),CSng([Snipper]),CSng([Vakantie]),CSng([Vakextra]),CDate([Geboortedatum]))

 

[rjoubert]

I just added your function to a code module in my database, and I was able to enter the control source of the textbox without error.

 

[rjoubert]

The only way I was able to get the same error message was if I was missing a parenthesis. Try copying the control source that I posted earlier and pasting it into the control source property of your textbox.

 

[apestaart]

rjoubert,
I will try your code.
Last question. Where have you put the function definition?
What do you mean exactly by a "code module"?
Apestaart

 

[k1dude]

About Code Modules

A module is essentially a collection of declarations, statements, and procedures stored together as one named unit to organize your Microsoft Visual Basic (Microsoft Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.) code. Microsoft Access has two types of modules: standard modules (standard module: A module in which you can place Sub and Function procedures that you want to be available to other procedures throughout your database.) and class modules (class module: A module that can contain the definition for a new object. Each instance of a class creates a new object. Procedures defined in the module become properties and methods of the object. Class modules can exist alone or with forms and reports.).

What is a module?

A module is a collection of Visual Basic declarations and procedures that are stored together as a unit.


Class modules

Form (form module: A module that includes code for all event procedures triggered by events occurring on a specific form or its controls.) and report modules (report module: A module that includes code for all event procedures triggered by events occurring on a specific report or its controls.) are class modules that are associated with a particular form or report. Form and report modules often contain event procedures (event procedure: A procedure that is automatically executed in response to an event initiated by the user or program code, or that is triggered by the system.) that run in response to an event on the form or report. You can use event procedures to control the behavior of your forms and reports, and their response to user actions, such as clicking a command button.

When you create the first event procedure for a form or report, Microsoft Access automatically creates an associated form or report module.

Standard modules

Standard modules contain general procedures that aren't associated with any other object, and frequently used procedures that can be run from anywhere within your database. The major difference between a standard module and a class module that is not associated with a particular object is one of scope and lifetime. The value of any variables or constants that are declared or exist in a class module without an associated object are available for use only while that code is running and only from that object.

 

[rjoubert]

Short answer - on the modules tab in Access, create a new module, paste the function code.

 

[apestaart]

I created a new module "Module 2"
I pasted the function code.
I copied Your code for the control in the detailsection and i receive the same error message "The expression you entered contains invalid syntax".
And I can't store the function.

I tried to use the code builder wizard , looking for functions but the defined function NVakantiedagen was not available.

What can I do?
I am a little bit desparate.

apestaart

 

[apestaart]

Dear rjoubert,
I have done a repair and compress of my application.
Turned the system of and started all over again.
Now the system finds the function NVakantieuren (If had to change the name, because it are infact "ours" in Dutch "uren" instead of "days" in dutch "dagen").
But when I past the function to the controlsource of the field in the detailsection I saw the following.

 = Nvakantieuren («sngUrencontract»; «sngSnipper»; «sngVakantie»; «sngTotaaluren»; «Geboortedatum»)

When I run this the program is asking for all individual parameters separately.
This maybe trickers you to find out what is wrong.
I can send you a screendump picture with my code and parts of the report if you want.
Regards apestaart.
Go for a sleep (12.15 at nigth)