Movelast not working as expected

[GraeagleBill]

Yes, it's like I said one would know that intuitively. Is there a method whereby the main can "Close" the sub-form and then re-Open? The screen might blink but it would then reflect any records that were added asynchronously by a sub in a general module.


Bill

[June7]

Never tried that. Maybe by setting the subform container control SourceObject property to nothing then resetting it back.

Wouldn't it just be easier to replicate the other code in another event?

[GraeagleBill]

Yes. The main code can issue the statement Me.ChildMySubForm.form.Requery to pick up any added records, but remembering the code posted earlier makes use of RecordsetClone to get the count and GoToRecord to position.

intCount = Me.RecordsetClone.RecordCount

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

What would be the equivalent code running in the main form?

Bill

[June7]

This works for me:

Me.ctrGames.SetFocus
If Me.ctrGames.Form.RecordsetClone.RecordCount > 1 Then DoCmd.GoToRecord , , acGoTo, Me.ctrGames.Form.RecordsetClone.RecordCount - 1

[GraeagleBill]

ctrGames is your test sub-form? If the code you posted is in the main, how does the DoCmd.GoToRecord "know" your referencing the sub-form's recordset?

[GraeagleBill]

Regarding my post 07:32 post, does the SetFocus on the form name answer my question?

[Missinglinq]

Trying to do things in a Subform, using code in the Subform's OnOpen and OnLoad events can be problematical because these fire before the Main Form loads, so the Subform doesn't actually know how many Records it contains.

Here's some code I have archived for doing this; simply replace SubFormControlName with the actual name of your Subform Control:

In the Main Form

Private Sub Form_Current()
  SubFormControlName.SetFocus
End Sub

Private Sub SubFormControlName _Enter()
  DoCmd.GoToRecord , , acLast
End Sub

Linq ;0)>

[GraeagleBill]

Thanks linq. I tried to use DoCmd.GoToRecord , , acLast, but the display of the sub-form was such that it was only the last record that displayed, rather than the sub-form filled to capacity with the last record appearing at the bottom of the display.

June's code suggestion: (My substituted particulars)

Private Sub Form_Open(Cancel As Integer)
Dim intCount As Integer

intCount = Me.RecordsetClone.RecordCount

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

End Sub

works perfectly. I just want to repeat that process from the main if a sub in a general module asynchronously adds a record to the sub-forms recordset, where that sub was called from within the main.

[June7]

ctrGames is name of my subform container control. Yes, had to set focus to the subform container first for the GoToRecord to work.

[GraeagleBill]

June,
I'm not familiar with container control. How does that relate to the name of a sub-form? There are a lot of references to "container" in A2003 help, but I have no idea which one would apply here. I think I just need to know how to relate my sub-form name to a container such that I can SetFocus as you've done in your suggested solution.
Thanks,
Bill

[Missinglinq]

When you click on the Subform Icon, on the Menu or Ribbon, and place it on the Form, or drag a Form, Table or Query onto the Form, you're actually placing a Subform Control on the Form. This Control has it's own name, and is what is commonly referred to as the 'subform container,' to distinguish it from the Form, Table or Query that is used as the Source Object of the Subform. The name of the Subform Control/subform container may or may not be the same as the Source Object, depending on exactly how you created the Subform, which can be confusing.

To find out the name of the Subform Control/subform container, Right-Click on one of the borders of the Control then click on Properties. In the top/left of the Properties Dialog box you'll see 'Selection Type Subform/Subreport,' and in the dropdown box the actual name of the Subform Control/subform container.

If the name is the same as the Source Object, I always change it, to avoid confusion.

Linq ;0)>

[GraeagleBill]

I have to laugh Linq, as I've called the "container" an object's "property sheet" for years. Whenever I've made that reference to anyone they always seemed to know what I was talking about. And yes, confusion arises when the control has the same name as the container, especially it seems with controls on a report.
Thanks for clearing this up for me.
Bill

[June7]

Bill, not sure you understand. The 'property sheet' is still 'property sheet'. The subform container is a control on a form. Confusion arises when the container control has same name as the object it holds (SourceObject property). The SourceObject can be table, query, form, report. The subform container control has its own properties which can be viewed on the property sheet. Click on the subform once and the property sheet shows properties of the container control. If the SourceObject is a form or report, click again in the upper left corner and will now see the properties of the object the container control holds.

[GraeagleBill]

Thanks June, that cleared up the distinction between a property sheet and a container, at least as far as sub-forms, sub-reports, tables and queries apply. Yet to come for me, I think, is a broader understanding of containers in their broader sense. At least that's the sense I get when I look in HELP and online descriptions.
Thanks again,
Bill