Crosstab Query Subform - Criteria from Combo Box on Main Form?

[Guest]

I'm trying to figure out how to (or if I can) have a Crosstab query datasheet
subform accept a selection criteria parameter from a Combo Box on the main
form.

I have a Main Form [frmOrgMaster] with 6 subforms, each on a tab.
[frmOrgMaster] has 2 Combo Boxes. You select a County in the
first Combo Box [cboCounties]. The [cboCounties] AfterUpdate Event Procedure
then requeries the second Combo Box [cboOrgs] which produces a list of
nonprofits in that County - and you choose one of them.

When you choose a specific nonprofit in [cboOrgs] the 6 subforms are
synchronized because each subform's LinkMasterFields property is set to
[cboOrgs].

But - I can't get one to work - [frmsubServices] which shows the services
provided by that nonprofit over the past several years. This subform has a
crosstab query datasheet that has years as columns, and specific services as
rows. But by itself this crosstab queries the entire database of 5000
nonprofits and produces a crosstab that has every service that any nonprofit
provides. I want to limit this crosstab only to the nonprofit chosen in
[cboOrgs].

Following a KB article (that wasn't about exactly what I'm trying to do) I
opened the Query/Parameters dialog for the crosstab query and set the
parameter as [Forms]![frmOrgMaster]![cboOrgs], with the appropriate data type.

I get what appears to be a dreaded error message - "You can't use a
pass-through query or non fixed column crosstab query as a record source for
a subform ..." Message tells me to set the query's ColumnHeadings property
before I bind the subform to a crosstab query.

I'm confused. Can someone help? Thanks

John D

 

[Guest]

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"

 

[Guest]

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"

Thanks Duane. I think I'm getting there, but I've run into a roadblock. I
get an error message "Run-time error '3265'" Item not found in this
collection".

I've got two questions. The first and major issue is - what do I need to
change to get the code to execute? The second is why couldn't I put a WHERE
clause in the code without putting the "If ... Then ..." statement around it
(see below)?

I've entered (as best I can) appropriate code in the AfterUpdate event for
my 2nd Combo Box. When I open the main form, I select a county in the first
Box [cboCounties]. That limits the organization names available in the 2nd
box to those in that County. So far, so good. But when I select an
organization in [cboOrgs] I get the run time error.

When I click "debug" the VB editor opens and highlights this line:

Set qd = db.QueryDefs(strXTabQueryName)

Below is the code I've entered in [cboOrgs_AfterUpdate]. I saw 3 different
aspects of your code I had to modify:

The major change (I think) I had to make - You have an If...Then SQL
statement built around Year such that if Year <> 0 (that is if the user has
chosen an individual year), then you add a WHERE clause to select the Year
chosen by the user. Then, you PIVOT by that Year as the column. My columns
are also years, but I don't want the user to choose which year to display,
but rather to choose which organization for which the crosstab will be run.
Therefore, I always want a WHERE clause to define the Organization, but
Organization Name or ID# will not appear in the results - rows are services
reported and columns are years.

But when I tried to put the WHERE clause in without the "If ... Then ..."
surrounding it I kept getting error messages in the Editor. I finally tried
it with the "If ... Then ..." clause and it "worked". Fact is under my logic
there will never be a case when the " ... Then ..." part of the clause will
"rule" because there will always be an organization chosen. (Don't know why I
couldn't just do a WHERE, and don't know if this has anything to do with my
debug problem. Do you see why?)

The other two changes, I think, are minor (I think). First, I don't need the
"Me.txtSQL = ..." statement because I don't have a field showing the code -
so I 'commented' that line out of the executing code. Second, whereas your
example is showing how to control a dynamically generated crosstab in a
subform, you don't actually have a subform - you have a field on the form
named [subformctrl]. You set the SourceObject of that field. So I tried in my
code to refer to my subform when I set the SourceObject and not a field on my
form. (I don't know if this is relevent because the code has not executed
down to that line yet.)

Here's the code I've got at this point. I'm producing my crosstab on the
results of a saved query - QOrg_S1_AllYrs_ServNumSort. I'm putting the
symbols <???> above and below the lines I think are involved in my questions.

Do you see something I need to change? - thanks.

Private Sub cboOrgs_AfterUpdate()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim strXTabQueryName As String
strXTabQueryName = "qxtbOrgServVsYr"
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 Me.cboCounties <> Null 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
' Me.txtSQL = strSQL - this was in Duane's code, but it was populating a
field on his form to show the code - not necessary for me
Set qd = Nothing
Set db = Nothing
' Me.subformctrl.SourceObject = "Query." & strXTabQueryName - also in
Duane's code, but it was for a form field named
' "subformctrl" - whereas I need to define the SourceObject of a subform
on a subform
<???>
Me.[FA1s5b_SCOSrvcs].SourceObject = "Query." & strXTabQueryName
<???>
End Sub

 

[Guest]

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"

