Need the Record Number

KWHAT · #1 (**permalink**) 01-04-12, 09:26

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

Sinndho · #2 (**permalink**) 01-05-12, 05:54

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

Internet Services

Web Development

Digital Marketing

Consumer Tech