Movelast not working as expected

[GraeagleBill]

I have a sub-form based on a simple table query. The sub-form is "continuous".

I have in the code sheet of the sub-form the following:

Private Sub Form_Open(Cancel As Integer)
    If Me.RecordsetClone.RecordCount > 0 Then
        Me.RecordsetClone.MoveLast
    End If
End Sub

What I want and what I expected is that the form will open displaying the last record at the bottom of the record's pane. It seems that the open code had no effect. The parent code does not contain any statements that would otherwise effect the recordsource of the sub-form.

What do I need in addition to what I've already coded?

Thanks,


Bill

[orange]

Just curious -- is there any relationship between the subform and main form? Do you really only want to show 1 record from the table? If so, why would the subform be continuous?
What is the sql for your recordset?

[pbaldy]

I think what you want is GoToRecord.

[GraeagleBill]

There 100's of records. The record pane of the sub-form displays about 25 or so at a time. I just want the form to open positioned at the bottom. (As though the operator has scrolled to the last record).

[pbaldy]

Did you try GoToRecord?

[June7]

Try:
Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acLast
End Sub

[GraeagleBill]

Paul, I tried this:

Option Compare Database
Option Explicit
Dim strTemp As Variant

Private Sub Form_Open(Cancel As Integer)
    If Me.RecordsetClone.RecordCount > 0 Then
        Me.RecordsetClone.MoveLast
        strTemp = Me.RecordsetClone.SDate
        DoCmd.FindRecord strTemp, acStart
    End If
End Sub

Where SDate is unique to the recordset. The DoCmd.FindRecord blows up with a 2162:



I ran the code in Debug and verified that the value in strTemp was valid. In this case,
it's "2013.07.28.0".

[GraeagleBill]

Hi June,
That's almost what I'm trying to accomplish. And while I do in fact display the last record in the recordset, it displays "alone" at the top of the sub-forms recordset, while what I'd like is for the last record to display at the bottom of the record pane with as many preceding records appearing above as will fit.
Bill

[June7]

If all you want to do is move to the last record on the form or to a new record, try GoToRecord as shown in previous post.

If you want to move to a specific record then why move to last?

That is an odd structure for a date value. Why a string? SDate is not a date/time field?

EDIT: I posted before seeing yours.

[GraeagleBill]

In the current app, the SDate field means more than just the date. While it looks like just a date it isn't in that it includes a suffix that's part of the expression.

[June7]

I tested in both Continuous and Datasheet view. Not getting the behavior you describe. The last record gets focus but I still see records above.

[GraeagleBill]

Maybe it's the difference between A2003 and A2010?

The size of the sub-form frame is such that the form will show 22 records at a time. If I get the record count AND the count is greater than 22 is there a way with GoToRecord to go to record "count-21" ? I hate code like that, but I'm at a lost as to how to solve this.

[GraeagleBill]

This achieved the results. I just don't like hard-coded numbers.

[CODEPrivate Sub Form_Open(Cancel As Integer)
Dim intCount

intCount = Me.RecordsetClone.RecordCount

If intCount > 22 Then DoCmd.GoToRecord , , acGoTo, intCount - 21

End Sub][/CODE]

[GraeagleBill]

June, as somewhat of an aside here, if the main form runs a sub in a general module that results in the addition of a record to the table upon which the recordsource query of the sub-form is based, will a Requery cause the sub-form's OnOpen code to run again? Intuitively I don't think so, but I want to run the GoToRecord again if the recordsource gets a record added to it. Am I clear here?

[June7]

The Open event will not run again.