By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,983 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,983 IT Pros & Developers. It's quick & easy.

Please tell me the best way of doing this....

P: n/a
Hi there, i know theres a number of ways I can achieve this but want
to know the best, (but still quite simple).

Up until a year ago I never used Access but have designed a few
databases for work. I am working on Access 2000.

I have basic SQL/VB skills - and am pretty accomplished at putting the
databases together.

Anyway...I've created a database to keep track of "Dayworks" we are
doing
(we are a building services company). I've done most of it, create
orders, quotations, pricing etc. I am having trouble thinking of the
best way to do this:

On the "Generate Quotation" form I have a tab control. Lots of
different things going on (like a contacts list box from underlying
contacts table etc) select the recipient etc. All works fine apart
from the "Exclusions" tab. I have a table called "Exclusions" with two
fields, [PRef] & [PText]. i.e.

[PRef] [PText]
"P21" "Any builders work"

Basically I want all the clauses in the table to be in a list box (or
continuous form) with a check box by the side. The user then selects
which clauses he wants in the letter of quotation.

And another thing that could complicate matters is the form is
unbound, and info is only saved to the "Correspondance History" table
on the click of a button (to get round people starting new quotes and
not finishing). On the click of the button, (which executes SQL code
to INSERT into tables), I have to insert the [PRef] and [PText] fields
into the "Exclusions Trail" table FOR EACH RECORD THAT IS CHECKED.

In other words, when the report comes to generate the quote, in the
exclusions part of it it opens up "Exclusions Trail" and lists every
value where the LetterRef = the LetterRef of this quote (i think this
is the right way to do things). So in other words, the "Exclusions
Trail" table has an entry for every exclusion on every quote done, and
when the report comes to generate it picks the ones relevant to this
letter.

So, anyone care to explain how I add each record that is checked to
the Exclusions Trail table - or even generally point me in the right
direction.

I've already tried having a ListBox, single selection, and onclick it
adds it to the "Exclusions Trail", but this goes against the whole
ethic of saving everything on one click, plus it really needs to be
check boxes.

Any help appreciated, replied on here as I cant check personal email
here.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.