T
ToyFixer
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?
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?