force new record to be at bottom of continuous subform?

[NKTower]

Access 2003

I have a continuous subform which is primarily used for adding records, but
it isn't a data-entry only subform as the user needs to be able to edit
previously entered recrods as well. I normally display 14 rows, If there
are more than 14 rows, I'd like to display the 13 most-recent rows and the
new record space at the bottom.

If in my parent form I have a button (or event proc based on other things)
that takes me to the new record, it usually positions the subform to the new
record, but the New Record is at the top of the scroll area of the subform,
with lots of blank space below.

This can be quite unnerving to my users - for example, one event is to
delete some row(s) based upon critieria entered in the parent form. What
happens is that all of the records seem to be gone - they aren't, they are
just scrolled out of site.

I've tried establishing a recordsetclone, going to the last record and then
backing up N and then bookmarking to position the subform "N-up" from the
bottom, then tried to go to the New Record. Besides being ugly in concept,
it doesn't work. I end up with the New Record at the top of the scroll area.

So how do I force the display of the last "N" records and position to the
New Record such that it is at/near the bottom of the scroll area?

 

[Marshall Barton]

Access 2003

I have a continuous subform which is primarily used for adding records, but
it isn't a data-entry only subform as the user needs to be able to edit
previously entered recrods as well. I normally display 14 rows, If there
are more than 14 rows, I'd like to display the 13 most-recent rows and the
new record space at the bottom.

You might find this example useful
http://www.lebans.com/SelectRow.htm

 

[NKTower]

Marshall - Thanks - I just looked at your reference. It works very nicely
for everything EXCEPT the GoTo NewRecord - the NewRecord appears at the top
of the scroll area. Sigh.

I note that it is dated 1999 - you'd think that by now someone at MS would
provide a mechanism for it. Oh well.

Since posting the question last night I played around and got a make-shift
way to do it:
a) Application.Echo False - suppresses screen painting for the momen
b) set up a recordset clone for the row source
c) position to last record
d) MovePrevious N-times (where N is number of visible rows you want leaving
room for the new record
e) MoveNext same number of rows
f) SetFocus to the right-most (i.e. last tab position) in the last existing
record
g) SendKeys - send a CR - this puts you in the first field of the new record
h) Application.Echo TRUE display the positioned screen

All of this presupposes that you are set to Cycle All Records rather than
Current Record.

Ugly, but it works. I guess I'll make a generic subroutine to do it - I'll
have to pass in the form, the number of rows, and the name of the last
control in the tab sequence.

Thanks again for the link, though.

 

[Marshall Barton]

Marshall - Thanks - I just looked at your reference. It works very nicely
for everything EXCEPT the GoTo NewRecord - the NewRecord appears at the top
of the scroll area. Sigh.

I note that it is dated 1999 - you'd think that by now someone at MS would
provide a mechanism for it. Oh well.

Since posting the question last night I played around and got a make-shift
way to do it:
a) Application.Echo False - suppresses screen painting for the momen
b) set up a recordset clone for the row source
c) position to last record
d) MovePrevious N-times (where N is number of visible rows you want leaving
room for the new record
e) MoveNext same number of rows
f) SetFocus to the right-most (i.e. last tab position) in the last existing
record
g) SendKeys - send a CR - this puts you in the first field of the new record
h) Application.Echo TRUE display the positioned screen

All of this presupposes that you are set to Cycle All Records rather than
Current Record.

Ugly, but it works. I guess I'll make a generic subroutine to do it - I'll
have to pass in the form, the number of rows, and the name of the last
control in the tab sequence.

If you really need to do it that way, then you should find
the Move method useful.

I strongly reccommend that you never use SendKeys. It is
plague ridden with bugs and can easily send the keystrokes
to the wrong place. Maybe a MoveNext or AddNew on the
Me.Recordset object?

Stephen had two examples on that page. I haven't dug into
your new record problem in his non-API example, but I seem
to remember that it's not as difficult as what you think it
is.

Just off the top of my head, in A2002 or later, I would try
something along the lines of:

With Me.Subform.Form.Recordset
If .RecordCount > 0 Then
.MoveLast
If .RecordCount > 13 Then
.Move -12
.MoveLast
End If
.MoveNext
End If
End With

For another example, I dug around in an old A07 app and
found this code that uses RecordsetClone:

Dim intRecs As Integer

Me.SubForm.SetFocus
With Me.SubForm.Form
If .RecordsetClone.RecordCount > 0 Then
.RecordsetClone.MoveLast
.Bookmark = .RecordsetClone.Bookmark
intRecs = (.InsideHeight - .Section(1).Height _
- .Section(2).Height) \ .Section(0).Height
If intRecs > .RecordsetClone.RecordCount Then
intRecs = .RecordsetClone.RecordCount
End If
.RecordsetClone.Move 1 - intRecs
.Bookmark = .RecordsetClone.Bookmark
End If
End With
DoCmd.GoToRecord , , acNewRec

Even if you can get the Recordset approach to work, think
about using the intRecs idea instead of hardcoding for 14
records. (If your subform does not have the header and
footer sections, remove the Section(1) and Section(2)
terms,)

 

[NKTower]

Very interesting.
I'll give your approaches a try. However I've observed that as soon as you
reference NewRecord all positioning is lost. That's what happened in the
demo you sent me to, for example. Maybe it behaves itself in A2007, but my
client is still at 2003.

I'll play with it over the weekend.

Thanks