Linking combo boxes in a form

[kev747]

Hey all.

I have a form with three combo boxes on it, and all are linked to their own table -

cboBuilding

cboDepartment

cboArea

Specific areas are linked to departments and the departments are linked to buildings.

What I would like to see is when the user selects Building A, only those departments located in that building are selectable from the Departments dropdown list. Consequently when they select the department, only those areas located in that particular department are selectable from the Area dropdown list.

My problem is that a single department can be found in more than one building, and a single area can be found in more than one department.

It's like when you go on the Net to find out details about cars, you select the make from the first dropdown list (say Chev or Ford), that then loads the info for the model (Nova, Suburban etc for Chev, Mustang, Bronco etc for Ford), then you select the body type from the third dropdown (sedan, wagon, pick-up etc).

The contents of each following dropdown is determined by the preceeding dropdown selection.

I'm failry certain this is attainable, but I've hit a mental brickwall and need some coaxing to get over it.

Thanks for any help/hints/advice offered.



This is a politically correct signature, due to constant whinging about my previous signature.
Long live the fun police.

 

[FoxProProgrammer]

You need to set up Queries for each combo box, or you could include the SQL string in the Rowsource of each combo box. I prefer to use Queries, but you could store the SQL in the Rowsource if you prefer. My example below assumes that you have set up a Query for each combo box. Include the code in the On Click event of each combo box. This code assumes that you set up a two column combo box, where the first column contains the ID and the second column contains the Name.

cboBuilding On Click event

' Display the departments for the selected building and select the first part.
cboDepartment.Requery
cboDepartment.Value = cboDepartment.Column(0, 1)

' Display the area the selected department
cboArea.Requery

cboDepartment On Click event

' Display the areas for the selected department
cboArea.Requery

Now for the SQL for each Query. You didn't post your table or field names so I had to make some assumptions. If you have trouble with the Queries, please post more information about the structure of your tables.

Query for cboBuilding

SELECT Building.BuildingID, Building.BuildingName
FROM Building
ORDER BY BuildingName


Where clause for cboDepartment Query

WHERE (Building.BuildingID=cboBuilding.Value)


Where clause for cboArea Query

WHERE (Department.DepartmentID = cboDepartment.Value)



dz
dzaccess@yahoo.com