MSAccess: Datasheet Properties (as subform) - Form Not Open, Err: 2489?

Question

I have a Datasheet as a subform on my MainForm. While the MainForm is open, and a filter is applied, I am attempting to collect details about the filtered datasheet...then (ultimately) step thru the datasheet row-by-row (via .movenext, I presume):

Anyway, here is a quick test via the OnClick event from a second form (pop-up/modal):

    If Forms!Mainform![Data subform].Form.FilterOn = True Then
        msgbox "Filter is ON"
        Forms!Mainform![Data subform].SetFocus
        msgbox Forms!Mainform![Data subform].RecordCount
        DoCmd.GoToRecord acDataForm, "Forms!Mainform![Data subform]", acFirst
        msgbox Forms!Mainform![Data subform].Form.ItemID.value
    Else
        msgbox "Filter is OFF"
    End If

Access can see when FilterOn = True, but is complaining the Form is not open (Err:2489) when I attempt to move to the first record. The MainForm really is Open.

I am assuming my subform references are to blame, but my adjustments have not worked, so far.

Any ideas to assist, please?

I believe the issue is your reference to the Object Name. i.e. you have "Forms!Mainform![Data subform]", but that is a literal that I don't think will resolve to the object at run time. Plus since your code is in your subform, I would use "DoCmd.GoToRecord acDataForm, Me.<yourobjectname>, acfirst — Wayne G. Dunn
Creating a public sub in MainForm to do the work and invoking that from the popup Forms!MainForm.PublicSubName [args] will negate the interdependency of the MainForm with the popup form - ie in the future if MainForm is changed, you won't have to change the popup form as well; If PublicSubName itself then calls a public sub defined in the subform as SubFormName.SubFormPublicSubName [args], then you move the logic to the subform itself where you can interrogate the properties of itself directly, and if need be use the immediate window to work out what is going wrong. — John Bingham
Thanks Wayne & John, I was about to put a Public Sub into MainForm as you note, but found a RecordsetClone approach will do what I need. I did not expect it would handle a filtered datasheet like it does. — Mark Pelletier

Mark Pelletier Mark Pelletier · Accepted Answer · 2014-10-14T21:45:45

Wow, I was going down the totally wrong path. The following RecordsetClone approach provides me exactly the access I need to all subform's FILTERED datasheet data (simple example below to simply step thru all the records).

Public Function mySelect()

    Dim rstData As dao.Recordset
    Set rstData = Forms!Mainform![Data subform].Form.RecordsetClone

    msgbox rstData.RecordCount
    rstData.MoveFirst
    Do Until rstData.EOF
        msgbox rstData.Fields(0)    'Field(0) = ItemID
        rstData.MoveNext
    Loop

End Function

I already do the recordsetclone thing in this same app, just in a slightly different context. Crazy I missed it so badly.

Anyway, I am good to go.

Thanks~

MSAccess: Datasheet Properties (as subform) - Form Not Open, Err: 2489?

1 Answers