Param driven # of columns and column names

L

Laurel

I have an application to collect scores across time. Right now I have 5
columns with hard coded names. I would like to generalize my application to
use a variable number of columns with parameter driven column names. (That
is, you define the number of columns and their names at setup time - not at
runtime, but it shouldn't require any code changes).

This is a common issue in dataprocessing, and I've solved it in other
languages, but I'd like some guidance on the best approach(es) in Access.

Here's what I've thought of so far
1. Use functions for the column names. The database columns are called
"First Score," "Second Score," etc. On my forms and reports I use
fnc_ColName("First Score"), etc.

2. Make columns on forms and reports visibile/invisible based on the number
of columns in play (stored in a parameter). This isn't completely
satsifactory, as the look of the forms will be nice or not nice, depending
on how many columns will be displayed.

I'm worried about charts. I've found it's hard to get info about working
with charts via code.
 
D

Duane Hookom

This is a common issue with developers who don't understand relational
databases.

You should be adding records, not fields. If you want to have fields like
"First Score","Second Score", consider using Excel.
 
K

Klatuu

In addition to Duane's observations (with which i concur), adding controls to
forms over time will create problems in that there is a maximum of about 700
or so (sorry, I don't remember the exact limit) controls for a form. Adding
a control adds to number, but deleting a control does not subtract from it.
At some point in time, you will run into a problem.

As Duane suggested, perhaps Excel would be a better solution. If you want
to use Access, you will need to redesign your database.
 
L

Laurel

I do understand relational databases. I am a programmer/analyst for a
software company. In this situation, what I am doing works very well. Do
you have any suggestions for how to make the number of columns parameter
driven?
 
L

Laurel

Is there anything you can do to "refresh" the database after having removed
deleted controls? Doesn't "repair/compact" do that sort of thing?

At this point, no one has suggested how I might add and remove controls
programmatically. Is there actually a way? As I had suggested in my
original e-mail, making them invisible and using function names seems to be
the only route I can think of.

Please see answer to Duane re the appropriateness of my database structure.
It definitely is relational. It has a lot of other parts/tables that I
don't discuss in this particular e-mail. 30 years of experience tells me it
would be a mistake to make a separate row for each score type.
 
K

Klatuu

My 30 yrs (well, as of 4/4/2006) experience tells me a lot, too.

I do believe a Compact and Repair might refresh that count, but since I have
never
had to consider this, I would have to research it to make sure.

Makeing those controls invisible would be one way to do it. Since I haven't
seen your data structure, I can't be sure, but how about creating a query or
temporary table programmactically that would present the data in the form you
want it and use a continuous or data sheet view? Just a suggestion.

As to the report, I have done exactly that. No knowing how many columns of
data I would have, I created a report with more columns that I thought I
might ever need. I then used the Format event of the report's Detail section
to make visible those I needed.

Good Luck.
 
D

Duane Hookom

Other than your experience, I haven't heard anything that would back up your
need to add fields and controls. However, you might want to create your
tables with as many extra fields as may ever be required. You can create
forms with extra controls and set them to invisible. Then use a data
definition table that ties a field with a name and other properties that
might be required.

When ever you need to open a form, you can open a recordset of dd table to
set visible and other properties.
 
L

Laurel

Is it reasonable to attempt to change the width of columns and position of
labels on a form dynamically?
 
L

Laurel

How does one make a temp table in Access? Do I have to do it via a SQL
statement? What is its persistence?
 
D

Douglas J. Steele

You can create it using DAO, ADOX or DDL (presumably what you're referring
to as a SQL statement)

Unlike temporary tables in SQL Server, once you've created it, it lasts
until you delete.

One thing you might consider is putting the temporary table(s) in a
temporary database, so that it doesn't contribute to the size of your
database. Tony Toews shows one approach at
http://www.granite.ab.ca/access/temptables.htm
 
J

John Spencer

My opinion:

As long as you are comfortable with VBA coding, it should not be much of a
problem in a form or a report.
 
D

Duane Hookom

You can add fields to your Data Definition table to store the width of
columns and position of labels.
 
A

Albert D.Kallal

Laurel said:
Is it reasonable to attempt to change the width of columns and position of
labels on a form dynamically?
Click to expand...

Yes, I do this for some of my continues forms. Here is some screen shots of
those forms

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Here is some code that sets the datasouce for the controls...and sizes
them....

intControls = UBound(vFields, 1)

For i = 0 To intControls
strFPtr = "F" & i + 1

' setup field
Me(strFPtr).ControlSource = vFields(i)
Me(strFPtr).Format = vColFormat(i)
Me(strFPtr).Left = lngXpos
lngWidth = vColWidths(i) * 1440
Me(strFPtr).Width = lngWidth
Me(strFPtr).Visible = True

lngXpos = lngXpos + lngWidth
lngFormWidth = lngFormWidth + lngWidth

Next i

in the above, I have a max of 10 columns, but often only 3, or 5 columns.
The above is thus dynamic...at least up to a max of 10 columns....

The controls are named f1....f10. You can see in the above, I set the
contorlsouce, the width..and even the format for phone numbers etc. In
addition, you can supply the form with any sql you want.....and I simply
fill in the source values of each column....

So, as long as you just need a dynamically form...fine. If you are taking
about a design that needs fields *added* to the given design, then you have
a MUCH different problem. A problem that we were discussion just the other
day here....

http://discuss.joelonsoftware.com/default.asp?design.4.319460.16

You can well see that most of the developers conclude that modifying table
structures at runtime for a appcation is just not going to work. By the way,
your question DOES NOT suggest you are actually doing this!!!
 
A

Albert D.Kallal

Laurel said:
Is there anything you can do to "refresh" the database after having
removed deleted controls? Doesn't "repair/compact" do that sort of thing?
Click to expand...

Do a compact and reprot. Open form in desing mode....save as temp new form
name...

Delete orgnal...ranme the save as form....
At this point, no one has suggested how I might add and remove controls
programmatically. Is there actually a way? As I had suggested in my
original e-mail, making them invisible and using function names seems to
be the only route I can think of.
Click to expand...

See my other post. I *often* build forms where the sql is defined at
runtime..and the controls are populated with that sql...

Just remember to NEVER create the controls at runtime. So, build a form with
a max of 10, or perhaps 15 columns, and use the ideas in my other post.

It works well, and does not modify the application at runtime (you do NOT
want do that..as your code would become un-compiled..and serous performance,
and even stability issues will arise.
 
L

Laurel

Thanks to all. This is good stuff.

Albert D.Kallal said:
Yes, I do this for some of my continues forms. Here is some screen shots
of those forms

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Here is some code that sets the datasouce for the controls...and sizes
them....

intControls = UBound(vFields, 1)

For i = 0 To intControls
strFPtr = "F" & i + 1

' setup field
Me(strFPtr).ControlSource = vFields(i)
Me(strFPtr).Format = vColFormat(i)
Me(strFPtr).Left = lngXpos
lngWidth = vColWidths(i) * 1440
Me(strFPtr).Width = lngWidth
Me(strFPtr).Visible = True

lngXpos = lngXpos + lngWidth
lngFormWidth = lngFormWidth + lngWidth

Next i

in the above, I have a max of 10 columns, but often only 3, or 5 columns.
The above is thus dynamic...at least up to a max of 10 columns....

The controls are named f1....f10. You can see in the above, I set the
contorlsouce, the width..and even the format for phone numbers etc. In
addition, you can supply the form with any sql you want.....and I simply
fill in the source values of each column....

So, as long as you just need a dynamically form...fine. If you are taking
about a design that needs fields *added* to the given design, then you
have a MUCH different problem. A problem that we were discussion just the
other day here....

http://discuss.joelonsoftware.com/default.asp?design.4.319460.16

You can well see that most of the developers conclude that modifying table
structures at runtime for a appcation is just not going to work. By the
way, your question DOES NOT suggest you are actually doing this!!!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal
Click to expand...
 
Top