How To Use A Sql Select Statement With Access Vba

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?

Solution 1:

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

Solution 2:

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")

Solution 3:

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)
 OnErrorGoTo resultsetError 
 dbValue = rs!Variable
 MsgBox dbValue, vbOKOnly, "RS VALUE"resultsetError:
 MsgBox "Error Retrieving value from database",VbOkOnly,"Database Error"