Hi
I'm attempting to use 2 listboxes to book out listings of pupils to various activities. In my code below, List11 is the list of all pupils, whereas List79 is based on a query where the activities are logged. The code below iterates though the selected pupils in List11 and populates List79 but it duplicates pupil names rather than overwriting any existing pupil names with most up to date details.
I've attempted numerous ways of looping through the list boxes in order not to duplicate any names already on List79 but keep making a mess of it. A few pointers would be great
I'm attempting to use 2 listboxes to book out listings of pupils to various activities. In my code below, List11 is the list of all pupils, whereas List79 is based on a query where the activities are logged. The code below iterates though the selected pupils in List11 and populates List79 but it duplicates pupil names rather than overwriting any existing pupil names with most up to date details.
I've attempted numerous ways of looping through the list boxes in order not to duplicate any names already on List79 but keep making a mess of it. A few pointers would be great
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
'On Error GoTo ErrorHandler
Set db = CurrentDb()
Set rs = db.OpenRecordset("PupilDetailsquery", dbOpenDynaset)
If Me.List11.ItemsSelected.Count = 0 Then
MsgBox "A record must be selected"
Exit Sub
End If
For Each varItem In Me.List11.ItemsSelected
rs.AddNew
rs!nameofpupil = Me.List11.Column(1, varItem)
rs.Update
Next varItem
rs.Requery
Me.List79.Requery