Access form assistance requested

[dknuth]

I have a small database (3 tables with a total of < 12 fields). It
will be used to identify users and owners of custom reports. There are
40 reports in a folder and roughly 50 people. Each report will have
one or more owner and each report will have one or more user.

The table structure looks like this:

Reports
ReportID
ReportName
ReportDescription

Users
UserID
UserLastName
UserFirstName
UserDepartment

Distribution
DistID
ReportID
Owner
User

I wish to design a form that allows a person to:
1 - pick his name from a list.
2 - view the list of 40 reports and check the "User" box or and/or
the"Owner" box next to each report he uses or owns.
3 - click on a Save button. The Save button will create a record in
the Distribution table for each report that the person checked.
Therefore, if a person uses 5 reports and owns 2 reports, 7 records
will be appended to the Distribution table.

I am at a loss as to how to make this happen. Can anyone help?

Thank you.

 

[strive4peace]

subform listing all Reports that can be selected and checkbox
---

Hello (what is your name?)

change structure of Distribution to:

Distribution
DistID
ReportID
UserID
TypeID

creates Types table

Types
TypeID, autonumber
Typ, text -- "Owner" or "User"
(am assuming Owners and Users come from the same people)
(Type with "e" on end is a reserved word -- that's why it is being
called "Typ")

rather than checking a bunch of reports and processing a batch, the way
your data is stored, the code must do one at a time...but it will be
transparent to those using the form...

~~~
mainform: base on Users table

unbound combobox to find User based on UserID in mainform header

make sure UserID is there

unbound combo with bound field TypeID

~~~

subform: based on Distribution table joining to Reports table

RecordSource:
SELECT R.ReportID
, R.ReportName
, R.ReportDescription
, D.UserID
, D.TypeID
, IIF(IsNull(D.DistID),false,true) as HasRpt
FROM Distribution as D
RIGHT JOIN Reports on D.UserID = R.UserID
LinkMasterFields: UserID
LinkChildFields: UserID

in the subform, make HasRpt a checkbox

on the click event of the checkbox:
1. is it is being checked, append a record to Distribution using SQL
2. if it is being unchecked, delete a record from Distribution using SQL

~~~
although you have called your table "Users", a more appropriate name
might be "People" so as you add different types of People to the
database, they can all be stored together...
~~~

once you get this far, post back with specific questions and we can help
you more

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*