Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Changing the Listbox Rowsource on the fly (Access XP)

    I have a listbox where depending on the value of an option group I need to change the rowsource of the listbox in VBA code. Can this be done?
    If so, then what is wrong with the following code:
    <pre>Private Sub FrameOptionYM_AfterUpdate()
    Dim sSql As String, iPos As Integer
    sSql = ListYearandMonth.RowSource
    iPos = InStr(1, sSql, "ORDER BY", vbTextCompare)
    If iPos = 0 Then
    iPos = InStr(1, sSql, ";", vbTextCompare) - 1
    End If
    sSql = Mid(sSql, 1, iPos) & " ORDER BY "

    Select Case FrameOptionYM
    Case 1 ' Year and Month
    sSql = sSql & " [tbl Visits].ServiceYear, [tbl Visits].ServiceMonth"
    Case 2 ' Month and Year
    sSql = sSql & " [tbl Visits].ServiceMonth, [tbl Visits].ServiceYear"
    End Select

    ListYearandMonth.RowSource = sSql
    ListYearandMonth.Requery
    End Sub
    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changing the Listbox Rowsource on the fly (Access XP)

    Where and how is it failing, Pat? Trying to parse out a section of the SQL like that needs a lot of careful testing to make sure you get back what you expect. I've always found it simpler to just have two saved queries, and select whichever is appropriate
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Changing the Listbox Rowsource on the fly (Access XP)

    Thanks for your response. Sorry about not explaining enough.
    It's ok, I have solved it, I needed to put an following instruction
    iPos = iPos - 1
    after the End If instruction. My code now looks like:
    <pre>
    Note the boldings.
    Private Sub FrameOptionYM_AfterUpdate()
    Dim sSql As String, iPos As Integer
    sSql = Forms![frm R1 Reports]!ListYearandMonth.RowSource
    iPos = InStr(1, sSql, "ORDER BY", vbTextCompare) taken the -1 off this instruction
    If iPos = 0 Then
    iPos = InStr(1, sSql, ";", vbTextCompare)
    End If
    iPos = iPos - 1
    sSql = Mid(sSql, 1, iPos) & " ORDER BY "

    Select Case FrameOptionYM
    Case 1 ' Year and Month
    sSql = sSql & " [tbl Visits].ServiceYear, [tbl Visits].ServiceMonth"
    Case 2 ' Month and Year
    sSql = sSql & " [tbl Visits].ServiceMonth, [tbl Visits].ServiceYear"
    End Select

    Forms![frm R1 Reports]!ListYearandMonth.RowSource = sSql & ";"

    ListYearandMonth.Requery
    End Sub
    </pre>


  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing the Listbox Rowsource on the fly (Access XP)

    Pat,

    I would add the line setting the rowsourcetype into your code. When/If you upgrade to A2003 you might get bitten. See my <post#=480803>post 480803</post#>.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Changing the Listbox Rowsource on the fly (Access XP)

    Thanks Steve, I will put that in my code now to save the pain of later.

Posting Permissions

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