Multi Select List Box Woes

  • Thread starter Thread starter Joshua Powell
  • Start date Start date
J

Joshua Powell

(I tried really hard to come up with a more descriptive subject name
but... this problem is sort of obtuse)

I am building a database that keeps track of volunteers. Theres a
table of volunteership types and a table that says what volunteership
types individuals are interested in. The main form has a listbox that
populates with what interests have been selected. I want users to
open a popup form and get a multiselect listbox to click on each of
the interests the volunteer will be associated with. Then later if
they want to edit I want it to open the same popup form that
automatically highlights all of the values they've already been
associated with, so the user can select more or deselect as they
please, then repopulate the main forms listbox with their new
selections.
________________________________________________________________________
FORM/TABLE/FIELD Definitions:
(Those with an asterisk are Primary Key, Auto-Number.)

frmKids (main form)
lstInvolvementDetails (This has the interests already associated with
the volunteer)

frmVolunteerInterests (pop-up form)
lstInterests (this is all interest options)

tblIndividuals (lngIndividualID*, etc.)
tblVolunteers (lngVolunteerID*,lngIndividualID)
tblVolunteershipTypes (lngVolunteershipTypeID*, strVolunteershipType)
tblVolunteerInterests (lngVolunteerID, lngVolunteershipTypeID)
____________________________________________________________________________

I'm guessing that I need frmVolunteerInterests to have a query as the
Record Source:

SELECT tblVolunteerInterests.lngVolunteerID,
tblVolunteers.lngIndividualID,
tblVolunteerInterests.lngVolunteershipTypeID FROM tblVolunteers INNER
JOIN tblVolunteerInterests ON tblVolunteers.lngVolunteerID =
tblVolunteerInterests.lngVolunteerID WHERE
(((tblVolunteers.lngIndividualID)=[forms]![frmKids]![txtIndividualID]));

Beyond that I don't know what I should do...
 
Joshua Powell said:
(I tried really hard to come up with a more descriptive subject name
but... this problem is sort of obtuse)

I am building a database that keeps track of volunteers. Theres a
table of volunteership types and a table that says what volunteership
types individuals are interested in. The main form has a listbox that
populates with what interests have been selected. I want users to
open a popup form and get a multiselect listbox to click on each of
the interests the volunteer will be associated with. Then later if
they want to edit I want it to open the same popup form that
automatically highlights all of the values they've already been
associated with, so the user can select more or deselect as they
please, then repopulate the main forms listbox with their new
selections.
________________________________________________________________________
FORM/TABLE/FIELD Definitions:
(Those with an asterisk are Primary Key, Auto-Number.)

frmKids (main form)
lstInvolvementDetails (This has the interests already associated with
the volunteer)

frmVolunteerInterests (pop-up form)
lstInterests (this is all interest options)

tblIndividuals (lngIndividualID*, etc.)
tblVolunteers (lngVolunteerID*,lngIndividualID)
tblVolunteershipTypes (lngVolunteershipTypeID*, strVolunteershipType)
tblVolunteerInterests (lngVolunteerID, lngVolunteershipTypeID)
________________________________________________________________________
____

I'm guessing that I need frmVolunteerInterests to have a query as the
Record Source:

SELECT tblVolunteerInterests.lngVolunteerID,
tblVolunteers.lngIndividualID,
tblVolunteerInterests.lngVolunteershipTypeID FROM tblVolunteers INNER
JOIN tblVolunteerInterests ON tblVolunteers.lngVolunteerID =
tblVolunteerInterests.lngVolunteerID WHERE
(((tblVolunteers.lngIndividualID)=[forms]![frmKids]![txtIndividualID]));

Beyond that I don't know what I should do...
Click to expand...

If I understand you correctly, you don't actually want your popup form,
frmVolunteerInterests, to have any RecordSource at all. The user isn't
going to directly edit any records on this form. Instead, the list box
on the form is going to serve as a mechanism for identifying (and
modifying) which of the list of possible volunteership types the current
volunteer is interested in. That can be done by querying
tblVolunteerInterests when the form opens and using the information
returned to pre-select the corresponding items in the list box. Then,
when the form closes, you run code that updates tblVolunteerInterests so
that it matches the items that are now selected in the list box.

