Listbox -- Need help with modifications

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

Guest

I have 2 listboxes on a form... Listbox1 shows "Available Members" while
Listbox2 shows "Selected Members".

When clicking on an "Available Member" and the "Single Arrow" (Add), I add
the selected member to the "Selected Member" listbox (which then creates a
unique key in a table).

Currently, the way this form is set up, I only can select a) one member at a
time or b) all members. I'm in need to modify the form so that I can drag
across consecutive members (maybe 5) or several members (not necessarily in
consecutive order; using the CTRL key) at one time. Then, I'd add them via
the "Add" command button.

The same function (several consecutive or non-consecutive) should work in
the "Selected Members" form (to remove the unique key from the table).

As always, pictures (or samples) are worth thousand words. Hence, I
created a small sample db that can be found at:

http://www.savefile.com/files/6644814
or
http://www.bestsharing.com/files/ms00167798/Listbox--SelectingSeveralEntries.zip.html


Does anyone know how to modify the function so that I can achieve what
described above?

Thanks,

Tom
 
Hi Tom,

(Bonus code)
Here is a sub to clear (deselect) a list box:

'***************
Private Sub cmdResetOthers_Click()
Dim n As Long

For n = 0 To Me!lstOthers.ListCount
Me!lstOthers.Selected(n) = False
Next
End Sub
'********************

Her is a sub to reverse selections in a list box. If you want to select 98
out of 100, select the TWO you don't want then run this sub:

'****************
Private Sub cmdRevOthers_Click()
Dim n As Long

For n = 0 To Me!lstOthers.ListCount
Me!lstOthers.Selected(n) = Not Me!lstOthers.Selected(n)
Next
End Sub
'****************


Now to you problem. In each list box, open properties. On the OTHER tab, set
the MultiSelect property to 'Simple'.

Then replace your code for the two "AddOne" buttons with the following two
subs:
(you might also want to change the tool tip to "Add Selected"/
"DeleteSelected")

For the AddOne button:

'*******************
Dim frm As Form
Dim ctl As Control
Dim lngNew As Long
Dim db As DAO.Database
Dim vItm As Variant
Dim ynrecordsChanged As Boolean

ynrecordsChanged = False
Set db = CurrentDb
Set frm = Forms!frmQuestionMatrix
Set ctl = frm!lstOthers

If ctl.ItemsSelected.Count > 0 Then
For Each vItm In ctl.ItemsSelected
'MsgBox "ID = " & ctl.ItemData(vItm)
lngNew = ctl.ItemData(vItm)
If IsNull(cboQuestionID) Then
db.Execute "INSERT INTO
tblQuestionsMatrix(IndexID,IndexQuestionID,CredentialID)" _
& " SELECT IndexID, IndexQuestionID, " & lngNew _
& " FROM tblQuestions WHERE IndexID = " &
cboIndexID
ynrecordsChanged = True
Else
db.Execute "INSERT INTO tblQuestionsMatrix" _
& " VALUES(" & cboIndexID & "," & cboQuestionID &
"," & lngNew & ")"
ynrecordsChanged = True
End If
ctl.Selected(vItm) = False
Next vItm
Else
lstOthers.SetFocus
End If

If ynrecordsChanged Then
CleanLists
'lstSelected.Value = lngNew
lstSelected.SetFocus
Else
'lstOthers.Value = Null
lstOthers.SetFocus
End If

Set db = Nothing

End Sub
'*******************


for the RemoveOne button:

'*******************
Private Sub cmdRemove_Click()
Dim frm As Form
Dim ctl As Control
Dim lngKill As Long
Dim db As DAO.Database
Dim vItm As Variant
Dim ynrecordsChanged As Boolean

ynrecordsChanged = False
Set db = CurrentDb
Set frm = Forms!frmQuestionMatrix
Set ctl = frm!lstSelected

If ctl.ItemsSelected.Count > 0 Then
For Each vItm In ctl.ItemsSelected
'MsgBox "ID = " & ctl.ItemData(vItm)
lngKill = ctl.ItemData(vItm)
If IsNull(cboQuestionID) Then
db.Execute "DELETE FROM tblQuestionsMatrix" _
& " WHERE IndexID = " & cboIndexID _
& " AND CredentialID = " & lngKill & ";"
ynrecordsChanged = True
Else
db.Execute "DELETE FROM tblQuestionsMatrix" _
& " WHERE IndexID = " & cboIndexID _
& " AND IndexQuestionID = " & cboQuestionID _
& " AND CredentialID = " & lngKill & ";"
ynrecordsChanged = True
End If
ctl.Selected(vItm) = False
Next vItm
Else
lstOthers.SetFocus
End If

If ynrecordsChanged Then
CleanLists
'lstOthers.Value = lngKill
lstSelected.SetFocus
Else
'lstSelected.Value = Null
lstOthers.SetFocus
End If

Set db = Nothing

End Sub
'*******************


You might also try setting the MulitSelect property to "Extended". See Help
for the differences between Simple and Extended.


HTH
 
Back
Top