SQL query to display values in a Combo box

[Guest]

I am new to VBA programming
I have a table named 'CostCentres' which has got the cost centre structure
of the company I work for (It has many divisions, departments and cost
centres).
The structure of the table is as follows
Company>Division>Department>CostCentre.
I want the users who are based on the cost centre to enter performance
values. Since there is a large number of cost centres, I want to filter the
cost centres until the user finds his own - through a set of combo boxes.

Combo0 - select the Division
Combo2 - should show the set of Departments based on the selected Division
of Combo0
Table - CostCentres
Fields - Company, Division, Department, CostCentreCode, CostCentreName

To do this I wrote the following code to run an SQL query. Please note the
comments in the code...

Private Sub Combo0_AfterUpdate()
Dim combo0value As String
Dim cnnx As ADODB.Connection
Dim myrecordset1 As New ADODB.Recordset
Dim SQLstatment As String

Set cnnx = CurrentProject.Connection
'sets Combo2 blank
Me!Combo2 = ""

'assigns the value selected on Combo0
combo0value = Me!Combo0

'SQL statement for query to select the departments which
belongs to Division selected with Combo0
SQLstatement = " SELECT Department"
SQLstatement = SQLstatement + "FROM CostCentres"
SQLstatement = SQLstatement + "WHERE (((CostCentres.Department)
Is combo0value))"

'connection setting for the recordset
myrecordset1.ActiveConnection = cnnx

'execution of the SQL query
myrecordset1.Open SQLstatement

'get the results of the query to display in Combo2
Me!Combo2.RowSource = myrecordset1

End Sub

I get an error message which reads as follows
Run Time Error '-2147217900 (80040e14);'

Syntax error (missing operator) in query expression
'DepartmentFROMCostCentresWHERE (((CostCentres.Department) Is combo0value))'

when I click debug it points to
myrecordset1.Open SQLstatement

Can someone please help me with this? Am I doing the correct thing to call a
SQL query to display values in a combo box? is there an easy way of doing
this without having previously stored queries?

 

[Guest]

1. When you connecting few lines you need to add a space, so the words won't
get connected
2. The SQL won't recognize combo0value, you need to take it outside the string
3. There is no need to open this query, just set the SQL and then run Requery

Try:
Private Sub Combo0_AfterUpdate()
Dim combo0value As String
Dim SQLstatment As String

'sets Combo2 blank
Me!Combo2 = ""

'SQL statement for query to select the departments which
belongs to Division selected with Combo0

SQLstatement = " SELECT Department "
SQLstatement = SQLstatement & " FROM CostCentres "
SQLstatement = SQLstatement & " WHERE Department = '" & Me!Combo0 & "'"

Me!Combo2.RowSource = SQLstatement
Me!Combo2.Requery

End Sub

 

[Guest]

1. When you connecting few lines you need to add a space, so the words won't
get connected
2. The SQL won't recognize combo0value, you need to take it outside the string
3. There is no need to open this query, just set the SQL and then run Requery

Try:
Private Sub Combo0_AfterUpdate()
Dim combo0value As String
Dim SQLstatment As String

'sets Combo2 blank
Me!Combo2 = ""

'SQL statement for query to select the departments which
belongs to Division selected with Combo0

SQLstatement = " SELECT Department "
SQLstatement = SQLstatement & " FROM CostCentres "
SQLstatement = SQLstatement & " WHERE Department = '" & Me!Combo0 & "'"

Me!Combo2.RowSource = SQLstatement
Me!Combo2.Requery

End Sub