I haven't quite figured out the relationship between tblIndividuals and
tblVolunteers. I don't see why you have a VolunteerID that is distinct
from an IndividualID. For this example, to make things easier for me,
I'm going to pretend that there is no VolunteerID or tblVolunteers;
instead, I'm going to assume that it is IndividualID that is stored in
tblVolunteerInterests. You can extend the ideas I'm presenting here to
insert that extra table if you want.

On frmVolunteerInterests, list box lstInterests has
tblVolunteershipTypes as its rowsource. For convenience, let's also
have a textbox on the form named "IndividualID", which we'll set to the
ID of the individual we're handling. In the Current event (or Load
event) of frmVolunteerInterests, you run code like this:

'----- start of code for Current event -----
Private Sub Form_Current()

Dim rs As DAO.Recordset
Dim intI As Integer

If CurrentProject.AllForms("frmKids").IsLoaded = False Then
' We don't know who we're relating to!
Exit Sub
End If

With Forms!frmKids!txtIndividualID
If IsNull(.Value) Then
' We *still* don't know who we're relating to!
Exit Sub
Else
Me!IndividualID = .Value
End If
End With

' Make sure there's nothing selected in our list box.
' There shouldn't be, but let's be safe.
With Me.lstInterests
For intI = (.ItemsSelected.Count - 1) To 0 Step -1
.Selected(.ItemsSelected(intI)) = False
Next intI
End With

' Get a recordset of the individual's current recorded interests.
Set rs = CurrentDb.OpenRecordset( _
"SELECT lngVolunteershipTypeID FROM tblVolunteerInterests " & _
"WHERE lngIndividualID=" & Me!IndividualID)

' Select the interests currently on record for this individual.
With Me.lstInterests
Do Until rs.EOF
For intI = 0 To (.ListCount - 1)
If .ItemData(intI) = CStr(rs!lngVolunteershipTypeID)
Then
.Selected(intI) = True
Exit For
End If
Next intI
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End With

End Sub
'----- end of code for Current event -----

Now, when the form is closed, we need to replace all the interests on
record for the current individual with those that are currently selected
in the list box:

'----- start of code for Unload event -----
Private Sub Form_Unload(Cancel As Integer)

On Error GoTo Err_Form_Unload

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim strSQL As String
Dim blnInTransaction As Boolean
Dim varItem As Variant

If IsNull(Me!IndividualID Then
' Can't do anything!
Exit Sub
End If

Set ws = Workspaces(0)
Set db = ws.Databases(0)

' Do all these database operations in a transaction.
' All must succeed, or none.

ws.BeginTrans
blnInTransaction = True

' Delete all interests now on record.
strSQL = "DELETE FROM tblVolunteerInterests " & _
"WHERE lngIndividualID=" & Me!IndividualID

db.Execute strSQL, dbFailOnError

' Add each VolunteershipTypeID selected in the list box.
With Me.lstInterests
For Each varItem In .ItemsSelected
strSQL = _
"INSERT INTO tblVolunteerInterests " & _
"(lngIndividualID, lngVolunteershipTypeID) " & _
"VALUES (" & _
Me!IndividualID & ", " & .ItemData(varItem) & ")"
db.Execute strSQL, dbFailOnError
Next varItem
End With

ws.CommitTrans
blnInTransaction = False

' Requery the list box on frmKids, since we've updated
' the individual's interests.
If CurrentProject.AllForms("frmKids").IsLoaded Then
Forms!frmKids!lstInvolvementDetails.Requery
End If

Exit_Form_Unload:
Set db = Nothing
Set ws = Nothing
Exit Sub

Err_Form_Unload:
If blnInTransaction Then
ws.Rollback
blnInTransaction = False
End If
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, _
"Unable to Update"
Resume Exit_Form_Unload

End Sub

'----- end of code for Unload event -----

That should give you the general idea, even if you do elect to use a
more complicated table structure.
 
Back
Top