0

I am using below function to create row number field, it works fine unless I run the query more than once. For instance if you run the query and there are 30 rows, the next time you run the query or just filter it, it starts with 31. 

Public StoredRowNumber As Variant
Function Rownumber(TheField) As Integer

    If OldlastField = TheField Then
        Else: ResetRowNum
    End If
    StoredRowNumber = StoredRowNumber + 1
    Rownumber = StoredRowNumber
    OldlastField = TheField

End Function

Function GetRowNum(TheField) As Integer
    GetRowNum = StoredRowNumber
End Function

Function ResetRowNum()
    StoredRowNumber = 0
End Function

What I expect is that the function to count from 1 every time the query is run.

Could anyone help with that?

Thanks.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
MariaMo
  • 11
  • 3
  • What is `OldlastField`, why is `StoredRowNumber` public, and why does `GetRowNum` need a `TheField` parameter? – Mathieu Guindon Oct 10 '17 at 17:20
  • Oldlastfield is use to compare with current field. If its the same as current field, rownumber will be reset. Storedrownumber is used by 2 functions. – MariaMo Oct 10 '17 at 17:22
  • Yeah I can see that. What I don't see, is its declaration. If it's not declared, then it's declared on-the-fly and locally scoped, which means your code breaks if you add `Option Explicit` at the top of the module, which means you must have a ton of other bugs lurking everywhere. – Mathieu Guindon Oct 10 '17 at 17:23
  • FWIW I'd warmly recommend you scrap this convoluted mess of global state and impure public functions, and look into [much, much better solutions](https://stackoverflow.com/q/14683226/1188513) – Mathieu Guindon Oct 10 '17 at 17:28
  • I actually have Option Explicit at the top. – MariaMo Oct 10 '17 at 17:29
  • I can't use that solution because I don't have a numeric ID field. – MariaMo Oct 10 '17 at 17:30
  • The use whatever primary key you're using instead! You *do* use primary keys, right? – Mathieu Guindon Oct 10 '17 at 17:31
  • Yes, 4 primary keys.... – MariaMo Oct 10 '17 at 17:32
  • It is not a duplicate of mat's mug's question. I have looked into it and it doesn't work for my case. If I use count, it only gives me the total of the records. – MariaMo Oct 10 '17 at 17:42
  • It's totally the same question, similar logic. The answer states plainly: reset the global counter variable **before** you run your query. And that's exactly what you need to do as well. Same. – Mathieu Guindon Oct 10 '17 at 17:43
  • A better way to reset the counter is to call it in the `WHERE` clause.... `SELECT GetRowNum(xxx) .... WHERE ResetRowNum()...` but you need to edit `ResetRowNum()` to return `true` to continue executing. – this Oct 10 '17 at 17:45
  • @Mat's Mug, Your query don't give me a sequential number. It gives me the total of records for every row. So if there are 30 records, my 30 records have the same row number 30. – MariaMo Oct 10 '17 at 17:46
  • Dude, you need to reset your row number, that's all. – Mathieu Guindon Oct 10 '17 at 17:49
  • I don't know how to reset it. If I put ResetRowNum() in where clause, it returns no record. – MariaMo Oct 10 '17 at 17:51
  • 1
    As per my edit, that's because it doesn't return `true`; add `ResetRowNum = True` in the function so it can continue processing. – this Oct 10 '17 at 17:52
  • I just did. Same result. If I run the query more than once without exit access, the seq change. – MariaMo Oct 10 '17 at 17:54
  • Okay, one last time: you need to reset your counter *every time* - global state won't reset automagically, and VBA can't guess that you intend to reset it at what it sees as completely arbitrary times. Global state lives on for as long as Access is open and the `End` keyword isn't encountered in code. – Mathieu Guindon Oct 10 '17 at 18:21
  • How to reset the counter? Could you please explain? Thanks. – MariaMo Oct 10 '17 at 18:28
  • I've added my [RowCounter](https://stackoverflow.com/a/46685323/3527297) function featuring an option for _automatic reset_ to the above mentioned answer. – Gustav Oct 11 '17 at 10:08

0 Answers0