Duane - I wrote a reply about an hour ago and thought I posted it - but it
hasn't shown up here. If it does show up - disregard this one. (Could the
Super Bowl cause Microsoft's servers to jam?)

Thanks - I downloaded the sample db, tried to apply your code, but I hit a
problem. (My code is at the bottom of this post after I pose my questions).

After I select a value in the Combo Box I get a run time error - "3265, Item
not found in this collection". When I click "Debug" and go into the VB
Editor, this line is highlighted:

Set qd = db.QueryDefs(strXTabQueryName)

First and major question - what do I need to do?

I tried to adapt your code to my situation. I ran into 3 issues as I adapted
your code:

First issue is my Second question - I wasn't able to take your "If ... Then
...." clause out and wonder why not?

You need your IF Then because a users choice may be "All Years" in which
case the "test" results in False, resulting in the WHERE clause not being put
into the SQL code. In contrast, I will always need a WHERE clause because the
user must select a specific organization in [cboOrgs], and the crosstab must
be run with that selection criterion. I tried to enter my WHERE clause
without the If..Then. But the VB Editor would not let me get beyond that
section of code until I put the "If ... Then ..." test back in, even though
the test will always result in True.

Do you know why - or what I should do to change it?

Second - I "noted" your reference to "Me.txtSQL - strSQL" out because I'm
not posting the SQL string to a field to be read.

Third - your last line is "Me.subformctrl.SourceObject = ...". In your db
subformctrl is a field; in mine it's a subform (actually a subform on a
subform - 2 levels down from the master form that has the Combo Box). I tried
to reference the subform, but I don't know if I did it correctly because the
code stops at the Run Time error noted above.

Here's my code: ([QOrg_S1_AllYrs_ServNumSort] is a saved query that is the
data source for the crosstab.)


Private Sub cboOrgs_AfterUpdate()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim strXTabQueryName As String
strXTabQueryName = "qxtbOrgServVsYr"
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 Me.cboCounties <> Null 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
' Me.txtSQL = strSQL - this was in Duane's code, but it was populating a
field on his form to show the code - not necessary for me
Set qd = Nothing
Set db = Nothing
' Me.subformctrl.SourceObject = "Query." & strXTabQueryName - also in
Duane's code, but it was for a form field named
' "subformctrl" - whereas I need to define the SourceObject of a subform
on a subform
Me.[FA1s5b_SCOSrvcs].SourceObject = "Query." & strXTabQueryName

End Sub

 

[Guest]

You stated:
"you don't actually have a subform - you have a field on the form named
[subformctrl]"
Forms don't have fields on them. They have various "controls" on them. The
control on my form named "subformctrl" is a subform.

This code will not work as desired:
"If Me.cboCounties <> Null Then"
You can't compare any value to Null. Null doesn't even equal Null. You need
to use:
If Not IsNull(Me.cboCounties) Null Then

Do you have a saved query named "qxtbOrgServVsYr"?
--
Duane Hookom
Microsoft Access MVP

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"

Thanks Duane. I think I'm getting there, but I've run into a roadblock. I
get an error message "Run-time error '3265'" Item not found in this
collection".

I've got two questions. The first and major issue is - what do I need to
change to get the code to execute? The second is why couldn't I put a WHERE
clause in the code without putting the "If ... Then ..." statement around it
(see below)?

I've entered (as best I can) appropriate code in the AfterUpdate event for
my 2nd Combo Box. When I open the main form, I select a county in the first
Box [cboCounties]. That limits the organization names available in the 2nd
box to those in that County. So far, so good. But when I select an
organization in [cboOrgs] I get the run time error.

When I click "debug" the VB editor opens and highlights this line:

Set qd = db.QueryDefs(strXTabQueryName)

Below is the code I've entered in [cboOrgs_AfterUpdate]. I saw 3 different
aspects of your code I had to modify:

The major change (I think) I had to make - You have an If...Then SQL
statement built around Year such that if Year <> 0 (that is if the user has
chosen an individual year), then you add a WHERE clause to select the Year
chosen by the user. Then, you PIVOT by that Year as the column. My columns
are also years, but I don't want the user to choose which year to display,
but rather to choose which organization for which the crosstab will be run.
Therefore, I always want a WHERE clause to define the Organization, but
Organization Name or ID# will not appear in the results - rows are services
reported and columns are years.

But when I tried to put the WHERE clause in without the "If ... Then ..."
surrounding it I kept getting error messages in the Editor. I finally tried
it with the "If ... Then ..." clause and it "worked". Fact is under my logic
there will never be a case when the " ... Then ..." part of the clause will
"rule" because there will always be an organization chosen. (Don't know why I
couldn't just do a WHERE, and don't know if this has anything to do with my
debug problem. Do you see why?)

The other two changes, I think, are minor (I think). First, I don't need the
"Me.txtSQL = ..." statement because I don't have a field showing the code -
so I 'commented' that line out of the executing code. Second, whereas your
example is showing how to control a dynamically generated crosstab in a
subform, you don't actually have a subform - you have a field on the form
named [subformctrl]. You set the SourceObject of that field. So I tried in my
code to refer to my subform when I set the SourceObject and not a field on my
form. (I don't know if this is relevent because the code has not executed
down to that line yet.)

Here's the code I've got at this point. I'm producing my crosstab on the
results of a saved query - QOrg_S1_AllYrs_ServNumSort. I'm putting the
symbols <???> above and below the lines I think are involved in my questions.

Do you see something I need to change? - thanks.

Private Sub cboOrgs_AfterUpdate()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim strXTabQueryName As String
strXTabQueryName = "qxtbOrgServVsYr"
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 Me.cboCounties <> Null 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
' Me.txtSQL = strSQL - this was in Duane's code, but it was populating a
field on his form to show the code - not necessary for me
Set qd = Nothing
Set db = Nothing
' Me.subformctrl.SourceObject = "Query." & strXTabQueryName - also in
Duane's code, but it was for a form field named
' "subformctrl" - whereas I need to define the SourceObject of a subform
on a subform
<???>
Me.[FA1s5b_SCOSrvcs].SourceObject = "Query." & strXTabQueryName
<???>
End Sub

 

[Guest]

Close - but no ceegar yet.

My apology - I'm still at an early stage of learning VBA and other
programming aspects of Access. Since I'd learned to create a form from the
Forms Design window and then "drop" it into another form to make it a
subform, I (incorrectly) assumed I'd see the name of any subform in the Forms
window. Wrong. I (think I)now see you "programatically created" (is that the
right word?) the subform with the "Me.subformctrl.SourceObject ..."
statement. Mea culpa. : |

And - I keep biting myself with that "Null" issue - thanks.

And - I now see (with your prod) that I needed a saved query named
"qxtb...". Now I do (the name is [FA1s4_AllYrs_ServNumSort_Crosstab]).

But - still not there.

I can't create the subform on the "face" of the mainform. I need to have the
crosstab show up on part of an existing subform [FA1s_Services] of the
mainform [FA1_OrgMaster_All]. Can I do that by modifying your code? I have
other items on that subform - so I need to specify where the crosstab would
go. (I have 6 subforms each showing a different info set about each
organization - people, locations, services, etc.)

Thanks - John D

 

[Guest]

The code can't be much simpler. You can start with any subform control
created on your main form. You then use 1 line of code to set the Source
Object of the subform control to the saved Crosstab query. This subform
control can only display the whole crosstab and nothing but the crosstab.

 

[Guest]

The code can't be much simpler. You can start with any subform control

created on your main form. You then use 1 line of code to set the Source
Object of the subform control to the saved Crosstab query. This subform
control can only display the whole crosstab and nothing but the crosstab.
--

I'm sorry Duane - I'm learning a lot of humility as I try to find my way
through what today seems like a swamp.

* Where * and * How * do I use the 1 line of code to set the Source Object
of the subform control to the saved Crosstab query? I could think of a couple
of options:

I assume it's in the AfterUpdate Event procedure of my [cboOrgs] Combo Box -
where you had -

Me.subformctrl.SourceObject = "Query.qxtbYourCrosstabName"

- in your first reply to me. If that's true, * how * do I refer to the
subform? I can't just put "subformctrl", right? Here's the hierarchy of forms:

MASTER: FA1_OrgMaster
1st Sub: Fa1s5a_Services
2nd Sub: FA1s5b_SCOSrvcs

The query that is the record source for the 2nd subform is
[QFA1s5b_SCOSrvcs] (changed the name).

Sorry to be so dense. Thanks for your time.

John D

 

[Guest]

Duane - I appreciate your help, but although I'm close - I think - it still
isn't working. I have a deadline. So, fearing that this thread was too far
down the list to get attention, I posted another question just now (2/7 -
6:10PM PST).

Thanks for your help.

John D

 

[Guest]

In review. You should be able to write code to modify the SQL of the saved
crosstab query. For instance, I created a crosstab
"qxtbCustomerFreightByYear" in the Northwind. I then created a form with a
combo box cboCust with a row source of SELECT CustomerID from Customers ORDER
BY CustomerID;

The form has a subform control named "subform". I want the subform to
display the results of qxtbCustomerFreightByYear in datasheet view. The
following is the code I placed in the After Update event of the cboCust combo
box.

Private Sub cboCust_AfterUpdate()
Dim strSQL As String
strSQL = "TRANSFORM Sum(Orders.Freight) AS SumOfFreight1 " & _
"SELECT Orders.CustomerID, Sum(Orders.Freight) AS SumOfFreight " & _
"FROM Orders " & _
"WHERE CustomerID = '" & Me.cboCust & "'" & _
"GROUP BY Orders.CustomerID " & _
"PIVOT Year([OrderDate]); "
CurrentDb.QueryDefs("qxtbCustomerFreightByYear").SQL = strSQL

Me.subform.SourceObject = "Query.qxtbCustomerFreightByYear"
End Sub