Results 1 to 6 of 6
  1. #1
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98

    Find record position in Table


    I've looked at a lot of pages from Google on this matter and I cannot find a good answer to finding the record number (row #) or position of a record using VBA. I've found if you are using a form you can use .recordcount, but I am using a Table. I've also come across .AbsolutePosition, but cannot get it working and after more research, found it may be un-useful in my circumstance. So is there anyway at all using DAO to find the row # a record is in a table? Thanks.

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    The Access Gnomes have no concept of the 'position' in relation to a record in a Table. The usual way of explaining this is to liken it to a 'bag full of marbles.'

    What, exactly, is your need for this info?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    I could do it in excel, but was hoping I could exclude the exporting factors. I have a table with sale numbers and line items. Some sale numbers have multiple info, i.e. one sale number will have 3 line items (so 3 records all with the same sale number). I am going to make the table update the sale to just one sale replacing the other line items with the item of my choosing. If that makes sense :/ sorry.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I agree with linq's comments

    However, one way of doing this might be to run a ranking query on your table which will allow you to assign a 'position number' for each record in a table

    There are 2 ways of doing these:
    a) using subqueries - see this article by Allen Browne http://allenbrowne.com/ranking.html
    b) using the Serialize function as I've described in this thread https://www.access-programmers.co.uk...d.php?t=297922

    If you go down this route, remember the position of records will change if you add/remove records or sort the data in a different way
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    yep, as others have posted - no such thing as 'position'. One identifies a record by values of its field(s) - if indexed that is 1 field but otherwise might require multiple fields.

    Assuming then you've created the record set via a query with the correct records... then I cannot quite tell whether you need to do an Aggregate query or a Delete query or an Update query to manipulate the records to what you seek ultimately. .....

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have a table with sale numbers and line items.
    This may be part of the problem, or at the least, it's an indicator of less than perfect design. I interpret this to mean it's all in one table when you should have sales (or invoice or whatever the parent thing is) and the line items in another table. The only thing that should be repeated in the items table is the primary key from the parent.

    I don't think that will solve your issue though. The best you might be able to do is identify a record via an ordered query that returns the autonumber id from the line items table (or single table if that's how you must have it). While you could order a table by autonumber ascending (kind of a row identifier) a properly built query is the only way to pretty much guarantee any kind of sort order, barring things like sorting on numbers that are really text. The part about keeping only 1 record for a sale is a bit strange, though.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query by record position in table
    By DaveC in forum Queries
    Replies: 6
    Last Post: 09-30-2014, 01:46 PM
  2. Replies: 4
    Last Post: 06-27-2014, 04:34 PM
  3. Replies: 4
    Last Post: 07-09-2013, 09:10 PM
  4. Replies: 4
    Last Post: 07-04-2013, 12:07 PM
  5. Insert a new record in a particular position in a table?
    By accessorizer in forum Programming
    Replies: 7
    Last Post: 04-07-2012, 04:21 PM

Posting Permissions

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