CODEWITHSUNDEEP
sqlvbams-accesslistboxpass-through
AlmostThere
AlmostThere

Reputation: 657

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?

Upvotes: 2

Views: 2624

Answers (1)

Andre
Andre

Reputation: 27644

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.

Upvotes: 3

Related Questions