Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Query Add Row Numbers

  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119

    Query Add Row Numbers


    I'm trying to add RowID to a simple query using the below VBA code I found online. All I get for results is RowID=1 for every row.

    The author commented: 'To reset the row number, you have to explicitly call ResetDim lastValue As Integer
    And I'm guessing this is where I'm getting it wrong. Can someone get me on track?

    Code:
    Public Function RowNumber(x) As Integer
        'We need this parameter so Access will call the function on each row, instead of only once
        Dim lastValue As Integer
        lastValue = lastValue + 1
        RowNumber = lastValue
    End Function
    
    
    Public Sub Reset()
        lastValue = 0
    End Sub

    Code:
    SELECT [Order Import Archive].City, RowNumber([Order Import Archive]![City]) AS RowID
    FROM [Order Import Archive];
    Thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg   S2.jpg  

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,288
    Please explain with some examples.
    Why do you need RowID? Access does not have an intrinsic RowID. Records in a table are in an arbitrary order- much like a bag of marbles, or as another colleague wrote recently -like a bucket of tennis balls.
    What are the attributes of your proposed RowID?
    As usual the devil is in the details.

    Access/SQL will process each qualifying record without having a Row number.

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119
    My need to apply a RowID is part of a much larger scheme in a couple projects mandated by mass merchant clients. Do I like or agree to their demands? Nope. But if I can get past this small hurdle it will make it much easier to achieve end results. My handicap is not knowing VBA. Getting to know more, but getting hung up some basics. The end results will be a query with some 20-30 fields from 3 different tables. To explain why I need it would be pointless. It will just make my challenges easier to have it.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,288
    To explain why I need it (rowId) would be pointless.
    Well that is your prerogative. But if you are new to VBA, and Access, you might want to elaborate your needs in plain English and get some experienced advice from members. Having a preconceived idea about how you will implement something with a tool you don't know often lengthens the learning process.

    You might want to review Autonumbers which are part of Access.

    Good luck with your project.

  5. #5
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,040
    Well, your code example at the top and the image example at the bottom do not agree.
    Your problem looks like the Dim of lastValue. If you dim it as a module level variable (outside of a sub or function) you will override that setting if you dim it again in a sub or a function.
    In two examples of your code that is exactly what happens.
    The third example (under A little vba goes a long way) looks right.

  6. #6
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119
    OK. I changed the Function code to reflect the original author's example. Then I figured out the Module name should not be the same as the function and I got results as expected, sort of. When I rerun the query the RowID numbers just keep incrementing and never return to 1 as a start. This is where the original author states to explicitly call Reset. Stuck again on VBA syntax. Thinking it'd have to be done in a macro, the Reset function is not there. Ony RowNumber. How do I explicitly "call Reset" when it's a Sub Function so that lastValue returns to 0?

    Thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg  

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,278
    if City is the trigger, pass city to the function as well and assign it to a variable strOldCity that will remain in scope. Compare city to strOldCity first and if not the same, reset counter and strOldCity.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  8. #8
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119
    I think this is being over thought. The original Function came from this page:
    https://stackoverflow.com/questions/...umber-in-query
    Scroll down to near bottom of page.

    The original poster had a simple list of cities and the VBA was offered as a solution. Granted, there was no acknowledgement it worked. I'm just assuming it did. I don't think City is a trigger so much as a next row would be the trigger to increment.

    thanks

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,278
    the trigger I referred to is the one that does the reset.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,288
    This is where the original author states to explicitly call Reset. Stuck again on VBA syntax.
    Again, it depends on the purpose/intended use of the RowId. The stackOverflow links show how it can be achieved. But Grouping and other criteria could be critical to the vba involved.
    As HasUp said in the linked material -- Row_number is not intrinsic to Access.
    Good luck with your project.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,224
    Textbox on report has RunningSum property which can calculate this row ID by group.

    Another approach uses DCount() in query. Example:

    SELECT *, DCount("*", "tablename", "CustomerID=" & CustomerID & " AND ID<" & ID) + 1 AS GrpSeq FROM tablename ORDER BY CustomerID, ID;

    Also review http://allenbrowne.com/ranking.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,288

  13. #13
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119
    Thanks all for your input. Not knowing VBA and needing to move forward, I found a solution that worked for what I needed to accomplish. Just in case someone else is struggling at my level of VBA skills I'm posting my end solution. Attached is a list of Cities, for convenience, in notepad to create a Table: Cities. Below is the Module: RowID and Query: qry_AddRowNumber. You should be able to cut-n-paste all and open query.

    Module: RowID
    Code:
    Private lngRowNumber As Long
    Private colPrimaryKeys As VBA.Collection
     
    Public Function ResetRowNumber() As Boolean
      Set colPrimaryKeys = New VBA.Collection
      lngRowNumber = 0
      ResetRowNumber = True
    End Function
     
    Public Function RowNumber(UniqueKeyVariant As Variant) As Long
      Dim lngTemp As Long
     
      On Error Resume Next
      lngTemp = colPrimaryKeys(CStr(UniqueKeyVariant))
      If Err.Number Then
        lngRowNumber = lngRowNumber + 1
        colPrimaryKeys.Add lngRowNumber, CStr(UniqueKeyVariant)
        lngTemp = lngRowNumber
      End If
     
      RowNumber = lngTemp
    End Function
    Query: qry_AddRowNumber
    Code:
    SELECT Cities.City, RowNumber([Cities]![City]) AS RowID
    FROM Cities
    WHERE (((ResetRowNumber())<>False))
    ORDER BY Cities.City;
    Thanks!
    Attached Files Attached Files

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,288
    Interesting. A temporary rank number that is assigned to records in a query. Ranking can be ascending or descending depending on Order By.
    Thanks for posting. I'd still like to know why since it isn't permanent, and will change if records are added/deleted.

    Anyway, good luck with your project and VBA.

  15. #15
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119
    I need to modify this a bit. In particular, this line: lngRowNumber = 0. Instead of 0, how can I get a prompt for a number input in VBA?

    I want to use this for a sequence of invoice numbers. In production, I'd get the prompt for the last invoice number, possibly 51234, and the count would start at that number +1.

    Code:
    Private lngRowNumber As Long
    Private colPrimaryKeys As VBA.Collection
     
    Public Function ResetRowNumber() As Boolean
      Set colPrimaryKeys = New VBA.Collection
      lngRowNumber = 0
      ResetRowNumber = True
    End Function
     
    Public Function RowNumber(UniqueKeyVariant As Variant) As Long
      Dim lngTemp As Long
     
      On Error Resume Next
      lngTemp = colPrimaryKeys(CStr(UniqueKeyVariant))
      If Err.Number Then
        lngRowNumber = lngRowNumber + 1
        colPrimaryKeys.Add lngRowNumber, CStr(UniqueKeyVariant)
        lngTemp = lngRowNumber
      End If
     
      RowNumber = lngTemp
    End Function
    Thanks!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Specified numbers in a query
    By sknechte in forum Access
    Replies: 8
    Last Post: 02-01-2018, 04:02 PM
  2. Replies: 2
    Last Post: 08-30-2017, 07:11 AM
  3. Replies: 5
    Last Post: 02-26-2016, 10:48 AM
  4. Replies: 8
    Last Post: 03-10-2014, 11:47 AM
  5. Replies: 1
    Last Post: 11-29-2011, 08:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums