By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,106 Members | 2,679 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,106 IT Pros & Developers. It's quick & easy.

How to use a list box through a junction table

P: 2
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
Mar 5 '07 #1
Share this Question
Share on Google+
3 Replies


P: 2
bump for sake
Mar 6 '07 #2

Rabbit
Expert Mod 10K+
P: 12,366
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.
Mar 6 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Interest.applicantID, Office.officeID, Office.officeName
  2. FROM Interest INNER JOIN Office
  3. 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSubmit_Click()
  2. Dim valSelect As Variant
  3. Dim strSQL As String
  4.  
  5.    For Each valSelect In Me.listboxName.ItemsSelected
  6.       strSQL = "INSERT INTO Interest (applicantID, officeID) VALUES (" & _
  7.       Me.applicantID & ", " & Me.listboxName.ItemData(valSelect) & ")"
  8.    Next valSelect
  9.  
  10.    Me.SubformObjectName.Requery
  11.  
  12. End Sub
  13.  
Substitute your listbox name and subform object name in the above.

Mary
Mar 6 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.