Requery Sub-Subform Crosstab

[John D]

I have a Master Form, 6 subforms, and another subform on one of those subforms:

MASTER: FA1_OrgMaster
1st Sub: Fa1s5a_Services (and 5 others - things like locations, key
people, etc)
2nd Sub: FA1s5b_RptSrvcs (showing a crosstab query results)

On FA1_OrgMaster I have a Combo Box - cboOrgs - where user selects nonprofit
organization name. The 6 subforms are updated through the subform Link
Child/Master Fields properties.

But I can't get the 2nd Sub with the crosstab to "work".

The 2nd sub crosstab shows what services the nonprofit reported to the state
(the rows) in the previous 3 years (the columns). Next to that subform on
Fa1s5a_Services users will record other services they know the nonprofit
provides that are not reported to the state.

The SQL for the crosstab is:

TRANSFORM First(QOrg_S1_AllYrs_ServNumSort.Y_N) AS FirstOfY_N
SELECT QOrg_S1_AllYrs_ServNumSort.Service
FROM QOrg_S1_AllYrs_ServNumSort
WHERE (((QOrg_S1_AllYrs_ServNumSort.OrgID)=3000))
GROUP BY QOrg_S1_AllYrs_ServNumSort.Service, QOrg_S1_AllYrs_ServNumSort.OrgID
PIVOT QOrg_S1_AllYrs_ServNumSort.Year;

QOrg_S1_AllYrs_ServNumSort is a query on which this crosstab query is based.
In this case, the WHERE clause selects OrgID = 3000, a particular nonprofit.
What I want to do, in effect, is have that "3000" actually be whatever value
is in the cboOrgs Combo Box in the Master Form and have the crosstab display
updated whenever a new Org is chosen in cboOrgs.

I was refered to a solution for a crosstab as a subform created
programatically directly on a master form:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I adapted this code (as shown below). Glory be, it does change the
underlying crosstab query. But I can't figure out how to get the
FA1s5b_RptSrvcs to be updated when cboOrgs changes.

I've tried 5 or 6 things I can think of, and so far nothing works. These
include -

I can't figure out how to programatically create the dynamic crosstab as a
subform on the Services subform (much less how to position it on that
subform).

I've tried to get the 1st and 2nd subforms in synch by specifying the Link
Child Field as OrgID and the Master Field as cboOrgs, but a get an error
message that Access doesn't recognize OrgID as a valid field name. It
suggests I declare OrgID as a parameter and change the ColumnHeadings
Property - but OrgID is not a Column Heading in the crosstab. No go.

I can't seem to get the 2nd subform - FA1s5b_RptSrvcs - to be requeried in
the AfterUpdate property of cboOrgs.

Etc.

Here's how far I've gotten on the code:
_________
Private Sub cboOrgs_AfterUpdate()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim strXTabQueryName As String
strXTabQueryName = "QFA1s4_AllYrs_ServNumSort_Crosstab"
Set db = CurrentDb
Set qd = db.QueryDefs(strXTabQueryName)
strSQL = "TRANSFORM First(QOrg_S1_AllYrs_ServNumSort.Y_N) AS FirstOfY_N"
& vbCrLf & _
"SELECT QOrg_S1_AllYrs_ServNumSort.Service" & vbCrLf & _
"FROM QOrg_S1_AllYrs_ServNumSort" & vbCrLf
If Not IsNull(Me.cboCounties) Then
strSQL = strSQL & "WHERE (QOrg_S1_AllYrs_ServNumSort.OrgID) = " &
[Forms]![FA1_OrgMaster_All]![cboOrgs] & vbCrLf
End If
strSQL = strSQL & " " & _
"GROUP BY QOrg_S1_AllYrs_ServNumSort.Service " & vbCrLf & _
"PIVOT QOrg_S1_AllYrs_ServNumSort.Year;"
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing

‘ Here’s why I’m stuck and have tried a bunch of things, including:

' Me.FA1s5b_SCOSrvcs.Requery
' Forms!FA1s5b_SCOSrvcs.SourceObject = "Query." & strXTabQueryName
‘ Etc


End Sub
____________

I'm working through 2 books on VBA in Access, but this is way beyond me
right now. Can you help?

Thanks

John D

 

[Duane Hookom]

I replied in the previous thread. I think you need to remove all parameters
from all queries and replace them with values. This can be done by updating
the SQL property of an query or source query. Once you have queries that no
longer depend on form controls you can set the SourceObject of your subform
control to the query name like:
FA1s5b_SCOSrvcs.SourceObject = "Query.QFA1s4_AllYrs_ServNumSort_Crosstab"
--
Duane Hookom
Microsoft Access MVP

I have a Master Form, 6 subforms, and another subform on one of those subforms:

MASTER: FA1_OrgMaster
1st Sub: Fa1s5a_Services (and 5 others - things like locations, key
people, etc)
2nd Sub: FA1s5b_RptSrvcs (showing a crosstab query results)

On FA1_OrgMaster I have a Combo Box - cboOrgs - where user selects nonprofit
organization name. The 6 subforms are updated through the subform Link
Child/Master Fields properties.

But I can't get the 2nd Sub with the crosstab to "work".

The 2nd sub crosstab shows what services the nonprofit reported to the state
(the rows) in the previous 3 years (the columns). Next to that subform on
Fa1s5a_Services users will record other services they know the nonprofit
provides that are not reported to the state.

The SQL for the crosstab is:

TRANSFORM First(QOrg_S1_AllYrs_ServNumSort.Y_N) AS FirstOfY_N
SELECT QOrg_S1_AllYrs_ServNumSort.Service
FROM QOrg_S1_AllYrs_ServNumSort
WHERE (((QOrg_S1_AllYrs_ServNumSort.OrgID)=3000))
GROUP BY QOrg_S1_AllYrs_ServNumSort.Service, QOrg_S1_AllYrs_ServNumSort.OrgID
PIVOT QOrg_S1_AllYrs_ServNumSort.Year;

QOrg_S1_AllYrs_ServNumSort is a query on which this crosstab query is based.
In this case, the WHERE clause selects OrgID = 3000, a particular nonprofit.
What I want to do, in effect, is have that "3000" actually be whatever value
is in the cboOrgs Combo Box in the Master Form and have the crosstab display
updated whenever a new Org is chosen in cboOrgs.

I was refered to a solution for a crosstab as a subform created
programatically directly on a master form:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I adapted this code (as shown below). Glory be, it does change the
underlying crosstab query. But I can't figure out how to get the
FA1s5b_RptSrvcs to be updated when cboOrgs changes.

I've tried 5 or 6 things I can think of, and so far nothing works. These
include -

I can't figure out how to programatically create the dynamic crosstab as a
subform on the Services subform (much less how to position it on that
subform).

I've tried to get the 1st and 2nd subforms in synch by specifying the Link
Child Field as OrgID and the Master Field as cboOrgs, but a get an error
message that Access doesn't recognize OrgID as a valid field name. It
suggests I declare OrgID as a parameter and change the ColumnHeadings
Property - but OrgID is not a Column Heading in the crosstab. No go.

I can't seem to get the 2nd subform - FA1s5b_RptSrvcs - to be requeried in
the AfterUpdate property of cboOrgs.

Etc.

Here's how far I've gotten on the code:
_________
Private Sub cboOrgs_AfterUpdate()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim strXTabQueryName As String
strXTabQueryName = "QFA1s4_AllYrs_ServNumSort_Crosstab"
Set db = CurrentDb
Set qd = db.QueryDefs(strXTabQueryName)
strSQL = "TRANSFORM First(QOrg_S1_AllYrs_ServNumSort.Y_N) AS FirstOfY_N"
& vbCrLf & _
"SELECT QOrg_S1_AllYrs_ServNumSort.Service" & vbCrLf & _
"FROM QOrg_S1_AllYrs_ServNumSort" & vbCrLf
If Not IsNull(Me.cboCounties) Then
strSQL = strSQL & "WHERE (QOrg_S1_AllYrs_ServNumSort.OrgID) = " &
[Forms]![FA1_OrgMaster_All]![cboOrgs] & vbCrLf
End If
strSQL = strSQL & " " & _
"GROUP BY QOrg_S1_AllYrs_ServNumSort.Service " & vbCrLf & _
"PIVOT QOrg_S1_AllYrs_ServNumSort.Year;"
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing

