Contract Generator (1 Viewer)

MC1-1

New member
Local time
Today, 22:46
Joined
Jul 19, 2010
Messages
3
Hi, I am maybe a little bit more than a beginner in access but certainly not an expert. I am learning MS Access in my spare time and realise that I will be working inefficiently and should probably talk my employer into employing a professional programmer but I enjoy the challenge and hope to become more proficient and one day to help others in forums such as this.

I am trying to decide the best way to approach the design of a contract generator for my firm.

The end result is a pdf contract that we send to customers to fill in their name and job title and then electronically sign. I have been able to design other forms in access and use these to populate a fillable pdf so that aspect should be fine and it is just the approach to the tables and form controls in access to handle the clause data that I am struggling with.

The contract has 50 clauses (fields) each of which can have a number of different wordings (values). Each clause is independent from each other clause. Examples of clause fieldnames are "Posting arrangements", "Transport arrangements", "Pricing arrangements". Examples of Transport arrangement values are "Customer to post", "We will arrange collection", "Courier to collect".

The form user should be presented with a form with 50 fields (perhaps in a tabbed control) each of which they edit as appropriate. When all fields have been edited they they then click a button to create the contract.

In order to help the form user, I would like to set up some templates of the most common combinations of the 50 field values. Examples of templates would be "Large customer handling their own transport arrangements", "Small customer requiring collection service".

I would need to enable the form user to import a template and then bespoke any of the clauses before saving that form as a customer record. If a new combination of field values is deemed to be worthy of later use the user can click a button to create and name a new template based upon those clauses.

In terms of setting up the tables, I have considered the following tables:

tblContractClause
ContractClauseID, ContractClauseName

tblContractClauseOption
ContractClauseOptionID, ContractClauseID, ContractClauseOptionValue

tblCustomerContract
CustomerContractID, ContractClauseID, ContractClauseChosenValue

tblContractTemplate
ContractTemplateID, ContractTemplateName, ContractClauseID, ContractClauseTemplateValue

In terms of setting up the user form in access I have considered a tabbed control with 5 tabs each with 10 clauses to edit. The clauses would be chosen by combo boxes which allow a user to.
(i) choose a clause and don't edit it
(ii) choose a clause, bespoke it and add that bespoke clause to the this combo box choices in future
(iii) choose a clause, bespoke it but don't add the clause to future this combo box choices

So that sounds reasonable to me so far however I could be well off the mark and quite open to be told as much.

Here is where I am stuck and would be grateful for a pointer in the right direction - I am more than happy to research the answers given the right pointers.

Is this the right approach:

(a) Set up the form with 50 comboboxes each with its own query based upon tblContractClauseOption. Or could one query be used to populate all comboboxes?
(b) For each clause combobox, enable the user to choose from options (i), (ii) or (iii) above. Based upon that choice do something - not sure how yet.
(c) Set up a template selector combobox with a query behind it. Based upon that choice loop through and populate the values from the query into the combobox fields.
(d) Add a save button to loop through the combobox controls and add the values to
(i) tblCustomerContract.
(ii) optionally tblContractTemplate.

Sorry for the long explanation but hoping the context will be useful.

Thanks in advance for any help/pointers.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:46
Joined
Feb 19, 2002
Messages
36,894
I am not sure how to integrate this with a fillable PDF but I'll tell you how I solved the problem for an insurance company who needed to fill out various contract documents as well as hundreds of endorsements per policy type.
The application used Word documents with bookmarks. The users created the Word documents and then using forms in the application mapped the bookmarks for each document to a data field. The challenge of this application was that the universe of data fields was variable and the company was creating several new policy types each year so rather than create tables for each policy type which would put us into a constant mainetnance cycle, I used an Entity Relationship model which is essentially an ultra normalized schema. It isn't particularly efficient for processing large sets of data but any given policy actually had a limited amount of data so performance was never an issue. And due to the type of application, there were no full table type reports that were necessary of the Entity Relationship data.

There was a table of "fields" with attributes including the data type and whether or not there would be a combo associated with them
The PolicyFields table than linked policies to the fields that would be used for that policy
The PolicyDocuments table linked the word documents to a particular policy
The Documents fields table linked the fields for a document to the PolicyFields table.
I'll post a couple of pictures of the forms to give you an idea of how the data entry looked.
This is the form that links the fields to a Policy type
DGT_ProductDefinition.JPG


This is the main data entry form. The fields in the top part are required for all policy types and so they are "normal". The variable fields are in the subform. The Policy definition form above defines which fields are required. But the app doesn't check that until you print a document. Then it checks that all fields used in the document that are marked as required are filled in.
DGT_DataEntry.JPG


This last picture is of the Endorsement mapping form. It lists the endorsements for the policy on the left. Top right defines the standard fields. and bottom right lists the variable fields. There is a tab on the form above that links endorsements to the policy for a particular client.

DGT_ThreeSubforms.JPG
 

MC1-1

New member
Local time
Today, 22:46
Joined
Jul 19, 2010
Messages
3
Hi Pat
That is absolutely fantastic feedback thank you.
It will take me some time to work through things and there are a number of great things that you have prompted me to explore.
I will come back to you as soon as I have implemented as much as I can.
Many thanks again, really helpful.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:46
Joined
Feb 19, 2002
Messages
36,894
You're welcome:) The app above manages thousands of word documents and allows users to make new ones and even create new data fields all without programming support because of the table design.
 

Users who are viewing this thread

Top Bottom