Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Posts
    88

    Unanswered: Sequntial Numbering in A Query, Lebans rownumber, Modules, ADOs and DAOs

    Hi,

    I'd like to add sequential numbers to a query. I've found a module at www.lebans.com that does this but I'm having difficulty using it.

    At first I had trouble because I had a newer version of Access and I had to Register DAO 3.6 and place it higher on the list than ADO. I did this in a small test database and it worked fine.

    When I tried using it in my actual database I got what I believe was the same error that I got before I registered it. I selected the same references in this database as I was using in my test database???

    I then figured that since the test database worked I would just import all the queries, tables, etc into it and use it. When I did this I still got the error.

    Does anyone have any ideas on this?

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2001
    Location
    Estonia
    Posts
    33

    Re: Sequntial Numbering in A Query, Lebans rownumber, Modules, ADOs and DAOs

    If you need to number rows in a query you can add GLOBAL e.g. lngRowNumber variable an define a function e.g. fNextRowNum
    sample code will be:
    '---declarations
    Global lngRowNum As Long
    '---function
    Function fRowNum(AnyVariable) As Long

    lngRowNum = lngRowNum + 1
    fRowNum = lngRowNum

    End Function

    reset lngRowNum to 0 before you run your query and define on of rows as:
    RowNumber: fRowNum([AnyOtherFieldInYourQuery]) - variable does not matter it is not used anyway.
    Voila! Rows are numbered.
    Should work in any version of Access and independent of libraries.

  3. #3
    Join Date
    Oct 2002
    Posts
    88

    Re: Sequntial Numbering in A Query, Lebans rownumber, Modules, ADOs and DAOs

    I don't know VB. Is this the complete code and I enter this as a module and then I just call fnextrownumber in a query?

    Thanks

    Originally posted by Alec
    If you need to number rows in a query you can add GLOBAL e.g. lngRowNumber variable an define a function e.g. fNextRowNum
    sample code will be:
    '---declarations
    Global lngRowNum As Long
    '---function
    Function fRowNum(AnyVariable) As Long

    lngRowNum = lngRowNum + 1
    fRowNum = lngRowNum

    End Function

    reset lngRowNum to 0 before you run your query and define on of rows as:
    RowNumber: fRowNum([AnyOtherFieldInYourQuery]) - variable does not matter it is not used anyway.
    Voila! Rows are numbered.
    Should work in any version of Access and independent of libraries.

  4. #4
    Join Date
    Jul 2001
    Location
    Estonia
    Posts
    33

    Re: Sequntial Numbering in A Query, Lebans rownumber, Modules, ADOs and DAOs

    Yes, this is complete code.
    Just in case I addded everything to your database.

    Code is in "Module1" - rename it to your liking if you want to.
    "Query1" is modified also - as I told before you can use ANy variable as argument for RowNumber - but reset lngRowNumber to 0 before you call your query, otherwise it will continue counting from where you left off.
    Experiment.. Good luck

    Originally posted by tmort
    I don't know VB. Is this the complete code and I enter this as a module and then I just call fnextrownumber in a query?

    Thanks
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2002
    Posts
    88

    Smile

    Thanks a lot. It works fine.

    For my purposes it really doesn't matter whether I reset if or not but how do I do it.

    I tried adding the line that lngRowNum = O to either the beginning or end of the code and it no longer counted.

    Thanks again

    Tom Mort

  6. #6
    Join Date
    Jul 2001
    Location
    Estonia
    Posts
    33
    You can't do it in the function itself. One possibe solution that if you use a button to open a form based on a Query1 insert

    lngRowNum = 0

    before
    DoCmd.OpenForm ....

    If you create the button with a wizard, open button properties and examine OnClick event then yo will see code behind it.
    Play around with it...
    Regards

    Originally posted by tmort
    Thanks a lot. It works fine.

    For my purposes it really doesn't matter whether I reset if or not but how do I do it.

    I tried adding the line that lngRowNum = O to either the beginning or end of the code and it no longer counted.

    Thanks again

    Tom Mort

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •