Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: How to "clear" the contents of a listbox when another listbox row is selected?

    Hello,

    I have 3 related tables for a library -
    tbluser, tblvisits, tblactivities.

    One user has many visits, one visit can have many activities.

    I have 3 listboxes on my form -

    1. lstuser

    1. lstvisits

    1. lstactivities


    I can select a user via lstuser, and this will display the selected users visits in lstvisits.

    Next, I select a one of these visits in lstvisits and this displays the activities for that visit in lstactivities.

    This works fine. But I cannot get lstactivities to automatically "clear" its contents when I go back to the start and select a different user in lstUsers.

    How do I get lstactivities to automatically clear itself when I click back on lstusers? I have tried entering the following into lstusers on-click event but it does nothing -
    Code:
    me.lstactivities.requery
    or
    Code:
    me.lstactivities = ""
    Any ideas?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure to understand what you try to achieve.

    1. If you want to void the list (i.e. empty it of its contents), you can set its RowSource property to an empty string ("").

    2. If you want to reset it to what it was when the form was open, you can store its RowSource property in a member variable when the form is open and reassign the value of this varianble to the RowSource property of the list later on.

    In the form class module:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_strListRowSource As String
    
    Private Sub Command_VoidList_Click()
    
    ' Delete all rows from the list.
    '
        Me.List0.RowSource = ""
        
    End Sub
    
    Private Sub Command_ResetList_Click()
    
    ' Restore the content of the list in the state
    ' it was when the form was open.
    '
        Me.List0.RowSource = m_strListRowSource
        
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    
    ' Store the RowSource property of the list
    ' in the m_strListRowSource member variable.
    '
        m_strListRowSource = Me.List0.RowSource
        
    End Sub
    In both cases no Requery is needed.
    Have a nice day!

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Ok, I havent tried your suggestions yet, but before I do I will try to explain the issue.

    The 3 listboxes each show 1)Users, 2)Visits, 3)Activities.

    If I select a User eg usr001 (lstusers), this users visits are displayed in lstvisits. This is working fine.

    Next I want view the activities taken place during this visit. So I click on a visit (lstvisits) and this shows the activities for that visit (lstactivities). This also works fine.

    To view a different user (eg usr002) I go back to lstusers, select usr002 and those visits are displayed in lstvisits. But the results in lstactivities are still those from the previous user (usr001).

    The third list box (lstactivities) is not emptying/clearing/ when a different user from the first listbox (lstusers) is selected. It still shows the other users activities until the second listbox (lstvisits) is selected.

    Does that make any sense? If not I will try again

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Ok. What code runs on the AfterUpdate event of the first and second list? Does the code in the the AfterUpdate event of the first list changes the RowSource property of the third list? If not, the problem may be there.
    Have a nice day!

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    The afterupdate events are -
    First listbox (lstusers) afterupate event = Me.lstvisits.Requery
    Second listbox (lstvisits) afterupdate event = Me.lstactivities.Requery

    I have also tried putting me.lstactivities.Requery in the first listbox (lstusers) afterupdate, but it makes no difference.

  6. #6
    Join Date
    Aug 2004
    Posts
    364
    If it helps, here is a zipped up database of what Im trying to achieve with the 3 listboxes. You will see the 3rd listbox doesnt clear itself when the 1st listbox has a different row selected..
    Attached Files Attached Files

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    any thoughts?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I get it finally:
    Code:
    Private Sub lst_beneficiaries_AfterUpdate()
    
        Me.lst_visits.Requery
        Me.lst_visits.Value = Null
        Me.lst_activities.Requery
    
    End Sub
    Have a nice day!

  9. #9
    Join Date
    Aug 2004
    Posts
    364
    Success!! I think 'Null' was the only one I hadn't tried. Genius, thankyou.

Posting Permissions

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