A97: Can I 'peek' to see if there is another record?

[MLH]

Before running the following line of code, I would like to first know
if there is another record in the form's record source. If I run this
line when the last record is current, an error is returned. Any way
to take a peek first?

DoCmd.GoToRecord acActiveDataForm, "frmEnterLienAmounts", acNext

[Trevor Best]

MLH wrote:

Before running the following line of code, I would like to first know
if there is another record in the form's record source. If I run this
line when the last record is current, an error is returned. Any way
to take a peek first?

DoCmd.GoToRecord acActiveDataForm, "frmEnterLienAmounts", acNext

I would have thought the next record would be a new one if you were on
the last <shrug>

Not tested but you might try using something like:
with me.recordsetclone
if (.AbsolutePosition+1) < .Recordcount then
DoCmd.GoToRecord acActiveDataForm, "frmEnterLienAmounts", acNext
else
' whatever you want to let user know
end if
end with

Note:
..AbsolutePosition is zero based so when it says 0 you're on record 1
therefore you always have to add 1 to it.

--
[OO=00=OO]

[Salad]

MLH wrote:

Before running the following line of code, I would like to first know
if there is another record in the form's record source. If I run this
line when the last record is current, an error is returned. Any way
to take a peek first?

DoCmd.GoToRecord acActiveDataForm, "frmEnterLienAmounts", acNext

Something like this might work

Dim rst As Recordset
Set rst = Me.RecordsetClone
If rst.recordcount > 0 then
rst.MoveLast 'get form's record count
rst.Bookmark = Me.Bookmark
If (rst.RecordCount <> rst.AbsolutePosition + 1) Then
DoCmd.GoToRecord acActiveDataForm, "frmEnterLienAmounts", acNext
End If
endif
Set rst = Nothing

[David W. Fenton]

Salad <oi*@vinegar.com> wrote in
news:wm**************@newsread1.news.pas.earthlink .net:

MLH wrote:

Before running the following line of code, I would like to first
know if there is another record in the form's record source. If I
run this line when the last record is current, an error is
returned. Any way to take a peek first?

DoCmd.GoToRecord acActiveDataForm, "frmEnterLienAmounts", acNext

Something like this might work

Dim rst As Recordset
Set rst = Me.RecordsetClone
If rst.recordcount > 0 then
rst.MoveLast 'get form's record count
rst.Bookmark = Me.Bookmark
If (rst.RecordCount <> rst.AbsolutePosition + 1) Then
DoCmd.GoToRecord acActiveDataForm, "frmEnterLienAmounts",
acNext End If
endif
Set rst = Nothing

Ack! Why do all of you continue to write code like this?

There is absolutely no need to set a recordset variable to refer to
something that can be done with a WITH block:

With Me.RecordsetClone
If .recordcount > 0 then
.MoveLast 'get form's record count
.Bookmark = Me.Bookmark
If (.RecordCount <> .AbsolutePosition + 1) Then
DoCmd.GoToRecord acActiveDataForm, _
"frmEnterLienAmounts", acNext
End If
End If
End With

This avoids needing to declare a variable, assign a recordset to it,
and then clean up after it.

What's not to like?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[jimfortune]

David W. Fenton wrote:

Ack! Why do all of you continue to write code like this?

There is absolutely no need to set a recordset variable to refer to
something that can be done with a WITH block:

With Me.RecordsetClone
If .recordcount > 0 then
.MoveLast 'get form's record count
.Bookmark = Me.Bookmark
If (.RecordCount <> .AbsolutePosition + 1) Then
DoCmd.GoToRecord acActiveDataForm, _
"frmEnterLienAmounts", acNext
End If
End If
End With

This avoids needing to declare a variable, assign a recordset to it,
and then clean up after it.

What's not to like?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Your point is well taken David. Perhaps we didn't know that
intellisense would work on a . inside the With..End With block? I'm
resolving to use With..End With more often. It does make things
slightly more readable. Setting rst for a RecordsetClone is more of a
habit that I will outgrow now that I know it's not required. I learned
several years ago that I was tying my shoes with the equivalent of a
granny knot rather than a square knot. I was able to relearn how to
tie my shoes properly. I'll be able to incorporate new Access
programming techniques although at this level it's mostly a matter of
style and mental organization (still necessary) rather than of making a
huge performance difference or a great conceptual leap. Thanks for the
reminder. Your comments are appreciated.

