March 27, 2014 at 3:25 pm
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
March 27, 2014 at 3:51 pm
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!
March 27, 2014 at 6:08 pm
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.
April 6, 2014 at 2:15 pm
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.
April 8, 2014 at 7:10 am
Thanks for the link, it has good information there for what I was looking for and more!
April 9, 2014 at 1:39 am
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!
April 9, 2014 at 5:56 pm
"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?
April 10, 2014 at 12:15 am
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!
April 10, 2014 at 4:25 am
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?
April 10, 2014 at 6:38 am
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!
April 10, 2014 at 6:51 am
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?
April 10, 2014 at 7:31 am
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