display join-table records in multi-select list box

[mary r]

In Access 2003, multi-user legacy system, I've been asked to graft in some
new functionality. There is a pair of tables with a many-to-many
relationship that I have inserted a join table in between for normalization.
Call them tblAccounts --> tblAccountContacts --> tblContacts for this
example.

In a form for users to manage Contacts, I would like for the related
tblAccountContacts records to be displayed and editable in a multi-select
list box. Even better, if possible, would be to use Stuart McCall's
multi-pick TwinList to display available choices in one box and selected
choices in the other. (I have already coded and tested the TwinList for
initial data entry into a different set of tables, so that might be a useful
starting point. I'm seeing now that using it for data-entry from scratch is
much simpler than using it for maintenance.)

Sanity check: does it sound reasonable to modify the TwinList modules for
display and maintenance? The users are looking for a balance between simple,
quick development and ease of use for data-entry personnel, and I'm wondering
if this is just too complicated on the development side.

Would there be a simpler way to provide users with the means to view, add,
and delete the join table records? I'm very much open to suggestions.

Any help or ideas from the forum will be much appreciated!

Thanks in advance-
-mary

 

[Dirk Goldgar]

In Access 2003, multi-user legacy system, I've been asked to graft in some
new functionality. There is a pair of tables with a many-to-many
relationship that I have inserted a join table in between for
normalization.
Call them tblAccounts --> tblAccountContacts --> tblContacts for this
example.

In a form for users to manage Contacts, I would like for the related
tblAccountContacts records to be displayed and editable in a multi-select
list box. Even better, if possible, would be to use Stuart McCall's
multi-pick TwinList to display available choices in one box and selected
choices in the other. (I have already coded and tested the TwinList for
initial data entry into a different set of tables, so that might be a
useful
starting point. I'm seeing now that using it for data-entry from scratch
is
much simpler than using it for maintenance.)

Sanity check: does it sound reasonable to modify the TwinList modules for
display and maintenance? The users are looking for a balance between
simple,
quick development and ease of use for data-entry personnel, and I'm
wondering
if this is just too complicated on the development side.

Would there be a simpler way to provide users with the means to view, add,
and delete the join table records? I'm very much open to suggestions.

Any help or ideas from the forum will be much appreciated!

I haven't seen Stuart's TwinList, and it sounds like a good possibility. I
have developed a model for displaying & editing many-to-many values using a
multi-select list box. Here's the write-up on it:

*** Storing Multiple Selections From A List Box ***

The best way to store multiple items in a single field is not to do it
at all. Instead, use multiple records in a related table to represent
these items. It's a principle of relational database design that a
single field (column) holds only one datum.

An Access subform is designed to display and edit multiple records from
a related table (these records being related to the record currently
displayed on the main form), and does it with no code at all. A list
box isn't designed to do this, but for small "pick-lists" I do like the
multiselect list box. However, you need to use code to read the related
records for each new main record and select the appropriate items in the
list box, and then whenever the list box is updated you need to use code
to update the set of records in the related table.


Here's code from a sample form that represents "family members", with a
list box named "lstHobbies" that represents, for each family member,
that person's hobbies from the list of all possible hobbies.


'----- start of code -----
Option Compare Database
Option Explicit

Private Sub ClearHobbySelections()

Dim intI As Integer

With Me.lstHobbies
For intI = (.ItemsSelected.Count - 1) To 0 Step -1
.Selected(.ItemsSelected(intI)) = False
Next intI
End With

End Sub

Private Sub Form_Current()

Dim rs As DAO.Recordset
Dim intI As Integer

' Clear all currently selected hobbies.
ClearHobbySelections

If Not Me.NewRecord Then

Set rs = CurrentDb.OpenRecordset( _
"SELECT HobbyID FROM tblFamilyMembersHobbies " & _
"WHERE MemberID=" & Me.MemberID)

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

End If

End Sub

Private Sub lstHobbies_AfterUpdate()

On Error GoTo Err_lstHobbies_AfterUpdate

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

' Make sure the current member record has been saved.
If Me.Dirty Then Me.Dirty = False

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

ws.BeginTrans
blnInTransaction = True

' Delete all hobbies now on record.
strSQL = "DELETE FROM tblFamilyMembersHobbies " & _
"WHERE Memberid = " & Me.MemberID

db.Execute strSQL, dbFailOnError

' Add each hobby selected in the list box.
With Me.lstHobbies
For Each varItem In .ItemsSelected
strSQL = _
"INSERT INTO tblFamilyMembersHobbies " & _
"(MemberID, HobbyID) VALUES (" & _
Me.MemberID & ", " & .ItemData(varItem) & ")"
db.Execute strSQL, dbFailOnError
Next varItem
End With

ws.CommitTrans
blnInTransaction = False

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

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

End Sub

Private Sub lstHobbies_BeforeUpdate(Cancel As Integer)

Dim intI As Integer

' Don't allow hobbies to be updated before a MemberID has
' been generated.
If IsNull(Me.MemberID) Then
MsgBox "Please enter other information for this family " & _
"member before choosing hobbies.", , _
"Define Member First"
Cancel = True
Me.lstHobbies.Undo
' Clear the user's selection.
ClearHobbySelections
End If

End Sub
'----- end of code -----

As you see, there's a fair amount of code involved, because we're using
the list box to do something it wasn't built to do, but it works quite
nicely.

 

[mary r]

Thanks, Dirk. After digesting your post, I've decided to use a normal
subform to list junction-table records when they exist and only activate the
twinlist when there are no join-table records. I've already coded appending
from a twinlist, so I can easily give the user this convenience when he/she
is starting from scratch. For maintenance, it's extremely unlikely that more
than a couple of junction-table records would be added or deleted at any
given time, so the usual subform will work just fine.

Thanks for your reply-

-mary