Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / January 2008

Tip: Looking for answers? Try searching our database.

Multiple selection on form, join tables, many-to-many relationship

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
HeidiN - 08 Jan 2008 21:41 GMT
I am using Access 2003.

Here is what I am trying to do:

I have a Contact table and a Contact Type table.  Each contact can have
multiple Contact Types (and each Contact Type will have many Contacts). I
want to be able to select multiple Contact Types from a drop down list (which
comes from the Contact Type table) by holding the Ctrl key and selecting the
Contact Type.  I then want those selections to go into the record for the
specific Contact that is being edited on the Contact form and the data for
the selections go into the join table.

Here’s what I have done.

1. Have a Contact table which lists details about the contact.
2. Have a Contact Type table which lists the various types that a contact
can be.
3. Have a join table where the Contact table and the Contact Type
relationships are linked.
4. Have a form to input the Contact information.
5. Have a form with the drop down box linked to the Contact Type table that
allows multi-select.

Problem:

1. I cannot figure out how to link the form with the drop-down box to the
specific Contact on the contact form.
2. I cannot figure out how to get the multiple selections to go into a table
(I assume that it should update to the join table?).

Any help would be greatly appreciated.
Dirk Goldgar - 08 Jan 2008 21:56 GMT
>I am using Access 2003.
>
[quoted text clipped - 31 lines]
>
> Any help would be greatly appreciated.

I'm confused, because you seem to be saying that you have a combo box
(dropdown) that allows multiselect.  But that's not a property of the Access
combo box, but rather of a list box, which doesn't drop down.  Have I
misunderstood?

I've demonstrated code that uses a multiselect list box to present and
update the juntion table of a many-to-many relationship.  I'd be happy to
post that, if you'd like, but I want to make sure that I understand what you
are doing.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

HeidiN - 14 Jan 2008 17:15 GMT
Sorry for the confusion.  The box that allows the multi-select is a list box.
If you could post the code to help me update the data from the multi-select
list box to the junction table, I would appreciate it.

I also have one additional question.  The PK in the subform with the list
box is linked to the PK in the mainform.  I will need to know how to update
the PK field with the multi-select info to the junction table where it is
pulling the PK from the main form.

Thank you.

> >I am using Access 2003.
> >
[quoted text clipped - 41 lines]
> post that, if you'd like, but I want to make sure that I understand what you
> are doing.
Dirk Goldgar - 14 Jan 2008 21:52 GMT
> Sorry for the confusion.  The box that allows the multi-select is a list
> box.
[quoted text clipped - 7 lines]
> the PK field with the multi-select info to the junction table where it is
> pulling the PK from the main form.

I don't understand your additional question.  The approach I'm talking about
doesn't use a subform to present the many-to-many relationship, because the
code that manages the list box takes care of that.  I'll post the
description of the example problem and the solution, so you can see how it
works and whether it corresponds to what you're trying to do.  What follows
is a stored discussion, so please pardon the lecturer's tone.

*** 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

HeidiN - 14 Jan 2008 22:33 GMT
Thank you, Dirk.  This looks like what I am looking for.  I appreciate your
assistance.

Sincerley,

Heidi

> > Sorry for the confusion.  The box that allows the multi-select is a list
> > box.
[quoted text clipped - 161 lines]
> the list box to do something it wasn't built to do, but it works quite
> nicely.
Jeanette Cunningham - 08 Jan 2008 21:58 GMT
Hi Heidi,
I have just answered a very similar question about 10 mins ago.

Here is the general idea of form set up for data entry when you have 2
tables
linked with a junction table.
Use a main form in single view for one of the Many side tables.
The main form has a continuous subform bound to the junction table with the
Link Master set to the main form's PK field. This subform has at least one
visible control that is a combo box bound to the second table's PK field.

Jeanette Cunningham

>I am using Access 2003.
>
[quoted text clipped - 31 lines]
>
> Any help would be greatly appreciated.
HeidiN - 14 Jan 2008 17:25 GMT
Thanks, Jeanette.  I believe I already have the forms setup in the manner you
suggest.  However, I obviously don't have something set properly, as it is
when I go to post the data that only one selection will add to the join table.

> Hi Heidi,
> I have just answered a very similar question about 10 mins ago.
[quoted text clipped - 44 lines]
> >
> > Any help would be greatly appreciated.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.