Hi there,
I have tried so many different options to achieve this and nothing seems to be working, someone please help!
I have a form which is based on a table called 'JobRequests', within this form i want to have a combo box called 'Directorates', and a combo box called 'Department'. I want the user to pick a directorate, which will then filter the department combo box to only show departments within that directorate. The data for these two combo boxes is stored in a tabled call 'AllDepartments', this table has two fields: 'Directorate' (CWB, CEF, C&I, SC, etc), and 'Department' (All the deaprtments within the organisation). So the each department has the relevant directorate next to it.
I have tried putting the follwing into the row source of the DirectorateCombo:
SELECT DISTINCT AllDepartment.Code FROM AllDepartment ORDER BY AllDepartment.Code;
I then entered the follwing code as an AfterUpdate event of the first combo box:
Department.RowSource = "Select AllDepartment.[Department] FROM AllDepartment WHERE AllDepartment.[Directorate] = ' " & cbo.Direct.Value & " ' ORDER BY AllDepartment.[Department];"
I am getting an expected end of statement error. Could anyone help? Am i getting the syntax slightly wrong?
Harriet
I have tried so many different options to achieve this and nothing seems to be working, someone please help!
I have a form which is based on a table called 'JobRequests', within this form i want to have a combo box called 'Directorates', and a combo box called 'Department'. I want the user to pick a directorate, which will then filter the department combo box to only show departments within that directorate. The data for these two combo boxes is stored in a tabled call 'AllDepartments', this table has two fields: 'Directorate' (CWB, CEF, C&I, SC, etc), and 'Department' (All the deaprtments within the organisation). So the each department has the relevant directorate next to it.
I have tried putting the follwing into the row source of the DirectorateCombo:
SELECT DISTINCT AllDepartment.Code FROM AllDepartment ORDER BY AllDepartment.Code;
I then entered the follwing code as an AfterUpdate event of the first combo box:
Department.RowSource = "Select AllDepartment.[Department] FROM AllDepartment WHERE AllDepartment.[Directorate] = ' " & cbo.Direct.Value & " ' ORDER BY AllDepartment.[Department];"
I am getting an expected end of statement error. Could anyone help? Am i getting the syntax slightly wrong?
Harriet