PC Review


Reply
Thread Tools Rate Thread

Clear items selected from multiselect list box

 
 
NKK
Guest
Posts: n/a
 
      17th Dec 2008
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

 
Reply With Quote
 
 
 
 
Dirk Goldgar
Guest
Posts: n/a
 
      17th Dec 2008
"NKK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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)

 
Reply With Quote
 
 
 
 
NKK
Guest
Posts: n/a
 
      18th Dec 2008
Thanks...that worked nicely!

"Dirk Goldgar" wrote:

> "NKK" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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)
>
>

 
Reply With Quote
 
New Member
Join Date: Jul 2012
Posts: 1
 
      21st Jul 2012
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
report based on selected items in list box(multiselect) babs Microsoft Access Reports 0 27th Jul 2009 12:11 AM
Clear Multiselect List box Anne Microsoft Access Form Coding 3 27th Sep 2004 01:10 AM
Selecting List items in Multiselect list box BobRoyAce Microsoft Access Forms 2 3rd Sep 2004 08:33 AM
Determining number of selected items in multiselect list box Adam Ruben Microsoft Outlook Form Programming 1 24th Oct 2003 02:03 PM
Multi-Selected Items from one List Box to other empty List Box and text box Sumit Microsoft Access Form Coding 3 15th Jul 2003 09:11 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:27 AM.