none
Creating running calculations without using AutoNumber RRS feed

  • Question

  • Hello,

    I may have realized that in Excel, I can create running calculations simply for example:

    =A1+1

    Thus, this gets the result:

    1

    2

    3

    4

    ...n

    Now I know that using AutoNumber can be a wasting memory space, if I do not use it correctly.

    So, I am trying to perform a basic recurring calculation in Access

    So far, I have this source code from Access VBA:

    Option Compare Database
    Dim Ctr As Long
    Public Function RunCalc(Ctr As Long)
    RunCalc = Forms!EntriesDatasheet!EntryID
    RunCalc = RunCalc + 1
    End Function

    The code snippet may not be complete, therefore it may produce errors when I run the program.

    How could I improve this code snippet, so that I can retrieve a recurring adding calculation of 1 per record?

    JohnDBCTX

    Friday, March 18, 2016 10:43 PM

Answers

All replies

  • Hi. Pardon me but  for what do you need this number? If only for display, this is easier  to do in Reports. Just my 2 cents...
    Friday, March 18, 2016 11:32 PM
  • Hello DB guy,

    How could I perform that calculation in Reports Design View?

    So far, I have tried this code snippet:

    ="Page " & [Page]

    therefore, EntryID=Reports!EntriesReport![Page]

    The result was a name? error in the EntryID attribute.

    This has been an issue for several hours; I should have done this by now.

    JohnDBCTX

    Saturday, March 19, 2016 12:10 AM

    • Edited by JohnDBCTX Saturday, March 19, 2016 1:26 AM Add comments regarding image
    • Marked as answer by JohnDBCTX Saturday, March 19, 2016 1:26 AM
    Saturday, March 19, 2016 1:25 AM
  • The current record within the brackets solved everything of what I want to perform.

    Thanks to my own self and to my own experience, I should inform every user how to do this task as alternative to the AutoNumber datatype in Table Design View.

    JohnDBCTX

    Saturday, March 19, 2016 1:29 AM
  • Hi John. Sorry for the delay. Glad to hear you found a working solution already. Just to answer your question about how to do the same thing in Reports, you need an unbound Textbox with a Control Source of =1. Then, you set the Running Sum to OverAll. Good luck with your project.
    Saturday, March 19, 2016 1:50 AM
  • I have looked for the OverAll function in Access.  I had to reply that you were referring to Access 2016 Right?

    I only have Access 2010.

    JohnDBCTX

    Saturday, March 19, 2016 4:13 AM
  • I have looked for the OverAll function in Access.  I had to reply that you were referring to Access 2016 Right?

    I only have Access 2010.

    JohnDBCTX

    Hi John. The Running Sum property is available to all version of Access. Select the unbound control in design view and go to the Properties window. The Running Sum property should be under the Data tab. Click on the dropdown and select Over All. Hope that helps...
    Saturday, March 19, 2016 3:57 PM
  • On the subject of row numbering or ranking generally, you might like to take a look at RowNumbering.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates methods of numbering or ranking rows in a query, overall or by group, returning read-only or updatable result tables.

    Ken Sheridan, Stafford, England

    Sunday, March 20, 2016 11:18 PM