Form using multicolumn multiselect listbox to add rows to a table

[accvbalearner]

Hello All,

I need some help and I'm sure there is a someone out there who know exactly how to solve my issue!

I have an Access form, in the form the user enters or picks a date, chooses a project from a combobox and then a report from a combobox. The first two boxes (date and project) filter the third box. There are also two multiselect listboxes as well WO and Emp. When the user chooses a project number the WO listbox filters for the work orders for that job. At the same time the Emp listbox filters for the employees that worked that day on that project.

The Emp listbox is also a multicolumn listbox. What i'd like to have happen is for the user to select the date, project and report then click each person that is listed as working on a the report for the day. Once the user has finished selecting then they can click a button and add the selection to a table.

I have it working with the work orders, but with the Emp I can only get it to add the data from the first column. After that it just repeats the data from the last row selected into the columns of the table.

Basically, I need to know how to tell the form to grab the data from columns in the rows that were selected, not just the data in the columns from the last row selected.

Below is my code:

Private Sub btn_AddEmployees_Click()
    ' This button will add selected Employees to a table
    ' alongwith the selected Job Number and Report Number.
    
    Dim db As Database
    Dim rs As Recordset
    Dim strsel_JNbr As String
    Dim strsel_RNbr As String
    Dim var_Emp As Variant
        
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("RNEmp", dbOpenDynaset, dbSeeChanges)
    
    strsel_JNbr = Inclsel_JNbr()
    strsel_RNbr = Inclsel_RNbr()
    
    For Each var_Emp In sel_Emp.ItemsSelected()
        rs.AddNew
        rs!EmpName = sel_Emp.ItemData(varItem)
        'rs!EmpClass = sel_Emp.Column(1, varItem)
        'rs!Badge = sel_Emp.Column(2, varItem)
        'rs!MicroEID = sel_Emp.Column(3, varItem)
        rs!JobNbr = strsel_JNbr
        rs!RepNbr = strsel_RNbr
        rs.Update
    Next
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Thanks in advance for any help or ideas you can provide!

Take Care,
accvbalearner

 

[theDBguy]

Hi. You seem to be using the Column() property correctly, just remember it is zero-based. Other than that, you may have to post a demo version of your db, so we can take a look why it's not working with that code.

 

[accvbalearner]

theDBguy,

Thanks for your quick reply.

I have the Column() statements remarked out because they work, but not correctly.

The multicolumn, multiselect listbox has 5 columns, basically name, rank, serial number, type stuff like below:
Name---------- Rank------------ EmpNbr------BdgNbr-------- Hrs
Bob-----------Bossman---------- BR549------- Bdg123--------- 12
Dave ---------Asst. Bossman--- BR550------- Bdg124----------- 8
George ------Worker1---------- BR551-------- Bdg125----------- 7
John ----------Worker2--------- -BR552-------- Bdg126---------- 8
Pete---------- Worker3---------- BR553-------- Bdg127--------- 11
Repete -------Worker4--------- BR554--------- Bdg128--------- 11
Mike ----------Worker5--------- BR555--------- Bdg129--------- 10
Joe -------------Worker6--------- BR556--------- Bdg130-------- 10

The date is 12-Oct-19, Project is 22 and the Report is 191012-22. Bob, George and Pete are selected, when the code runs the following is the result:

ID-------RepNbt------- PrjNbr-- Name------ Rank------- EmpNbr------ BdgNbr----- Hrs
1--------191012-22------22------Bob-------Worker3-------BR553-------Bdg127------11
2--------191012-22------22-----George----Worker3------BR553--------Bdg127------11
3--------191012-22------22------Pete-------Worker3------BR553--------Bdg127------11

My problem is getting the info for Bob in the list box to stay with Bob, George with George, etc.

Any ideas?

Take Care,
accvbalearner

 

[MajP]

Yes, you need Option Explicit at top of your code so you will know when you have undeclared variables (the stupidest thing that vba allows you to do).

var_emp and varitem

No such variable declared as varItem

Is this a global? Because I would assume it is a undeclared variant and should return null. Obviously it is returning 0 and thus each record gets row 0 data, "Bob's" data