The VB/VBA language has a strict syntax. You cannot just type whatever you want an expect that the interpreter/compiler will go with it.
1. cbox AS TaskSelection.TaskSelect
To declare and instanciate a variable on a control of type ComboBox, you use:
Dim cbox As ComboBox
Set cbox = <ComboBox Name>
I can't imagine what TaskSelection.TaskSelect
is supposed to be in this case.
2. A Select Case block is built as follows:
Select Case <Some variable>
Case <Value 1>
'Code to be executed when <Some variable> = <Value 1>
Case <Value 2>
'Code to be executed when <Some variable> = <Value 2>
3. The VB/VBA interpreter cannot directly perform any SQL expression. You must build (assemble) the SQL expression in a String variable then pass this variable to "something" that can execute the query contained in the string expression in the variable. In MS Access, "something" can be a Form
(with it's RecordSource
property), a ComboBox
or a ListBox
control (with it's RowSource
property), the CurrentDb
object (through it's Execute
method - this only works with action queries). You can also declare and instanciate a "data aware" object from a library (DAO, ADO, ...) such as a Database
object (DAO), a Command
or a RecordSet
You don't explain what you want to do with the query. In MS Access, a query is often used to open a RecordSet or to be used as the RecordSource property of a Form (or as the RowSource property of a ListBox/ComboBox, which is almost the same).
Here is a rewritten version of your code where the following assumptions were made:
- The code is in the Class Module of a Form.
- This Form has a ComboBox named ComboTaskSelect
among its controls.
- The Bound Column of the ComboBox contains string data such as "Names", "Locations", etc.
- The SQL string will be used later on for a purpose that remains to be defined (see above).
Private Sub ComboTaskSelect_AfterUpdate()
Dim cbox As ComboBox
Dim strSQL As String
Set cbox = Me.ComboTaskSelect
Select Case cbox.Value
strSQL = "SELECT * FROM Names"
strSQL = "SELECT * FROM Locations"
Have a nice day!