Having trouble with combobox, but maybe it should be subform?

[JH]

Hi,

I've created a database of historical buildings. Each building has several
staff members who help look after the building, with one staff as the
designated staffer for the week.

My tables are:

Buildings (ID, BuildingID, Name, DesignatedStafferID)
StaffDetails (StaffID, BuildingID - multiple staff are possible for each
BuildingID, and the same staff can be entered for different buildings -- I
create the associations here manually for each building)
StaffPhotos (StaffID, PhotoFile -- I add new staff here manually, each
record has a photo)

I have a form for "buildings" which lets me add/modify information about
buildings. But how do I add a combobox that does the following?

1) shows the staff assigned to this building (does a query on the
StaffDetails table to show staff designated ONLY for this building?)
2) lets you designate the staffer for the week (saves the value of the
selected StaffID row as the DesignatedStafferID in the buildings table?)

I've tried creating queries, using subforms, using bound comboboxes, but I
can't figure this out for the life of me.

Any help would be appreciated.

J

 

[Kailash Kalyani]

The trick I think is to do it by using a combobox bound to your form.
Assuming that BID is the field containing the building id in the current
form, you could create a combo box with the datasource as a query which uses
a statement that generally looks like
select StaffID, BuildingID from
staffdetails

this gives u the whole table data. Now to filter it. Below the BuildingId,
in the criteria, right click and select build wizard and select the
form->loaded forms-> my form and click the appropriate field in the middle
window and the value in the rightmost. That should give you the criteria
that looks something like Forms![myform]![myfield]

Hope that helps!