change form/report fields through vb

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a very simple Access 2000 database, that was previously in Excel 2000,
where operators enter daily totals for different types of activities. Each
activity is a different field in the main table. The user who maintains the
database would now like the ability to add/remove the activity fields by
adding/deleting the field names in a form. I can set up the code to make
these changes to the table, but what is the best way to make the changes to
the input form and report (both of which are simple and just list the
fields)? Is the best option to have code to delete all the text boxes on the
form/report and then have it create text boxes for the valid fields and save
the form/report? Or is there a better option?

Thanks for your help!
 
abbarition said:
I have a very simple Access 2000 database, that was previously in Excel 2000,
where operators enter daily totals for different types of activities. Each
activity is a different field in the main table. The user who maintains the
database would now like the ability to add/remove the activity fields by
adding/deleting the field names in a form. I can set up the code to make
these changes to the table, but what is the best way to make the changes to
the input form and report (both of which are simple and just list the
fields)? Is the best option to have code to delete all the text boxes on the
form/report and then have it create text boxes for the valid fields and save
the form/report? Or is there a better option?
Click to expand...


Excuse me for being blunt here, but this is a terrible
design. You are committing spreadsheet on your database,
one of the deadly sins of database design ;-)

The first thing you should do is call timeout while you
"normalize" the database's table structure (search the web
for what that means). One aspect of normalization is that
you should have a separate table for each different entity
(e.g. people, activities. etc) and use a form or subform for
each entity. Each form's Record Source query can then be
used to select the items to display.
 
I will normalize my tables, but I still have the same question when it comes
to the input form & report. When the user creates a new activity, it will
create a subform for the new activity table. Then it will need to delete/add
the valid subforms (one for each activity) on the form and delete/add text
boxes on the report. Is this the best way to make the changes to my
form/report?
 
Either I don't understand what you're saying or vice-versa.
I just can not imagine a situation where you would need a
new form/report for each activity.

A new member of an entity (Activity) should be just a row in
the Activities table. A form based on the Activities table
should be able to deal with any existing and new activitiy,
no new forms needed. This a fundamental concept of
normalizing the tables to a single kind of entity for each
table, where a row in a table contains the data for a single
instance (and no more) of the table's entity.
 
Back
Top