AlmostThere AlmostThere - 3 months ago 14
SQL Question

Setting RowSource in Access with Pass through query

I have a list box name animal and the row source is set to the following code, in which the query "animal" is a pass through query. However, the list box does not populate with any animals. Note: if I run the query "animal" as a stand alone pass through query it runs correctly, it just does not populate the list box. It's almost as if the query is not executing when clicking into the list box.



Private Sub animallist_Enter()
Dim Q As QueryDef
Dim DB As Database

' Use for dynamic SQL statement'
Dim strSQL As String

' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("animal")

strSQL = "Select distinct(animal) From AnimalDB.Animaltable"

Q.SQL = strSQL
Q.Close

Me.animal.RowSource = strSQL
End Sub


If I connect to "AnimalDB.Animaltable" via ODBC and run the following code (switching the query to a select instead of pass through), the list box will populate with animals.

Private Sub animallist_Enter()
Dim Q As QueryDef
Dim DB As Database

' Use for dynamic SQL statement'
Dim strSQL As String

' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("animal")

strSQL = "Select distinct(animal) From [AnimalDB_Animaltable]"

Q.SQL = strSQL
Q.Close

Me.animal.RowSource = strSQL
End Sub


Why will the pass through query not populate the list box?

Answer

You are setting a querydef, but not using it.

Me.animal.RowSource = strSQL

should be

Me.animal.RowSource = "animal"

or

Me.animal.RowSource = Q.Name

Your second code example works, because an Access SELECT SQL can be used in an Access query or directly as rowsource.

P.S. Q.Close should be Set Q = Nothing, but that isn't really needed either, because it is a local variable which is destroyed at the end of the sub.

Comments