How to tell if you're on the last record

[Rico]

Hi All,

Just wondering, in vb code, how to if the last record on a cascading form is
the current record?

Thanks!

[salad]

Rico wrote:

Hi All,

Just wondering, in vb code, how to if the last record on a cascading form is
the current record?

Thanks!

This is example code from the help file under the topic AbsolutePositio n
property. In a code module, enter AbsolutePositio n and press F1

Sub AbsolutePositio nX()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim strMessage As String

Set dbsNorthwind = OpenDatabase("N orthwind.mdb")
' AbsolutePositio n only works with dynasets or snapshots.
Set rstEmployees = _
dbsNorthwind.Op enRecordset("Em ployees", _
dbOpenSnapshot)

With rstEmployees
' Populate Recordset.
.MoveLast
.MoveFirst

' Enumerate Recordset.
Do While Not .EOF
' Display current record information. Add 1 to

' AbsolutePositio n value because it is zero-based.
strMessage = "Employee: " & !LastName & vbCr & _
"(record " & (.AbsolutePosit ion + 1) & _
" of " & .RecordCount & ")"
If MsgBox(strMessa ge, vbOKCancel) = vbCancel _
Then Exit Do
.MoveNext
Loop

.Close
End With

dbsNorthwind.Cl ose

End Sub

[Rico]

Hi Salad,

I appreciate it, but I'm using a form with a bound recordset. I'd like to
be able to tell if the user is on the last record so when they update it, I
can set the focus to a command button rather than staying on the current
record (AllowAdditions is off).

Rick

"salad" <oi*@vinegar.co m> wrote in message
news:w2******** *********@newsr ead1.news.atl.e arthlink.net...

Rico wrote:

Hi All,

Just wondering, in vb code, how to if the last record on a cascading form
is the current record?

Thanks!

This is example code from the help file under the topic AbsolutePositio n
property. In a code module, enter AbsolutePositio n and press F1

Sub AbsolutePositio nX()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim strMessage As String

Set dbsNorthwind = OpenDatabase("N orthwind.mdb")
' AbsolutePositio n only works with dynasets or snapshots.
Set rstEmployees = _
dbsNorthwind.Op enRecordset("Em ployees", _
dbOpenSnapshot)

With rstEmployees
' Populate Recordset.
.MoveLast
.MoveFirst

' Enumerate Recordset.
Do While Not .EOF
' Display current record information. Add 1 to

' AbsolutePositio n value because it is zero-based.
strMessage = "Employee: " & !LastName & vbCr & _
"(record " & (.AbsolutePosit ion + 1) & _
" of " & .RecordCount & ")"
If MsgBox(strMessa ge, vbOKCancel) = vbCancel _
Then Exit Do
.MoveNext
Loop

.Close
End With

dbsNorthwind.Cl ose

End Sub

[salad]

Rico wrote:

Hi Salad,

I appreciate it, but I'm using a form with a bound recordset. I'd like to
be able to tell if the user is on the last record so when they update it, I
can set the focus to a command button rather than staying on the current
record (AllowAdditions is off).
The code can be modified to suit. First, set a recordset clone to the
form. Then get the record count. Then find out if the current record
is at the end.

Dim rst as recordset
set rst = Me.RecordsetClo ne
Dim lngPos As Long
rst.MoveLast
lngPos = rst.RecordCoutn
rst.FindFirst "ID = " & Me.ID
If rst.AbsolutePos ition = lngPos then Msgbox "EOF"
....

or something similar.


Rick

"salad" <oi*@vinegar.co m> wrote in message
news:w2******** *********@newsr ead1.news.atl.e arthlink.net...

Rico wrote:

Hi All,

Just wondering, in vb code, how to if the last record on a cascading form
is the current record?

Thanks!

This is example code from the help file under the topic AbsolutePositio n
property. In a code module, enter AbsolutePositio n and press F1

Sub AbsolutePositio nX()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim strMessage As String

Set dbsNorthwind = OpenDatabase("N orthwind.mdb")
' AbsolutePositio n only works with dynasets or snapshots.
Set rstEmployees = _
dbsNorthwind. OpenRecordset(" Employees", _
dbOpenSnapsho t)

With rstEmployees
' Populate Recordset.
.MoveLast
.MoveFirst

' Enumerate Recordset.
Do While Not .EOF
' Display current record information. Add 1 to

' AbsolutePositio n value because it is zero-based.
strMessage = "Employee: " & !LastName & vbCr & _
"(record " & (.AbsolutePosit ion + 1) & _
" of " & .RecordCount & ")"
If MsgBox(strMessa ge, vbOKCancel) = vbCancel _
Then Exit Do
.MoveNext
Loop

.Close
End With

dbsNorthwind. Close

End Sub

[Rico]

Perfect! Thanks!
"salad" <oi*@vinegar.co m> wrote in message
news:bk******** *********@newsr ead3.news.atl.e arthlink.net...

Rico wrote:

Hi Salad,

I appreciate it, but I'm using a form with a bound recordset. I'd like
to be able to tell if the user is on the last record so when they update
it, I can set the focus to a command button rather than staying on the
current record (AllowAdditions is off).

The code can be modified to suit. First, set a recordset clone to the
form. Then get the record count. Then find out if the current record is
at the end.

Dim rst as recordset
set rst = Me.RecordsetClo ne
Dim lngPos As Long
rst.MoveLast
lngPos = rst.RecordCoutn
rst.FindFirst "ID = " & Me.ID
If rst.AbsolutePos ition = lngPos then Msgbox "EOF"
...

or something similar.

Rick

"salad" <oi*@vinegar.co m> wrote in message
news:w2******** *********@newsr ead1.news.atl.e arthlink.net...

Rico wrote:

Hi All,

Just wondering, in vb code, how to if the last record on a cascading
form is the current record?

Thanks!

This is example code from the help file under the topic AbsolutePositio n
property. In a code module, enter AbsolutePositio n and press F1

Sub AbsolutePositio nX()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim strMessage As String

Set dbsNorthwind = OpenDatabase("N orthwind.mdb")
' AbsolutePositio n only works with dynasets or snapshots.
Set rstEmployees = _
dbsNorthwind .OpenRecordset( "Employees" , _
dbOpenSnapsh ot)

With rstEmployees
' Populate Recordset.
.MoveLast
.MoveFirst

' Enumerate Recordset.
Do While Not .EOF
' Display current record information. Add 1 to

' AbsolutePositio n value because it is zero-based.
strMessage = "Employee: " & !LastName & vbCr & _
"(record " & (.AbsolutePosit ion + 1) & _
" of " & .RecordCount & ")"
If MsgBox(strMessa ge, vbOKCancel) = vbCancel _
Then Exit Do
.MoveNext
Loop

.Close
End With

dbsNorthwind .Close

End Sub