Multi-select list record creation code

[thesaurusrex]

I have a multi-select list box for selecting which faculty members apply to a project. The faculty table and project table are linked in a many-to-many relationship. I have the following code which should create entries in the link table:

Private Sub Command5_Click()
Dim varItm As Variant

rs.Open "tblFacultyLink", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

For Each varItm In lstFaculty.ItemsSelected
rs.AddNew
rs!FacultyID = Me.lstFaculty
rs!EntryID = Me.EntryID
rs.Update
Next varItm

rs.Close
Set rs = Nothing
End Sub

It successfully creates new records and enters the EntryID and LinkID (autonumber). However, FacultyID is always left blank. lstFaculty is the unbound list box which has three columns from the faculty table and is bound to FacultyID. Any ideas on why FacultyID isn't created in the link table (I get no error messages)?

Also, any ideas on how to prevent duplicate links being created every time the button is pushed? I was planning on having it first run a delete query for that EntryID in the link table so that it replaces the old links and any that have now been unselected are no longer linked. Comments on that idea?

Thanks again to everyone on the forums for your help.

 

[WayneRyan]

Rex,

Me.lstFaculty.Column

n starts at 0; the second column is .Column(1)

Wayne

 

[thesaurusrex]

Thanks very much. I had forgotten that setting the bound column in the list properties doesn't take care of it in this case. I am now getting the FacultyID put in, but it is not using the highlighted values. Instead, it uses whatever the select box is on. So, if I select the first 4 names, 4 records are created - but the FacultyID for each is whatever was last clicked on in the list. If I then unselect number 2, 3 records are created - all with the FacultyID of the second.

So it correctly determines how many items are selected, but the value of lstFaculty doesn't change. What should I change Me.lstFaculty.Column(0) to? Thanks again

 

[thesaurusrex]

Works perfectly, Pat. Thanks for all the help, Pat and Wayne.