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

 

[Pat Hartman]

1. As the others have mentioned, you should be using the ID rather than the name.
2. "Name" is a very poor choice as the name of a field in a table. It is the name of a property which EVERY object has and you will end up with strange results if you ever have to write code in a form that has a control bound to this field. For example, what does Me.Name refer to? Do you think it will refer to YOUR Name field or will it refer to the Name property of the form?
3. The easiest way to display additional columns from the combo without using code is to include the lookup table in the RecordSource query. Be sure to use a left join. Also, to prevent accidentally updating the lookup data, ALWAYS set the locked property of the controls bound to the lookup fields to Locked.

 

[Dana_]

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

 

[Pat Hartman]

If you only need one additional column, the domain function works but since it runs a separate query, it is the least efficient method. Including the additional columns in the combo's RowSource is better since it doesn't require running a separate query. I generally use the method I suggested because I tend to reuse queries and so that allows the query to work for a report also without having to do anything extra.

If you add the additional columns to the combo's RowSource, you just reference them using the .Column property.

Me.txtDescr = Me.cboID.Column(2)

Keep in mind that the columns of the RowSource are a zero-based array so .Column(2) is referring to the THIRD column (ID, Name, Description)

PS. Hopefully, you aren't really using "Name" as a column name. It is a reserved word and one of the two that cause the most problems when used as column names. "Date" is the other. Try writing VBA code and see what happens when you use Me.Name. You won't be happy. If you use Date as a column name, don't try to use the Date() function. You've lost it. That's why a lot of novices use Now() and end up with time in fields where it doesn't belong. But they think that the Date() function isn't working. Technically, they're right. They broke it.