VBA Codin of cascading Combo Boxes

[scoe]

Hi

I have been trying tocorrect my coding with no avail. I have a combo box called 'Area' - Source: [Dept_code] which I want to use to filter in the combo box called 'Course' - Source: [Course Title], both are from the table [Annual Programme].

At the moment I get the error 'sub or function not defined' and it highlights 'Area' in the coding (I've bolded it and underlined it here).
[Private Sub Area_AfterUpdate()
Dim strSource As String
strSource = "SELECT Course_Title " & _
"FROM tblAnnual_Programme"
WHERE Dept_code = "" & Me.Area & "ORDER BY Course"
Me.Course.RowSource = strSource
Me.Course = vbNullString
End Sub]



I would be grateful if someone could tell me what I'm missing as I am at a loss at present.
Many thanks

[pbaldy]

Your concatenation is all off. Conquer one thing at a time. For now, try to do it all on one line. When you get it working, you can try splitting it up.

[scoe]

Thank you, may leave them as separate boxes as have no problem with that but doesn't matter how I code it I can't get it to work as cascading so must be missing something very obvious, seem to be going round in circles.

Thanks for the help.

[pbaldy]

What does it look like on one line? What is the data type of Dept_code?

[scoe]

The data type of Dept_code is text.

I have changed the second combo box to a more appropriate one.

At present I have got thus far
[Me.coursecode.RowSource = "SELECT Parent AOS FROM" & _
" tblAnnualProgInput WHERE dept_code = " & Me.Area & _
" ORDER BY Parent AOS"]

But it still is not happy I have the message 'Syntax error (missing operator) in query expression 'Parent AOS'

[scoe]

Sorted: Added a query to each combo box to filter the choices, works a dream.....Thank you for your help.

[pbaldy]

Glad you sorted it. Because your field is text you needed delimiters, like

Me.coursecode.RowSource = "SELECT [Parent AOS] FROM" & _
" tblAnnualProgInput WHERE dept_code = '" & Me.Area & _
"' ORDER BY Parent AOS"

note also the brackets around the field name, required because of the inadvisable space.

[scoe]

Thank you for that, I tried delimiters but I obviously was not placing them correctly.

[pbaldy]

No problemo.