Cascading Combo Issue

[Guest]

Hello,
I have a registration form for my employees that pops up from a button on
the Employee Information screen. This form is connected by the EmpID and
shows all that employees training registrations as well as being able to add
new. I first set it up just for new registrations. Among other information
I have a Course Name (cboCourse) and a Trainer Name (cboTrainer). Once a
course is selected, the trainer box is filtered with trainers for that
specific course. Works great.

However, I realized that at times this registration info would need to be
updated. Say if an employee was scheduled to take that training but missed
it due to vacation. The registration would still be tracked stating a status
of 'Vacation'. When the employee came back and made up the training, the
'Vacation' training would then be updated to 'Completed' and any other
changes necessary, including date and trainer. But because of the cascading
code in my cboTrainer box, when viewing all this employees registrations, my
trainer box is blank. I have to re select the course in the cboCourse box
which then activates the filter for the cboTrainer box. Still it doesn't
fill the trainer that was previously selected. If I remove the cascading
code, the trainer name is filled in all previously registered trainings like
I would like to see it, however, if I go to make changes or add a new
training, then of course my cascading filter is non existent and the
cboTrainer box lists all the trainers in my database.

My cascading code is below. My question is, can I have both? I need the
trainer names filtered based on which course is chosen but when updating
previous registrations I need to see which trainer was selected as well as it
still filtered for that course without having to reselect it from the combo
box to reactivate it.
Hope that all makes sense and thank you in advance for any advice.

Private Sub cboCourse_Click()
Dim sqlString As String

sqlString = "SELECT TR.TrainerID, EMP.LastName & ', ' & EMP.FirstName
FROM tblTrainerReg as TR, tblEmployees as EMP, tblTrainers as T WHERE
TR.CourseID = '" & cboCourse.Value & "' AND TR.TrainerID = T.TrainerID AND
T.empID = EMP.empID"


cboTrainer.RowSourceType = "Table/Query"
cboTrainer.RowSource = sqlString

 

[Guest]

I found my answer elsewhere. Here's my solution in case anyone else runs
into this problem.


On the forms On Current event I put the row source as normal:
-------------------------------------------------------------------------
Private Sub Form_Current()
Me.AllowEdits = Me.NewRecord
Trainer_ID.RowSource = "SELECT qryTrainerNames.TrainerID,
qryTrainerNames.TrainerName FROM qryTrainerNames;"
End Sub
----------------------------------------------------------------------
This showed the trainer name as entered in previously registered training
courses.
Then, on my Trainer names combo box Got Focus Event, I used the following
code that allowed for the box to be filtered based on the course chosen
----------------------------------------------------------------------------------
Private Sub Trainer_ID_GotFocus()
Trainer_ID.RowSource = "SELECT TR.TrainerID, EMP.LastName & ', ' &
EMP.FirstName FROM tblTrainerReg as TR, tblEmployees as EMP, tblTrainers as T
WHERE TR.CourseID = '" & cboCourse.Value & "' AND TR.TrainerID = T.TrainerID
AND T.empID = EMP.empID"
End Su
----------------------------------------------------------------------------------
So the box is blank if it's a new registration and once I choose a course,
the box filters the names

If it's a current record, it shows the trainer and if I need to edit the
trainer I click on the drop down and it gives a filtered list for that
course.