Multiselect Listbox code is giving me a duplicate record - help!

[lithium1976]

Hello,

This is my first post!

Im using Access 2003 to create bookings known as visits.

On my form for I will select from 2 listboxes - a Group and the Users of that group who will attend the visit. The users listbox is Multiselect.

When I press the command button on the form to create the visit, one of the subforms called frmLinkUserGroups is producting a duplicated record of users I dont know why. For example, if I select 3 users from the listbox and click the button, I actually get 4 users added.

I've uploaded my database here if anyone wants to try it out, I've been racking my brains now for weeks trying to work it out with no luck.

The code Im using on my command button is -

Forms![visits_frm]![groupID] = Forms!visits_frm.ListOne.Column(0)

Forms![visits_frm].Form![frmLinkUserVisits]![userID] = Forms!visits_frm.List59.Column(0)

The code for my subform frmLinkUserVisits before update event is -

Dim strSQL        As String
Dim db            As DAO.Database
Dim rs            As DAO.Recordset
Dim ctl           As Control
Dim varItem       As Variant

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblLinkUserVisits", dbOpenDynaset, dbAppendOnly)

'make sure a selection has been made
If Forms![visits_frm]!List59.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 user!"
Exit Sub
End If

'add selected value(s) to table
Set ctl = Forms![visits_frm]!List59
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!userID = ctl.ItemData(varItem)
rs!visitID = Forms![visits_frm]![txt_visitID]
rs.Update
Next varItem

I just need to be able to select multiple users from the listbox and add them to the table.

[HiTechCoach]

Hello,

This is my first post!

Im using Access 2003 to create bookings known as visits.

On my form for I will select from 2 listboxes - a Group and the Users of that group who will attend the visit. The users listbox is Multiselect.

When I press the command button on the form to create the visit, one of the subforms called frmLinkUserGroups is producting a duplicated record of users I dont know why. For example, if I select 3 users from the listbox and click the button, I actually get 4 users added.

I've uploaded my database here if anyone wants to try it out, I've been racking my brains now for weeks trying to work it out with no luck.

The code Im using on my command button is -

Forms![visits_frm]![groupID] = Forms!visits_frm.ListOne.Column(0)

Forms![visits_frm].Form![frmLinkUserVisits]![userID] = Forms!visits_frm.List59.Column(0)

The code for my subform frmLinkUserVisits before update event is -

Dim strSQL        As String
Dim db            As DAO.Database
Dim rs            As DAO.Recordset
Dim ctl           As Control
Dim varItem       As Variant

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblLinkUserVisits", dbOpenDynaset, dbAppendOnly)

'make sure a selection has been made
If Forms![visits_frm]!List59.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 user!"
Exit Sub
End If

'add selected value(s) to table
Set ctl = Forms![visits_frm]!List59
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!userID = ctl.ItemData(varItem)
rs!visitID = Forms![visits_frm]![txt_visitID]
rs.Update
Next varItem

I just need to be able to select multiple users from the listbox and add them to the table.

This appears to be a duplicate. See original post here:

http://forums.databasejournal.com/sh...ad.php?t=52122