Best way to link subforms together

I have a main form that is unbound.  Within that main form I have 4 subforms, 2 that are continuous forms (ContMain and Cont2) and 2 that are pivotcharts (PC1 and PC2).  Subform ContMain has a list of items that when one row is selected, the other three subforms should update.

I have Cont2 updating based on two text boxes on the main form that have their control source linked to the appropriate fields on ContMain.  When a new row is selected in ContMain, the two text boxes on the main form change to reflect the linked fields.  Cont2 has its Link Master and Link Child fields linked to the text boxes on the main form.

Is this the best way to handle this or would it be better to use VBA and have the other subforms update using the OnCurrent event of ContMain?
SDKCompanyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pdebaetsCommented:
Sounds like you have it all worked out with no VBA code. That is often the best way.
0
IrogSintaCommented:
Personally, I use this method for small recordsets, but for large recordsets where I would notice a slowdown, I would have the subform recordsets be based of the item selected and just requery the other subforms in the OnCurrent event.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SDKCompanyAuthor Commented:
I have updated my form to use the OnCurrent event of the subform and it seems to update Cont2 subform quicker.

Does anyone know how I can programmatically change the filter values of the pivotcharts?  I have two fields that are being filtered, Period and Trend.  I need to pass those specific values (ie 3, WN) from ContMain to PC1 and PC2 based on the current record.  I have searched this forum and Google and I can not find specifically how to do this.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pdebaetsCommented:
I recall that getting charts to dynamically change was tricky. You might want to base your chart off of a saved query, then in the controlling subform OnCurrent event procedure, modify the SQL of the saved query to include your new criteria, then close and re-open the chart by setting the chart subform SourceObject property to "" (empty string), then set it back to the name of the chart form. This will re-open the chart based on the updated saved query.
0
Jeffrey CoachmanMIS LiasonCommented:
<aside>

Note that "Pivots" are deprecated (not available anymore) in access 2013
0
SDKCompanyAuthor Commented:
What is the best way to filter my charts if pivots are deprecated?
0
Jeffrey CoachmanMIS LiasonCommented:
My post was just an aside, ...The previous experts can help you with your issue as it stands now.

Then you can post a new question about filtering in Acc 2013.
0
SDKCompanyAuthor Commented:
OK, so here is my code that is in the OnCurrent event of the controlling subform (ContMain per the example above).  You will notice that the code below contains the actual names of the form and subforms that are being used.

Dim intPer As Byte, strTrend As String, strSQL As String, chrtSQL As String
    intPer = Me.Period
    strTrend = Me.Trend
    strSQL = "SELECT tbl_TC_Counts_Final.Period, tbl_TC_Counts_Final.TCMonth, tbl_TC_Counts_Final.Trend, tbl_Methods.Method_Desc FROM tbl_Methods INNER JOIN tbl_TC_Counts_Final ON " _
        & "tbl_Methods.Method_Number = tbl_TC_Counts_Final.Method WHERE tbl_TC_Counts_Final.Period=" & intPer & " AND tbl_TC_Counts_Final.Trend LIKE '" & strTrend & "' ORDER BY tbl_TC_Counts_Final.Period, tbl_TC_Counts_Final.TCMonth DESC;"
    Forms!frm_Main!sfrm_TC_SubList.Form.RecordSource = strSQL
    Forms!frm_Main!sfrm_TC_SubList.Requery
    
    chrtSQL = "SELECT tbl_TC_Counts_Final.Period, tbl_TC_Counts_Final.TCMonth, tbl_TC_Counts_Final.Trend, tbl_TC_Counts_Final.TCCount, tbl_TC_Counts_Final.Average, tbl_TC_Counts_Final.StdDev," _
        & " tbl_TC_Counts_Final.UCL, tbl_TC_Counts_Final.MCL, tbl_TC_Counts_Final.LCL, tbl_TC_Counts_Final.Adverse FROM tbl_TC_Counts_Final WHERE " _
        & "(((tbl_TC_Counts_Final.Period)=" & intPer & ") AND ((tbl_TC_Counts_Final.Trend)='" & strTrend & "'));"
    Forms!frm_Main!sfrm_TC_Upr_Chart.Form.RecordSource = chrtSQL
    Forms!frm_Main!sfrm_TC_Upr_Chart.Requery
    Forms!frm_Main!sfrm_TC_Lwr_Chart.Form.RecordSource = chrtSQL
    Forms!frm_Main!sfrm_TC_Lwr_Chart.Requery

Open in new window

When I load the main form, I get the following error:

Run-time error '2455:  You entered an expression that has an invalid reference to the property Form/Report.

When I click on Debug, it highlights the following piece of code:

Forms!frm_Main!sfrm_TC_SubList.Form.RecordSource = strSQL

Open in new window

However, when I click on End, it loads the form and the subform functions as I would expect.

Does anyone have an idea why it is doing this?
0
IrogSintaCommented:
I don't see why you'd be getting that error. Try using the Parent property instead.
Also, you do not need the requeries since changing the recordsource automatically does that.
Parent.sfrm_TC_SubList.Form.RecordSource = strSQL
chrtSQL = "SELECT tbl_TC_Counts_Final.Period, tbl_TC_Counts_Final.TCMonth, tbl_TC_Counts_Final.Trend, tbl_TC_Counts_Final.TCCount, tbl_TC_Counts_Final.Average, tbl_TC_Counts_Final.StdDev," _
        & " tbl_TC_Counts_Final.UCL, tbl_TC_Counts_Final.MCL, tbl_TC_Counts_Final.LCL, tbl_TC_Counts_Final.Adverse FROM tbl_TC_Counts_Final WHERE " _
        & "(((tbl_TC_Counts_Final.Period)=" & intPer & ") AND ((tbl_TC_Counts_Final.Trend)='" & strTrend & "'));"
Parent.sfrm_TC_Upr_Chart.Form.RecordSource = chrtSQL
Patent.sfrm_TC_Lwr_Chart.Form.RecordSource = chrtSQL

Open in new window

0
SDKCompanyAuthor Commented:
Changing to Parent did not correct the error.  This only happens when the form loads and then it functions perfectly.  While not ideal, until I can figure out why it is throwing the error, I have added On Error Resume Next.  This avoids the error for now.
0
IrogSintaCommented:
This only happens when the form loads
That's because your controls have null values when the form loads.  You may want to specify some default values for Period and Trend in your form's load event.
intPer = nz(Me.Period,1)
strTrend = nz(Me.Trend,"*")

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.