Clear items selected from multiselect list box

  • Thread starter Thread starter NKK
  • Start date Start date
N

NKK

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 said:
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
Click to expand...


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
 
Thanks...that worked nicely!

Dirk Goldgar said:
NKK said:
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
Click to expand...


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)
Click to expand...
 
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
 
Back
Top