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" <(E-Mail Removed)> wrote in message
news:735927EB-9F1F-4369-BC1D-(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)

Thanks...that worked nicely!

"Dirk Goldgar" wrote:

> "NKK" <(E-Mail Removed)> wrote in message
> news:735927EB-9F1F-4369-BC1D-(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)
>
>

[HeathBarber]

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