A better way to multiselect data and use controls to update a record source

[GeoffreyB]

I've read a few previous posts but none have really given me the answer
I was looking for (I'm also very new to access). I am creating a form
generator that would allow users to select multiple clauses (they vary
in length from 25 to over 255 characters) from a list to be output to a
report. I would like to create a form that allows the user to view the
complete listing of clauses and simply highlight which ones they need.
I also would like to give the user the ability to create additional
clauses to add to the existing clause list and the ability to edit and
delete existing clauses.

At present the best way I have found to do this is through creating a
datasheet based on a table that includes a clause field and a selector
Yes/No field. The report is then based on a query which displays the
clauses which match the criteria of Yes.

However, I saw a similar program which allows the user to select from a
list box which clauses they would like to add, and then by using some
sort of macro control they can add the clause to the report. Also, the
form had the ability to display the full text of the listbox, which was
condensed due to the window size, in a large text box below the list.
Increasing the wow factor for me was the fact that there were simple
control buttons which allowed the user to add/edit and delete clauses.
Unfortunately, the program was proprietary so I couldn't take a peak at
some of the code. Any tips that anyone has to help me improve my
existing form generator to be more like the one I mentioned I would
greatly appreciate.

 

[Allen Browne]

Geoffrey, a multi-select list box is not the right way to approach this kind
of data.

In a relational database, one of the basic rules is to ensure all fields are
atomic. That means you don't store multiple pieces of information (such as
multiple clauses) in one field. Instead, you create
a) Comment table (one record for each boilerplate clause):
CommentID (primary key)
Comment (memo)

b) xxx table (your existing table, with an xxxID primary key)

c) xxxComment table (one record for each clause added to an xxx record:
xxxCommentID primary key
xxxID relates to xxxID above.
CommentID relates to CommentID above.

Now you will use a subform, where the user can select one comment per row,
related to the record in the main form. No code needed. Easy to query (all
in one field), and so on.

If you are actually trying to create a letter where the clauses can be
combined and then modified, that's a different approach again.

 

[GeoffreyB]

Thanks for the great response. I've been stumped for a week or so on
this and your advice has really helped. However, I have one last
question in relation to your response. What is the relationship
between xxxID and xxx table? I am having a hard time understanding how
it fits together. Please pardon my ignorance.

Geoffrey

 

[Allen Browne]

"xxx" stands for whatever table you currently have.
I assume this table has a primary key, which I referred to as "xxxID".

The important concept is that table (c) in the suggestions has a many-to-one
relationship with your existing table. This table therefore has a foreign
key field that links to the primary key of your existing table.

To take a concrete example, if your existing table is tblVisit, with a
VisitID primary key, and you want to have many comments related to the
visit, then your new table will be VisitComment. It will have a
VisitCommentID primary key, and also a VisitID field that relates to
tblVisit.VisitID so you know which visit this comment relates to.

 

[GeoffreyB]

Thanks for the responses Allen. I think I finally have a handle on
things.

Geoffrey

 

[GeoffreyB]

Using the response you provided as a sample with tblVisit and
tblVisitComment. How can I construct a form for the user where the
tblVisitComment.VisitID will be automatically linked to the
tblVisit.VisitID primary key. For example, if the tblVisit.VisitID
field is "Trip to Hawaii" is there a way for me to specify in the form
that I only want tblVisitComment.VisitCommentID to correspond to "Trip
to Hawaii" and have all records entered in tblVisitComment
automatically show "Trip to Hawaii" in the tblVisitComment.VisitID box?
Thanks

Geoffrey

 

[Allen Browne]

I think you are wanting to limit the VisitCommentID combo so it shows only
those comments that relate to a particular VisitID?

See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm