Hi Chris,
This is a long reply, but I've assessed three solutions for you - your
two solutions (using Checkbox and Listbox) and solution that comes out
of a textbook (using two Listboxes).
Have a look at them and decide what's best for you. If you want my
help in implementing any of them, contact me at my email address (at
end) and I'll send you a database with the necessary code in it.
In summary, you want to know:
What's the best straightforward way to solve this?
How do you add records to the Exclusions Trail table?
NB: I refer to your "Generate Quotation" form as the "Quote" form.
1. Your Solution - Checkboxes
On the Exclusions tab of the Quote form, you want to select Exclusion
clauses using checkboxes.
There's a possible problem with this solution.
To display checkboxes, you'd do something like this:
1. Add a boolean field, called "Selected", to the Exclusions table.
2. Create a new form whose data source is the Exclusions table.
3. Name the form, say, "frmExclusions".
4. Put the three fields ("Selected", "PRef" and "PText") on the
form.
5. Set the form's "Default View" property to "Datasheet".
6. Set the form's "Views Allowed" property to "Datasheet".
7. Close frmExclusions.
8. Open the Quote form in design view.
9. Position the form so you can see the Database window.
10. Click the Exclusions tab to bring it to the front.
11. Click the Exclusions page so it has handles.
12. Drag the new "frmExclusions" form from the database window to
the Exclusions tab of the Quote form.
13. Run an update SQL from the Form_Open event of the Quote form to
set the "Selected" field to "False" each time the Quote form opens.
You've now got checkboxes for selecting clauses.
The problem is that the Quote form would now also allow users to edit
the PRef and PText fields. What would happen if they did? Chances
are someone will sooner or later.
That's the disadvantage of this solution.
However, appending records from the Exclusions table to the Exclusions
Trail table would be easy. Using the On_Click event of the command
button on the Quote form, you'd run an append SQL statement where the
"Selected" field = True.
The SQL would also need to write the LetterRefID to each appended
record.
I assume (1) that the LetterRefID is created by the Quote form, (2)
that it is written to the Correspondence History table and the
Exclusions Trail table, and (3) that its purpose is to create the
one-to-many link between the Correspondence History table and the
Exclusions Trail table. (Section 4 below contains more information
about this when you get to it.)
2. Your Solution - Listbox
As you say, a Listbox with its Multi Select property set to "None"
wasn't ideal. (You don't want to add records to the Exclusions Trail
table one at a time.)
However, this solution would work OK if tweaked a bit. It would be
better than the checkbox solution above.
To make the listbox solution work, you'd do something like this:
1. Create a Listbox on the Exclusions tab of the Quote form.
2. Set the Listbox's Name property to "LstExclusions".
2. Set the Listbox's Row Source property to the Exclusions table
(or a Select SQL statement).
3. Set the Listbox's Multi Select property to Simple.
4. The user can now highlight more than one item in the list.
5. No action is triggered when the user selects items.
6. Add some code to the Quote form's command button.
The code would go through the "Me.LstExclusions.ItemsSelected"
collection. For each item in the collection, a record would be added
to the Exclusions Trail table. This is straightforward using DAO.
The possible disadvantage with one Listbox (believe it or not) is that
some users will ask: "Which exclusion clauses will be included in the
quote? The white rows or the black rows?"
You could put a label on the form to make it clear, or you could
implement the third solution (below).
3. Textbook Solution - Two Listboxes
This solution would give a professional look to your database.
It isn't difficult to implement. Everything's already done for you.
You'd import a form and a class module from an example database (which
I could send you). You'd copy the form (actually the controls on the
form) to the Exclusions tab of your Quote form, then delete the
example form you imported. This would place two Listboxes with some
left- and right-pointing arrows between the listboxes on the
Exclusions tab. You'd tweak the Listboxes so they'd work with your
Exclusions table.
To make the two Listbox solution work, you'd do something like this:
1. Add a boolean field, called "Selected", to the Exclusions
table. (Same as Solution 1.)
2. On the Exclusions tab of the Quote form:
(a) The left Listbox would be headed "Available Exclusions".
(b) The right Listbox would be headed "Selected Exclusions".
3. Initially, all exclusions would be in the left Listbox.
4. The Listboxes would have their Multi Select property set to
Simple.
5. The user selects one or more clauses in the left listbox.
6. The user clicks the right arrow.
7. The selected clauses are moved to the right Listbox.
8. The user can move clauses back to the left Listbox.
9. No action is taken yet (ie writing of records).
10. When all clauses to be included in the quote are in the right
Listbox, the user clicks the command button.
11. Just as in Solution 1, you'd run an append SQL from the command
button on the Quote form. This would append records from the
Exclusions table to the Exclusions Trail table where the "Selected"
field = True. Also it would add the LetterRefID to each appended
record.
Unlike Solution 1, you don't need code to set the "Selected" field to
False. That would be taken care of by the class module as the Quote
form opens.
To make things work, you only need to CALL the code in the class
module. You don't need to know how it works. So things are pretty
straightforward.
4. Correspondence History Table
Finally, it seems you're linking the Correspondence History table and
the Exclusions Trail table using a LetterRefID number. It appears
this is unique to each quote and is generated by your Quote form.
Linking these two tables with a unique number is definitely the right
way to go.
You've probably done this already, but for the record. You want to
create a "LetterRefID" field in the Correspondence History table
(where it would be the Primary Key), and you want to create a
"fkLetterRefID" field in the Exclusions Trail table (where it would be
the Foreign Key). You should set up a one-to-many relationship
between the two tables.
If the LetterRefID is not unique to each quote, you'll end up in a
real mess. So...
If you're happy with the way you're generating unique LetterRefIDs,
then your Quote form can happily remain an unbound form.
But if creating unique LetterRefIDs might be a problem, then it'd be
better to have the Quote form bound to the Correspondence History
table. The LetterRefID field should be AutoNumber as its DataType.
Then you're guaranteed a unique number for each quote.
If your Quote form is bound to the Correspondence History table, you'd
have to be careful to put command buttons on the Quote form for
"Quote" and "Cancel". It'd be important that these buttons should be
the ONLY way to add new records (or cancel the adding of new records)
to the Correspondence History and Exclusions Trail tables. To ensure
this, you'd set the Quote form's "Data Entry" property to "Yes",
disable the form's close button, Control menu and record navigation
buttons, set a number of other form properties, and put appropriate
code in the two command buttons (eg to check if the form is dirty
before cancelling, in which case you'd Undo the form before
cancelling).
I hope this all makes sense and helps you evaluate your options.
Good luck with your project.
Geoff
Remove "nooospam" for correct address:
ge***@ggnewsnooospam.wanadoo.co.uk 