James A. Fortune

[rkc]

David W. Fenton wrote:

Salad <oi*@vinegar.com> wrote in
news:wm**************@newsread1.news.pas.earthlink .net:

MLH wrote:

Before running the following line of code, I would like to first
know if there is another record in the form's record source. If I
run this line when the last record is current, an error is
returned. Any way to take a peek first?

DoCmd.GoToRecord acActiveDataForm, "frmEnterLienAmounts", acNext

Something like this might work

Dim rst As Recordset
Set rst = Me.RecordsetClone
If rst.recordcount > 0 then
rst.MoveLast 'get form's record count
rst.Bookmark = Me.Bookmark
If (rst.RecordCount <> rst.AbsolutePosition + 1) Then
DoCmd.GoToRecord acActiveDataForm, "frmEnterLienAmounts",
acNext End If
endif
Set rst = Nothing

Ack! Why do all of you continue to write code like this?

There is absolutely no need to set a recordset variable to refer to
something that can be done with a WITH block:

With Me.RecordsetClone
If .recordcount > 0 then
.MoveLast 'get form's record count
.Bookmark = Me.Bookmark
If (.RecordCount <> .AbsolutePosition + 1) Then
DoCmd.GoToRecord acActiveDataForm, _
"frmEnterLienAmounts", acNext
End If
End If
End With

This avoids needing to declare a variable, assign a recordset to it,
and then clean up after it.

What's not to like?

What's your point? That the operations can be done using recordsetclone
without setting a new variable equal to it or that the With statement
is magical?

[Salad]

David W. Fenton wrote:

Salad <oi*@vinegar.com> wrote in
news:wm**************@newsread1.news.pas.earthlink .net:

MLH wrote:

Before running the following line of code, I would like to first
know if there is another record in the form's record source. If I
run this line when the last record is current, an error is
returned. Any way to take a peek first?

DoCmd.GoToRecord acActiveDataForm, "frmEnterLienAmounts", acNext

Something like this might work

Dim rst As Recordset
Set rst = Me.RecordsetClone
If rst.recordcount > 0 then
rst.MoveLast 'get form's record count
rst.Bookmark = Me.Bookmark
If (rst.RecordCount <> rst.AbsolutePosition + 1) Then
DoCmd.GoToRecord acActiveDataForm, "frmEnterLienAmounts",
acNext End If
endif
Set rst = Nothing

Ack! Why do all of you continue to write code like this?

There is absolutely no need to set a recordset variable to refer to
something that can be done with a WITH block:

With Me.RecordsetClone
If .recordcount > 0 then
.MoveLast 'get form's record count
.Bookmark = Me.Bookmark
If (.RecordCount <> .AbsolutePosition + 1) Then
DoCmd.GoToRecord acActiveDataForm, _
"frmEnterLienAmounts", acNext
End If
End If
End With

This avoids needing to declare a variable, assign a recordset to it,
and then clean up after it.

What's not to like?

I figured if someone had a better solution it would be presented. I
write most of my stuff on the fly and hope for the best.

[Trevor Best]

rkc wrote:

What's your point? That the operations can be done using recordsetclone
without setting a new variable equal to it or that the With statement
is magical?

It is magical, with this method you can actually fix something that
isn't broken... without breaking it :-)

--
[OO=00=OO]

[David W. Fenton]

Trevor Best <no****@besty.org.uk> wrote in
news:42**********************@news.zen.co.uk:

rkc wrote:

What's your point? That the operations can be done using
recordsetclone
without setting a new variable equal to it or that the With
statement
is magical?

It is magical, with this method you can actually fix something
that isn't broken... without breaking it :-)

It's less code, and code that doesn't require creating a new memory
structure, and then cleaning up after it.

Again, what's not to like?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc