471,355 Members | 1,655 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

How to use a list box through a junction table

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
3 2218
bump for sake
Mar 6 '07 #2
Rabbit
12,516 Expert Mod 8TB
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
14,534 Expert Mod 8TB
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.

Similar topics

1 post views Thread by mksql | last post: by
1 post views Thread by LurfysMa | last post: by
2 posts views Thread by Henry Stockbridge | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.