Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2002
    Posts
    403

    Unanswered: Manipulating a list box

    I'm confused again. I am trying to manipulate a listbox based on SQL statements. I thought as a start I would use the one that is there now and working. The code I have written is as follows:

    Private Sub MakeList()
    Dim strSql As String


    If Me.txtCategory.Column(0) = 4 Then

    Debug.Print Me.txtCategory.Column(0)

    strSql = "SELECT tblPoolData.ID, tblPoolData.PoolID, " & _
    "tblPoolData.Area, tblPoolData.Volume, tblPoolData.TargetTemp AS Temp, " & _
    "tblChlorineDonour.ChlorinationType, tblPoolData.Sanitiser, " & _
    "tblFilterType.FilterType, tblHeatSource.HeatSource, " & _
    "tblPoolData.MaintenanceClient, tblPoolData.OperationsClient" & _
    "FROM tblSanitiser RIGHT JOIN " & _
    "(tblHeatSource RIGHT JOIN (tblFilterType RIGHT JOIN (tblChlorineDonour RIGHT JOIN tblPoolData ON tblChlorineDonour.ChlorinationID = tblPoolData.ChlorinationType) " & _
    "ON tblFilterType.FilterTypeID = tblPoolData.FilterType) " & _
    "ON tblHeatSource.HeatID = tblPoolData.HeatSource) " & _
    "ON tblSanitiser.SanitiserID = tblPoolData.Sanitiser;"

    Me.lstPools.RowSourceType = "Table/Query"

    Me.lstPools.RowSource = strSql

    End If


    End Sub

    As far as I can tell it is bug free and if I understand help and Microsoft correctly it should run. But nothing happens, it is definitely being called and running as I have stepped through on debug, to no avail?

  2. #2
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    Does the query that you build in STRSQL return any records if you run the sql-statement in the query builder?

    anna

  3. #3
    Join Date
    Feb 2002
    Posts
    403
    Yes the SQL statement is a direct copy of a query that was previously populating the list box.

  4. #4
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    and you are sure that

    "If Me.txtCategory.Column(0) = 4 Then"

    is true

    anna

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    How about

    Me.lstPools.Requery

    ?

  6. #6
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    Damn,
    that hurts......

  7. #7
    Join Date
    Feb 2002
    Posts
    403
    Thanks I added the requery to the bottom of the If statement just after setting the rowsource and I still do not get any return value in the list box.

    Before anyone asks previously the list box was working with this exact SQL statement. All I have done so far is copy the SQL statement into the code, delete it as the rowsource from the property box. All other properties at this time remain the same as it was when the query was the row source. In other words this should work.

  8. #8
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Ya, a .requery shouldn't be neccessary. Setting the rowsource implicitically (oh boy, spelling is hard) requeries.

    Is the data in those tables filled at runtime, if so when are you calling the function? Maybe it's a timing prob.

  9. #9
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Right now, there does not appear to be anything is this query which is "dynamic."

    So, if you simply copy and past the SQL string in to the comboboxe's RowSource and you set the RowSourceType to "Table/Query," does the combobox work.

    What is it about this query that you are going to make "dynamic?"

    Since the RowSourceType does not change because the query is "dynamic," just set it in the combobox properties and don't fool with it in code.

    Have you put this SQL in to a named query and set the RowSource to that named query. Does it work then?

  10. #10
    Join Date
    Feb 2002
    Posts
    403
    Thanks for the help. I managed to fix the problem, which was a space after one line I had missed.

    Now I can fool with this and make the List box dynamic.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •