Crosstab Columns in Datasheet Subform

[Guest]

Hello,

I am using a xtab query to generate the columns I am using in a subform,
which is in datasheet view.

As the columns will vary over time, how can I create the subform to use the
columns generated each time the query is run? Also, how can I make those
column headings the field name? Finally, anyway to order the columns?

Thanks!

 

[Tom Ellison]

Dear David:

When I have done this, I used the properties of the query to generate the
effect you describe. First, the xtab query will likely have one or more
fixed columns on the left that never change. This will be followed by a
variable number of columns. The properties of the query object will tell
you how many total columns there are, and from this you can calculate how
many of the variable columns you have.

These columns do not have to be linked to your form by name. They can be
discovered and used by index, that is, they are numbered.

On a form, you will typically have a limited number of columns that can be
displayed. If the xtab returns more than you can display, you can put a
horizontal scroll bar under the form. The whole display can be scrolled one
column at a time, maintaining the leftmost column(s) in place, and scrolling
only the variable columns.

This is a rough outline of what can be done. It requires a moderately
expert level of competence in using objects and coding VBA.

This method can control the column names and scroll them, as well as the
data.

Tom Ellison

 

[Duane Hookom]

There is a sample dynamic subform at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
shows how to assign a crosstab query to a Source Object property of a
subform control. The columns won't be ordered unless you have code to
identify all the columns and modifying the SQL property of the crosstab
query.

 

[Guest]

Tom,

Thanks for replying. You are right on topic here.

Any suggestions where I might dig into some samples or otherwise to get to
this result?

Some other comments in CAPS below:

These columns do not have to be linked to your form by name. They can be
discovered and used by index, that is, they are numbered. YES, THIS IS WHAT
IS DESIRED.

On a form, you will typically have a limited number of columns that can be
displayed. If the xtab returns more than you can display, you can put a
horizontal scroll bar under the form. The whole display can be scrolled one
column at a time, maintaining the leftmost column(s) in place, and scrolling
only the variable columns. MY FORM CURRENTLY HAS THE HORIZONTAL SCROLL BAR,
ANTICIPATING THIS NEED.

This is a rough outline of what can be done. It requires a moderately
expert level of competence in using objects and coding VBA. OK. I'M A 20+
YEAR PROGRAMMER, WITH MODERATE LEVEL OF VBA EXPERIENCE. DOES THAT QUALIFY??

This method can control the column names and scroll them, as well as the
data. THIS WOULD BE WONDERFUL!!

Thanks,

 

[Guest]

Duane,

Right On. Exactly what I was looking for.

One other question: The "variable" fields which are driving the number of
columns in the xtab query have an associated SORT field indicating the order
they should appear in. I can concat the SORT & FIELD to get the order, but
the column name has the sort value in it.

Any way to get the sort order, without the representation of that Sort field
in the column heading name?

 

[Duane Hookom]

You would need to write code to modify the SQL property of the query and add
the Column Headings value like:

Currentdb.QueryDefs("qxtb").SQL = "..... IN ('Jan','Feb','Mar','Apr',...)"

 

[Guest]

Duane,

Thanks.

I did download your suggested MDB and the suggestion for the dynamic subform
works great.

But as these column names are derived from the Cross Tab query, how do I
know what they are when I am writing the code you suggest, and how to I order
those columns?

 

[Tom Ellison]

Dear David:

I have never heard that anyone else may have done this, but it is likely
they have. Whether anyone has published on how this is done I cannot say.
It is not unlikely, but I don't have any information on it.

The process is really one of manipulating the indexes to the columns' names
and values, based on the state of a scroll bar. That's not really so
difficult. Complex, yes. Requires fairly advanced skill, yes. Is
mysterious, no.

I have this topic on my own list of things to publish, but I only get around
to publishing something about once a year. There's not much money in it.
In fact, my experience is that it is far below the minimum wage. This
doesn't prevent me from doing so, but it doesn't encourage things much,
either. I actually get more from answering a few questions in the
newsgroups, so here I am. But covering a subject in detail is not so well
rewarded, either financially or in terms of being helpful to others. That's
really too bad, because I have a bit of a passion to publish.

Well, enough about me. Given your level of skill (I'm 20+ years in, well
30+) I expect you can tackle this. I can think of no great mystery about
it. I had nothing in mind but what I have written you when I started it,
and I pretty much zipped right through it. Everything you will need is
right at hand, and if you don't see it, let me know and I can point you that
way.

Here is one alternative I haven't mentioned. A query has a maximum of 255
columns. In a horizontally scrolling form you could actually requery the
data as the form scrolls. This might be just a bit slower, depending on the
query, but it lifts the restriction on number of columns. Just Rank the
columns and filter to that ranking using the operation of the scroll bar
control. That would work even with millions of columns. Actually, when I
did this later, it was probably less coding. You still must address the
columns by index, but that's simplified because you have exactly the right
columns needed.

This can actually be faster, as the volume of data required is less.

I'd be glad to answer specific questions about the implementation as you
proceed.

Enjoy.

Tom Ellison

 

[Tom Ellison]

From Duane's post, obviously someone has. Go for it!

Tom Ellison

 

[Duane Hookom]

You would need to create a record set of your potential column headings in
the order that you want the columns displayed. Then, you build your SQL
statement with the potential column headings in the " IN (....)" at the end.
Update the SQL property of your crosstab query with your "built" SQL syntax.

 

[Guest]

Duane,

I'll give that a try, thanks. I'll assume I need to create a text string
for the IN clause (i.e. VALUE1 comma VALUE2) derived by looping through the
recordset I procure through an SQL retrieval process.

One other question:
1. As the subform is dynamically created from the results of the SQL
statement, I do not know how to assign properties/actions to a specific
field. For example, for one field, I want to assign an event when the field
is CLICKED on, to open a form. For other fields, I want those fields to be
COMBO BOXES, from which a value can be selected. Any ideas?

 

[Duane Hookom]

The subform is a query datasheet. There is no way you can add event code.