Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Using CASE in real world examples Expand / Collapse
Posted Tuesday, March 26, 2013 8:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:42 PM
Points: 1, Visits: 1

I am new to this forum.

I am using Microsoft Access 2010, which includes SQL.

Here's my problem:

What I want to do is to reference a parameter to a combo box control, so that I can
perform multiple CASE statements. For example:

cbox AS TaskSelection.TaskSelect

CASE "Locations" THEN SELECT * FROM Locations

I do hope I got this SQL coding correct;otherwise, correct me on this.

I do hope I get some feedback on this issue.


Post #1435741
Posted Wednesday, March 27, 2013 7:35 AM



Group: General Forum Members
Last Login: Tuesday, December 6, 2016 8:08 PM
Points: 16,145, Visits: 16,850
What you are describing is VBA code. You want to run different queries based on the selection from a combobox. However you can't just mix sql and VBA code like that. What you would need to do is execute different sql based on the selection. Maybe you can use your case statement to build a sql string and then run your sql string. You need to do some research on how to execute a query in VBA.


Need help? Help us help you.

Read the article at for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1435903
Posted Thursday, March 28, 2013 1:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 13, 2016 1:06 AM
Points: 86, Visits: 1,088
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>
Case ...
End Select

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, QueryDef or RecordSet object (DAO), a Command or a RecordSet object (ADO).

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
Case "Names"
strSQL = "SELECT * FROM Names"
Case "Locations"
strSQL = "SELECT * FROM Locations"
End Select

End Sub

Have a nice day!
Post #1436289
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse