form with variable field number

[FRSPA]

I would like to show in a form a dynamic cross analysis query which can have a varying number of columns that all have to be displayed anytime. I don't know how to do this.



Thanks for help.

FR

[June7]

Does cross analysis query mean crosstab query? I have little experience with crosstabs but maybe using datasheet view in the form will handle this.

[FRSPA]

Yes I mean Crosstab query. Unfortunately, I'm already using the datasheet view in my form but once the form is created, if I add data to my source table that request an additional column in the display, it is not shown...

Note that to create the form, I originally used the assistant, which created a form based on my crosstab query with a determinated number of fields shown in the creation mode. These fields are then displayed as datasheet in the form mode.

P.S.: Sorry for my poor translation of technical terms... I'm working with a french version.

Thanks for help,

FR

[alansidman]

Look at this video on creating cross tab queries.

http://www.datapigtechnologies.com/f.../crosstab.html

Alan

[FRSPA]

Sorry Mr Sidman. My problem is not creating crosstab queries but rather displaying them in a form that would show a varying number of rows or columns...

[June7]

Oh, of course, just hit me that the form would still require controls to be added even if using datasheet view. You could programmatically add controls to form. Basically, build the form each time it is opened. I have never done this, but seen code.

[FRSPA]

Thanks for this hint. If you can have a rapid hand on this sample code I would be interested. If not its OK.

Thanks a lot,

FR

[June7]

Google: vba add textbox to form
http://www.ozgrid.com/forum/showthre...t=77864&page=1
http://www.pcreview.co.uk/forums/add...-t3684985.html

[pbaldy]

You might search on "dynamic crosstab". Methods I've seen (and use) involve a form/report with the maximum possible number of controls pre-built. Code in the open event examines the source query and assigns control sources or hides controls as appropriate. To me this is simpler than adding controls, as then you have to position them. I use code from the Access Developer's Handbook, so I don't think it's kosher for me to post the code (copyright violation).

[June7]

pbaldy's alternative sounds reasonable. Although with displaying form in datasheet view positioning the created controls is not critical. However, creating controls requires programmatically opening the form in DesignView first then switching to FormView (not necessary to save the revised form). pbaldy's suggestion skips the DesignView step.

[FRSPA]

Thanks to all for your help. I will look further on my side through this avenue.

FR