Move to Last record on a subform

[sixsoccer]

I have built a database with a <Mainform> and a <Subform>. My problem is
twofold.

1. My subform is set as a continuos form with AllowAddiotions set to NO (ie.
a list of Issues to the client on the mainform)
2. To add new issues, a button is used and a pop-up form is used to add an
issue with more detail than what's available on the subform.
3. On accepting the new Issue from the pop-up the pop-up closes and you are
returned to the subform, and the new line is added, but the active record is
the first record and not the new one.

Problem: I have tried docmd.runcommand accmdrecordsgotolast and
docmd.GoToRecord acDataForm ,,acLast with no avail. I am getting errors
that the cmd is not available. I preface that with a requery and moving
control to the subform, either to the form itself or field on the subform,
still no luck. If I use the GoToRecord and specify the
Foms!mainform!subform as the object, I get an error that tells me the form
is not open (but it is).

Is there an easy way to move to the last record, I'm not certain what I may
be doing wrong.

The second problem I am running into is I need an event to occur when I move
out of a record (similar to onExit) and not into record that OnCurrent
provides. The afterupdate is of no use to me since changes may not occur.

Any help would be appreciated.

Thanks

[Allen Browne]

This code will requery the record in the subform, and move focus to the last
record:

Dim frm As Form
Set frm = Forms![YourMainForm]![YourSubformControl].Form
frm.Requery
With frm.RecordsetClone
If .RecordCount > 0 Then
.MoveLast
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing

There is no event of exiting a record. The solution might depend on what you
need to do, but one approach is to declare a form-level variable to store
the primary key value, and then read it in the form's Current or Unload
event (the 2 occasions when you "leave" a record).

In the General Declarations of the form's module (at the top, with the
Option statements):
Dim mvarID As Variant

In the Current event procedure of the form:

Dim strMsg As String
If Not IsEmpty(varID) Then
If IsNull(varID) Then
strMsg = "You just left the new record"
Else
strMsg = "You just left record ID " & mvarID
End If
mvarID = Me.[YourPrimaryKeyField]
MsgBox strMsg
End If

Do something similar in the form's Unload event.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sixsoccer" <si*******@yahoo.com> wrote in message
news:Vu******************@news04.bloor.is.net.cabl e.rogers.com...

I have built a database with a <Mainform> and a <Subform>. My problem is
twofold.

1. My subform is set as a continuos form with AllowAddiotions set to NO (ie. a list of Issues to the client on the mainform)
2. To add new issues, a button is used and a pop-up form is used to add an
issue with more detail than what's available on the subform.
3. On accepting the new Issue from the pop-up the pop-up closes and you are returned to the subform, and the new line is added, but the active record is the first record and not the new one.

Problem: I have tried docmd.runcommand accmdrecordsgotolast and
docmd.GoToRecord acDataForm ,,acLast with no avail. I am getting errors
that the cmd is not available. I preface that with a requery and moving
control to the subform, either to the form itself or field on the subform,
still no luck. If I use the GoToRecord and specify the
Foms!mainform!subform as the object, I get an error that tells me the form
is not open (but it is).

Is there an easy way to move to the last record, I'm not certain what I may be doing wrong.

The second problem I am running into is I need an event to occur when I move out of a record (similar to onExit) and not into record that OnCurrent
provides. The afterupdate is of no use to me since changes may not occur.

Any help would be appreciated.

Thanks