Determining first and last records displayed in a form

[Lyn]

I have a form set to Single Form mode with which I can cycle through the
records in a table via Next and Previous buttons. To avoid users pressing
the Previous button on the first record and the Next button on the last
record, I would like to disable one or both buttons when the first and/or
last record is displayed. I am not sure how to do this when the
RecordSource is simply the table.

I know that if the record source were a Recordset, I could use the
AbsolutePosition and RecordCount properties, but in this case the record
source is not a recordset.

In another post, MacDermott drew my attention to the Recordset property of a
form. According to the help manual, this property "returns the recordset
object that provides the data being browsed in a form". I think that this
may be the answer to my problem, but I have not been able to get it to work
in the ADO environment.

The sort of code I am looking at for the Current event is:

If <recordset>.AbsolutePosition = 1 Then
Me!btnPrev.Enabled = False
Else
Me!btnPrev.Enabled = True
End If
If <recordset>.AbsolutePosition = <recordset>.RecordCount Then
Me!btnNext.Enabled = False
Else
Me!btnNext.Enabled = True
End If

Is using the form's Recordset property the right way to go? If so, how
would I code it? (I have tried declaring a recordset variable and then
Setting its value to Me.Recordset, but this gives an error -- "Runtime error
13. Mismatch".)

Or is there a better approach than this?

Any help appreciated.
--
Cheers,
Lyn.

[Tom van Stiphout]

On Mon, 24 Jan 2005 23:50:19 +1100, "Lyn" <lh******@ihug.com.au>
wrote:

The better approach may be to use the built-in navigation buttons.

If you have a REALLY good reason you need to run your own buttons, I
would suggest the form's RecordsetClone property:
set rs=me.recordsetclone
rs.bookmark=me.bookmark ' sync rs with form
btnPrev.enabled = (rs.absoluteposition>1)
set rs=nothing

-Tom.

I have a form set to Single Form mode with which I can cycle through the
records in a table via Next and Previous buttons. To avoid users pressing
the Previous button on the first record and the Next button on the last
record, I would like to disable one or both buttons when the first and/or
last record is displayed. I am not sure how to do this when the
RecordSource is simply the table.

I know that if the record source were a Recordset, I could use the
AbsolutePosition and RecordCount properties, but in this case the record
source is not a recordset.

In another post, MacDermott drew my attention to the Recordset property of a
form. According to the help manual, this property "returns the recordset
object that provides the data being browsed in a form". I think that this
may be the answer to my problem, but I have not been able to get it to work
in the ADO environment.

The sort of code I am looking at for the Current event is:

If <recordset>.AbsolutePosition = 1 Then
Me!btnPrev.Enabled = False
Else
Me!btnPrev.Enabled = True
End If
If <recordset>.AbsolutePosition = <recordset>.RecordCount Then
Me!btnNext.Enabled = False
Else
Me!btnNext.Enabled = True
End If

Is using the form's Recordset property the right way to go? If so, how
would I code it? (I have tried declaring a recordset variable and then
Setting its value to Me.Recordset, but this gives an error -- "Runtime error
13. Mismatch".)

Or is there a better approach than this?

Any help appreciated.

[rkc]

Lyn wrote:

I have a form set to Single Form mode with which I can cycle through the
records in a table via Next and Previous buttons. To avoid users pressing
the Previous button on the first record and the Next button on the last
record, I would like to disable one or both buttons when the first and/or
last record is displayed. I am not sure how to do this when the
RecordSource is simply the table.

I know that if the record source were a Recordset, I could use the
AbsolutePosition and RecordCount properties, but in this case the record
source is not a recordset.

In another post, MacDermott drew my attention to the Recordset property of a
form. According to the help manual, this property "returns the recordset
object that provides the data being browsed in a form". I think that this
may be the answer to my problem, but I have not been able to get it to work
in the ADO environment.

The sort of code I am looking at for the Current event is:

If <recordset>.AbsolutePosition = 1 Then
Me!btnPrev.Enabled = False
Else
Me!btnPrev.Enabled = True
End If
If <recordset>.AbsolutePosition = <recordset>.RecordCount Then
Me!btnNext.Enabled = False
Else
Me!btnNext.Enabled = True
End If

Is using the form's Recordset property the right way to go? If so, how
would I code it? (I have tried declaring a recordset variable and then
Setting its value to Me.Recordset, but this gives an error -- "Runtime error
13. Mismatch".)

Zero is the first record position.
..RecordCount -1 is the last record position.

[Lyn]

I think that applies to DAO. I am using ADO and according to the Help file,
the first record is 1 and -1 means adPosUnknown. Very confusing!

--
Cheers,
Lyn.

"rkc" <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in message
news:%_*******************@twister.nyroc.rr.com...

Zero is the first record position.
.RecordCount -1 is the last record position.

[Lyn]

Tom,
Thanks for the advice -- I will try it out.

--
Cheers,
Lyn.

"Tom van Stiphout" <no*************@cox.net> wrote in message
news:ae********************************@4ax.com...

On Mon, 24 Jan 2005 23:50:19 +1100, "Lyn" <lh******@ihug.com.au>
wrote:

The better approach may be to use the built-in navigation buttons.

If you have a REALLY good reason you need to run your own buttons, I
would suggest the form's RecordsetClone property:
set rs=me.recordsetclone
rs.bookmark=me.bookmark ' sync rs with form
btnPrev.enabled = (rs.absoluteposition>1)
set rs=nothing

-Tom.

[Darryl Kerkeslager]

"Lyn" <lh******@ihug.com.au> wrote

In another post, MacDermott drew my attention to the Recordset property of a form. According to the help manual, this property "returns the recordset
object that provides the data being browsed in a form". I think that this
may be the answer to my problem, but I have not been able to get it to work in the ADO environment. [snip] Is using the form's Recordset property the right way to go? If so, how
would I code it? (I have tried declaring a recordset variable and then
Setting its value to Me.Recordset, but this gives an error -- "Runtime error 13. Mismatch".)

Just to be sure, but you are using an ADP, right? Because the recordset of
an mdb uses DAO - you can't set a DAO recordset to an ADO recordset.
Darryl Kerkeslager

[rkc]

Lyn wrote:

I think that applies to DAO. I am using ADO and according to the Help file,
the first record is 1 and -1 means adPosUnknown. Very confusing!

Why think. Try it. It's real simple.

Put a textbox on your form and in the form's current event set it's
value to Me.Recordset.AbsolutPosition. When you are on the first
record the value will be zero. When you are on the last record the
value will be 1 less than Me.Recordset.count.

[Lyn]

OK, this really had me confused for a few minutes. But I think I have it
figured out. I have been using the ADO library for my own recordsets. The
Help file specifically says that AbsolutePosition is "1-based".

Using the reference that you suggested (Me.Recordset.AbsolutePosition), I am
in fact accessing the form's "internal" recordset -- if that is the right
way to put it. And it seems that "internally" Access uses DAO where
AbsolutePosition is "0-based".

This was an interesting and educational exercise. Thanks for your input.

--
Cheers,
Lyn.

"rkc" <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in message
news:S8*************@twister.nyroc.rr.com...

Put a textbox on your form and in the form's current event set it's
value to Me.Recordset.AbsolutPosition. When you are on the first
record the value will be zero. When you are on the last record the
value will be 1 less than Me.Recordset.count.

[Lyn]

I am using an mdb. Something that has only just become clear to me is that
Access forms use DAO. When creating your own recordsets, you are free to
use the DAO or ADO libraries and formats. But it seems that Access forms
have DAO built in, so that properties like .Recordset are in DAO format, and
there is no option to change it to ADO.

As you have probably guessed, I am new to Access. A couple of the manuals
that I have used suggested that ADO is the way of the future and recommended
that new projects should use ADO. One even suggested that DAO may be phased
out in future versions of Access. Hence I decided to use ADO for my
projects.

I don't want to open a can of worms with this -- I know that there strong
arguments in favour of both types and each has its loyal adherents. I am
just explaining why I chose to learn and use ADO, and that I now realise
that I also have to deal with DAO where Access seems to use it internally.

--
Cheers,
Lyn.

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:kt********************@comcast.com...

Just to be sure, but you are using an ADP, right? Because the recordset
of
an mdb uses DAO - you can't set a DAO recordset to an ADO recordset.
Darryl Kerkeslager

[Darryl Kerkeslager]

> "Darryl Kerkeslager" <Ke*********@comcast.net> wrote

Just to be sure, but you are using an ADP, right? Because the recordset
of an mdb uses DAO - you can't set a DAO recordset to an ADO recordset.

"Lyn" <lh******@ihug.com.au> wrote I am using an mdb. Something that has only just become clear to me is that Access forms use DAO. When creating your own recordsets, you are free to
use the DAO or ADO libraries and formats. But it seems that Access forms
have DAO built in, so that properties like .Recordset are in DAO format, and there is no option to change it to ADO.

As you have probably guessed, I am new to Access. A couple of the manuals
that I have used suggested that ADO is the way of the future and recommended that new projects should use ADO. One even suggested that DAO may be phased out in future versions of Access. Hence I decided to use ADO for my
projects.

I have used ADO since I started seriously using Access, and my only problem
with ADO was a lack of good examples. At this point, I see no real
difference in either technology. Both are adequate to the task, and both
may/will be phased out. The issue of mdb forms, and the inabilty to
manipulate mdb objects, are the only significant negatives of ADO. BTW, you
can manipulate tables, columns, indexes, groups, users, etc - just not
objects specific to mdb files.

I have also been in the habit since day one of fully qualifying all ADODB
and DAO references. It makes life easier, and perhaps makes your code
..00001 seconds faster.
Darryl Kerkeslager

[Lyn]

Interesting thought -- has anybody ever come up with a function that will
convert an ADO recordset and related objects to DAO, and vice versa?

--
Cheers,
Lyn.

The issue of mdb forms, and the inabilty to
manipulate mdb objects, are the only significant negatives of ADO.