If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
populates several records in a single form window
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 a- 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. |
#2
|
|||
|
|||
populates several records in a single form window
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 "smk" wrote: 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 a- 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. |
#3
|
|||
|
|||
populates several records in a single form window
Many thanks Sprinks. It gaves me some idea on how I should go ahead creating
the form and reports. "Sprinks" wrote: 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 "smk" wrote: 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 a- 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. |
Thread Tools | |
Display Modes | |
|
|