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.
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.