reference recordsource on subform

  • I have a form with 3 subform. On Currrent event on subform1, I am getting the ID and then using that to filter the query in subform2. I get an error msg when I try that (Run-Time error '2455'" You entered an expression that has an invalid reference to the property Form/Report."

    I am using the code below in the event (On Current) of subform1. The weird thing is that when I hit end to the error msg that pops up, It takes me to the form and then the code works fine.

    It is stopping at this line:

    Forms!ProjectAdmin.ReportListX.Form.RecordSource = sqlstr

    Private Sub Form_Current()

    'Dim db As DAO.DataBase

    Dim sqlstr As String

    Dim ProjID As Integer

    'Set db = CurrentDb

    'On Error Resume Next

    ProjID = Me.ProjectsID

    If ProjID > 0 And IsNull(ProjID) = False Then

    sqlstr = "Select * from ReportlistX Where ProjectsID = " & ProjID

    Else

    sqlstr = ""

    End If

    Forms!ProjectAdmin.ReportListX.Form.RecordSource = sqlstr

    Forms!ProjectAdmin.ReportListX.Form.Requery

    End Sub

  • I presume that your ReportListX is the name of a subform control where subform2 is residing, and that you have the subform properly linked to the main form. In that case, I believe you need to have the syntax modified to

    Forms!ProjectAdmin.ReportListX.Form!RecordSource = sqlstr

    See the knowledge base article ACC: How to Refer to a Control on a Subform or Subreport for the details on referencing a subform. Note that if the code is running on an event from your main form, you can use the Me. prefix in place of Forms!ProjectAdmin.

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • Yes, ReportListX is the unboound subform2 in my main form. I've close out of my app, but will try again later and reply back if your solution worked.

  • Grasshopper,

    There's a really good article on referring to controls and their properties on the dying website AccessWeb, which is here: http://www.mvps.org/access. Look for an article by Keri Hardwick. She gives examples of how to refer to and manipulate various form properties at runtime.

  • Thanks for the link, it has good information there for what I was looking for and more!

  • There are two problems here.

    1. When a form has subforms, every of there subforms are initialized before the initialization sequence of the main (parent) form (i.e. the Form_Open --> Form_Load --> Form_Current sequence of events for each subform occurs before the Form_Open --> Form_Load --> Form_Current sequence of events for the main (parent) form). However, you cannot control the order of initialization of the subforms. This means that, in a subform, you cannot reference a property of another subform that could not be loaded yet.

    2. When a form is open as a subform in a main (parent) form, it is not added to the Application.Forms collection. This means that you cannot reference it using the syntax Forms!Formname.

    Here's a solution:

    Note: In this example, the main (parent) form is Frm_Orders, the subform (SF1) that controls the RecordSource property of the other subform (SF2) is SF_OrderLines and the second subform (SF2) is SF_OrderLineItems. The SubForm/SubReport controls of both subforms are named Child_OrderLines for SF1 (SourceObject = SF_OrderLines) and Child_OrderLineItems for SF2 (SourceObject = SF_OrderLineItems). The relationships are: PK_Tbl_Orders --> FK_Tbl_Orders and PK_Tbl_OrderLines --> FK_Tbl_OrderLines

    A. Add this to the class module of the main (parent) form:

    Option Compare Database

    Option Explicit

    Private m_booReady As Boolean

    Private m_strSQL As String

    Public Property Get Ready() As Boolean

    Ready = m_booReady

    End Property

    Public Property Let SQL(ByVal strSQL As String)

    m_strSQL = strSQL

    End Property

    Private Sub Form_Open(Cancel As Integer)

    m_booReady = True

    Me.Child_OrderLineItems.Form.RecordSource = m_strSQL

    End Sub

    B. Add this to the class module of the subform (SF1) that controls the RecordSource of the other subform (SF2):

    Option Compare Database

    Option Explicit

    Private Sub Form_Current()

    Dim strSQL As String

    strSQL = "SELECT * FROM Tbl_OrderLineItems WHERE FK_Tbl_OrderLines = " & Me.PK_Tbl_OrderLines

    If Me.Parent.Ready = True Then

    Me.Parent.Controls("Child_OrderLineItems").Form.RecordSource = strSQL

    Else

    Me.Parent.SQL = strSQL

    End If

    End Sub

    How it works:

    - When the Form_Current event of SF1 occurs for the fist time (i.e. when SF1 is initialized) we don't know whether SF2 is already loaded or not. However we know (from 1 here above) that the Form_Current did not occured yet. The Ready property of the parent form is False at that time and SF1 will store the SQL string in a member variable (m_strSQL) of its parent through the SQL property of this parent.

    - When the Form_Open event occurs form the parent form, we know that the sequence of events Form_Open --> Form_Load --> Form_Current already occured for both subforms (SF1 and SF2). This means that SF1 already stored the SQL string in the member variable of the parent. This SQL string is passed to the RecordSource property of SF2 and the Ready property of the parent is set to True (through its m_booReady member variable).

    - When subsequents Form_Current events occurs for SF1, the Ready property of its parent is True and SF1 can safely send the SQL string to SF2. However, as SF2 is not referenced in the Forms collection of the Application object (see 2 here above), SF1 must get a reference to SF2 though its parent, hence the expression:

    Me.Parent.Controls("Child_OrderLineItems").Form.RecordSource = strSQL

    Have a nice day!

  • "the Ready property of the parent is set to True (through its m_booReady member variable)."

    so in the main form Open event you have

    m_booReady = True

    but then what triggers the main form's Ready property?

    Or is m_booReady = True enough to trigger it?

    Also what is the content of the RecordSource property for SF1?

  • grovelli-262555 (4/9/2014)


    "the Ready property of the parent is set to True (through its m_booReady member variable)."

    so in the main form Open event you have

    m_booReady = True

    but then what triggers the main form's Ready property?

    Or is m_booReady = True enough to trigger it?

    The member variable m_booReady is set to True in the event handler of the Form_Open event of the parent form. This event can only occurs when both subforms have been initialized (i.e. when their Form_Open --> Form_Load --> Form_Current sequence of events is completed).

    grovelli-262555 (4/9/2014)


    Also what is the content of the RecordSource property for SF1?

    The RecordSource for SF1 is SELECT * FROM Tbl_OrderLines;

    Have a nice day!

  • Thanks 🙂

    "This event can only occurs when both subforms have been initialized" and that's for the Open event but what triggers the main form's Ready property?

  • grovelli-262555 (4/10/2014)


    Thanks 🙂

    "This event can only occurs when both subforms have been initialized" and that's for the Open event but what triggers the main form's Ready property?

    Private Sub Form_Open(Cancel As Integer)

    m_booReady = True

    Me.Child_OrderLineItems.Form.RecordSource = m_strSQL

    End Sub

    And:

    Public Property Get Ready() As Boolean

    Ready = m_booReady

    End Property

    Have a nice day!

  • Thank you again

    what calls the Public Property Get Ready()?

    Is the process of setting

    m_booReady = True

    in the main form Open event

    enough to call it?

  • m_booReady is what's usually called a member variable, meaning that:

    1. It is declared outside any procedure in the module.

    2. It is declared as Private.

    The consequence is that such a variable is available from any procedure in the module where it is declared but cannot be accessed from outside this module.

    The Ready property acts as an interface to provide the value of m_booReady to any procedure calling from outside the module of the parent form. It is called by the Form_Current event handler of SF1. Is the Ready property returns True, that means that the Form_Open event handler of the parent form has been executed, which cannot occur before all subforms (SF and SF2) are initialized.

    That way, we know whether SF1.Form_Current can address the RecordSource property of SF2 or not. If Parent.Ready is False, that mean that SF2 probably is not loaded yet (or at least that the whole compound Parent + Subforms is not totally initialized). In such a case, we store the SQL string that must be used as the RecordSource for SF2 into another property of the parent: SQL.

    When the Form_Open event handler of the parent will be processed, this SQL string (stored in the member variable m_strSQL in the parent form) will be sent to SF2. Without that precaution, SF2 would be left uninitialized (i.e. blank RecordSource) when the form is open and will only receive its RecordSource property when the next Form_Current event occurs for the parent form.

    Have a nice day!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply