By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

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

P: 37
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.
Feb 18 '12 #1
Share this Question
Share on Google+
12 Replies

Narender Sagar
P: 189
MS Access 'reports' are having built in feature of running sum. That should serve your purpose.
Feb 18 '12 #2

P: 759
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.
Feb 19 '12 #3

P: 37
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.
Feb 19 '12 #4

P: 759
I think that this is a possible solution for you (see attachment).
Feel free to ask more.
Attached Files
File Type: zip (29.4 KB, 319 views)
Feb 20 '12 #5

P: 37
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?
Feb 20 '12 #6

P: 759
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.
Feb 20 '12 #7

P: 37
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.
Feb 20 '12 #8

P: 759
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub cmdSaveRecord_Click()
  5. '    If Not IsNumeric(txtRevToAdd) Then
  6. '        MsgBox ("Error in ""Revolutions to add""")
  7. '        txtRevToAdd.SetFocus
  8. 'GoTo Ex
  9. '    End If
  11.     Number_of_Revolutions = Number_of_Revolutions + Val(txtRevToAdd)
  12.     DoCmd.RunCommand acCmdSaveRecord
  13.     txtRevToAdd = ""
  15. Ex:
  16.     txtRevToAdd.SetFocus
  17. End Sub
  19. Private Sub Form_Current()
  20.     txtRevToAdd = ""
  21. 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 !
Feb 21 '12 #9

P: 37
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.
Feb 21 '12 #10

P: 759
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtRevToAdd_Change
  2.     NewTextBox = Number_of_Revolutions + Val(txtRevToAdd)
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. Dim ActualNumbRev as Long 'At module level
  3. Private Sub Form_Current()
  4.     ActualNumbRev = Number_of_Revolutions
  5. End Sub
  7. Private Sub txtRevToAdd_Change
  8.     Number_of_Revolutions = ActualNumbRev + Val(txtRevToAdd)
  9. 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".
Feb 21 '12 #11

P: 37
Ok I think I'll leave it as it is. Thanks Mihail.
Feb 21 '12 #12

Expert Mod 15k+
P: 31,494
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.
Feb 21 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.