Reference specific record in recordset 1

[BrockLanders]

I have an Access 2000 db and writing code to export records in a table to Excel. I've got that working, but I want to include some validation "rules".
If it's the first record in the recordset I want the code to export to column E in Excel. However, I don't know how to code VB to do this. It seems like there should be a function like RecordNumber, but I can't find it. That way I could say: If RecordNumber = 1 Then.... In searching the VB help file I found AbsolutePosition, but couldn't get it to work.

Any ideas? Help is very much appreciated.

 

[RoyVidar]

The .AbsolutePosition property is supported by both ADO and DAO (in ADO it might be dependent on provider, I think use clientside cursor...). Another thing to note (also found in the help files) DAO .Absoluteposition starts at 0 (first record is .absoluteposition 0), while ADO returns 1 for the first record. Take note of what the help files states on .absoluteposition - it is not an absolute record number, but just the current position in the current recordset, based on the current filter, sort...

Roy-Vidar

 

[Zion7]

Brock,
Using Absolute position, didn't work how? Wrong record? Error message? Did you check your references?

This worked for me..

Dim rec As New ADODB.Recordset
    
rec.Open "tblCountries", CurrentProject.Connection, adOpenStatic, adLockOptimistic

With rec
   While Not .EOF
      If rec.AbsolutePosition = 10 Then
   Debug.Print !txtCountry
   End If
    .MoveNext
    Wend
End With
rec.Close: Set rec = Nothing

...As Roy said, with ADO, 10 is 10


From another concept, I found the that bookmark property, offers some insight as to what position one is in, in a recordset.

I've used this...

If rec.bookmark = 512 Then
...you are on the first record.
and all subsequent records, increment by 1, after that.

So if you want the 10th record...

If rec.bookmark = 512 + 9 Then

Why 512? I don't know.

This was with ADO, & the adOpenDynamic recordset type DIDN'T work, but adOpenStatic did, (that was all I tried).

Hope this helps, good luck!

 

[BrockLanders]

Below is my code. The line in bold is giving me the error: "Invalid operation." I think it's because I'm using dbOpenForwardOnly, but I tried others to no avail. Any ideas? Thanks!

Dim DBase As Database
Dim appExcel As Excel.Application, xlSheet As New Excel.Worksheet
Dim DataShts As Sheets, wDataSht As Worksheet
Dim lngCol As Long, lngRow As Long

'Open Excel File
Set appExcel = New Excel.Application
appExcel.Workbooks.Open "C:\Documents and Settings\BLanders\My Documents\RCM\Test.xls"
appExcel.Visible = True

'Gets new data from imported tables
query = "SELECT * FROM tblSummary ORDER by [AcctName]"

Set DBase = CurrentDb
Set RecSet = DBase.OpenRecordset(query, dbOpenForwardOnly)

'Adds new data to Excel sheet
With RecSet
  Do While Not .BOF And Not .EOF
    Set xlSheet = appExcel.Worksheets(1)
    [b]If .AbsolutePosition = 0 Then[/b]
        lngCol = 5
    Else
        lngCol = lngCol + 1
        For lngRow = 0 To .Fields.Count - 1
            xlSheet.Cells(lngRow + 1, lngCol) = .Fields(lngRow)
        Next lngRow
    End If
    .MoveNext
  Loop
End With

 

[RoyVidar]

Works on my setup with dbOpenDynaset (or by omitting the arguement).

Roy-Vidar

 

[PHV]

The help file clearly state that the AbsolutePosition property is unavailable for forward only recordsets.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BrockLanders]

Thanks RoyVidar and Zion7. dbOpenDynaset worked, for some reason, I didn't try that one.