Solved

How to use a juntion table with a list box?

Posted on 2011-02-11
6
246 Views
Last Modified: 2012-05-11
I have a main form that has a juction table as the subform. I can then assign multiple employees to a case. Now at this point it works but I would like to use a list box to select multiple employees and then insert them or delete them from juntion table. I have attached a sample database with where I am at.
JuntionTable.mdb
0
Comment
Question by:seanlhall
  • 3
  • 3
6 Comments
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 34872895
tblSubjects should not have EmployeeID -- you can remove that field.  You can use a multi-select listbox to assign employees (for a selected subject) to the junction table, but you can't do this in a datasheet subform.  I would just put the listbox in a standard subform, on the main form, with the subform bound to the junction table and the row source the Employees table.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34872903
Here is some code for working with the ItemsSelected collection of a multi-select listbox:
Private Sub cmdExportData_Click()

On Error GoTo ErrorHandler

   Dim intColumn As Integer
   Dim intColumns As Integer
   Dim intCount As Integer
   Dim intIndex As Integer
   Dim intRow As Integer
   Dim intRows As Integer
   Dim lst As Access.ListBox
   Dim strData As String
   Dim strPrompt As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
   
   Set lst = Me![lstSelectContacts]
      
   'Check that at least one item has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one item"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      lst.SetFocus
      GoTo ErrorHandlerExit
   End If
   
   intColumns = lst.ColumnCount
   intRows = lst.ItemsSelected.Count
   strTitle = "Information missing"
   
   'Test for required information, using listbox columns
   For Each varItem In lst.ItemsSelected
      'Check for required address information (or whatever you need to check)
      strTest = Nz(lst.Column(5, varItem))
      Debug.Print "Street address: " & strTest
      If strTest = "" Then
         strPrompt = "Skipping this record -- no street address!"
         MsgBox prompt:=strPrompt, _
            buttons:=vbExclamation + vbOKOnly, _
            Title:=strTitle
         GoTo NextItem
      End If
      
      'Do something with info from the listbox columns, using
      'this syntax
      strData = Nz(lst.Column(5, varItem))
      
NextItem:
   Next varItem

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34872915
In your code, you would pick up the SubjectID from the main form when writing records to the junction table.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:seanlhall
ID: 34873158
I have no idea. I need to select multiple employees and insert into TtblLKemployee.
JuntionTableV2.mdb
0
 

Author Comment

by:seanlhall
ID: 34891250
Still trying to fiqure this out. I think Im close. I have attached what I have. JuntionTableV3.accdb
0
 

Author Closing Comment

by:seanlhall
ID: 34943056
This lead me in a different direction
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question