Listbox select

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a listbox that the user uses to select participants from for courses
that are held. The user clicks on the names of the participants and then uses
a button to migrates those attendents over to a participants listbox. I have
been asked to provide the ablity to use the Control key on the keyboard to
allow the user to select a number of participants. How would I go about
coding something like that so that the selected participants would all
transfer over to the other list box when the button is pressed?
 
No coding is necessary (sort of)
Make your list box's Multi Select Property "Extended"
Now, you can use the Shift key to select a range or the Ctl key to select
multiples.

As to moving them to the other list box, you will need to use the
ItemsSelected collection of the list box. There is a great example of its
use in VBA Help under (strangely enough) ItemsSelected.
 
Hum, I think I actually have something similar to that. I do have the listbox
set to extended. But for some odd reason when a user clicks on the button
that is to transfer the participants they only get the last selected person
in the list. Even though there might be 10 that where selected using the
control key.

I have attached the code that I am currently using:

Public Sub MoveToAttendees_Click()
On Error GoTo MoveToAttendeesError

Dim MyDB As Database
Dim MyStudents As Recordset
Dim MyEmployees As Recordset
Dim vExpiryDate As String
Dim I, X As Integer

Set MyDB = CurrentDb
Set MyStudents = MyDB.OpenRecordset("SessionAttendance", dbOpenDynaset)

DoCmd.Hourglass True

If [Forms]![CoursesMain]![RecertPeriod] <> 99 Then
vExpiryDate = DateAdd("d", (365 *
[Forms]![CoursesMain]![RecertPeriod]), Me.Date)
Else
vExpiryDate = "01-Jan-2999"
End If

For I = 0 To Me.EmployeesList.ListCount
Debug.Print Me!EmployeesList.Selected(I)
If Me!EmployeesList.Selected(I) Then
With MyStudents
.AddNew
!CourseID = Me.CourseID
!SessionID = Me.SessionID
!EmpID = Me.EmployeesList.ItemData(I)
!Attended = True
.Update
AttendAdd Forms!CoursesMain!TrainingType,
Me.CourseID, Me.SessionID, Me.EmployeesList.ItemData(I), Me.Date, vExpiryDate
End With
End If
Next I

MyStudents.Close
MyDB.Close
Set MyDB = Nothing
Form.Refresh
DoCmd.Hourglass False
Me.MoveToAttendees.DefaultValue = False
Exit Sub

MoveToAttendeesError:
If err.Number = 3022 Then 'Duplicate record
MsgBox "The employee '" & Me!EmployeesList.Column(2, I) & " " &
Me!EmployeesList.Column(1, I) & _
"' is already attending this session."
End If

If err.Number = 3140 Then Resume Next
'Else
'MsgBox Error$ & err.Number
'End If
Form.Refresh
MyStudents.Close
MyDB.Close
Set MyDB = Nothing
DoCmd.Hourglass False
Me.MoveToAttendees.DefaultValue = False
End Sub

Any ideas on why this isn't working?
 
With one minor error and one not so minor, you code appears to be okay. The
minor error is this line
For I = 0 To Me.EmployeesList.ListCount
Should be
For I = 0 To Me.EmployeesList.ListCount - 1

The ListCount collection index is 0 based, so if it says the count is 3,
they are referenced as 0, 1, and 2. If you use .ListCount(3), you will get a
subscript out of range error. Why you are not getting this error, I don't
know

The other problem is how you are adding the the items to the destination
list box.
Since I can't see the code for AttendAdd, I don't know what is going on. If
you look at the example in VBA Help for the Selected property, there is an
example there of exactly how to do it. (at least one way to do it). I would
actually use the AddItem method to do this.
--
Dave Hargis, Microsoft Access MVP


Cameron said:
Hum, I think I actually have something similar to that. I do have the listbox
set to extended. But for some odd reason when a user clicks on the button
that is to transfer the participants they only get the last selected person
in the list. Even though there might be 10 that where selected using the
control key.

I have attached the code that I am currently using:

Public Sub MoveToAttendees_Click()
On Error GoTo MoveToAttendeesError

Dim MyDB As Database
Dim MyStudents As Recordset
Dim MyEmployees As Recordset
Dim vExpiryDate As String
Dim I, X As Integer

Set MyDB = CurrentDb
Set MyStudents = MyDB.OpenRecordset("SessionAttendance", dbOpenDynaset)

DoCmd.Hourglass True

If [Forms]![CoursesMain]![RecertPeriod] <> 99 Then
vExpiryDate = DateAdd("d", (365 *
[Forms]![CoursesMain]![RecertPeriod]), Me.Date)
Else
vExpiryDate = "01-Jan-2999"
End If

For I = 0 To Me.EmployeesList.ListCount
Debug.Print Me!EmployeesList.Selected(I)
If Me!EmployeesList.Selected(I) Then
With MyStudents
.AddNew
!CourseID = Me.CourseID
!SessionID = Me.SessionID
!EmpID = Me.EmployeesList.ItemData(I)
!Attended = True
.Update
AttendAdd Forms!CoursesMain!TrainingType,
Me.CourseID, Me.SessionID, Me.EmployeesList.ItemData(I), Me.Date, vExpiryDate
End With
End If
Next I

MyStudents.Close
MyDB.Close
Set MyDB = Nothing
Form.Refresh
DoCmd.Hourglass False
Me.MoveToAttendees.DefaultValue = False
Exit Sub

MoveToAttendeesError:
If err.Number = 3022 Then 'Duplicate record
MsgBox "The employee '" & Me!EmployeesList.Column(2, I) & " " &
Me!EmployeesList.Column(1, I) & _
"' is already attending this session."
End If

If err.Number = 3140 Then Resume Next
'Else
'MsgBox Error$ & err.Number
'End If
Form.Refresh
MyStudents.Close
MyDB.Close
Set MyDB = Nothing
DoCmd.Hourglass False
Me.MoveToAttendees.DefaultValue = False
End Sub

Any ideas on why this isn't working?

Klatuu said:
No coding is necessary (sort of)
Make your list box's Multi Select Property "Extended"
Now, you can use the Shift key to select a range or the Ctl key to select
multiples.

As to moving them to the other list box, you will need to use the
ItemsSelected collection of the list box. There is a great example of its
use in VBA Help under (strangely enough) ItemsSelected.
Click to expand...
Click to expand...
 
Back
Top