March 26, 2013 at 8:31 pm
Hello,
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
SELECT cbox
CASE "Names" THEN SELECT * FROM Names
CASE "Locations" THEN SELECT * FROM Locations
END SELECT
I do hope I got this SQL coding correct;otherwise, correct me on this.
I do hope I get some feedback on this issue.
johndb264
March 27, 2013 at 7:35 am
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 http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2013 at 1:20 am
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!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply