Design advice - changing data not bound to a form

M

McGeeky

Hi. The new database project I am working on has stepped up a level in
complexity; some of the forms do not map 1 to 1 with a table so the
create/delete/update methodology of Access does not apply (well, at least
that's what I think!).

We need to create relationships between two tables - we are doing this
through the use of an intermediate linking table to create the many to many
joins. The user's experience should be as follows; they click on a check box
on the form and a record should be inserted in the linking table - when they
uncheck the check box the record should be removed.

Is there best practice on how to manage this in Access? E.g. do we have to
use custom SQL insert statements, if so, then should we use a transaction to
do it in, should we create a new connection to do it in, how can we
associate that insert with changes to the table that the form is bound to?

Any best practice/guidance on this would be gratefully received!

Regards,

McGeeky
 
A

Allen Browne

In general, you want to keep the interface as simple as you can, so the
first approach would be things like:

a) Use subforms for related records. (Put the subforms on the pages of a tab
control if screen real estate is the issue.)

b) Use combos for values from lookups (unless the lookups have many
thousands of records.)

c) Use a query if you need to display fields from lookup table(s), but in
general you try to write only one table from any form.

d) Use cascading deletes (or cascade to null) and cascading updates if you
need to reflect changes on to other tables as well.


e) Use the AfterInsert event of the form if you need to create records in
related tables as well (i.e. Execute an INSERT query statement.)

f) Use subqueries in the Filter of the form to show only those records that
have values in the related tables.

That kind of thinking copes with more than 90% of cases. For the example you
gave, this would mean using a combo in a continous subform for the related
records, rather than a check box.

If you must use the check box approach, you might check out the way
Multi-Valued fields work in Access 2007. You may find that using this
version gives you the way to get the interface you want. IMHO, it's not a
good idea because of other issues (e.g. the junction table is hidden where
you can't get at it, bugs, incompatibilities with other databases), but it's
quick and painless to initiate.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top