Crosstab & Forms

[namliam]

Hmz,

I have never tried this, it seems easy enough but in fact... I simply cannot find the answer.

I want to display a crosstab query as a (sub)form. The basis of this is that a form requires the columns to be fixed. The crosstab will offcourse grow and grow in the number of columns.

I would like to show the form as a datasheet to support this purpose.
I was thinking maybe I can dynamicaly add the (new) column(s) before opening the form, but how do I do that?

Greets & Thanks

Yours

 

[Rich]

Why would the number of columns increase?

 

[namliam]

Hey Rich,

It is a crosstab going over different invoice(date)s. The invoice(date) is the means by which we need the columns to be defined.

So (nearly) every week new invoices are generated in another system, I extract (some of) them and report them in this form.
I have colums per week with totals per customer ... Therefor the columns will change/grow...

I was dabbling with a real crosstab in Access but its slow and you cannot do much with clickable feels to pull extra information.
Where with a real form you can do all sorts of neat stuff! Plus a crosstab query seems to work (much) faster than a "real" crosstab.

Yours

 

[The_Doc_Man]

The only problem with a crosstab as the basis for forms is that, as you point out, the "fields" and "records" are very dynamic. The catch is that even if you managed to build a form, you would not be able to do much with it. See, a cross tab is a form of summation query, which usually is not editable. So the form would always have to be a no-edit form.

I suppose you could always do what a wizard does... create a new report in design mode, then start placing controls on the form based on your crosstab query. You would have to enumerate the "fields" in the query by stepping through the query's "Fields" collection and for a cross-tab, I won't tell you that I know what those fields look like.

 

[namliam]

Well yeah something like that....

Tho I dont like the idea of nore have I ever opened a form in design layout and placed controls on it.

Any pointers/examples ??

I have thought of an alternative where I can mess with the fields behind the controls... but just opening it with everthing visible for the user to browse would be much better...