I have a combobox whose value I want to use with a SQL WHERE clause. How do you run a SELECT statement inside VBA based on the combobox value?
Add a comment
|
3 Answers
If you wish to use the bound column value, you can simply refer to the combo:
sSQL = "SELECT * FROM MyTable WHERE ID = " & Me.MyCombo
You can also refer to the column property:
sSQL = "SELECT * FROM MyTable WHERE AText = '" & Me.MyCombo.Column(1) & "'"
Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset(sSQL)
strText = rs!AText
strText = rs.Fields(1)
In a textbox:
= DlookUp("AText","MyTable","ID=" & MyCombo)
*edited
7 Comments
Nag Hammadi
Thanx. Won't that only create a string containing SQL statements?
Fionnuala
Yes. Your question does not include any information on what you want to do with the where statement.
Nag Hammadi
I want to actually run the SQL statement to return a value which I can pass to a textbox, something like Me!textBox = sSQL
Fionnuala
In that case, you should probably be using DLookUp
Fionnuala
You cannot run a select statement, you can only get a recordset or such like, which means that you really need to say what you are going to do with the select statement before anyone can provide a valid answer. In this case, you need a value, but you could have wished to iterate through a recordset to perform further operations.
|
Access 2007 can lose the CurrentDb: see http://support.microsoft.com/kb/167173, so in the event of getting "Object Invalid or no longer set" with the examples, use:
Dim db as Database
Dim rs As DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("SELECT * FROM myTable")
Comments
Here is another way to use SQL SELECT statement in VBA:
sSQL = "SELECT Variable FROM GroupTable WHERE VariableCode = '" & Me.comboBox & "'"
Set rs = CurrentDb.OpenRecordset(sSQL)
On Error GoTo resultsetError
dbValue = rs!Variable
MsgBox dbValue, vbOKOnly, "RS VALUE"
resultsetError:
MsgBox "Error Retrieving value from database",VbOkOnly,"Database Error"
