E
E.Q.
I'm working on a database that includes tracking employee training. The db
includes a number of tables, including tblTrainingClass (PK - lngClassID),
tblEmployee(PK - chrEmpID), and an intersection table (tblTrainee) which
contains just those two key fields.
I have a from to enter class information with a subform used to populate the
intersection table with employee IDs.
I'm working to create a unique front end for a group working off-site. They
are identified by a field in tblEmployee (lngDept = 9), and I used that to
create the source query for the combo box in the subform.
PROBLEM: Most records in the subform are for employees for other
departments; by limiting the combo box source to the query it displays an
empty box for each record.
I've tried a couple work-arounds; first, I created an alternate training
class form that used a query to only show classes for which employees in Dept
9 attended. I did this with a query that looked like this:
SELECT DISTINCT tblTrainingClass.*
FROM tblTrainingClass INNER JOIN (tblEmployee INNER JOIN tblTrainee ON
tblEmployee.chrEmpID = tblTrainee.chrEmpID) ON tblTrainingClass.lngClassID =
tblTrainee.lngClassID
WHERE (((tblEmployee.lngDept)=9));
This provided a nice filter for the form, but I couldn't add a new training
class.
The other work around felt more a sleight of hand trick; I added an unbound
text box and used DLookup to display a name and carefully positioned it in
front of the combo box such that the text box shows the name of the employee
(regardless of department). This looks a bit better except for the record
currently selected in the subform will show blank for any employee not in
dept 9. (the subform is in continuous view).
BTW, it is possible for a training class to be attended by employees from
any department. (One result of meetings was the need to keep each other
informed of respective training so we can piggyback training and save cost).
So I would like for the training form to reflect all training.
I have something that is functional, but doesn't seem very elegant (with the
sleight of hand thing). Is there a better way to create a front end? Can
either of the methods I've tried be refined to create a more robust and
refined application?
Thanks in advance,
EQC
includes a number of tables, including tblTrainingClass (PK - lngClassID),
tblEmployee(PK - chrEmpID), and an intersection table (tblTrainee) which
contains just those two key fields.
I have a from to enter class information with a subform used to populate the
intersection table with employee IDs.
I'm working to create a unique front end for a group working off-site. They
are identified by a field in tblEmployee (lngDept = 9), and I used that to
create the source query for the combo box in the subform.
PROBLEM: Most records in the subform are for employees for other
departments; by limiting the combo box source to the query it displays an
empty box for each record.
I've tried a couple work-arounds; first, I created an alternate training
class form that used a query to only show classes for which employees in Dept
9 attended. I did this with a query that looked like this:
SELECT DISTINCT tblTrainingClass.*
FROM tblTrainingClass INNER JOIN (tblEmployee INNER JOIN tblTrainee ON
tblEmployee.chrEmpID = tblTrainee.chrEmpID) ON tblTrainingClass.lngClassID =
tblTrainee.lngClassID
WHERE (((tblEmployee.lngDept)=9));
This provided a nice filter for the form, but I couldn't add a new training
class.
The other work around felt more a sleight of hand trick; I added an unbound
text box and used DLookup to display a name and carefully positioned it in
front of the combo box such that the text box shows the name of the employee
(regardless of department). This looks a bit better except for the record
currently selected in the subform will show blank for any employee not in
dept 9. (the subform is in continuous view).
BTW, it is possible for a training class to be attended by employees from
any department. (One result of meetings was the need to keep each other
informed of respective training so we can piggyback training and save cost).
So I would like for the training form to reflect all training.
I have something that is functional, but doesn't seem very elegant (with the
sleight of hand thing). Is there a better way to create a front end? Can
either of the methods I've tried be refined to create a more robust and
refined application?
Thanks in advance,
EQC