How to use a SQL SELECT statement with Access VBA

[Dana_]

good afternoon everyone,

I have a sql Table "AT_Abteilung" with three columns ID, Name, Description.
I created a form where I have a combobox, where I can select a name. If I choose
a name it should be able to show the description in the Textfield, where I can change the
content. For this I made a select statement, but it doesnt work. Could someone please give me a hint, what I am doing wrong?

Code:
Private Sub AT_NameCombo_Change()

Dim sSQL As String
Dim rs As DAO.Recordset

sSQL = "SELECT Description FROM Name WHERE = '" & Me.AT_NameCombo.Column(1) & "'"

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

strText = rs!Description
strText = rs.Fields(1)

Me.DescriptionField = strText

End Sub

 

[Ranman256]

You don’t need sql.
Use a combo box Connected to that table.
set the combo property to have 3 columns.

in the combo AFTERUPDATE event, load the 3rd column
(Note, in vb , columns begin with zero)

txtDescr= cboBox.column(2).

that’s the only line of code you need.

 

[Ranman256]

Version 2 would be to use DLOOKUP to fill the text box.
txtDescr= Dlookup(“description”,”table”,”[id]=“ & cboBox)

no need to use sql.

 

[arnelgp]

you can also make your form Bound to AT_Abteilung table.
then on the AfterUpdate event of your combobox, you can "search" for the ID (if you have Newer version of Access):

private sub AT_NameCombo_AfterUpdate()
DoCmd.SearchForRecord acActiveDataObject, , , "ID = " & Me!AT_NameCombo
end sub

 

[Dana_]

Thank you guys for the quick reply. It worked with DLOOKUP like Ranman256 said.