473,289 Members | 1,743 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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.
Nov 13 '05 #1
10 3687
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.


Nov 13 '05 #2
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.

Nov 13 '05 #3
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.

Nov 13 '05 #4
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.

Nov 13 '05 #5

"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
Nov 13 '05 #6
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.
Nov 13 '05 #7
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.

Nov 13 '05 #8
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

Nov 13 '05 #9
> "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
Nov 13 '05 #10
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.

Nov 13 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Duke Hamacher | last post by:
Hello: This title is a little ambiguous but this is an interesting puzzle. Lets's say we have a JSP page with a form. The ActionForm associated with this form has, say, 4 String properities. ...
10
by: Alain Guichaoua | last post by:
Good evening to all Here is my problem : I have a form with a subform. They are linked. When I open the form I would like the subform to reach its last record. I tried the method...
4
by: deko | last post by:
I can't move a multi-page report to the last record unless I keep the popup form (that defined it's subreports) open. DoCmd.OpenReport "rptStandard", acViewNormal DoCmd.Close acForm,...
2
by: Ryan | last post by:
Access97 I hope someone out there can help. This is driving me crazy. Basically, I am trying to recreate VBA's built in navigation feature (The one that allows you to move first, next,...
2
by: trgpham | last post by:
hi all, I have a continuous form that bound to a couple of fields. When the records are displayed, the last record which have nothing also display (blank record). I tried to Group it in the...
3
by: brucedodds | last post by:
My application has a form based on a parent table with a subform based on a child table. The relationship is Cascade Delete. The first record displayed when the form opens has five child records....
7
by: robtyketto | last post by:
Greetings, I have a main form. Upon filling in a combo box it then displays a subform (based on a query that uses values in the combo box on where clause) THe subform has the paramater Data...
3
by: Gord | last post by:
If I have a form open with a subform control on it in datasheet view that has its record source set to a query or a table, is it possible to determine which record the user has clicked into with...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.