I need help with a running total in a form. Access 2010?

[Corwin Moyne]

I have a table called "Dies". A die is used in a production process, and after its used I want to update it's "Revolutions". Amongst others, I have a field called Total Revolutions and Repeat in this table. I have created a table query which allows me to calculate the job revolutions. On the query, I have a: Lifetime Start, Lifetime End and job revolutions(None of these are in the table). The job revolutions are calculated by subtracting the lifetime start from the end and dividing it by the Repeat. So it looks like this: [Lifetime End] - [Lifetime Start] / [Repeat]. This works perfectly, but how do I get this total to sum in the Total Revolutions?

So if my Job revolutions calculates to 2000 on the first run, then 3000 the next time, my Total Revolutions will sum to 5000, and so forth. My primary key is called MOD Number (a unique serial number). I think I need to use DSUM, but I'm not sure how.

When I perform a search for a particular MOD Number, it will show me the total revolutions.

[Narender Sagar]

MS Access 'reports' are having built in feature of running sum. That should serve your purpose.

[Mihail]

Hi !
First of all, is the formula you use to calculate revolution the real one ?
I think it must be:
( [Lifetime End] - [Lifetime Start] ) / [Repeat]
But this is only my opinion.

Let me understand how your query work ?!
When you run the query you are prompted to input Lifetime Start and Lifetime End in order to calculate the Job Revolutions ?
How Access know for what Die perform this computation ?!?!?

As far as I can see (but I am not sure I understand well your request as well as your background) you need to define two more fields in your Dies table. One to store the Lifetime Start and another one to store the Lifetime End.
This approach will help you to calculate anything you wish anytime.

I assume that you store, in Dies table, every die you use in machining process.
If, in the Die table you store the type of dies you need another approach.


Clarify, please, your problem.

[Corwin Moyne]

Hi Mihail, thanks for the reply. Firstly, the only field I need to update is Total Revolutions which is in my table. I have set up a search query so that the user can input the serial number of the die and when they hit search it brings up the info for that die which includes the Total Revolutions. The fields, Lifetime Start, Lifetime End and Job Revolutions come up on the form too, but only to enable me to calculate the revolulutions for that job which will (finger crossed) update the Total Revolutions in the die table, but the fields themselves don't need to be stored. So as an example, the user opens the search form and types in the serial number and hits search. A form opens displaying the die info. Lets say it shows Total Revolutions 10,000. At this point Lifetime Start, Lifetime End and Job Revolutions are at zero as they are not stored. I stick in the Lifetime start as 25,000(i get this number off the machine the die was in) and Lifetime end as 50,000. When I hit return or tab, the Job Revolutions displays 25,000, then adds this 25,000 to the Total Revolutions which should now show 35,000. I hit save and exit. Next time I search for that same die it will show the Total Revolutions as 35,000 and so forth.

[Mihail]

I think that this is a possible solution for you (see attachment).
Feel free to ask more.

[Corwin Moyne]

Mahail, I've had a look at your file. How are you performing the calculation? I don't see any calculations in the property sheet?

[Mihail]

The calculation is performed in VBA code.
How to see the VBA ? That depend of your Access version.
I use 2007. If you use the same version I'll explain how to see the VBA code. But if you use another version hope someone else will help you with that.
Just tell us what Access version you use.

[Corwin Moyne]

I'm using 2010. I had a look at the VBA code. I'm a Java programming student but unfortunately I know nothing about VBA.

[Mihail]

If you can see the VBA code is Ok.
As far as I know Java is also a visual language based on events. So must be easy for you to understand my simple code.

The code is:

Option Compare Database
Option Explicit
 
Private Sub cmdSaveRecord_Click()
'    If Not IsNumeric(txtRevToAdd) Then
'        MsgBox ("Error in ""Revolutions to add""")
'        txtRevToAdd.SetFocus
'GoTo Ex
'    End If
 
    Number_of_Revolutions = Number_of_Revolutions + Val(txtRevToAdd)
    DoCmd.RunCommand acCmdSaveRecord
    txtRevToAdd = ""
 
Ex:
    txtRevToAdd.SetFocus
End Sub
 
