How to reach the last record in a subform ?

[Alain Guichaoua]

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 docmd.gotorecord aclast but i did not
succeed.

Thank you for your help.

--
_________________________________

Site photo personnel :

http://perso.wanadoo.fr/photodim

[Tom van Stiphout]

On Sat, 30 Aug 2003 19:03:50 +0200, "Alain Guichaoua" <[e-mail]>
wrote:

mysubformcontrol.form.recordsetclone.movelast

-Tom.

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 docmd.gotorecord aclast but i did not
succeed.

Thank you for your help.

[Tom van Stiphout]

On Sat, 30 Aug 2003 10:20:34 -0700, Tom van Stiphout
<to*****@no.spam.cox.net> wrote:

Sorry, that previous post ran away from me. Here is the full code:
dim rs as dao.recordset
set rs = mysubformcontrol.form.recordsetclone
rs.movelast
mysubformcontrol.form.bookmark = rs.bookmark
set rs=nothing

-Tom.

On Sat, 30 Aug 2003 19:03:50 +0200, "Alain Guichaoua" <[e-mail]>
wrote:

mysubformcontrol.form.recordsetclone.movelast

-Tom.

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 docmd.gotorecord aclast but i did not
succeed.

Thank you for your help.

[Trevor Best]

On Sat, 30 Aug 2003 19:03:50 +0200 in comp.databases.ms-access, "Alain
Guichaoua" <[e-mail]> wrote:

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 docmd.gotorecord aclast but i did not
succeed.

Just stick to putting one record in the subform - voilla :-)

In the main form:
Private Sub Form_Current()
Dim rs As Recordset
Set rs = MySubForm.Form.RecordsetClone
If rs.RecordCount Then
rs.MoveLast
MySubForm.Form.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
End Sub
--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)

[Bruce M. Thompson]

> 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 docmd.gotorecord aclast but i did not
succeed.

And yet another approach:

'************
Dim frm as Form
'Set reference to "subform"
Set frm = Me.MySubformControlName.Form

'Goto last record in subform
If frm.RecordsetClone.RecordCount > 0 Then
frm.SelTop = frm.RecordsetClone.RecordCount
End If
'************

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Alain G.]

Thank you Tom and Trevor, it works well.

Have a nice week-end

--
_________________________________

Site photo personnel :

http://perso.wanadoo.fr/photodim

[Alain G.]

Thank you to you too, Lyle; It is really nice to get many
solutions. I want to reach the last record (the one before a new
one) as in your exemple.

Bye !
--
_________________________________

Site photo personnel :

http://perso.wanadoo.fr/photodim

[Bruce M. Thompson]

If the subform were showing ten records, would this show the last ten, with
the last one selected?

Yes.

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Lyle Fairfield]

"Bruce M. Thompson" <bthmpson@big_NOSPAM_foot.com> wrote in
news:vl************@corp.supernews.com:

Thank you to you too, Lyle; It is really nice to get many
solutions. I want to reach the last record (the one before a new
one) as in your exemple.

Uh ... as in my example, actually. <chuckle>

I'm very happy to confirm that it's Bruce's example.

<chuckle>

<chuckle>

--
Lyle

[Bruce M. Thompson]

Comments interspersed.

"Lyle Fairfield" <ly******@yahoo.com> wrote in message
news:Xn******************************@130.133.1.4. ..

"Bruce M. Thompson" <bthmpson@big_NOSPAM_foot.com> wrote in
news:vl************@corp.supernews.com:

If the subform were showing ten records, would this show the last ten,
with the last one selected?
Yes.
More specifically, when the recordcount *is* 10 - and the last record is the
Current record.

Hmmmm ... I'll try my question again.

If frm.recordcount is >10
Okay, there is a slight difference from that which I reported in what will be
visible in the subform control because I used exactly 10 records.
and

frm shows 10 only records
.... frm *exposes* only 10 records (just to make sure we are on the same
wavelength).
and record 1 is selected
In the current event, which is where my example code was supposed to be entered,
there is a current record, not a selected record, but that may not be what you
are trying to say.
what happens when this code is run:

frm.SelTop = frm.RecordsetClone.RecordCount

?

Here in Canada only the last record is shown (and a "new" blank record if
appending is allowed).
Here in the US using Access 2000 on WinXP, when .Recordcount > 10, on Sunday,
the 31st of August, 2003, I see 9 records exposed (and a "new" blank record, if
appending is allowed). You may obtain different results. <g>
or am I missing something?
? <eg>
a kludge that could be used to display the last n records (where n =
number of records displayed in frm) and select the last is:

With Me
.SelTop = .RecordsetClone.RecordCount - _
(.InsideHeight - .FormHeader.Height - .FormFooter.Height) / _
.Detail.Height
.SelTop = .RecordsetClone.RecordCount
End With

Cool. Thanks, Lyle. :-)

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access) NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Lyle Fairfield]

Trevor Best <bouncer@localhost> wrote in
news:s5********************************@4ax.com:

(Looks at Lyle now)
When you access that .RecordsetClone without explicity setting a
variable and closing and setting it to nothing afterwards, is the
reference to it still lingering around somewhere?

I believe referring to .recordsetclone creates not only another recordset,
but also another database, well, ... instance of the database. These two
objects die with the form; that is they do not have to be slaughtered by
helicopter gunships or murdered in the woods, for justification or revenge
or to be sure they don't do or say anything embarrassing to ourselves;
they're just like McArthur - they just fade away (but they don't smell as
bad).

Try this:

Private Sub Form_Load()
Dim z As Long
With Me
.SelTop = .RecordsetClone.RecordCount - _
(.InsideHeight - .FormHeader.Height - .FormFooter.Height) / _
.Detail.Height
.SelTop = .RecordsetClone.RecordCount
For z = 0 To DAO.DBEngine(0).Databases.Count - 1
MsgBox z & ": " & DAO.DBEngine(0)(z).Recordsets.Count
Next z
End With
End Sub

One gets two messages:

0: 0

and

1: 1

(assuming nothing else is happening in the db at the time.)

--
Lyle