469,150 Members | 1,971 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

How do I add a sequential row number to a textbox in subform and subtract rows

547 512MB
I have this problem - need to add a row number starting from 1 that is added everytime you update the subform, that is filtered on various fields in a query.
Must work like =1 in a report that starts at the top and gets added to every line independantly of a "time" in this instance, as the query is filtered in a specific way to produce a desired result.
I got the "GetLineNumber" method from Stephen Lebans on the internet to work, but its way to slow - takes more that 20sec before showing the numbers.

Together with this, i would also like to show the "time gap" moving downwards between ie the 1sts row and 2nd row etc see screen pic pls.

I included the access 2003 file. See the 2x screen pics also pls. (pls note that this is only part of application - to make it smaller)
Anybody that can help please?
Attached Files
File Type: zip XC position.zip (89.4 KB, 3 views)
File Type: zip qry fields.zip (9.9 KB, 1 views)
File Type: zip Numbering subform 2003.zip (87.7 KB, 3 views)
May 13 '11 #1

✓ answered by ADezii

  1. Declare a Public Variable as follows:
    Expand|Select|Wrap|Line Numbers
    1. Public lngCtr As Long
  2. Create a Public Function that will increment the Value of the Public variable intCtr by +1 for each successive Call:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcNextSeqNum(varDummy As Variant) As Long
    2.   lngCtr = lngCtr + 1
    3.     fCalcNextSeqNum = lngCtr
    4. End Function
  3. Create a Calculated Field in Query XCSort2Q which is the underlying Data Source for the Sub-Form, passing to it any Field Value:
    Expand|Select|Wrap|Line Numbers
    1. Seq: fCalcNextSeqNum([RaceDate])
  4. Reset the Value of the Public Variable intCtr in the Click() Event of the Refresh Command Button:
    Expand|Select|Wrap|Line Numbers
    1. lngCtr = 0
    2.  
    3. DoCmd.RunCommand acCmdRefresh
  5. In the AfterUpdate() Event of the category Combo Box, Execute Code in the Refresh Command Button:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub category_AfterUpdate()
    2.   Call ref_Click
    3. End Sub
  6. Click the Refresh Command Button whenever necessary to Reset the Value of the Public Variable, and Restart the Sequence again.
  7. Download the Revised Attachment for a Visual.

2 2605
ADezii
8,800 Expert 8TB
  1. Declare a Public Variable as follows:
    Expand|Select|Wrap|Line Numbers
    1. Public lngCtr As Long
  2. Create a Public Function that will increment the Value of the Public variable intCtr by +1 for each successive Call:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcNextSeqNum(varDummy As Variant) As Long
    2.   lngCtr = lngCtr + 1
    3.     fCalcNextSeqNum = lngCtr
    4. End Function
  3. Create a Calculated Field in Query XCSort2Q which is the underlying Data Source for the Sub-Form, passing to it any Field Value:
    Expand|Select|Wrap|Line Numbers
    1. Seq: fCalcNextSeqNum([RaceDate])
  4. Reset the Value of the Public Variable intCtr in the Click() Event of the Refresh Command Button:
    Expand|Select|Wrap|Line Numbers
    1. lngCtr = 0
    2.  
    3. DoCmd.RunCommand acCmdRefresh
  5. In the AfterUpdate() Event of the category Combo Box, Execute Code in the Refresh Command Button:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub category_AfterUpdate()
    2.   Call ref_Click
    3. End Sub
  6. Click the Refresh Command Button whenever necessary to Reset the Value of the Public Variable, and Restart the Sequence again.
  7. Download the Revised Attachment for a Visual.
Attached Files
File Type: zip Numbering_SubForm_Revised.zip (91.2 KB, 3 views)
May 14 '11 #2
NeoPa
32,165 Expert Mod 16PB
Remembering of course that queries are often processed backwards as well as forwards, for instance when they are bound to reports or forms. This makes doing such things using VBA highly unreliable except in certain, quite limited, scenarios.
May 14 '11 #3

Post your reply

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

Similar topics

7 posts views Thread by GAVO. | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.