CODEWITHSUNDEEP
sqlms-accessvba
Nag Hammadi
Nag Hammadi

Reputation: 297

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?

Upvotes: 16

Views: 217145

Answers (3)

Fionnuala
Fionnuala

Reputation: 91376

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

Upvotes: 33

user28864
user28864

Reputation: 3463

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"

Upvotes: 3

user2885978
user2885978

Reputation: 103

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

Upvotes: 8

Related Questions