Private Sub Form_Current()
    txtRevToAdd = ""
End Sub

Line #1: I don't know what is it :) (I am also new to databases). This line appear as default when I open a new module (code window).
Line #2 Say to the compiler to not accept to use a variable if that variable is not yet declared (usual using a DIM statement). That prevent the usage of misspelled names for variables. It is a very very useful instruction.

Lines #4 to #17 Here is the procedure for cmdSaveRecord "Click" event.
cmdSaveRecord is the internal name for "Save" button. What you see (Save) is the Caption (a visual propertie) for cmdSaveRecord button.

Lines #5 to #9 You can remove this lines because I forget to do that before ZIP the file. Anyway for the compiler this lines are simple comments. This lines are not compiled (and, of course, not running).

The next two lines do your job:
Line #11 Here appear the names for the text boxes you see in the form. First, Number_of_Revolutions is bound to field also named Number_of_Revolutions (total number of revolutions). The second one, txtRevToAdd, is an unbound text box. Here the user input the new number of revolutions.
Because Number_of_Revolutions is bound to a numeric field its value will be numeric. Unlike that one, the txtRevToAdd, because is unbound, will return the default type of data for text boxes: String. It is why I use the VAL() function: to transform the string value into a numeric value.
So, in this line I add the value from txtRevToAdd to the Number_of_Revolutions and I store the result in Number_of_Revolutions text box. This happen ONLY in the form. At table level nothing is changed yet.

Line #12 Save the current record. After this line is executed the changed from your form will be reflected in the table.
Note please that the DoCmd is a very useful Access object. In fact is a library with a lot of functions and routines.

Line #13 Clean the txtRevToAdd text box.
Line #15 Forget that for now. You can remove it.
Line #16 Keep the focus in txtRevToAdd text box. (Keep this text box as active control)

Lines #19 to #21 This routine will be executed every time when you change the current record (when you navigate from one record to other one.

Hope this was a help for you. And the first step in VBA.

Good luck !

[Corwin Moyne]

It works!!! Thankyou so much. One more question. Is there anyway to update the Number_of_Revolutions as soon as the txtRevToAdd box changes? I've been messing about with the on change method but I can't figure it out.

[Mihail]

I think you fall into a logical error.
Say the new number of revolutions is 435.
Of course you type, first, "4". How can you tell to Access that must add 400 not 4 to the existing number of revolutions ? You can't. So Access will add 4.
Then you type "3". Now you must say to Access: "Sorry but now you must subtract the preview 4 then add 43".
And again when you type "5".
And so on.

Of course, Access can't decide when you finish to enter the whole number. So you must click anyway the "Save" button.

Something you can do:
Add a new text box.
Use the OnChange event (not method) in order to calculate the result and put it in your new text box.
Something like this:

Private Sub txtRevToAdd_Change
    NewTextBox = Number_of_Revolutions + Val(txtRevToAdd)
End sub

Note please that I don't try this code so maybe it will not work because something misspelled. But as idea is Ok.

Another approach can be to store the initial Number_of_Revolutions into a variable:

Dim ActualNumbRev as Long 'At module level
 
Private Sub Form_Current()
    ActualNumbRev = Number_of_Revolutions
End Sub
 
Private Sub txtRevToAdd_Change
    Number_of_Revolutions = ActualNumbRev + Val(txtRevToAdd)
End sub

But... believe me !
Try to keep things as simple as possible.
At least at the beginning.
After your program do very well the job, with no unhandled errors, yo can add some "flowers".

[Corwin Moyne]

Ok I think I'll leave it as it is. Thanks Mihail.

[NeoPa]

Corwin:
One more question. Is there anyway to update the Number_of_Revolutions as soon as the txtRevToAdd box changes? I've been messing about with the on change method but I can't figure it out.

The Change event triggers when anything within the control changes. As Mihail points out, that would not suit your purpose very well.

However, another event, AfterUpdate, may well do so.

Consider though, you will probably need to subtract the previous value as well as to add the new one. It's easy to imagine a situation where a value of 211 is entered instead of 21, for instance. No record is saved, nevertheless you wouldn't want a cumulative update of 232 to be applied instead of 21.