Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to use a juntion table with a list box?

Posted on 2011-02-11
6
Medium Priority
?
250 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 1500 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…
Suggested Courses

971 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