Results 1 to 9 of 9
  1. #1
    Join Date
    May 2007
    Posts
    74

    Unanswered: How can I find the SQL source for a listbox?

    I want to make sure that a dynaset recordset and a listbox are based on the same query.

    I suppose that I can make a private constant that is used by both, but that seems a little kludgy.

    Thanks!

    azjazz

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    The listbox should list it in the properties for that box under "rowsource" in the data tab.

  3. #3
    Join Date
    May 2007
    Posts
    74
    Oops ... I guess I wasn't clear in my original post ...

    I'm looking for the SQL source via VBA. My code needs to find out what the SQL statement is.

    azjazz

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by azjazz
    Oops ... I guess I wasn't clear in my original post ...

    I'm looking for the SQL source via VBA. My code needs to find out what the SQL statement is.

    azjazz

    if your list box rowsource is a query, like on my command button for a list box, you see this:

    Code:
    Private Sub cmdFind_Click()
    'this is the 'find us!' command button
    
    Dim strSQL As String
    Dim strDescription As String
    
    strDescription = Nz(Me.txtKeyword, "")
    
    strSQL = "SELECT tblTask.Task_ID, tblTask.TaskDescription, " & _
                "tblTask.DateOriginated, tblTask.Status " & _
                "FROM tblTask " & _
                "WHERE (((tblTask.TaskDescription) Like '*" & strDescription & "*'));"
    Me.List7.RowSource = strSQL
    Me.List7.Requery
    
    End Sub
    That is what displays in the listbox on the form. Is that what you're asking? It is in my 'event' tabs on the properties for the listbox.

  5. #5
    Join Date
    May 2007
    Posts
    74
    Grafixx01 - Thanks for responding!

    What I am looking for is how to find (via VBA) the SQL that populates the RowSource for a listbox.

    I was thinking that I could use something like: Me.myListBox.Properties.Item, but I haven't gotten that to work yet.

    If I add Me.myListBox to my Watch list, I can see the properties for the list box. I can see the source SQL under the "Value" for Item 6. I'm thinking that I should be able to get to that, but haven't figured out how. Also, will the source SQL always appear under Value for Item 6?

    azjazz

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by azjazz
    Grafixx01 - Thanks for responding!

    What I am looking for is how to find (via VBA) the SQL that populates the RowSource for a listbox.

    I was thinking that I could use something like: Me.myListBox.Properties.Item, but I haven't gotten that to work yet.

    If I add Me.myListBox to my Watch list, I can see the properties for the list box. I can see the source SQL under the "Value" for Item 6. I'm thinking that I should be able to get to that, but haven't figured out how. Also, will the source SQL always appear under Value for Item 6?

    azjazz

    From my personal experiences with Access, when you select a 'rowsource' for a listbox to be a query / SQL source, it will be there unless you take it out and then use like an "afterupdate" event from another control on the form. Then it'll show up blank, as for data that is.

  7. #7
    Join Date
    May 2007
    Posts
    74
    Grafixx01 ... I figured it out. (I'm sooooo embarrassed! )

    It was so basic, you probably didn't think of it.

    For some reason, I was thinking it was something more obscure, but it was really simple. I just needed to read the Me.myListBox.RecordSource property. Normally, I just set it ... and it slipped my mind that I should just read it for this particular application.

    Thanks again for helping!

    azjazz

  8. #8
    Join Date
    Aug 2006
    Posts
    559
    Yeah, I forgot about that too! Oh well, sometimes the most obvious isn't!

  9. #9
    Join Date
    May 2007
    Posts
    74
    Ooops! I didn't mean to say "It was so basic, you probably didn't think of it."

    What I meant to say was, "It was so basic, you probably thought I was asking about something else."

    Excuse me while I use a crow-bar to extricate my foot from my mouth.

    azjazz

Posting Permissions

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