Adding Items from a Multiple Select List Box to a Table

[beacon]

Hi everybody,

[Access 2003]

I'm creating a database that will assign standards to departments. My plan is to have a form that will allow the user to use 'Next Record' to go to each standard and have a multiple select list box with all of the departments, so the user can select those departments that are responsible for the standards.

I don't have much experience with list boxes, so is it possible to create a form like this? If so, and I think it is, will I have to add code to the list box to update the table based on the selections or is there a way to set up the form to have it automatically update the table? I searched online, but the results were limited and didn't seem to address what I'm trying to do.

Here's my tables (only relevant fields are shown):

tblDepartments
 
DepartmentID - Autonumber
DepartmentName - Text
 

tblStandards
 
StandardsID - Autonumber
StandardText - Text
 

tblStandardsDepartment
 
StandardsDepartmentID - Autonumber
StandardsFK - Number
DepartmentsFK - Number
 

There's a one-to-many relationship from tblStandards to tblStandardsDepartment and a one-to-many relationship from tblDepartment to tblStandardsDepartment.

Thanks,
beacon

[ADezii]

@beacon
This would be the general idea, make sure that you set a Unique, Non-Primary Key, Composite Index on the tblStandardsDepartment Table based on the [StandardsFK] and [DepartmentsFK] Fields in order to avoid Duplication.

Dim intI As Integer
Dim lst As ListBox
Dim varItem As Variant
 
Set lst = Me![lstDepartments]
 
With lst
  If .ItemsSelected.Count = 0 Then Exit Sub
    For Each varItem In .ItemsSelected
      CurrentDb.Execute "INSERT INTO tblStandardsDepartment ([StandardsFK], [DepartmentsFK]) VALUES (" & _
                         Me![txtStandardsID] & "," & .Column(0, varItem) & ");", dbFailOnError
    Next
End With

[beacon]

I'm not sure what you mean when you say, "make sure that you set a Unique, Non-Primary Key, Composite Index on the tblStandardsDepartment Table based on the [StandardsFK] and [DepartmentsFK] Fields in order to avoid Duplication." Are you saying that I need to create another autonumber field?

Between the time I posted my initial question and now I showed the person that requested the database what my plans were and they gave me new instructions for the listbox. They want the listbox to include check boxes.

I know that won't be possible using the listbox control, but thought that it might be possible using a subform with check boxes on it. My question now is whether or not I could still use the code you provided to insert the departments selected into the table.

One other question, the subform would need to be unbound if I went that route, right?

[ADezii]

@beacon

Are you saying that I need to create another autonumber field?

No, just eliminating duplication on the Foreign Key Fields.

I know that won't be possible using the listbox control, but thought that it might be possible using a subform with check boxes on it. My question now is whether or not I could still use the code you provided to insert the departments selected into the table.

No, completely different scenario.

[beacon]

To eliminate the duplication on the foreign keys, all I have to do is change the foreign key fields on the tblStandardsDepartment to "Yes (No Duplicates)" for the index, right?

Just to make sure I'm clear on your post prior to your last one, the code that you provided will update my tblStandardsDepartment after the selection has been made, right?

What about the display and if items are unselected? If, for the first standard, I'm supposed to have Dept 1, 3, and 5 assigned to it and I go to the second standard, where Dept 2 and 4 should be assigned, will the selections on the list update based on the standard? And would I need to write a block of code similar to yours for when the user unselects an item in the list box so it updates the table?

I'm also concerned what I need to enter for the control source, if anything, for the Department List Box.