cascading combo, a different kind of query!

[Guest]

I am currently designing a training records database. I am still working on
the structure but a suggestion is that I take the list of training
supervisors straight from the employee list (using a check box).

I have a query listing all the supervisors. In the subform I have a field
for the instructor who run the course. I can obviously use the query to
select all those employees that are supervisors but, for each employee, I
want to select supervisors excluding the current employee. Every time the
employee record changes, the instructor list in the subform changes so that
it excludes that employee. Would like to point out that not all employees are
supervisors (but I can't see that would be a problem)

I think I have got the following vaguely correct.

SELECT [Supervisor].[name] FROM supervisor WHERE
((([supervisor].[name])<>[forms]![Employee]![Name]));

I am not sure about the "after update event". At the moment I have the
following:

Private Sub Name_AfterUpdate()

[Forms]![employee]![employee subform].instructor.Requery

End Sub

Cheers for any help.

 

[Guest]

I forgot to point out that this isn't a normal cascading combo where I have a
second option list based on a first option list (but I hope that might have
been apparent!).

Also, the after update event is in the "Name" field in the main form.

 

[Douglas J. Steele]

Okay, so you've got a subform on form employee. Is the name of the subform
control on form employee "employee subform"? (Depending on how you added the
subform to the form, the name of the control may not be the same as the name
of the form being used as the subform.)

If the name of the subform control is, in fact, "employee subform", and
you're trying to requery a control named "instructor" on the subform, use:

[Forms]![employee]![employee subform].Form!instructor.Requery

If the name of the subform control is something other than "employee
subform", use the control name rather than "employee subform".

 

[Guest]

By "every time the employee record changes", do you mean when the data
changes, (and/)or when the user moves to a different record? If the latter,
you'll also need to requery the combo box when the OnCurrent event fires for
the main form.

BTW, it's also a good idea to avoid the "!" operator where you can - it's
considerably slower than a fully qualified object reference using ".". I
think the only place you can't avoid "!" is in queries. Assuming that your
subform object is called Child1:

Private Sub Form_OnCurrent

Child1.Form.instructor.Requery

End Sub

Hope that helps.

I forgot to point out that this isn't a normal cascading combo where I have a
second option list based on a first option list (but I hope that might have
been apparent!).

Also, the after update event is in the "Name" field in the main form.

I am currently designing a training records database. I am still working on
the structure but a suggestion is that I take the list of training
supervisors straight from the employee list (using a check box).

I have a query listing all the supervisors. In the subform I have a field
for the instructor who run the course. I can obviously use the query to
select all those employees that are supervisors but, for each employee, I
want to select supervisors excluding the current employee. Every time the
employee record changes, the instructor list in the subform changes so that
it excludes that employee. Would like to point out that not all employees are
supervisors (but I can't see that would be a problem)

I think I have got the following vaguely correct.

SELECT [Supervisor].[name] FROM supervisor WHERE
((([supervisor].[name])<>[forms]![Employee]![Name]));

I am not sure about the "after update event". At the moment I have the
following:

Private Sub Name_AfterUpdate()

[Forms]![employee]![employee subform].instructor.Requery

End Sub

Cheers for any help.

 

[Guest]

Sorry,

it is when the user moves from one record to another.

In the "oncurrent" procedure I put the following line and it works a treat.

Me.Instructor.Requery

cheers.

By "every time the employee record changes", do you mean when the data
changes, (and/)or when the user moves to a different record? If the latter,
you'll also need to requery the combo box when the OnCurrent event fires for
the main form.

BTW, it's also a good idea to avoid the "!" operator where you can - it's
considerably slower than a fully qualified object reference using ".". I
think the only place you can't avoid "!" is in queries. Assuming that your
subform object is called Child1:

Private Sub Form_OnCurrent

Child1.Form.instructor.Requery

End Sub

Hope that helps.

I forgot to point out that this isn't a normal cascading combo where I have a
second option list based on a first option list (but I hope that might have
been apparent!).

Also, the after update event is in the "Name" field in the main form.

I am currently designing a training records database. I am still working on
the structure but a suggestion is that I take the list of training
supervisors straight from the employee list (using a check box).

I have a query listing all the supervisors. In the subform I have a field
for the instructor who run the course. I can obviously use the query to
select all those employees that are supervisors but, for each employee, I
want to select supervisors excluding the current employee. Every time the
employee record changes, the instructor list in the subform changes so that
it excludes that employee. Would like to point out that not all employees are
supervisors (but I can't see that would be a problem)

I think I have got the following vaguely correct.

SELECT [Supervisor].[name] FROM supervisor WHERE
((([supervisor].[name])<>[forms]![Employee]![Name]));

I am not sure about the "after update event". At the moment I have the
following:

Private Sub Name_AfterUpdate()

[Forms]![employee]![employee subform].instructor.Requery

End Sub

Cheers for any help.

 

[Guest]

I have put in some dummy info and the list changes for different records.

Cheers

Okay, so you've got a subform on form employee. Is the name of the subform
control on form employee "employee subform"? (Depending on how you added the
subform to the form, the name of the control may not be the same as the name
of the form being used as the subform.)

If the name of the subform control is, in fact, "employee subform", and
you're trying to requery a control named "instructor" on the subform, use:

[Forms]![employee]![employee subform].Form!instructor.Requery

If the name of the subform control is something other than "employee
subform", use the control name rather than "employee subform".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am currently designing a training records database. I am still working on
the structure but a suggestion is that I take the list of training
supervisors straight from the employee list (using a check box).

I have a query listing all the supervisors. In the subform I have a field
for the instructor who run the course. I can obviously use the query to
select all those employees that are supervisors but, for each employee, I
want to select supervisors excluding the current employee. Every time the
employee record changes, the instructor list in the subform changes so
that
it excludes that employee. Would like to point out that not all employees
are
supervisors (but I can't see that would be a problem)

I think I have got the following vaguely correct.

SELECT [Supervisor].[name] FROM supervisor WHERE
((([supervisor].[name])<>[forms]![Employee]![Name]));

I am not sure about the "after update event". At the moment I have the
following:

Private Sub Name_AfterUpdate()

[Forms]![employee]![employee subform].instructor.Requery

End Sub

Cheers for any help.