Jesper Fjølner! Phone home!

[Bill R via AccessMonster.com]

Jesper,

You posted a question about displaying a crosstab query in a subform, but I'm
missing an important piece of information. I can't get it to work based on
you explanation in your post. Here's your post:

http://www.accessmonster.com/Uwe/Fo...oding/24422/Display-crosstab-query-in-subform


I followed your instructions to the letter and got a blank subform.

I hope you get this post.

Thanks,

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via AccessMonster.com

 

[Duane Hookom]

Without looking through that thread, you can display a crosstab query
datasheet view in a subform by setting the subform control Source Object to
the crosstab query.
For instance, assume a combo box named cboQueries on a form with a row
source of:
SELECT msysObjects.Name FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND
((msysObjects.Type)=5))
ORDER BY msysObjects.Name;
The form has a subform named fsubOne. The code in the after update event of
the combo box is:
Private Sub cboQueries_AfterUpdate()
If Not IsNull(Me.cboQueries) Then
Me.fsubOne.SourceObject = "Query." & Me.cboQueries
End If
End Sub

 

[Bill R via AccessMonster.com]

Duane,

I have been trying to make that work using the excellent code below (you
posted it to an earlier thread of mine that referenced this thread from
Jesper), but crosstab queries don't display. I've tested it by running the
crosstab query, which displays all the records just fine, but when I try to
assign it as a source object for the subform via your program and forms below,
I get a white space (no error msg).

Bill

Without looking through that thread, you can display a crosstab query
datasheet view in a subform by setting the subform control Source Object to
the crosstab query.
For instance, assume a combo box named cboQueries on a form with a row
source of:
SELECT msysObjects.Name FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND
((msysObjects.Type)=5))
ORDER BY msysObjects.Name;
The form has a subform named fsubOne. The code in the after update event of
the combo box is:
Private Sub cboQueries_AfterUpdate()
If Not IsNull(Me.cboQueries) Then
Me.fsubOne.SourceObject = "Query." & Me.cboQueries
End If
End Sub

Jesper,

[quoted text clipped - 12 lines]

Bill

 

[Duane Hookom]

Would you mind providing some information about your crosstab?

--
Duane Hookom
MS Access MVP
--

Duane,

I have been trying to make that work using the excellent code below (you
posted it to an earlier thread of mine that referenced this thread from
Jesper), but crosstab queries don't display. I've tested it by running the
crosstab query, which displays all the records just fine, but when I try
to
assign it as a source object for the subform via your program and forms
below,
I get a white space (no error msg).

Bill

Without looking through that thread, you can display a crosstab query
datasheet view in a subform by setting the subform control Source Object
to
the crosstab query.
For instance, assume a combo box named cboQueries on a form with a row
source of:
SELECT msysObjects.Name FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND
((msysObjects.Type)=5))
ORDER BY msysObjects.Name;
The form has a subform named fsubOne. The code in the after update event
of
the combo box is:
Private Sub cboQueries_AfterUpdate()
If Not IsNull(Me.cboQueries) Then
Me.fsubOne.SourceObject = "Query." & Me.cboQueries
End If
End Sub

Jesper,

[quoted text clipped - 12 lines]

Bill

 

[Bill R via AccessMonster.com]

Duane,

