Clear items selected from multiselect list box

Discussion in 'Microsoft Access Form Coding' started by NKK, Dec 17, 2008.

  1. NKK

    NKK Guest

    I have a form bound to a table (dt_accounts) with an unbound listbox that I
    have set up to insert multiple records into another table (dt_accountrole)
    that has a many to 1 relationship to the table the form is based on. The
    field Account_id is the primary key in dt_accounts and a foreign key in
    dt_accountrole. For every selection in the list box, a record is entered
    into the dt_accountrole table using the following vba code (thanks to someone
    else in this group!):

    Dim varItem As Variant
    Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset("dt_AccountRole", dbOpenDynaset)

    With Me.lboAcctRole
    For Each varItem In .ItemsSelected
    rst.AddNew
    rst![Role_id] = .ItemData(varItem)
    rst![Account_id] = Me.Account_id
    rst.Update
    Next varItem
    End With
    rst.Close
    Set rst = Nothing
    End Sub

    This code is on the exit event...my problem is that if I move to the next
    record, the selections from the previous record are still highlighted. How
    to I "clear" the selections from the list box when moving between records?
    Thanks in advance
     
    NKK, Dec 17, 2008
    #1
    1. Advertisements

  2. NKK

    Dirk Goldgar Guest

    "NKK" <> wrote in message
    news:...
    >I have a form bound to a table (dt_accounts) with an unbound listbox that I
    > have set up to insert multiple records into another table (dt_accountrole)
    > that has a many to 1 relationship to the table the form is based on. The
    > field Account_id is the primary key in dt_accounts and a foreign key in
    > dt_accountrole. For every selection in the list box, a record is entered
    > into the dt_accountrole table using the following vba code (thanks to
    > someone
    > else in this group!):
    >
    > Dim varItem As Variant
    > Dim rst As DAO.Recordset
    >
    > Set rst = CurrentDb.OpenRecordset("dt_AccountRole", dbOpenDynaset)
    >
    > With Me.lboAcctRole
    > For Each varItem In .ItemsSelected
    > rst.AddNew
    > rst![Role_id] = .ItemData(varItem)
    > rst![Account_id] = Me.Account_id
    > rst.Update
    > Next varItem
    > End With
    > rst.Close
    > Set rst = Nothing
    > End Sub
    >
    > This code is on the exit event...my problem is that if I move to the next
    > record, the selections from the previous record are still highlighted.
    > How
    > to I "clear" the selections from the list box when moving between records?
    > Thanks in advance



    Here's a procedure you can call, passing it the list box to be cleared:

    '----- start of code -----
    Public Sub ClearListbox(lst As Access.ListBox)

    Dim lngx As Long

    With lst
    For lngx = (.ItemsSelected.Count - 1) To 0 Step -1
    .Selected(.ItemsSelected(lngx)) = False
    Next lngx
    End With

    End Sub

    '----- end of code -----

    With that procedure stored in a standard module, you can call it from your
    form's Current event:

    Private Sub Form_Current()

    ClearListbox Me!lboAcctRole

    End Sub


    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
    Dirk Goldgar, Dec 17, 2008
    #2
    1. Advertisements

  3. NKK

    NKK Guest

    Thanks...that worked nicely!

    "Dirk Goldgar" wrote:

    > "NKK" <> wrote in message
    > news:...
    > >I have a form bound to a table (dt_accounts) with an unbound listbox that I
    > > have set up to insert multiple records into another table (dt_accountrole)
    > > that has a many to 1 relationship to the table the form is based on. The
    > > field Account_id is the primary key in dt_accounts and a foreign key in
    > > dt_accountrole. For every selection in the list box, a record is entered
    > > into the dt_accountrole table using the following vba code (thanks to
    > > someone
    > > else in this group!):
    > >
    > > Dim varItem As Variant
    > > Dim rst As DAO.Recordset
    > >
    > > Set rst = CurrentDb.OpenRecordset("dt_AccountRole", dbOpenDynaset)
    > >
    > > With Me.lboAcctRole
    > > For Each varItem In .ItemsSelected
    > > rst.AddNew
    > > rst![Role_id] = .ItemData(varItem)
    > > rst![Account_id] = Me.Account_id
    > > rst.Update
    > > Next varItem
    > > End With
    > > rst.Close
    > > Set rst = Nothing
    > > End Sub
    > >
    > > This code is on the exit event...my problem is that if I move to the next
    > > record, the selections from the previous record are still highlighted.
    > > How
    > > to I "clear" the selections from the list box when moving between records?
    > > Thanks in advance

    >
    >
    > Here's a procedure you can call, passing it the list box to be cleared:
    >
    > '----- start of code -----
    > Public Sub ClearListbox(lst As Access.ListBox)
    >
    > Dim lngx As Long
    >
    > With lst
    > For lngx = (.ItemsSelected.Count - 1) To 0 Step -1
    > .Selected(.ItemsSelected(lngx)) = False
    > Next lngx
    > End With
    >
    > End Sub
    >
    > '----- end of code -----
    >
    > With that procedure stored in a standard module, you can call it from your
    > form's Current event:
    >
    > Private Sub Form_Current()
    >
    > ClearListbox Me!lboAcctRole
    >
    > End Sub
    >
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
    NKK, Dec 18, 2008
    #3
  4. NKK

    HeathBarber

    Joined:
    Jul 21, 2012
    Messages:
    1
    Likes Received:
    0
    I added the following to the forms On Current Event, It seems to work and is very simple:

    Private Sub Form_Current()
    Dim varItm As Variant

    With List25 (Where List25 is equal to your listbox name)

    For Each varItm In .ItemsSelected
    .Selected(varItm) = False
    Next varItm

    End With
    End Sub
     
    HeathBarber, Jul 21, 2012
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sumit
    Replies:
    3
    Views:
    2,012
    Allen Browne
    Jul 15, 2003
  2. Anne

    Clear Multiselect List box

    Anne, Sep 26, 2004, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    263
  3. Jay
    Replies:
    4
    Views:
    194
  4. Guest

    Filter Multiselect Listbox with another multiselect listbox?

    Guest, Aug 10, 2006, in forum: Microsoft Access Form Coding
    Replies:
    5
    Views:
    1,031
    Guest
    Aug 10, 2006
  5. big.brown
    Replies:
    6
    Views:
    462
    big.brown
    Oct 8, 2009
Loading...

Share This Page