How to use a list box through a junction table

[inthemix]

My goal is to have a sub-form located on the main form which containts a listbox. The user will be able to select anany number of offices (by officeName) from this listbox.

IMO the design is very sound, in 3NF.

I just can't figure out how to get the listbox control to work as desired.

Table 1 - Applicant
Table 2- Interest (Junction)
Table 3 - Office

Reason for junction:
applicant can be interested in many offices
office can have many applicants interested in it

Relevant fields
Applicant - applicantID PK

Interest
interestID PK
applicantID FK
OfficeID FK

Office
officeID
officeName

Any advice would be great

Thanks

[inthemix]

bump for sake

[Rabbit]

How did you want to store this?

If someone chose multiple offices did you want a record for each office or all the offices on one line.

[MMcCarthy]

Change this table slightly.

Interest
interestID PK
applicantID FK
officeID FK

As it is a join table you can remove the interestID and make applicantId and OfficeID a joint primary key as follows:

Interest
applicantID PK
officeID PK

Assuming your main form is based on the Applicant and you want your listbox to allow the applicant to express interest in multiple offices then I wouldn't use the subform for this. The subform should only be used to display the interested offices after they are selected and the subform would be based on the following query.

SELECT Interest.applicantID, Office.officeID, Office.officeName
FROM Interest INNER JOIN Office
ON Interest.officeID = Office.officeID

and would have a link to the main table based on applicantID.

Put your multi select listbox on the main table and have a submit button with the following code.

Private Sub cmdSubmit_Click()
Dim valSelect As Variant
Dim strSQL As String
 
   For Each valSelect In Me.listboxName.ItemsSelected
      strSQL = "INSERT INTO Interest (applicantID, officeID) VALUES (" & _
      Me.applicantID & ", " & Me.listboxName.ItemData(valSelect) & ")"
   Next valSelect
 
   Me.SubformObjectName.Requery
 
End Sub
 

Substitute your listbox name and subform object name in the above.

Mary