Subforms & Crosstab Querys

[Guest]

I have a subform based on a crosstab query. I get the following error
message: "You can't use a pass through query or a non-fixed column crosstab
query as record source for a subform or subreport. Before you bind the
subform o subreport to a crosstab query, set the query's columnheadings
property." I have tried, even using the Help menu, to figure out how to "set
the query's columnheadings property" in order to fix the problem, but I
couldn't. Could someone help with that?

Thanks in advance,

 

[Allen Browne]

Can you know ahead of time what the Column Headings will be? That's all the
values you want to become fields of the crosstab query (and therefore of the
subreport.)

If so, open the crosstab query in design view.
Open the Properties box (View menu.)
Make sure you are looking at the properties of the query (not of a field.)
Enter the list of values into the Column Headings property, e.g.:
1,2,3,4,5

 

[Duane Hookom]

There is a sample mdb "dynamsubfrm.mdb" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which
demonstrates how to set a subform to a crosstab query. This is actually
using the query object as the subform so it can only be displayed as a
datasheet.

 

[Guest]

Duane,

I used your code for a subform and it works great except for one problem.
One of the queries that I used to build my Crosstab uses the value from a
field on the main form. I added it as a paramter to my Crosstab and the
query works fine, but nothing shows up in the subform. No error, just
nothing.

Thanks for any insight.

Steve

 

[Duane Hookom]

It's a bit difficult to trouble-shoot without seeing the SQL view of the
query. Are you suggesting there is no problem displaying the datasheet view
of the query from the database window?

 

[Guest]

That is correct. Here is the crosstab SQL:

PARAMETERS [Forms]![frmMainScheduler]![SelectedDate] DateTime;
TRANSFORM Max(qryDailyInstallSched.[Scheduled?]) AS [MaxOfScheduled?]
SELECT qryDailyInstallSched.Time
FROM qryDailyInstallSched
GROUP BY qryDailyInstallSched.Time
PIVOT qryDailyInstallSched.Installer;

which is built off of:

SELECT qryInstallerTimeSlots.Installer, qryInstallerTimeSlots.Time,
IIf([qryScheduledInstalls]![ScheduleTime]=[qryInstallerTimeSlots]![Time],[qryScheduledInstalls]![MID],[qryInstallerTimeSlots]![SheduleDate])
AS [Scheduled?], qryScheduledInstalls.ScheduleDate
FROM qryInstallerTimeSlots LEFT JOIN qryScheduledInstalls ON
(qryInstallerTimeSlots.Time = qryScheduledInstalls.ScheduleTime) AND
(qryInstallerTimeSlots.Installer = qryScheduledInstalls.Installer);

"qryScheduledInstalls" is the query that actually references the form field
SelectedDate. What I'm attempting to build is a scheduling form that shows
time slots, installers and jobs assigned for a particular day. The field on
the main form has the day that I want to display. The subform would look
something like:

Bob Sue Tom
8:00 Open Open Open
8:30 Job 1 Open Open
9:00 Open Job 2 Open
9:30 Open Open Open
10:00 Job 3 Open Open
.. . . .
.. . . .
.. . . .

Thanks for taking a look.

Steve

 

[Duane Hookom]

I can't identify why the subform won't display the query. Have you tried a
requery of the subform control?

I would actually do this with a calendar report rather than a form.

--
Duane Hookom
MS Access MVP

That is correct. Here is the crosstab SQL:

PARAMETERS [Forms]![frmMainScheduler]![SelectedDate] DateTime;
TRANSFORM Max(qryDailyInstallSched.[Scheduled?]) AS [MaxOfScheduled?]
SELECT qryDailyInstallSched.Time
FROM qryDailyInstallSched
GROUP BY qryDailyInstallSched.Time
PIVOT qryDailyInstallSched.Installer;

which is built off of:

SELECT qryInstallerTimeSlots.Installer, qryInstallerTimeSlots.Time,
IIf([qryScheduledInstalls]![ScheduleTime]=[qryInstallerTimeSlots]![Time],[qryScheduledInstalls]![MID],[qryInstallerTimeSlots]![SheduleDate])
AS [Scheduled?], qryScheduledInstalls.ScheduleDate
FROM qryInstallerTimeSlots LEFT JOIN qryScheduledInstalls ON
(qryInstallerTimeSlots.Time = qryScheduledInstalls.ScheduleTime) AND
(qryInstallerTimeSlots.Installer = qryScheduledInstalls.Installer);

"qryScheduledInstalls" is the query that actually references the form
field
SelectedDate. What I'm attempting to build is a scheduling form that
shows
time slots, installers and jobs assigned for a particular day. The field
on
the main form has the day that I want to display. The subform would look
something like:

Bob Sue Tom
8:00 Open Open Open
8:30 Job 1 Open Open
9:00 Open Job 2 Open
9:30 Open Open Open
10:00 Job 3 Open Open
. . . .
. . . .
. . . .

Thanks for taking a look.

Steve

It's a bit difficult to trouble-shoot without seeing the SQL view of the
query. Are you suggesting there is no problem displaying the datasheet
view
of the query from the database window?

 

[Guest]

Duane,

Thanks for sticking with me. How do I create a report based on the crosstab
query? Since the number of installers can change, my column headers could
change over time.

Thanks again,
Steve

I can't identify why the subform won't display the query. Have you tried a
requery of the subform control?

I would actually do this with a calendar report rather than a form.

--
Duane Hookom
MS Access MVP

That is correct. Here is the crosstab SQL:

PARAMETERS [Forms]![frmMainScheduler]![SelectedDate] DateTime;
TRANSFORM Max(qryDailyInstallSched.[Scheduled?]) AS [MaxOfScheduled?]
SELECT qryDailyInstallSched.Time
FROM qryDailyInstallSched
GROUP BY qryDailyInstallSched.Time
PIVOT qryDailyInstallSched.Installer;

which is built off of:

SELECT qryInstallerTimeSlots.Installer, qryInstallerTimeSlots.Time,
IIf([qryScheduledInstalls]![ScheduleTime]=[qryInstallerTimeSlots]![Time],[qryScheduledInstalls]![MID],[qryInstallerTimeSlots]![SheduleDate])
AS [Scheduled?], qryScheduledInstalls.ScheduleDate
FROM qryInstallerTimeSlots LEFT JOIN qryScheduledInstalls ON
(qryInstallerTimeSlots.Time = qryScheduledInstalls.ScheduleTime) AND
(qryInstallerTimeSlots.Installer = qryScheduledInstalls.Installer);

"qryScheduledInstalls" is the query that actually references the form
field
SelectedDate. What I'm attempting to build is a scheduling form that
shows
time slots, installers and jobs assigned for a particular day. The field
on
the main form has the day that I want to display. The subform would look
something like:

Bob Sue Tom
8:00 Open Open Open
8:30 Job 1 Open Open
9:00 Open Job 2 Open
9:30 Open Open Open
10:00 Job 3 Open Open
. . . .
. . . .
. . . .

Thanks for taking a look.

Steve

It's a bit difficult to trouble-shoot without seeing the SQL view of the
query. Are you suggesting there is no problem displaying the datasheet
view
of the query from the database window?

--
Duane Hookom
MS Access MVP

Duane,

I used your code for a subform and it works great except for one
problem.
One of the queries that I used to build my Crosstab uses the value from
a
field on the main form. I added it as a paramter to my Crosstab and
the
query works fine, but nothing shows up in the subform. No error, just
nothing.

Thanks for any insight.

Steve

:

There is a sample mdb "dynamsubfrm.mdb" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
which
demonstrates how to set a subform to a crosstab query. This is
actually
using the query object as the subform so it can only be displayed as a
datasheet.
--
Duane Hookom
MS Access MVP

I have a subform based on a crosstab query. I get the following error
message: "You can't use a pass through query or a non-fixed column
crosstab
query as record source for a subform or subreport. Before you bind
the
subform o subreport to a crosstab query, set the query's
columnheadings
property." I have tried, even using the Help menu, to figure out how
to
"set
the query's columnheadings property" in order to fix the problem,
but I
couldn't. Could someone help with that?

Thanks in advance,

 

[Duane Hookom]

Take a look at the calendar reports at
http://www.access.hookom.net/Samples.htm.
If that doesn't meet your needs, consider the crosstab reports at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP

Duane,

Thanks for sticking with me. How do I create a report based on the
crosstab
query? Since the number of installers can change, my column headers could
change over time.

Thanks again,
Steve

I can't identify why the subform won't display the query. Have you tried
a
requery of the subform control?

I would actually do this with a calendar report rather than a form.

--
Duane Hookom
MS Access MVP

That is correct. Here is the crosstab SQL:

PARAMETERS [Forms]![frmMainScheduler]![SelectedDate] DateTime;
TRANSFORM Max(qryDailyInstallSched.[Scheduled?]) AS [MaxOfScheduled?]
SELECT qryDailyInstallSched.Time
FROM qryDailyInstallSched
GROUP BY qryDailyInstallSched.Time
PIVOT qryDailyInstallSched.Installer;

which is built off of:

SELECT qryInstallerTimeSlots.Installer, qryInstallerTimeSlots.Time,
IIf([qryScheduledInstalls]![ScheduleTime]=[qryInstallerTimeSlots]![Time],[qryScheduledInstalls]![MID],[qryInstallerTimeSlots]![SheduleDate])
AS [Scheduled?], qryScheduledInstalls.ScheduleDate
FROM qryInstallerTimeSlots LEFT JOIN qryScheduledInstalls ON
(qryInstallerTimeSlots.Time = qryScheduledInstalls.ScheduleTime) AND
(qryInstallerTimeSlots.Installer = qryScheduledInstalls.Installer);

"qryScheduledInstalls" is the query that actually references the form
field
SelectedDate. What I'm attempting to build is a scheduling form that
shows
time slots, installers and jobs assigned for a particular day. The
field
on
the main form has the day that I want to display. The subform would
look
something like:

Bob Sue Tom
8:00 Open Open Open
8:30 Job 1 Open Open
9:00 Open Job 2 Open
9:30 Open Open Open
10:00 Job 3 Open Open
. . . .
. . . .
. . . .

Thanks for taking a look.

Steve

:

It's a bit difficult to trouble-shoot without seeing the SQL view of
the
query. Are you suggesting there is no problem displaying the datasheet
view
of the query from the database window?

--
Duane Hookom
MS Access MVP

Duane,

I used your code for a subform and it works great except for one
problem.
One of the queries that I used to build my Crosstab uses the value
from
a
field on the main form. I added it as a paramter to my Crosstab and
the
query works fine, but nothing shows up in the subform. No error,
just
nothing.

Thanks for any insight.

Steve

:

There is a sample mdb "dynamsubfrm.mdb" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
which
demonstrates how to set a subform to a crosstab query. This is
actually
using the query object as the subform so it can only be displayed
as a
datasheet.
--
Duane Hookom
MS Access MVP

I have a subform based on a crosstab query. I get the following
error
message: "You can't use a pass through query or a non-fixed
column
crosstab
query as record source for a subform or subreport. Before you
bind
the
subform o subreport to a crosstab query, set the query's
columnheadings
property." I have tried, even using the Help menu, to figure out
how
to
"set
the query's columnheadings property" in order to fix the problem,
but I
couldn't. Could someone help with that?

Thanks in advance,