Form and crosstab query

  • Thread starter Thread starter George Hutto
  • Start date Start date
G

George Hutto

Folks,

I know I asked this question about 4 years ago regarding a report, but now
the issue has resurfaced regarding a form.

It is based on a crosstab query that gets a new column of data each month.
How can I make the form dynamic so that when the user opens it up so that
enough columns can be seen with the data?

I tried using application.createcontrol, but it tells me I can't add
controls to a form unless I'm in design view. I can open up the form
through code in design view, but I don't want the user to see it or to have
these temporary changes to the columns viewed on the form. Additionally,
the user wants to be able to select the columns of data which do appear
(which are grouped by Month), so that one time he may want to see 4 months
of data, or at another time see five different months of data that aren't
sequential.

Thanks,

George
 
I have had to do this many times in the past. Here are some ideas that
might help you with your project.

Create a form (datasheet type) with a bunch of text boxes and attached
labels. Use as many as you think you will *Ever* need. I typically get
bored and quit after creating 50 or so, but if I thought that the Crosstab
could one day return 150 columns I'd create 150 Text boxes. Give the text
boxes cool names like txt1, txt2, etc. The attached labels need to be named
lbl1, lbl2, etc.

In the form load event open a recordset that is your crosstab. Iterate
through the recordset.Fields setting the ControlSource of your text boxes to
the fields, seting the captions of your labels to the recordset's field
names, setting the ColumnHidden property to false. Once you have iterated
through all of the fields returned in your recordset set the balance of the
text boxes ColumnHidden properties to true.

Good Luck

Ron W
 
Thanks Ron for the help -- I ended up doing something like this but with 5
columns with forward/back buttons. Loading the form establishes the initial
ControlSources, Captions, etc. Part of the reason for this is that I'm
using the form as a subform, and I need to be able to show totals for the
columns.

Thanks,

George
 
Back
Top