How to use a SQL SELECT statement with Access VBA
If you wish to use the bound column value, you can simply refer to the combo:
sSQL = "SELECT * FROM MyTable WHERE ID = " & Me.MyComboYou 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!ATextstrText = rs.Fields(1)In a textbox:
= DlookUp("AText","MyTable","ID=" & MyCombo)*edited
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 DatabaseDim rs As DAO.RecordsetSet db = CurrentDBSet rs = db.OpenRecordset("SELECT * FROM myTable")
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"