How to have combobox populate based on a another cbo within form? 1

[fdcusa]

tbl_DivisionsDefs
Divison Title
------- -------------
Board President
Fire Fire Chief
Board Member
Fire Fire Fighter

In form for "Division" combo box, I added this to the Row Source: "SELECT DISTINCT Division FROM tbl_DivisionsDefs;", and it works great.

The next combo box is "Title", and what I want to have happen is the form combo box populate only with Titles that are of the Division selected in "Division" combo box.

ie: If Division = Board, Only return President & Member in Title combobox on form.

What I tried in the Control Source is:

SELECT tbl_DivisionsDefs.Title FROM tbl_DivisionsDefs WHERE Division = tbl_DivisionsDefs.Division ORDER BY tbl_Divisions.Title;

Should it be in the control source or row source? And what is the correct statement to return what I desire?

(New to SQL - OTJ training, and practicing at home at the moment.)

John

 

[PHV]

In the AfterUpdate event procedure of cboDivision:
Me!cboTitle.RowSource = "SELECT Title FROM tbl_DivisionsDefs WHERE Division=[tt]'"[/tt] & Me!cboDivision & [tt]"'[/tt] ORDER BY 1"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fdcusa]

Worked great, Thanks PHV! Rather than calling it as an [Event Procedure], is it possible to put the SQL statement directly in cboDivision.AfterUpdate, albeit with modification?

Appreciate your help!

I'm okay with Access, but the SQL part is my opportunity. Moments ago found w3schools.com/sql which is an awesome reference.

John