Getting form to display autolookup query info

[Guest]

I'm an access newbie so bear with me
I have 3 related tables; incidents, managers, locations. Each manager has
many locations and each location can have many incidents. When people put in
an incident (using a form based on the incident table), they pick a location
from a list and I want it to display the manager name for that location. I
have created a lookup query that can tell you the name of the manager for an
incident id but I can’t get it to display in the form (these two tables are
not directly linked - is this the problem?) – it seems to pick a manager and
then stick with them regardless of which location you select. I am using a
list box to display the result.
I tried to put it in a text box instead and get #Name? displayed.
I have also tried adding a subform based on a location/manager name query
but don't know how to link it in properly.
Let me know if I haven't given enough detail.
Lucy

 

[Guest]

You could use DLookup to display it, but I normally prefer to use a query to
obtain the related fields in the combo box, and use the Column property of
the combo to display them in other textboxes.

Assuming a structure like:

Locations
--------------
LocationID AutoNumber (PK)
Location Text
ManagerID Integer (Foreign Key to Managers)

Managers
---------------
ManagerID AutoNumber(PK)
Manager Text

use the following as the RowSource query:

SELECT Locations.LocationID, Locations.Location, Managers.Manager
FROM Locations INNER JOIN Managers ON Locations.ManagerID =
Managers.ManagerID;

Set the ColumnCount property to 3.

To store the LocationID in the Incidents table, set the BoundColumn to 1.

To show only the Location field in the drop-down list and in the combo box
after the selection is made, set the ColumnWidths property to 0";x";0", where
x is wide enough to display the longest Location.

To display the Manager's name in a textbox, set its ControlSource property to:
YourComboBox.Column(2). (The index for the Column property starts at zero).

Hope that helps.
Sprinks

 

[Guest]

You're a genius thank you thank you thank you! I have spent DAYS trying to
figure this out and I never would have got there.
You've also given me a greater understanding of what I'm up to
Lucy