‘ Here’s why I’m stuck and have tried a bunch of things, including:

' Me.FA1s5b_SCOSrvcs.Requery
' Forms!FA1s5b_SCOSrvcs.SourceObject = "Query." & strXTabQueryName
‘ Etc


End Sub
____________

I'm working through 2 books on VBA in Access, but this is way beyond me
right now. Can you help?

Thanks

John D

 

[John D]

Duane - Everything seems to be working EXCEPT for the last line - here's what
I have in the AfterUpdate Event of the Combo Box:

Private Sub cboOrgs_AfterUpdate()

Dim strSQL As String
strSQL = "TRANSFORM First(QOrg_S1_AllYrs_ServNumSort.Y_N) AS FirstOfY_N"
& vbCrLf & _
"SELECT QOrg_S1_AllYrs_ServNumSort.Service" & vbCrLf & _
"FROM QOrg_S1_AllYrs_ServNumSort" & vbCrLf
If Not IsNull(Me.cboCounties) Then
strSQL = strSQL & "WHERE (QOrg_S1_AllYrs_ServNumSort.OrgID) = " &
[Forms]![FA1_OrgMaster_All]![cboOrgs] & vbCrLf
End If
strSQL = strSQL & " " & _
"GROUP BY QOrg_S1_AllYrs_ServNumSort.Service " & vbCrLf & _
"PIVOT QOrg_S1_AllYrs_ServNumSort.Year;"

CurrentDb.QueryDefs("QFA1s5sa_AllYrs_ServNumSort_Crosstab").SQL = strSQL

FA1s5b_SCOSrvcs.SourceObject =
"Query.QFA1s5sa_AllYrs_ServNumSort_Crosstab"

End Sub

The last line won't execute ("FA2s5b_SCOSrvcs.SourceObject ..."). (I changed
the name of the query slightly from before.)

The underlying query IS being changed by the code above - that is, if I
select a new organization in cboOrgs, then run the saved query, it has the
correct information for the newly chosen org.

Can you see what I need to do? Thanks - John D

 

[Duane Hookom]

