Many-to-Many: How relate on form?

[Jenna B]

Hi there,

I am trying to wrap my brain around the following problem unsuccessfully.
(Using Access 2003)

I have the following tables: Contacts, Certifications and a "junction" table
which includes the foreign keys for both to create a so-called "many-to-many"
relationship. (Note: I'm simplifying my table names for clarity's sake in
this explanation.)

A contact can have several certifications, and certifications can belong to
multiple contacts.

Contact Table:
ContactID - PK
ContactFName
ContactLName
etc, etc, etc

Certification Table:
CertID - PK
CertAbbreviation (Example: PLS)
CertDescription (Example: Professional Land Surveyor)

Junction Table:
junctID - PK
junctContID - FK
junctCertID - FK

I'm pretty sure this is set up correctly. I have the relationships defined
as one-to-many in the appropriate directions.

I want the user to be able to open the Contact form and view/modify
certifications that a specific contact has. My plan was to use a multi-select
list box. However, I'm not sure how to proceed in making this information
available on the form. My form is based on a query that combines info from
several tables, including the Contacts table.

My question(s):
What field(s) is the multi-select list box based on?
With the current setup that I have, which fields will need to be on the
form, even if hidden?
Does it have to be a subform?

Thank you in advance for your help.

 

[Jeff Boyce]

Jenna

A fairly standard design approach would be to have Contacts feed your main
form, and a query based on your junction table joined to the Certifications
table feed a subform.

Is there a reason that a subform is not acceptable?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jenna B]

Jeff,

Thanks for your reply.

My idea is to have a multi-select list box on the form with all the possible
certifications, so that they can click and select from this list all the
certifications that apply to a specific contact.

I'm not sure how to populate the list box with all the values. Would it be
based only on the Certification table? If so, how do I tie it to the contact
info on the form?

If it is based on a query, I'm just not sure which fields I need to include
in the query for the subform. I've tried several options, and am trying again
right now to figure something out.

Hope this makes sense. Thanks again!

 

[John Vinson]

I want the user to be able to open the Contact form and view/modify
certifications that a specific contact has. My plan was to use a multi-select
list box. However, I'm not sure how to proceed in making this information
available on the form. My form is based on a query that combines info from
several tables, including the Contacts table.

The most common solution is: Use a Subform instead, based on the
junction table. The Subform can have a combo box allowing you to
select the Certifications for each record.

If you really want a multiselect listbox, you'll need some VBA code to
copy the CertificationID's into the junction table, and from the
junction table into the listbox. Not too hard but a fair bit of code,
and you need ZERO code if you use the subform.


John W. Vinson[MVP]

 

[Jenna B]

I finally got something that basically works. But it's not very pretty.

I have a subform on the main contact form that has the whole list of certs
available. I've played around with creating a multi-select list box here, but
it gives me this error:
"You tried to assign the Null value to a variable that is not a Variant data
type." (Error 3162)

I also created a subform that would show which certs currently applied to
the contact, along with a delete button to remove the cert from that contact.

There has got to be a simpler way of doing this..... Please!?!

Thanks!

 

[Jenna B]

I've got a rudimentary version working based on that sort of setup.

It's not what I had in mind (as far as being aesthetically pleasing and
user-friendly), but maybe that just depends on how I implement it.

Thanks for your help (both John and Jeff).

 

[Jeff Boyce]

Jenna

If you are up for the coding, here's an approach:

A main form lets you select the Contact you are working with.

A listbox (on the left) lists all possible Certifications (except for those
the Contact already has).

A listbox (on the right) lists all Certifications the Contact already has.

If you use one of the wizards, like a query wizard, you'll see this "paired
listbox" approach, complete with command buttons to select/de-select
one/all.

To actually pull this off, you need queries that look at the
"ContactsCertifications" table and the "Certifications" table. You need to
add code behind the button clicks that add a new record (or records) to the
ContactsCertifications table, based on which Contact and which (one or more)
Certification(s) are selected in the left listbox. Or, to delete, you need
to write the code that deletes a record (all records) from the CC table
where ContactID = your contact and CertificationID = the selected (right
side listbox) Certification(s)... or all.

If this sounds like more coding that you care to do, consider what John & I
have suggested - main form/subform.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

I finally got something that basically works. But it's not very pretty.

Well, try adapting this code:

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the
' selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table,
' newly cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub




John W. Vinson[MVP]

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImplementingM2MRelationship.mdb" which illustrates a couple
of ways to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jenna B]

Roger,

I'll take a look at it. I appreciate the help - it's always easier to look
at an example to get ideas.

Thanks!

 

[Jenna B]

Roger,

Thanks again for the link. You have some great resources on your website.
Thanks for making those examples available to others!

 

[Jenna B]

Roger,

I downloaded your ListBoxExcludeOnClick2k.mdb, which I am attempting to use
to allow users to select a certification to apply to a specific contact. I am
getting the following error: Subscript out of range (Error 9), when I attempt
to double click on an entry.

I think this may have something to do with the fact that the listbox on your
form has only one field, and I am trying to incorporate 3. My listbox
(lstContactCerts) is based on the following: SELECT DISTINCT
tlkpContactCertification.ContactCertID,
tlkpContactCertification.ContactCertAbb,
tlkpContactCertification.ContactCertDescrip FROM tlkpContactCertification
ORDER BY [ContactCertAbb];

I would really appreciate your help in figuring out why/how to fix this.

Thanks so much!

Here's the code: _____________________________

Option Compare Database
Option Explicit

Dim CertList() As String

Function BuildExclusionList() As String
Dim i As Integer
Dim strTemp As String
CertList(UBound(CertList)) = lstContactCerts
strTemp = "" & CertList(2) & ""
If UBound(CertList) > 1 Then
For i = 2 To UBound(CertList) - 1
strTemp = strTemp & ",'" & CertList(i) & "'"
Next i
strTemp = strTemp & ",'" & CertList(UBound(CertList)) & "'"
End If
MsgBox strTemp
BuildExclusionList = strTemp
ReDim Preserve CertList(UBound(CertList) + 1)
End Function



Private Sub cmdReset_Click()
On Error GoTo Err_cmdReset_Click
Dim strSQL As String

ReDim CertList(2)
strSQL = "SELECT DISTINCT tlkpContactCertification.ContactCertID, " & _
"tlkpContactCertification.ContactCertAbb,
tlkpContactCertification.ContactCertDescrip " & _
"FROM tlkpContactCertification ORDER BY [ContactCertAbb]"
Me.lstContactCerts.RowSource = strSQL
Me.lstContactCerts.Requery
Exit_cmdReset_Click:
Exit Sub

Err_cmdReset_Click:
MsgBox Err.Description
Resume Exit_cmdReset_Click

End Sub

Private Sub lstContactCerts_DblClick(Cancel As Integer)

Dim strSQL As String, strWhere As String

strSQL = "SELECT DISTINCT tlkpContactCertification.ContactCertID, " & _
"tlkpContactCertification.ContactCertAbb,
tlkpContactCertification.ContactCertDescrip " & _
"FROM tlkpContactCertification ORDER BY [ContactCertAbb]"
strWhere = " WHERE ContactCertAbb NOT IN (" & BuildExclusionList & ")"
strSQL = strSQL & strWhere
MsgBox strSQL

Me.lstContactCerts.RowSource = strSQL
Me.lstContactCerts.Requery
End Sub