Cross tab.... in a FORM?

[Martyh]

Hi!

I have data that I have looks like this:

ID CID Phas FYr Contrib Funds_Delivered
1 1 1 2002 10000 0
2 2 1 2002 10000 0
3 3 1 2002 10000 0
...
25 1 1 2003 15000 -1
26 2 1 2003 12000 0
27 3 1 2003 18000 -1
...
46 1 1 2004 19000 -1
47 2 1 2004 14000 -1
48 3 1 2004 20000 -1
...
88 1 2 2005 25000 -1
89 2 2 2005 20000 -1
90 3 2 2005 20000 -1
...
130 1 2 2006 30000 -1
131 2 2 2006 30000 -1
132 3 2 2006 35000 -1

I need a FORM that looks like:

Phase
1
FYr
2002 2003 2004
1 10000 15000 19000
2 10000 12000 14000
3 10000 18000 20000
...

and then by just changing the phase to 2, have the form change to

Phase
2
FYr
2005 2006 2007
1 25000 30000 35000
2 20000 30000 37000
3 20000 35000 40000
...

I know that I need a cross tab.... but this is a FORM ... not a REPORT. How do I do this?

Marty H.

 

[Banana]

The easiest way to get it set up and running is to create a temporary table based on a crosstab query, binding the appropriate controls and re-labeling them where appropriate.

For a example of this, I wrote a sample demonstrating this.

Beware: Lot of VBA will be required, so even though it's "easiest", it's not simple.

 

[Martyh]

Thanks Banana,

I'll be investigating this!

 

[Martyh]

Banana,

OK I've investigated... How do I use textboxes instead of checkboxes?

I have no experience using "Class Modules" ... perhaps a few words?

Thanks,

 

[Banana]

To use textboxes instead, you'd probably change all checkboxes into textboxes and use similar naming scheme (Text1, Text2...)

Then edit the class module so it asks for textbox instead of Checkbox:

Private WithEvents cTextBox As Textbox

So you know, forms' and reports' modules are class modules. Class module are the 'template' where you create a new object from; some make the analogy to a cookie cutter, with the object being the actual cookie. Cookie cutter can be used on different kinds of cookie batter; chocolate chip batter or oatmeal raisin batter; they are similar in what they can do but are different from each.

This is why when we have a class module, we can't use it directly as we would in a standard module. To illustrate, suppose we wrote a function to tell us time in standard module:

Public Function TellMeTimeNow() As Date

TellMeTimeNow = Now()

End Function[/end]

We can just call that function anywhere else in the project by using this statement:
[code]Debug.Print TellMeTimeNow

But if we had the exact same code in a class module, and it's named "Timekeeper", a call to TellMeTimeNow no longer works. Instead we have to create a object, just like we can't eat a cookie cutter; we have to use it to make cookies if we are to eat cookie.

So the code to call the same function from anywhere else in the project should be changed to:

Dim Tim As New TimeKeeper

Debug.Print Tim.TellMeTimeNow

Set Tim = Nothing

I hope that helps illustrate the difference between a standard module and class module.

In the sample I wrote, I used a class module to act as a "sink" for all controls I want to behave the same. In start up, the class will collect all controls that are Checkboxes, set it to a new object, then add the object to the collection.

Those object then define the event procedure for each checkboxes and tell that when such event occurs to call the object's methods. This allow me to code the events for all checkboxes once, instead of coding each checkbox individually, resulting in 40+ duplicates of the same code. More importantly, it makes changing the code or adding more functionality easier because you only go to one place and that's the class module where it defines each control's event and instruct it to call its own methods.


There is also other option that doesn't require use of class modules. ChrisO wrote about 'soft-coding' the event handlers, by writing functions into the controls' event handlers. This is more easier than monkeying with a class module. This solves the problem that class modules tries to solve: coding a common process for several controls.