Last record of Recordset

[Scott]

I wanted to know when I'm at the last record of a recordset because I want
to add a ";" between the records but not after the last record, I tried to
use rst.EOF but that's after the last record and I want to know before I
reach the last record that now I'm going to reach it.

Thanks in advance.

Scott

 

[Dirk Goldgar]

I wanted to know when I'm at the last record of a recordset because I
want to add a ";" between the records but not after the last record,
I tried to use rst.EOF but that's after the last record and I want to
know before I reach the last record that now I'm going to reach it.

If you do a .MoveLast and a .MoveFirst before starting your loop, then
the recordset's .RecordCount property will have the number of records in
the recordset and you can check to see if .AbsolutePosition =
(.RecordCount - 1).

However, depending on what you're trying to do, this may not be
necessary. It sounds as if you're concatenating the recordset data into
a string variable, delimited by semicolons. What I do in cases like
this is add a semicolon to the string before each entry, then remove the
first, leading semicolon when I'm done. For example:

Dim rs As DAO.Recordset
Dim strList As String

Set rs = CurrentDb.OpenRecordset( ... )
With rs
Do Until .EOF
strList = strList & ";" & !SomeField
.MoveNext
Loop
.Close
End With
Set rs = Nothing

strList = Mid$(strList, 2)

 

[Scott]

Thanks,

Scott

If you do a .MoveLast and a .MoveFirst before starting your loop, then
the recordset's .RecordCount property will have the number of records in
the recordset and you can check to see if .AbsolutePosition =
(.RecordCount - 1).

However, depending on what you're trying to do, this may not be
necessary. It sounds as if you're concatenating the recordset data into
a string variable, delimited by semicolons. What I do in cases like
this is add a semicolon to the string before each entry, then remove the
first, leading semicolon when I'm done. For example:

Dim rs As DAO.Recordset
Dim strList As String

Set rs = CurrentDb.OpenRecordset( ... )
With rs
Do Until .EOF
strList = strList & ";" & !SomeField
.MoveNext
Loop
.Close
End With
Set rs = Nothing

strList = Mid$(strList, 2)


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)