Looking for some suggestions on many forms

[NJudson]

I'm running Acces 2k and I have a database that basically is an audit program. I have approximately 80 tables that are "Master Data" tables and then the set of "Raw Data" tables. What I have here is 5 regions with 16 "Master Data" tables apiece. (5x16 = 80). A user will typically update 1 region at a time and not all 80 tables. I suppose I can create 80 forms and manage them from a main switchboard form but there's gotta be a better way...right?!? I would say about 50% tables only have 2 fields, 30% have between 7-12 fields and the other 20% have between 20-32 fields.

I believe I can do this one form at a time but thought that there may be people on this forum who has experience with something like this and I could learn from them. Thanks for any ideas.

 

[llkhoutx]

I'm doing something similar.

I have 78 forms with common and non-common fields. Spent a lot of time designing the tables then wrote a little program to build them with VBA. That worked very well. Modifications to the tables are also accomplished by VBA.

I have a main form with 3 combo boxes (Category, sub-Category and sub-sub-Category) which determine the subform to be dislayed via setting a dummy subform source object with the appropriate subform name. This works very, very well in a multi-user environment because big records are avoided, many of the tables have short rows.

I also create common form designs which are copied to the extent possible. They have common overhead info and keys. When the source object is set, I also set the source object record source with VBA. Most froms look and feel the same. The only exception is that I have single record forms (with many fields filling the screen) and a few continuous forms (with a few fields which fit on one or two rows). The optomizes user input.

The key is that the forms have about 10 common fields and 2 or more other fields. Common field & control event VBA code is developed once and placed in public modules so as to be available to all forms.

The most important this is getting the table design fixed first. Modifications to tables cause lots of grief using this method because that usually precipitates form modification.

Hope the foregoing isn't too cryptic.

 

[NJudson]

Thank you for your response. Sorry it took a while for me to respond. This give me some good ideas on how I'll carry out this task.

 

[llkhoutx]

I suppose that I could use one table with 800 columns instead of 80 tables, or fewer columns with field datatype indicators, and then another table with a field map. If I did the latter, I, let alone someone else, wouldn't be able to understand the VBA, the day after the code was generated. In six months it would be a nightmare.

Understand table sturcture and code by mere inspection beats elegant structure every time. I'm a firm believer in "keep in simple stupid."