Best way to show multiple tables on one form?

[MikeDuffield]

Good afternoon all,

I typed a long message earlier and got a database error, so excuse me if this is a little short.

I'm looking for a solution to the following issue:

I have three tables. Each heading is a criteria. Each row is a customer account number. On each row I can assign one letter to each criteria, which are "B", "W" or "S".

What I need is a form that shows this data and allows it to be modified. Usually I would use "Add existing fields" and design my form around those fields. Why can't I now? Because the user can add a new field in using an "Add field" button I made.

So, my question is, how do I make the form automatically show any new fields that users have added? Is it even possible??

Sorry if this is obvious, I've searched with no luck and am pretty new to Access!


Thanks in advance

Mike.

 

[pbaldy]

What do the new fields represent? I can't imagine a properly normalized database that would need to have fields added by the user. You almost certainly have a design issue.

 

[MikeDuffield]

I may well have design issues - I'm very new to this.

Basically, at my company we do "CPS" (Customer Perception Survey". So, on the database there is 4 tables - one contains customer data (Name and account number). The other three are as follows:

"expected", "bestother" and "competitor"

These three tables contain criteria that the customer finds important - for example, price, leadtime, quality etc...

The reason there is 3 tables is because we like to compare ourselves to what the customer expects and the customers best other supplier and also against another competitor. Underneath each criteria we enter "B", "S" or "W" (Better than, Same as or Worse than)

At the moment each of the previously mentioned tables are laid out like this: (Where Criteria 1 would be Price, Criteria 2 would be quality etc...)

Criteria 1 | Criteria 2 | Criteria 3
Customer number B S W
Customer number S S W
Customer number B B B

Does that make sense?

I assume this is not the best way to do it, but I can't think of a better way myself.

Thanks for your time!

 

[MikeDuffield]

Sorry to bump again so soon, can anyone help?

 

[Trevor G]

Mike, can you upload your database take out any sensitive data and someone will take a look.

 

[MikeDuffield]

Thanks Trevor,

There's no sensitive info in there at the moment anyway so that's fine.

See attached.

Based on my previous explanations I'm sure you'll see what I'm trying to achieve. I'm happy to do a total re-design if need be, I understand how I've done it may not be the best way.


Thanks for any help,
Mike.

 

[Trevor G]

Am I right in thinking that the form DataMain is to display the fields from all of the tables and if you add a field through your Add New Criteria form then when you go to the DataMain it should then update the form showing the New Field?

You have some issues to look at. In the AddCriteria form if you add a space between names it will not accept it, if the name already exist it will take you to the debug window, so you need to look at some sort of error handling here.

You haven't used any of the correct naming convension to define what is a table, query, form, report, macro and module. which you should do because as you need further assistance it makes it a lot easier to follow someone elses database if naming conventions are followed.

For the rules look at these links:

http://www.acc-technology.com/namconv.htm

http://www.databasedev.co.uk/naming_conv.html

There are no relationships from the tables so are they to display different forms? How are you going to compare each table?

 

[MikeDuffield]

Hi Trevor,

Regarding paragraph 1, you're correct, that's what I'm trying to do. Is it easy to do? That's what I'm stuck on to be honest.

I'm aware of the need to error trap, that's fine.

Thanks for the heads up on the naming errors, I'll get those fixed.

 

[Trevor G]

The only way I can think of is to create individual forms first like a spreadsheet (Datasheet View), then place them on your main form.

So no VBA required at this level.

If you then add an extra field as this is based on the table the subforms should show the new field, a question though is, if extra fields are being added after the forms where being populated you would need to then go back to past records and update them.