Dynamically Changing SubForm Columns

  • Thread starter Thread starter gr8t99one
  • Start date Start date
G

gr8t99one

I am pretty new to Access, so pardon my inexperience. I have an interesting
problem. I am trying to track a number of figures, let's call them X's, by
date, with one value for each date (call the dates 'Y').

I would like to have a subform that shows in a datasheet view the X's as rows
and the Y's (dates) as columns. I've created a crosstab query that shows the
information, but the problem I am having is that every day I use the table I
have a new date, and therefore a new column. Since I've set up the subform
with only the dates through yesterday as columns, the new column doesn't
appear.

Is there a way to dynamically create columns of a subforrm from a recordset
in VBA so that every day, I get the appropriate number of columns with the
correct column headings? Let me know if I need to explain further, and thank
you for your help.

Matt
 
I am pretty new to Access, so pardon my inexperience. I have an interesting
problem. I am trying to track a number of figures, let's call them X's, by
date, with one value for each date (call the dates 'Y').

I would like to have a subform that shows in a datasheet view the X's as rows
and the Y's (dates) as columns. I've created a crosstab query that shows the
information, but the problem I am having is that every day I use the table I
have a new date, and therefore a new column. Since I've set up the subform
with only the dates through yesterday as columns, the new column doesn't
appear.

Is there a way to dynamically create columns of a subforrm from a recordset
in VBA so that every day, I get the appropriate number of columns with the
correct column headings? Let me know if I need to explain further, and thank
you for your help.

Matt
Click to expand...

I'm not sure what you expect here, Matt. After you've been using the
application for three years will you want 750+ columns? If so you're out of
luck; even with a crosstab you're limited to 255 fields. And I hope you're
aware that crosstab queries cannot be edited - are you OK with that?

I was curious enough to see if there were any posted solutions to this so I
did a Google search; one example can be found down the list of answers at
http://www.utteraccess.com/forums/access/access24937.html.

John W. Vinson [MVP]
 
Thanks for your reply John. I should have clarified. This data will be used
to track a rolling month or two of work, so I should never reach the
limitations of the crosstab query. I will be adding a second subform later
on to product month-end data to review over a longer time period, but
hopefully using the same logic. Further, the information is read-only, as it
is being used as a reporting tool with no connection to the back-end data
feed for updates. I will review the solution below and post if I have any
more questions. Thank you again for your time.

Matt
I am pretty new to Access, so pardon my inexperience. I have an interesting
problem. I am trying to track a number of figures, let's call them X's, by
Click to expand...
[quoted text clipped - 13 lines]
Matt
Click to expand...

I'm not sure what you expect here, Matt. After you've been using the
application for three years will you want 750+ columns? If so you're out of
luck; even with a crosstab you're limited to 255 fields. And I hope you're
aware that crosstab queries cannot be edited - are you OK with that?

I was curious enough to see if there were any posted solutions to this so I
did a Google search; one example can be found down the list of answers at
http://www.utteraccess.com/forums/access/access24937.html.

John W. Vinson [MVP]
Click to expand...
 
Thanks for your reply John. I should have clarified. This data will be used
to track a rolling month or two of work, so I should never reach the
limitations of the crosstab query. I will be adding a second subform later
on to product month-end data to review over a longer time period, but
hopefully using the same logic. Further, the information is read-only, as it
is being used as a reporting tool with no connection to the back-end data
feed for updates. I will review the solution below and post if I have any
more questions. Thank you again for your time.
Click to expand...

A query Datasheet might be easier to manage, if it's just for display. I
rarely expose table/query datasheets to users, but with all the hassles of
adjusting a form or report to the exigencies of a crosstab, it might be a
better choice!

John W. Vinson [MVP]
 
I'm not sure what you expect here, Matt. After you've been using
the application for three years will you want 750+ columns? If so
you're out of luck; even with a crosstab you're limited to 255
fields. And I hope you're aware that crosstab queries cannot be
edited - are you OK with that?

I was curious enough to see if there were any posted solutions to
this so I did a Google search; one example can be found down the
list of answers at
http://www.utteraccess.com/forums/access/access24937.html.

John W. Vinson [MVP]
Click to expand...
With dates, one can work around the issue by making the column
headers a datediff() from today's date, so that yesterday is -1, the
day before is -2 etc. then using a little code in the form that uses
dateadd() to convert the numbers back to the date they represent in
unbound labels over the columns.. Filtering the query to always
return the last, say 10, days then gives a readable display.

Use the crosstab query's Column Headings property to force null
columns to appear.
 
With dates, one can work around the issue by making the column
headers a datediff() from today's date, so that yesterday is -1, the
day before is -2 etc. then using a little code in the form that uses
dateadd() to convert the numbers back to the date they represent in
unbound labels over the columns.. Filtering the query to always
return the last, say 10, days then gives a readable display.
Click to expand...

Bob, this is one of the reasons I hang out in these groups: I keep learning
neat new tricks! THANK YOU! I actually have a use for this...

John W. Vinson [MVP]
 
Thanks guys! I have it up and running now. It's a bit slow and seems to
increase the size of my database each time I run it until I do a "compact and
repair" but it's working well. This is exactly what I needed, thank you once
again.
 
Bob, this is one of the reasons I hang out in these groups: I keep
learning neat new tricks! THANK YOU! I actually have a use for
this...

John W. Vinson [MVP]
Click to expand...
Learning neat new things is why I come here too, and read every
message. And I answer some too, when I can.
 
Back
Top