Multiselect Listbox

[wwhit]

I have a Multiselect Listbox with a "Form Load" code as follows:

Dim rst As New ADODB.Recordset



ListBox2.ListStyle = 1
ListBox2.MultiSelect = 1
ListBox2.ColumnCount = 3
ListBox2.ColumnWidths = "2 in;1 in;3 in"
ListBox2.Font.Name = "Calibri"
ListBox2.Font.Size = 11

rst.Open "SELECT [Org Employee Info from Maximo Table].[NAME], [Org Employee Info From Maximo Table].[Job Position] FROM [Org Employee Info from Maximo Table] ORDER BY [NAME]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
ListBox2.Column = rst.GetRows
rst.Close
Set rst = Nothing

I would like to know how to take the selected item (EMPLOYEE) to a table called "Class Requirement Table" and put in the EMPLOYEE field. I do not need the Job Position field brought to the table. I also need the "checkboxes" in the multiselect listbox to clear. I found this code however I am not sure if it will work. Please help!!!

Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!EmpID = ctl.ItemData(varItem)
rs!OtherValue = Me.txtOtherValue
rs.Update
Next varItem

[pbaldy]

That code looks reaaaaallllyyy familiar.

If you're trying to add the selected items to another table, that technique will certainly work. I don't understand why you're setting everything in the load event rather than in design view. There's nothing dynamic in your code that would require it.

I'm not sure what checkboxes you're referring to. The only one I'm familiar with is associated with the new multi-value field, which 2003 didn't have.

[wwhit]

This should be in Access 2007. I have tried this code and it is not working.

[pbaldy]

Pet peeve: "not working" doesn't tell me anything. The code obviously works when applied correctly, as demonstrated in the sample db. I have no idea what modifications you made to it, what error you're getting or what it's doing wrong.

[wwhit]

Here is what I put in my code for a command button to click on to take the information from the multiselection listbox to the table:

Set ctl = Me.ListBox1
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!EMPLOYEE = ctl.ItemData(varItem)
rs.Update
Next varItem

I'm just thinking that because this is a listbox that is only displaying information that the code will not work. Not sure what I am doing wrong. It is giving me an error which I can not look at it right now as it is at work and I am at home.

[pbaldy]

There would have to be more to it. Did you look at the sample db that accompanied that code:

http://www.baldyweb.com/MultiselectAppend.htm

The actual code includes more; as noted in the link, that's just the "guts" of the code.

[wwhit]

yes i looked at the database and the code is for an unbound textbox not a listbox. guess im back to square one.

[pbaldy]

The code in the link uses both a listbox and the unbound text box. You can simply exclude the bits related to the text box if you don't need it.

[wwhit]

Here is what I have in for the code on the ListBox:

Dim ctlList As Control, varItem As Variant
Set ctlList = [Forms]![Class Requirement Form]![ListBox1]
For Each varItem In ctlList.ItemsSelected
rs.AddNew
rs!EMPLOYEE = ctl.ItemData(varItem)
rs.Update
Next varItem

Here is the error I am receiving:

Run-time error '2455':

You entered an expression that has an invalid reference to the property ItemsSelected.

I looked up the listbox property and it is the same code as what you are using. Any suggestions?

[pbaldy]

That's the name of the listbox, and it's multi-select? You use two different variables for the control. You still don't have all the code from the sample, like opening the recordset. Can you post the db here?

[wwhit]

I have attached the database for your review. Any suggestions would be must appreciated. Thanks.

[pbaldy]

Your "listboxes" are ActiveX controls, not listboxes. My code is intended to be used with the built-in listbox control. You'll need to find a method appropriate to the ActiveX control if you decide to keep using them. My first clue was when I got an error opening the db about broken references, which you'll face if you use this on other PC's. That's one of the reason many of us avoid ActiveX controls.

[wwhit]

That explains alot. Is there a listbox I can use that has the "checkboxes"?

[pbaldy]

Not having seen the listbox in action, I'm not sure what they are/do. Do they signify selected or something? If so, the built-in listbox signifies that by highlighting the row. I don't know of one with checkboxes.

[alaskahawk]

I'm doing something similar and most of your code will work; one thing I'd like to do though is to be able to add a new record to an open subform from a pick list; your example adds it directly to the table. I need it to add to whatever record is already opened on the subform on the main form. Make any sense? Thanks in advance.