populates several records in a single form window

[Guest]

I hope someone can help me on my problem below:-

A user wanted to key in the data in a single form where she can select where
a particular procedure is located. The procedure could be duplicated and
placed in a few departments.

My tables are as follows:-
tbl_Proc_Dept:-
ProcNo - Text (Primary)
TrgConducted - Yes/No
DeptAbbv - Text (Primary)

tbl_Dept:-
DeptAbbv:- Text (Primary)
DeptDetails:- Text

I created a form where i have the following fields in the form:-
1. ProcedureNo - Text
2. TrgConducted - CheckBox
3. MainDept- ComboBox (DeptAbbv data field)
4. Finance – CheckBox (DeptAbbv data field?)
5. Admin – CheckBox (DeptAbbv data field?)
6. Purchasing – CheckBox (DeptAbbv data field?)
7. Facilities – CheckBox (DeptAbbv data field?)
8. MIS – CheckBox (DeptAbbv data field?)

Thus my questions are:-
1. How can I add multiple deptabbv field in a single form?
2. User needs to print by Dept on the procedures that are located in that
particular department. How do I filter based on a Yes/No data type?

Would appreciate if someone could help me out here.

Thanks.

 

[Guest]

smk,

1. You don't say, but I assume there is also a Procedure table that
consists of a Procedure Number (Primary Key) and a description of the
procedure. There is a natural many-to-many relationship between Procedure and
Department, so you have correctly created a linking table, tbl_Proc_Dept,
which is in a one-to-many relationship with each of them.

You can enter data by creating a continuous subform based on the linking
table and embedding it on a main form based on Procedures, linked by the
Procedure number. In the subform, use a combo box that lists all of the
departments with the following properties:

RowSourceType: Table/Query
RowSource: SELECT tbl_Dept.DeptAbbv, tbl_Dept.DeptDetails FROM tbl_Dept
ORDER BY tbl_Dept.DeptDetails;
Bound Column: 1
ColumnWidths: 0"; x" (where x is large enough to display the widest column)

The main form displays the procedure detail, and you will enter one record
per department in the subform.

Note that because tbl_Proc_Dept is also in a one-to-many relationship with
Departments, you could alternatively base a main form on it, with the subform
now linked on the Department abbreviation. In this case, each record will
show a Department with all of its associated procedures. It's likely that
each of these forms will be useful.

2. To select the procedures for each department, you can use the form
described above, or base a report on a query consisting of the tbl_Dept,
tbl_Proc, and tbl_Proc_Dept tables. Establish two links,

tbl_Dept.DeptAbbr<->tbl_Proc_Dept.DeptAbbr
tbl_Proc.ProcNo<->tblProc_Dept.ProcNo

and select the Procedure name, the Department name, the boolean TrgConducted
field, and the Proc_No if desired. In your report, create a grouping by
Department name, and place the Department name in its header.

Hope that helps.
Sprinks

 

[Guest]

Many thanks Sprinks. It gaves me some idea on how I should go ahead creating
the form and reports.