Create dbase and Form to add Client & report access

If at all practical, stay away from hard coding report names and
titles into labels on your form or into the field names of the
underlying table. Businesses and their applications evolve.
Individual reports may eventually fall into disuse. New reports will
come into being. You would be continually going back to re-design and
coding of your table form.

I recommend that you create a new table: tblCustomerReport which will
be in a one to many relationship with tblCustomer. It will have a
record for each report for this/each customer.

For Customer Report management, create a form based on tblCustomer and
a subform based on tblCustomerReport.

At this point, you have something that will work and that doesn't
require constant re-design/re-code. However, it leaves lots of typing
to do in order to get the correct reports associated with individual
Customers. Also, those selfsame changes I referenced above would
require individually removing defunct reports from customers' lists.
A new report coming into the system would require manual entry into
every appropriate customer list.

If this were mine to do I would replace the subform above with a
custom multiselect list box. That listbox would load all of the
reports in the database. tblCustomerReport would then be scanned and
each report found would be marked in the listbox. The user could then
add or remove reports from the list by selecting them on or off (I
always provide "Select All" and "Unselect All" so that the user can
decide which way to begin). At the Before_Update event for this form,
all of this customer's records in tblCustomerReport are deleted and
the currently selected list of reports is added. Now you have a
system that will allow you to maintain report lists without manual
data entry.

I don't use the MSLB provided by Microsoft because it's vulnerable to
version changes of Access and the OS but I'm sure you can use it
somehow to achieve the same effects..

HTH
--
-Larry-
--

"ttbbgg" wrote in message
...
Hi there.
Trying to create a dbase that keeps track of client name, their
affiliated
codes (could be more than 1), and the reports they use. We have
about 150
reports but not all clients use every report. Trying to sort out a
proper
dbase and the required form for entry of new client, their code(s),
and
reports they use. I was hoping to have a form that had a series of
checkboxes for each report but if there is a better or easier way to
do it
I'm open to suggestion.