Please review our previous saga (To paraphrase WC Fields, "never give a saga
an even break"):

http://www.accessmonster.com/Uwe/Fo...umn-heading-in-a-calculation#5a8c96c5835c7uwe


If you need further clarification, I'll be happy to provide it. The upshot of
the whole previous effort was that it worked great for everything BUT a
crosstab query.

Thanks,

Bill

Would you mind providing some information about your crosstab?

Duane,

[quoted text clipped - 33 lines]

 

[Jesper F]

You posted a question about displaying a crosstab query in a subform, but

I'm
missing an important piece of information. I can't get it to work based on
you explanation in your post. Here's your post:
http://www.accessmonster.com/Uwe/Fo...oding/24422/Display-crosstab-query-in-subform
I followed your instructions to the letter and got a blank subform.
I hope you get this post.

Hi Bill,

Sorry for the delay. I think I ended up using a different approach for this
challenge and not using the crosstab solution after all. I think it was
working for a while though. It's a little hazy for me, but I'd be glad to
try to help.
Is the problem that the content of the crosstab is not displaying in the
subform, but it seems to be working when you open it by itself?


Jesper Fjølner

 

[Duane Hookom]

I reviewed the thread and have gone back to the Northwind where I created
the solution. The method correctly displays the crosstab query "Quarterly
Orders by Product".

--
Duane Hookom
MS Access MVP
--

Duane,

Please review our previous saga (To paraphrase WC Fields, "never give a
saga
an even break"):

http://www.accessmonster.com/Uwe/Fo...umn-heading-in-a-calculation#5a8c96c5835c7uwe


If you need further clarification, I'll be happy to provide it. The upshot
of
the whole previous effort was that it worked great for everything BUT a
crosstab query.

Thanks,

Bill

Would you mind providing some information about your crosstab?

Duane,

[quoted text clipped - 33 lines]

Bill

 

[Bill R via AccessMonster.com]

Jesper,

Precisely. The query runs just fine, but does not display in the subform.

I implemented Duane's scenario described in this thread. I added a small
refinement to include tables along with queries. When I make a selection in
the combo box, the query or table displays as a datasheet in the subform. The
only exceptions are crosstab queries.

It's a tantalizing prospect. The only other alternatives are to design a
subform specifically for the crosstab query (requiring a lot of annoying code)
, or compiling a table based on the crosstab query and displaying it as a
datasheet. Then I would have to create these huge tables and maintain them in
the db. Neither prospect is attractive. The crosstab query in a subform
datasheet is the optimal solution.

Bill

You posted a question about displaying a crosstab query in a subform, but
I'm

[quoted text clipped - 3 lines]

I followed your instructions to the letter and got a blank subform.
I hope you get this post.

Hi Bill,

Sorry for the delay. I think I ended up using a different approach for this
challenge and not using the crosstab solution after all. I think it was
working for a while though. It's a little hazy for me, but I'd be glad to
try to help.
Is the problem that the content of the crosstab is not displaying in the
subform, but it seems to be working when you open it by itself?

Jesper Fjølner

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via AccessMonster.com

 

[Bill R via AccessMonster.com]

Duane,

I repeated your example in Northwind and, indeed, it works as advertised.
I must conclude that there is something about my query that is preventing it
from running in my db.
I suspect it is the reference to the Combo Box on the main form.
I'll play around with it.

Thanks,

Bill

Without looking through that thread, you can display a crosstab query
datasheet view in a subform by setting the subform control Source Object to
the crosstab query.
For instance, assume a combo box named cboQueries on a form with a row
source of:
SELECT msysObjects.Name FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND
((msysObjects.Type)=5))
ORDER BY msysObjects.Name;
The form has a subform named fsubOne. The code in the after update event of
the combo box is:
Private Sub cboQueries_AfterUpdate()
If Not IsNull(Me.cboQueries) Then
Me.fsubOne.SourceObject = "Query." & Me.cboQueries
End If
End Sub

Jesper,

[quoted text clipped - 12 lines]

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via AccessMonster.com

 

[Bill R via AccessMonster.com]

See response to Duane above.

You posted a question about displaying a crosstab query in a subform, but
I'm

[quoted text clipped - 3 lines]

I followed your instructions to the letter and got a blank subform.
I hope you get this post.

Hi Bill,

Sorry for the delay. I think I ended up using a different approach for this
challenge and not using the crosstab solution after all. I think it was
working for a while though. It's a little hazy for me, but I'd be glad to
try to help.
Is the problem that the content of the crosstab is not displaying in the
subform, but it seems to be working when you open it by itself?

Jesper Fjølner

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via AccessMonster.com

 

[Duane Hookom]

Is the form open? Did you set the column headings property? Did you specify
the parameter data types?

--
Duane Hookom
MS Access MVP
--

Duane,

I repeated your example in Northwind and, indeed, it works as advertised.
I must conclude that there is something about my query that is preventing
it
from running in my db.
I suspect it is the reference to the Combo Box on the main form.
I'll play around with it.

Thanks,

Bill

Without looking through that thread, you can display a crosstab query
datasheet view in a subform by setting the subform control Source Object
to
the crosstab query.
For instance, assume a combo box named cboQueries on a form with a row
source of:
SELECT msysObjects.Name FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND
((msysObjects.Type)=5))
ORDER BY msysObjects.Name;
The form has a subform named fsubOne. The code in the after update event
of
the combo box is:
Private Sub cboQueries_AfterUpdate()
If Not IsNull(Me.cboQueries) Then
Me.fsubOne.SourceObject = "Query." & Me.cboQueries
End If
End Sub

Jesper,

[quoted text clipped - 12 lines]

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via AccessMonster.com

 

[Bill R via AccessMonster.com]

Duane,

Yes the form is open. I just tried the form/subform technique with a stripped
down calculation (as follows) and it worked! Now I just have to find out what
I'm doing in my complex calculation that's causing the failure.

Bill

Is the form open? Did you set the column headings property? Did you specify
the parameter data types?

Duane,

[quoted text clipped - 33 lines]

 

[Bill R via AccessMonster.com]

Sorry, I neglected to include the stripped down calculation example which
worked. Here it is:

qryXTab:
TRANSFORM Float*Volatility*CalcMonth
SELECT Params.SpotMonth, Params.Float, Params.Volatility
FROM tblCurveDtls AS Params, Params2
WHERE Params.curveid=3
GROUP BY Params.SpotMonth, Params.curveid, Params.Float, Params.Volatility
PIVOT Params2.CalcMonth;

Params2:
SELECT SpotMonth AS CalcMonth
FROM tblCurveDtls
WHERE curveid=3
ORDER BY SpotMonth;

tblCurveDtls has a field, SpotMonth, which is an integer field, 0-120. Each
of those records has a different float (3.25, etc.) and volatility (.43, .
586, etc.).

I got it to display. The investigation continues...


Bill

Is the form open? Did you set the column headings property? Did you specify
the parameter data types?

Duane,

[quoted text clipped - 33 lines]

 

[Duane Hookom]

Why are you grouping by curveid and not including it in the SELECT? Did you
not want to have a join between tblCurveDtls and Params2? Is it possible
that Float (not a good field name), Volatility, or CalcMonth might be Null?

--
Duane Hookom
MS Access MVP
--

Sorry, I neglected to include the stripped down calculation example which
worked. Here it is:

qryXTab:
TRANSFORM Float*Volatility*CalcMonth
SELECT Params.SpotMonth, Params.Float, Params.Volatility
FROM tblCurveDtls AS Params, Params2
WHERE Params.curveid=3
GROUP BY Params.SpotMonth, Params.curveid, Params.Float, Params.Volatility
PIVOT Params2.CalcMonth;

Params2:
SELECT SpotMonth AS CalcMonth
FROM tblCurveDtls
WHERE curveid=3
ORDER BY SpotMonth;

tblCurveDtls has a field, SpotMonth, which is an integer field, 0-120.
Each
of those records has a different float (3.25, etc.) and volatility (.43,
.
586, etc.).

I got it to display. The investigation continues...


Bill

Is the form open? Did you set the column headings property? Did you
specify
the parameter data types?

Duane,

[quoted text clipped - 33 lines]

Bill

 

[Bill R via AccessMonster.com]

Duane,

I group curveid because it's in the WHERE statement. I don't need it for
subsequent calculations. It does no harm, but I will remove it from the GROUP
BY and see what effect it's removal has. There is no join between Params
(tblCurveDtls) and Params2. Params2 just returns SpotMonth transposed as
column headings.
None of the fields are ever null although, in the actual calculation,
SpotMonth and/or CalcMonth may be 0.

Bill

Why are you grouping by curveid and not including it in the SELECT? Did you
not want to have a join between tblCurveDtls and Params2? Is it possible
that Float (not a good field name), Volatility, or CalcMonth might be Null?

Sorry, I neglected to include the stripped down calculation example which
worked. Here it is:

[quoted text clipped - 32 lines]

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via AccessMonster.com

 

[Bill R via AccessMonster.com]

Duane,

FYI: I got the crosstab query to display in my subform! I found a problem in
the TRANSFORM statement that was preventing it from displaying. There was no
error msg or anything, it just didn't display.

Are you aware of anyway to freeze or hide columns in a datasheet displayed in
this manner? The form object ceases to exist and there are of course, no text
boxes whose properties might otherwise be set.

Bill

Why are you grouping by curveid and not including it in the SELECT? Did you
not want to have a join between tblCurveDtls and Params2? Is it possible
that Float (not a good field name), Volatility, or CalcMonth might be Null?

Sorry, I neglected to include the stripped down calculation example which
worked. Here it is:

[quoted text clipped - 32 lines]

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via AccessMonster.com

 

[Duane Hookom]

You might find some code that would alter properties of the crosstab query.
You might find some code that freezes columns. Setting the SourceObject of a
subform control to a query will retain the "saved" frozen columns of the
query.

--
Duane Hookom
MS Access MVP

Duane,

FYI: I got the crosstab query to display in my subform! I found a problem
in
the TRANSFORM statement that was preventing it from displaying. There was
no
error msg or anything, it just didn't display.

Are you aware of anyway to freeze or hide columns in a datasheet displayed
in
this manner? The form object ceases to exist and there are of course, no
text
boxes whose properties might otherwise be set.

Bill

Why are you grouping by curveid and not including it in the SELECT? Did
you
not want to have a join between tblCurveDtls and Params2? Is it possible
that Float (not a good field name), Volatility, or CalcMonth might be
Null?

Sorry, I neglected to include the stripped down calculation example
which
worked. Here it is:

[quoted text clipped - 32 lines]

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via AccessMonster.com