Multiple Loistboxes + Query

[jdtyler via AccessMonster.com]

Hi there,

I'm sure there must be a simple way of doing this but I can't figure it out
after around 5 hours! Any help much appreciated. I'm using Access 97 if that
makes any difference.

I have a table which lists all the managers in our organisation and a second
table that contains a list of staff. This second table has a lookup to find
the name of that advisors manager so that I can have a combo-box to choose
the managers name when adding a new member of staff. I then have a third
table which contains the actual records and within this I want to store the
advisor name and manager name (amongst other things).

Thing is, we have around 120 staff and so choosing the advisor from such a
long menu would be difficult. I would like to have one listbox that displays
the name of the managers and then a second listbox next to it that will
display the staff that belong to the manager selected in the first listbox.
So basically, the first listbox narrows down the search and you then choose
the actal member of staff from the second listbox.

I know there must be an easy way to do this with a query but I can't find it!

Please help me!

 

[Joan Wild]

I have a table which lists all the managers in our organisation and a
second
table that contains a list of staff. This second table has a lookup to
find
the name of that advisors manager so that I can have a combo-box to choose
the managers name when adding a new member of staff. I then have a third
table which contains the actual records and within this I want to store
the
advisor name and manager name (amongst other things).

Well, you don't need or want to store the Manager name in this third table.
That's redundant; since you store the advisor name in table three, that is
sufficient to know who the manager is - that can always be looked up in the
advisor table.

So basically, the first listbox narrows down the search and you then
choose
the actal member of staff from the second listbox.

Have a look at http://www.mvps.org/access/forms/frm0028.htm for an example.

Combo1 in the example would be to select the Manager (but that wouldn't be
bound to any field in the recordsource for your form). It's just used to
aid in selecting the advisor (in combo2).

 

[jdtyler via AccessMonster.com]

Thanks very much for your help. I can get the second listbox to display the
correct information now using the second part of that solution (I cannot get
the code provided to work and don't understand what SQL statement it is
trying to achieve).

The problem is that I have set the second listbox to have the Control Source
of the StaffID but this sets the Manager's ID rather than the one for the
member of staff. If I can work out how to get it to store the StaffID rather
than the ManagerID I'll die a happy man.

I'm not sure if the code is trying to do that or not?

Can anybody provide any further information?

Thanks again.

 

[Joan Wild]

OK, so the first listbox is not bound to a field, but its row source is set
to the Managers table. In the afterupdate of this listbox you have code
that sets the rowsource of the second listbox to the Staff who belong to the
Manager selected in the first listbox.

That part is working. What you need to do is set the controlsource of the
second listbox to StaffID.

If this doesn't help, please post the following:
The RecordSource of your form
The name, ControlSource, RowSource, BoundColumn of listbox1
The name, ControlSource, RowSource, BoundColumn of listbox2
The code you have in the AfterUpdate for listbox1