Is OrgID numeric or text? If text, you would need to change the code to:
If Not IsNull(Me.cboCounties) Then
strSQL = strSQL & "WHERE (QOrg_S1_AllYrs_ServNumSort.OrgID) = """ &
[Forms]![FA1_OrgMaster_All]![cboOrgs] & """ " & vbCrLf
End If

Otherwise can you open the crosstab after the SQL is changed?

Does QOrg_S1_AllYrs_ServNumSort have any criteria or parameters?

 

[John D]

Duane -

Is OrgID numeric or text?

* NUMERIC *

Otherwise can you open the crosstab after the SQL is changed?

* YES* - and the SQL has the new OrgID chosen in cboOrgs - this works.

Does QOrg_S1_AllYrs_ServNumSort have any criteria or parameters?

* NO *


The SourceObject property of the subform control "SCOReportedServices" is
"Query.QFA1s5sa_AllYrs_ServNumSort_Crosstab"; I did that directly on the
Property Sheet - not in the code. If I put

Me.SCOReportedServices.SourceObject =
"Query.QFA1s5sa_AllYrs_ServNumSort_Crosstab"

as the last line of the code, then choose a new OrgID in cboOrgs I get a
"Method or Data Member not found" message and " .SCOReportedServices " is
highlighted.


I THINK the last thing I need to do in the code is requery the subform
control "SCOReportedServices".

1) IS THIS CORRECT?

2) Since "SCOReportedServices" is a subform control that is itself on a
subform in a tab control, do I need to define the "path" to
"SCOReportedServices" - that is, somehow include the name of the "1st level
subform control" - "subctrlServices"?

If this line of thinking is correct (need to requery "SCOReportedServices"),
what code do I need to put in the cboOrgs AfterUpdate Event Procedure?


Thanks

 

[Duane Hookom]

I am not sure what is happening with your app. I have been playing with this
in Northwind. I have an unbound subform control on a form. The form has a
combo box named cboLetter with a Row Source of:
SELECT Left([CustomerID],1) AS Expr1
FROM Customers GROUP BY Left([CustomerID],1);

My code in the After Update event of the combo box is:

Private Sub cboLetter_AfterUpdate()
Dim strSQL As String
strSQL = "TRANSFORM Sum(Orders.Freight) AS SumOfFreight1 " & _
"SELECT Orders.CustomerID, Sum(Orders.Freight) AS SumOfFreight " & _
"from Orders " & _
"WHERE CustomerID Like '" & Me.[cboLetter] & "*'" & _
"GROUP BY Orders.CustomerID " & _
"PIVOT Year([OrderDate]); "
CurrentDb.QueryDefs("qxtbCustomerFreightByYear").SQL = strSQL
Me.subform.SourceObject = "Query.qxtbCustomerFreightByYear"
End Sub

This all works as expected. Can you determine how your's if different?

 

[John D]

Duane - something significant just happened. I cut the subform control
(SCOReportedServices) from the subform on which it has been located and
pasted it directly onto the Master Form. Lo and Behold - IT WORKED!!!

Somehow I'm not correctly "referencing" or "naming the path" to
(SCOReportedServices) when it's in my perferred location - a subform on a
subform:

Here's the relationship between the Master Form that has (cboOrgs) on it,
and the Sub Form that I want the crosstab to be on:


MASTER FORM: - (Form) that has (cboOrgs) on it
A Tab Control - (TabCtlOrgSubs) - that has 6 Page Controls on it
One of 6 Page Controls - (OrgSubServices)
1 Subform Control - (subctrlServices)
1 Subform Control - (SCOReportedServices)

Now - when (SCOReportedServices) is directly on the Master Form, this line
of code "works" to produce the desired results:

Me.SCOReportedServices.SourceObject =
"Query.QFA1s5sa_AllYrs_ServNumSort_Crosstab"

BUT, when (SCOReportedServices) is located on (subctrlServices), that line
of code does NOT "work".

I think the question is - "How do I write this last line of code setting the
SourceObject when (SCOReportedServices) is located several "layers" down from
the Master Form?

 

[Duane Hookom]

Somehow I missed the fact the crosstab sub was on another subform. The code
would need to be something like:
Me.FirstSFCtlName.Form.SecondSFCtlName.SourceObject

 

[John D]

Halleluiah, Glory Be!!! It works. Thanks - thanks - thanks. (That took a
while.)

A little detail - I tried to hide the crosstab in the Master Form's OnOpen
property because the underlying SQL will be for the last OrgID that was
chosen, and when a user opens the form they haven't yet chosen a new OrgID.
But this didn't work:

Me.subctrlServices.Form.SCOReportedServices.Hide

Off the top of your head - do you know how I should code that?

And, I assume I'd put " ... . Show" in the AfterUpdate event procedure for
cboOrgs?

Thanks for sticking with me Duane. All's well that ends well.

John D

 

[Duane Hookom]

I have just deleted the SourceObject property value in design view and then
saved the form. Whenever the form is open, there is nothing there. You could
also use the Visible property of the subform control:
Me.sfrmOne.Form.sfrmTwo.Visible = False
Your code would then need to set the Visible property to True.

 

[John D]

Duane - 2 things.

1) When I put:

Me.subctrlServices.Form.SCOReportedServices.Visible = False

into the Form's Open Event, I get an error message saying I can't hide a
control that has the focus. But SCOReportedServices does not have the focus
as far as I can tell - cboCounties on the Main Form does. Do you see what's
wrong?

2) I want to "give you credit" for answering my question and have one
posting for others to read, but the answer is a composite of several
responses over both threads. I could put "the answer" in 1 reply - but I
can't mark my own posting as "answering the question". So - if you think it's
good to have one post with the answer, I think the following is it. If you
post this (or whatever is correct) as a reply - I'll check it as answering my
question.
_______

You might want to just set the SourceObject of the subform control to the
query name. There is a sample of how this is done at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

The significant code is:
Me.subformctrl.SourceObject = "Query.qxtbYourCrosstabName"

I have been playing with your situation in Northwind. I have an unbound
subform control on a form. The form has a combo box named cboLetter with a
Row Source of:
SELECT Left([CustomerID],1) AS Expr1
FROM Customers GROUP BY Left([CustomerID],1);

My code in the After Update event of the combo box is:

Private Sub cboLetter_AfterUpdate()
Dim strSQL As String
strSQL = "TRANSFORM Sum(Orders.Freight) AS SumOfFreight1 " & _
"SELECT Orders.CustomerID, Sum(Orders.Freight) AS SumOfFreight " & _
"from Orders " & _
"WHERE CustomerID Like '" & Me.[cboLetter] & "*'" & _
"GROUP BY Orders.CustomerID " & _
"PIVOT Year([OrderDate]); "
CurrentDb.QueryDefs("qxtbCustomerFreightByYear").SQL = strSQL
Me.subform.SourceObject = "Query.qxtbCustomerFreightByYear"
End Sub

Because your crosstab sub is on another subform. The last line would need to
be something like:

Me.FirstSFCtlName.Form.SecondSFCtlName.SourceObject

________________________

 

[Duane Hookom]

I think each open form or subform might have a focus. Did you try delete the
source object as I suggested?

--
Duane Hookom
Microsoft Access MVP

Duane - 2 things.

1) When I put:

Me.subctrlServices.Form.SCOReportedServices.Visible = False

into the Form's Open Event, I get an error message saying I can't hide a
control that has the focus. But SCOReportedServices does not have the focus
as far as I can tell - cboCounties on the Main Form does. Do you see what's
wrong?

2) I want to "give you credit" for answering my question and have one
posting for others to read, but the answer is a composite of several
responses over both threads. I could put "the answer" in 1 reply - but I
can't mark my own posting as "answering the question". So - if you think it's
good to have one post with the answer, I think the following is it. If you
post this (or whatever is correct) as a reply - I'll check it as answering my
question.
_______

You might want to just set the SourceObject of the subform control to the
query name. There is a sample of how this is done at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

The significant code is:
Me.subformctrl.SourceObject = "Query.qxtbYourCrosstabName"

I have been playing with your situation in Northwind. I have an unbound
subform control on a form. The form has a combo box named cboLetter with a
Row Source of:
SELECT Left([CustomerID],1) AS Expr1
FROM Customers GROUP BY Left([CustomerID],1);

My code in the After Update event of the combo box is:

Private Sub cboLetter_AfterUpdate()
Dim strSQL As String
strSQL = "TRANSFORM Sum(Orders.Freight) AS SumOfFreight1 " & _
"SELECT Orders.CustomerID, Sum(Orders.Freight) AS SumOfFreight " & _
"from Orders " & _
"WHERE CustomerID Like '" & Me.[cboLetter] & "*'" & _
"GROUP BY Orders.CustomerID " & _
"PIVOT Year([OrderDate]); "
CurrentDb.QueryDefs("qxtbCustomerFreightByYear").SQL = strSQL
Me.subform.SourceObject = "Query.qxtbCustomerFreightByYear"
End Sub

Because your crosstab sub is on another subform. The last line would need to
be something like:

Me.FirstSFCtlName.Form.SecondSFCtlName.SourceObject

________________________

I have just deleted the SourceObject property value in design view and then
saved the form. Whenever the form is open, there is nothing there. You could
also use the Visible property of the subform control:
Me.sfrmOne.Form.sfrmTwo.Visible = False
Your code would then need to set the Visible property to True.

 

[John D]

Did you try delete the

source object as I suggested?

I put:

Me.subctrlServices.Form.SCOReportedServices.SourceObject = ""

in both the Form_Open Event and the cobCounties_AfterUpdate Event and it
works like a charm. Thanks.

Do you want to copy/paste the "answer" from my previous post for me to flag
as "answering the question"? Otherwise I'll click the several posts that
together got to the answer.

Thanks for your help Duane - John D

 

[Duane Hookom]

Glad to hear you got all this to work.

Check whichever posts you think appropriate.