473,326 Members | 2,173 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 2887
ADezii
8,834 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,556 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

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

Similar topics

7
by: GAVO. | last post by:
Hello every one I have a database with a form called "frmOrders" on that for I need to create a sequential number for each city apart from the original autonumber. So the table "tblorders" would...
1
by: jmarr02s | last post by:
Is it possible to limit the number of Subform records using MS Access 2003? That is, my end users want the capability of entering up to 12 records on their subform. Is that possible? ...
9
by: Nooby | last post by:
New to Access here. I inherited a db that has the first column as an automatically generated sequential number. I want to bump it up, but for the life of me I can't figure out how to do it. Is...
2
by: masker | last post by:
I was on the web trying to find a javascript that would allow me to input a number on my website and have it increase by a given percentage every second. During my search I found the Earth...
1
by: Luzuko | last post by:
I would like to know how can i restrict textbox input in VB.net using code instead of VB.net controls. e.g If i want a user to type numbers only in a textbox(ID number textbox), how can i make...
2
by: simon penny | last post by:
I have a table in which I record referral records. I have a second table in which I record service records. One referral can have many service records attached. The tables are linked by referral ID -...
16
by: neelsfer | last post by:
I need to add sequential lap numbers here. If person's first racenumber is added, it shows lapno =1; if same person in same Race and racedate do 2nd lap then lapno must show =2 Mainform is...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.