Unbound Multiselect Listbox Highlighting and updating records

[Tsilatipac]

I am very new so I apologize for the fusilade of questions to follows. I
have been searching the web and this forum and find pieces but not enough for
me to put together...

Here is my problem:

tblActivity
ActivityID, Primary Key-Autonum
ActivityDesc, Text
ActivityCreateDate, Date
ActivityCloseDate, Date
etc...

tblPeople
PeopleID, Primary Key-Autonum
PeopleNameLast, Text
PeopleNameFirst, Text
PeopleTitle, Text
etc...

tblActivityOwners
ActivityID, Foreign Key-Number
PeopleID, Foreign Key-Number

I created a form for entering the activity information. On this form is an
unbound multiselect listbox and I chose "will remember for later" during the
wizard. Now it's time to cut code and I cannot seem to get started. Here
are the things I need to do:

(1) When the form loads, check to see if there is data for this activity in
tblActivityOwners. If there is, prehighlight the listbox with the owners so
we can see who are already identified as the owners.

(2) When the activity record is updated, delete any entries for this activity
in tblActivityOwners and write the new selections (if any) to
tbleActivityOwners to capture any changes made.

(3)Put a button beside the listbox to add a new person. OnClick open a small
form to add a record to tblPeople. Click "Okay"button and close form then
requery the listbox highlighting people for this activity in
tblActivityOwners. I don't care if we need to click on the person(s) we just
added.

GEEZ!!! All this grief from one little listbox. This same set of activities
must be done all of the time. It would make a good case study to post
somewhere because I cannot find it anywhere. Of course, knowing where to
look is usually 9/10's of the battle.

Thanks for any help or pointers.

 

[Sandra Daigle]

This sounds like a good place to use a pair of multiselect listboxes - one
showing the available people (those not already assigned to the activity)
and the other showing those that are already assigned. Take a look at my
example which uses classes and students. This might be a helpful starting
place for you to build your form:

It is in the SelectRecords sample database:

http://www.daiglenet.com/msaccess.htm

 

[Tsilatipac via AccessMonster.com]

I really wanted to keep this to one multiselect listbox. We came up with a
neat solution that seems to be working quite well. It is not very effecient
code but, we got it working in a few hours and this database will have a
short life cycle. If anybody wants to make it more effecient, I'd love to
see what you come up with. Here is the code we used:
-----------------begin code ----------------------------
Option Explicit

Private Sub Form_Current()
'Each time the form becomes current, update selections
'If form is not a new record execute SelectList
'If form is a new record, clear all selections.
Dim var As Variant

If Me.NewRecord = False Then
SelectList Me.ActivityID
Else
ClearList
End If

End Sub

Private Sub lstOwners_Click()
If Not IsNull(Me.ActivityID) Then
UpdateOwners Me.ActivityID
End If
End Sub

Private Function UpdateOwners(lngActID As Long)
Dim var As Variant
Dim strSQL As String, strInits As String

' Clear out existing items
strSQL = "DELETE * from tblActivityOwners WHERE ActivityID = " & lngActID

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' Add the new ones
For Each var In Me.lstOwners.ItemsSelected
strSQL = "INSERT INTO tblactivityOwners " & _
"(ActivityID, " & _
"OwnerID) " & _
"SELECT " & _
lngActID & " as Expr1, " & _
Me.lstOwners.Column(0, var) & " as Expr2;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
strInits = strInits & Me.lstOwners.Column(2, var) & ";"
Next var

If strInits <> "" Then
If Right(strInits, 1) = ";" Then strInits = Left(strInits, Len(strInits) -
1)
Me.txtActivityOwners.Value = strInits
End If
End Function

Private Sub SelectList(lngActID As Long)
'Clear all items that are selected
'Create selQuery string to select current activity owners
'Generate the record set using the query string you just created
'Step through record set and highlight those names.
Dim var As Variant
Dim i As Integer
Dim strSQL As String
Dim rs As DAO.Recordset

ClearList

strSQL = "SELECT DISTINCT tblActivityOwners.OwnerID " & _
"FROM tblActivityOwners " & _
"WHERE ActivityID=" & lngActID

Set rs = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF
For i = 0 To Me.lstOwners.ListCount - 1
If CStr(Me.lstOwners.Column(0, i)) = CStr(rs.Fields("OwnerID")) Then
Me.lstOwners.Selected(i) = True
End If
Next i
rs.MoveNext
Loop
End Sub

Private Sub ClearList()
'Clear all selected names that happen to be in the list
Dim var As Variant
For Each var In Me.lstOwners.ItemsSelected
Me.lstOwners.Selected(var) = False
Next var
End Sub
-----------------------End Code---------------------------------------

That is working as desired. List box is populated with names. When a record
is new, nothing is highlighted. We can highlight names as desired. When the
activity is updated, the names are stored correctly. When we come back to
the record, the proper names are displayed. We change the names they update
correctly.

Carl