Question Suggestions for Form \ Report Design

[Meinthecorner]

Hello - Need some suggestions on the way forward with a Form \ Report design.

There are two parts to data, 15 fixed fields, ie customer name, address, tell, serial number etc all laid out in a MS Word Document.

The second part currently consists of 20 MS Word Docs that are just numbered lines of Text with the Bold parts telling the employee what data they need and where to put it. The employee selects the Word Doc depending on the type of customer problem.

So at the moment, the employee fills in the first part of the Word Doc (fixed standard fields), they then select the correct template for the problem at hand and fill in the Bold Area's required, copy the Text and then pasted into the first Word Doc.

........ Before any of this Word Doc Creation, they first have to copy the data from Our Customers CRM system, paste it to columns in an Excel file, then they use the excel file to populate the MS Word Docs.

My problem
----------------

I want to get rid of MS Word and Excel so they paste the data straight into an Access Form(s) The first fixed part is fine, it's how I'm going to deal with 20 Templates that I'm struggling to find a decent solution.

What i would like, is for the agent to select a template name from a ComboBox and after update only show them the fields they need to complete.

Bear in mind that each template has maybe 6 unique data fields, so I could just have a flat table and a form with a huge amount of hidden fields and they only show when their corresponding template is selected. This would result in a form with 200 potential hidden text fields.

My next idea was to have a new form for each template that loads as a popup form. This means 20 new forms to build.

So my question is, Am i missing a way of handling this type of data collection? Any other idea's?

 

[James Deckert]

If you have 20 templates and each has at most 6 unique fields, how are you getting to 200 hidden fields? If most are the same with only a few unique to a template, I'd think hiding the fields would be easier to maintain than to have 20 forms that are mostly alike. I suppose you could have one form with a subform that changed based on the template.

 

[Meinthecorner]

Each template has on average 6 unique fields, each template is different. So 6 fields and 20 templates leaves me with roughly 120 different fields. I'm looking forward and can see more templates being added as different products \ services are created.

 

[James Deckert]

Are the unique fields in a different table or the same table as the fixed fields?
How different are the unique fields for the different templates?
So the 15 fields are the same for all 20 templates?
Providing your table design might help me give better advice.
You should try the form/subform and see if that works for you.

 

[HiTechCoach]

Hello - Need some suggestions on the way forward with a Form \ Report design.

There are two parts to data, 15 fixed fields, ie customer name, address, tell, serial number etc all laid out in a MS Word Document.

The second part currently consists of 20 MS Word Docs that are just numbered lines of Text with the Bold parts telling the employee what data they need and where to put it. The employee selects the Word Doc depending on the type of customer problem.

So at the moment, the employee fills in the first part of the Word Doc (fixed standard fields), they then select the correct template for the problem at hand and fill in the Bold Area's required, copy the Text and then pasted into the first Word Doc.

........ Before any of this Word Doc Creation, they first have to copy the data from Our Customers CRM system, paste it to columns in an Excel file, then they use the excel file to populate the MS Word Docs.

My problem
----------------

I want to get rid of MS Word and Excel so they paste the data straight into an Access Form(s) The first fixed part is fine, it's how I'm going to deal with 20 Templates that I'm struggling to find a decent solution.

What i would like, is for the agent to select a template name from a ComboBox and after update only show them the fields they need to complete.

Bear in mind that each template has maybe 6 unique data fields, so I could just have a flat table and a form with a huge amount of hidden fields and they only show when their corresponding template is selected. This would result in a form with 200 potential hidden text fields.

My next idea was to have a new form for each template that loads as a popup form. This means 20 new forms to build.

So my question is, Am i missing a way of handling this type of data collection? Any other idea's?

Meinthecorner,

What your are describing as solutions are really still Word and Excel (non RDBMS) way and not using the power of a RDMS, like Access.

The way I see it is that you as basically doing a survey. You have many surveys (templates) from which the user can select.

See: At Your Survey (click here)

Example of the RDBMS way:

There are two parts to data, 15 fixed fields, ie customer name, address, tell, serial number etc all laid out in a MS Word Document.

1) the user created a parent record. (5 fixed fields, ie customer name, address, tell, serial number) and a fiedl for "template ID"
2) On the form for the parent record there is a combo box to select the template ID.
3) the after update event of the combo box it would append the template question into the the child table from the template lookup.

One form and sub form can handle as many templates as needed. One report can be used to print any template.

When a database is properly design with normalized tables then you should be able to add, edit or delete templates without any changes to the design of the database forms, reports, etc. You only should have to edit data.