Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    35

    Unanswered: Need the Record Number

    Is there a vba code where I can do a select and where clause, and also found out what record number it is at the same time ??

    Ive tried

    Code:
    Dim rst As Recordset
    Dim ctr As Integer
    ctr = 1
    Set rst = CurrentDb.OpenRecordset("SELECT [PROVIDER] FROM Master_Database;")
       rst.MoveFirst
         Do While Not rst.EOF
            If rst![PROVIDER] <> " & PROVIDER.Value & " Then  
            ctr = ctr + 1 
                Exit Do
            End If
            rst.MoveNext
        Loop
    But this doesn’t really work i made the record i am looking for in the 4th position but i get 2 returned
    Last edited by KWHAT; 01-04-12 at 15:32.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the table has a primary key (it should) or at least an Identity column, you can use a query to create a pseudo-counter column. In the following example, "SysCounter" is the Identity column of the table "Table1":
    Code:
    SELECT Table1.*, b.cnt
    FROM (
    SELECT Table1.SysCounter, COUNT(*) as cnt
    FROM Table1 INNER JOIN (
        SELECT Table1.SysCounter
        FROM Table1) AS a
    ON a.SysCounter <= Table1.SysCounter
    GROUP BY Table1.SysCounter) as b, Table1
    WHERE b.SysCounter = Table1.SysCounter
    Have a nice day!

Posting Permissions

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