Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

reference recordsource on subform Expand / Collapse
Author
Message
Posted Thursday, March 27, 2014 3:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:40 AM
Points: 150, Visits: 393
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



Post #1555675
Posted Thursday, March 27, 2014 3:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:30 AM
Points: 146, Visits: 535
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!
Post #1555683
Posted Thursday, March 27, 2014 6:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:40 AM
Points: 150, Visits: 393
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.


Post #1555718
Posted Sunday, April 6, 2014 2:15 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 8:49 PM
Points: 773, Visits: 4,971
Grasshopper,

There's a really good article on referring to controls and their properties on the dying website AccessWeb, which is here: 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.
Post #1558859
Posted Tuesday, April 8, 2014 7:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:40 AM
Points: 150, Visits: 393
Thanks for the link, it has good information there for what I was looking for and more!


Post #1559474
Posted Wednesday, April 9, 2014 1:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:23 AM
Points: 84, Visits: 1,028
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!
Post #1559814
Posted Wednesday, April 9, 2014 5:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:17 PM
Points: 100, Visits: 794
"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?
Post #1560216
Posted Thursday, April 10, 2014 12:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:23 AM
Points: 84, Visits: 1,028
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!
Post #1560263
Posted Thursday, April 10, 2014 4:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:17 PM
Points: 100, Visits: 794
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?
Post #1560342
Posted Thursday, April 10, 2014 6:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:23 AM
Points: 84, Visits: 1,028
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!
Post #1560395
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse