Dynamic Continuous Form for Crosstab Query

[Guest]

I would like to put the output of a crosstab query on a continuous form, that
has a command button on each row that executes an update query based on the
records associated with that row.

If I use a Pivot Table form view, which would create columns as required, I
cannot use the command button.

As a continuous form, the columns are "hard-wired" into the form, meaning
that if we add or lose a new employee (represented by the columns), we have
to change the form, where I'd rather the application worked now and down the
road with no additional modifications required.

I know that a form can be created with VBA code, and thought that might be a
good solution, based on the output of the crosstab query, but I have no idea
how to go about it, including creating the code behind the command button,
setting form and control properties, etc..

Can anyone point me in the right direction? Thank you.

Sprinks

 

[Guest]

I have a similar problem. Please let me know if you find a solution.

jaybird

 

[Guest]

OK, Jaybird. Will do. I ended up though implementing a different approach,
which might work for your situation.

While it doesn't present the data in exactly the same way as a crosstab, the
information is all there, in actually more detail than the crosstab, and I
find it an acceptable solution.

The form presents information to our company's principal about time spent on
each project that he uses to decide what to bill the client that month. The
main form is unbound with two side-by-side subforms on it. The query for the
one on the left is based on shows the overall totals for each project:

Proj # Project Total Hours Billing Total
----------------------------------------------------------------
06123 Crozer Gym 35 $1,350
06135 Smeal Business School 100 $20,000

The one on the right shows the detail by employee:

Initials Hours Extd
-----------------------------
CD 15 $1,350

This subform is linked to a hidden textbox control on the main unbound form.
The OnCurrent event of the left subform writes the project number to the
hidden control:

Me.Parent("txtProjectNumber") = Me![txtProjectNumber]

The subform on the right, being provided with the corresponding key to the
current record on the left, then displays the associated detail records,
which will work regardless of how many project managers work on a given
project, or who joins or leaves the company.

Hope that helps.

Sprinks

 

[Guest]

Thanks, buddy. I'll try it.

OK, Jaybird. Will do. I ended up though implementing a different approach,
which might work for your situation.

While it doesn't present the data in exactly the same way as a crosstab, the
information is all there, in actually more detail than the crosstab, and I
find it an acceptable solution.

The form presents information to our company's principal about time spent on
each project that he uses to decide what to bill the client that month. The
main form is unbound with two side-by-side subforms on it. The query for the
one on the left is based on shows the overall totals for each project:

Proj # Project Total Hours Billing Total
----------------------------------------------------------------
06123 Crozer Gym 35 $1,350
06135 Smeal Business School 100 $20,000

The one on the right shows the detail by employee:

Initials Hours Extd
-----------------------------
CD 15 $1,350

This subform is linked to a hidden textbox control on the main unbound form.
The OnCurrent event of the left subform writes the project number to the
hidden control:

Me.Parent("txtProjectNumber") = Me![txtProjectNumber]

The subform on the right, being provided with the corresponding key to the
current record on the left, then displays the associated detail records,
which will work regardless of how many project managers work on a given
project, or who joins or leaves the company.

Hope that helps.

Sprinks

I have a similar problem. Please let me know if you find a solution.

jaybird