I am building a database for our HR dept.
I have a combo box that allows the user to select an employee name
(this is completed) then a second combo box that allows the user to
select a report that is available for the employee selected in the
first combo box (this is completed).
So if the user selects John Doe in the first combo box, and Mr. Doe has
a disciplinary record in the Disciplinary Table, a "Disciplinary
Incident Report" is available in the second combo box. Whereas if the
user selects Chuck Norris, who does not have any disciplinary incidents
(Texas Rangers only play it by the book, after all) then no
"Disciplinary Incident Report" shows up in the second combo box. I have
this process completed up to a point. I currently have the two combo
boxes syncronized, with the help of the Sample forms database from MS.
The process is currently utilizing a test query (cboReportsQry) that
has the following fields: Report_ID, Report_Name, EmpID, Emp_Name. This
table only has test data and is working fine. The 2nd combo box pulls
its contents from the query.
I'm not sure how I should create and store the relationship between
each employee and their available report. For instance, after Mr. Does
disciplinary record has been created how should create and store the
record or marker that says "Hey, Doe has an available Disciplinary
report". I could store it in the same table that is currently being
used now, and I like that idea but I'm not sure how.
How do I programmitically have an entry created in cboReportsQry for
each potential report? I can get the query to pull the data from the
Disciplinary_Incident table but I will need it to pull the info from
mulitple tables ("CDL_Certification", "Accident_Incident", etc.) into
the same fields (Report_ID, Report_Name, EmpID, Emp_Name) because the
2nd combo box pulls its contents out of the Report Name and Report ID
fields.
