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 -
Code:
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 -
Code:
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.