Automatic form generation.

[novice83]

I am currently setting up a database for an engineering firm, in which any person should be able to enter, create a new table, and then enter data into a form rather than into the table. Each new table will correspond to a new project and will be named accordingly, but each table will have exactly the same fields, hence the forms will be the same.

My problem is that if a person were to create a new table, they would have to use the form wizard to build a form for it, which is quick and easy enough, but the wizard outputs the form in a jumbled mess which then would require a lot of time to re-arrange and make it neat and comprehensible. Is there a way around this as this would be a complete waste of time?

I was thinking along these lines: Since each table has the same fields, i would like every form for each new table to have the same layout, hence i created a form, formated it to make it look good and named it 'template'. Is it now possible that each time a person creates a new table, access will automatically generate a form in the layout of 'template' without having to use the form wizard. I am assuming this will involve VBA, or is there a simpler solution?

Can either of these situations be implemented:

1a) Create a new table b) code automatically opens 'template' c) code links 'template' to new table

or

2a) Create new table b) code builds a form for new table that uses layout of 'template' instead of jumbled mess that wizard produces

Or even better, can i modify the wizard somehow to have it produce a better (in terms of layout) form.

Note: I also have a table named 'template' which corresponds to the from named 'template' and the idea is that a person will copy and paste this 'template' table and then name it after their project. Is this a bad way to go about it? I don't want people to have to manually build tables and forms for each new project as each one will have the exact same fields. Any suggestions?

Sorry for the long post, any help on the form issue will be greatly appreciated. If it involves code i don't expect any1 to waste time providing it for me, the most important thing i need to know is wether it is possible?

[Norie]

It is possible to generate forms through code and I have used that method in the past.

I didn't use it to create forms based on tables but to create forms for navigating through a database.

So I only added buttons and code, not data.

What I'm wondering though is why you actually want to do this?

Why do you want to allow users to create multiple tables with the same data structure?

Surely you should have one table with all the fields, including one that identifies each set of distinct data.

[novice83]

I need a new table for each new project as i already have VBA code in excel which populates a spreadsheet with the data from the corresponding table in access. The idea is that the data for each seperate project will be sent to different sites in spreadsheet format and that is why i can't have 1 table holding all the information regarding all projects. Does this make sense or is there a way to use only one table and split it up into seperate sections somehow?

[Norie]

All you would need would be an extra/column field that held the name/id of the project.

You could adapt your VBA code to populate using either user input of the project name/id or getting that from a range.

This field could also be used to summarize data using total/crosstab queries.

[novice83]

Thanks Norie, i'm gona have a play with the database and see what i can come up with