Last record of Recordset

  • Thread starter Thread starter Scott
  • Start date Start date
S

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
 
Scott said:
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.
Click to expand...

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)
 
Thanks,

Scott


Dirk Goldgar said:
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)
Click to expand...
 
Back
Top