HELP! with Data Entry using Multi-Select Listbox 4

[reachaf]

I am fairly new to ACCESS, so I would really appreciate any advice on this ACCESS issue I've been trying to solve in the last few days. Thanks.

The following is what I am trying to accomplish with the multi-select listbox on my form:

1. Users will make selections (>= 1 selection) from a multi-select listbox.
2. The values selected, along with other fields on the same form, will be poured into a database. So the listbox is mainly to facilitate data entry by different users and to make the form more organized and shorter(this way, I won't have lots and lots of Yes/No eheck boxes for each option in the listbox).

I am confused by what other treads said about using multi-select as parameters for a query. I know it makes what have been selected searchable. But what if I don't care about searching the values selected, all I want is for the values selected to fall into a field in a database along with other fields on the same form. I just want to populate a database with the multi-selected values.

Thanks in advance for any suggestion/input!

 

[fneily]

This is to make a comma delimited "row" of selected items from a list box:

Go to VBA (ALT+F11), click on Insert, Module and Type the following procedure:

Sub mslbSaveList(ctlMSLB As Control, ctlBound As Control)
Dim MyList, i
MyList = Null
On Error Goto Err_mslbSaveList
For Each i In ctlMSLB.ItemsSelected
MyList = MyList & ";" & ctlMSLB.ItemData(i)
Next
If Len(MyList) > 0 Then MyList = Mid(MyList, 2)
ctlBound = MyList
Bye_mslbSaveList:
Exit Sub
Err_mslbSaveList:
Beep
MsgBox Error$, 16
Resume Bye_mslbSaveList
End Sub

Open a table in Design view. Add a new field called Employees with data type Text, and set the FieldSize property to 255.
Save and close the table.

Open the form you want to have the list on and add a text box with the following properties:
Name: txtEmployees
ControlSource: Employees

Add a new list box with the following properties:
Name: mslbEmployees
RowSource: Employees
ColumnCount: 2
ColumnWidths: 0
Bound Column: Column number with data you want to appear.
MultiSelect: Simple

On the View menu, click Code to open the form's module and type the following procedure:

Private Sub mslbEmployees_AfterUpdate()
mslbSaveList Me![mslbEmployees], Me![txtEmployees]
End Sub

View the form in Form view, and make different selections in the new multiple-selection list box and you will see the comma delimited list being generated in the text box.

Neil

 

[reachaf]

Neil,

Thanks so much for the codes. I tried them out, and the form looks like what I am looking to accomplish. However, I am not sure where the multi-select options go. Are they suppopsed to be in the table (using text --> lookup), the text box, or the list box. Since the listbox uses Employees as the row source, it looks like the selection options should be created in the table (where the field "Employees" is first created)?

Also, I am wondering if the same codes apply if I have many multi-select listboxes on my form.

Thanks so mcuh for the reply. I am in such a bind to solve this quickly.

- Janet

 

[fneily]

Ok, remember, I don't know the name of your fields or tables so you're going to have to substitute in the code. I have a table with a field called Names. In that table I created another field called employees (these are just made up names). The field Names is in the second column of my table. I created a listbox, the wizard is turned off, on a form with the attributes mentioned. The Row Source is the name of the Table you are creating the list from. Employees is just an example. My Bound Column would then be 2 because that's what I want the listbox to show, a list of Names which is in the second column of my table. I also created a text box to hold the comma delimited "row" being created. Again with the attributes mentioned and I "bound" it to the field called employees so I could save it. You don't have to do this. I depends on what you want to do with it. Then I added the above codes and now on my list of names, when I select, let's say, Bill and Barb, it will show Bill;Barb in the text box.
This works with any listbox, just be careful to change the names where appropriate.

Neil

 

[reachaf]

Hi Neil,

Thanks a bunch for the prompt and detailed explanation. It makes a lot of sense now when I modify the codes. However, I guess I am still quite ??? about where exactly the "list of names" (Bill; Barb; Barbara) should be created. Since the text box and listbox both reference back to the table as either the control source or the row source, where should I type in that list of names? In the table under "employees"?

- Janet

 

[fneily]

I'm assuming that you have a field in a table that has some data you want in a list, eg. a field with firstnames. Ok. Create your listbox without the wizard. Click on it, bring up it's property sheet, click on the tab ALL and let's look at it's properties. The listbox's Name option will be mslbemployees (you can make up one if you like, but then you have to change the code.). The Column Count will be 2 (Usually in the first position is the primary key and one position to show the data to select from). The Column Width will be 0". The Bound Column is the number of the column that has the data you are showing in the listbox (this would be 2). The Row Source is the name of the table that has that field in it. This creates your LIST.
The text box you'll create will store the items that are clicked on in the Listbox separated by a ; . The control source of this textbox just stores the result.
The control source relates to a FIELD. The row source relates to the TABLE.

It's too bad we can't have video conferencing so you can actually see what I'm saying. Next project for TEK-TIPS.

Neil

 

[reachaf]

Ok, the list of the names (I am following your specifics for now) is now showing up in the listbox and everything looks good in the form view. However, just as I was testing out the form by making selections, a message popped out that says "You can't assign a value to this object" and I was not able to make selections nor see the selections fall into the text box. What may be causing this?

I know, I wish there's video conferencing and thanks for your patience.

- Janet

 

[fneily]

Ok. Check the AfterUpdate code (go to design view, click on the listbox, bring up it's property sheet, click on the Event tab and click on the AfterUpdate event. It should open up in VBA).

mslbSaveList Me![mslbEmployees], Me![txtEmployees]

The argument after the first Me! is the name of your LISTBOX. The second ME! is the name of your textbox.

It's probably now making sure everything is spelled correctly. To check the names of controls, go to design view, click on the control, bring up it's property sheet, click on the ALL tab and scroll to the very top. You'll see and option called Name. Whatever is in the box next to Name is the name.

Neil

 

[reachaf]

I double-checked the AfterUpdate code on the form (though they are direct cut-n-pastes from the post)and also the names of the listbox and textbox. Everything looks okay to me. But the message still pops out. I am not sure what might be helpful in figuring out what I am doing incorrectly, but my email is reach_af@yahoo.com and I would really appreciate it if you could send me what you have (the sample form) or I can send you the access file I made following this exercise. Thanks, Neil!

 

[herbivorous]

I came across this thread months later, and found Neil's sub very helpful, though I recast it as a function that returned the semi-colon delimited string so that I could insert it into a field in the underlying table, rather than into a control. This would be useful to do after update or on change to make sure changes get recorded. Then I was able to retrieve this info with this kind of code --

Dim i As Integer
i = 0

Do While i <> Me!lboType.ListCount
If InStr(Me!Type, Me!lboType.ItemData(i)) > 0 Then
Me!lboType.Selected(i) = True
Else
Me!lboType.Selected(i) = False
End If
i = i + 1
Loop

Type being the underlying field, lboType being the multi-select box. This could be cast as a subroutine.

Put all together, this seems like a decent way to save and retrieve multi-select info into a table. Though it has its limitations, for example in the use of the InStr function -- one has to be careful that there aren't ambiguous entries in the list box's bound column. In my case, it's unique two-letter codes, so not a problem.

A more space-efficient model would pack using a binary method on 2^i. But that has its limitations, too. Anyhow, I hope this information will help any future scroungers for information on this topic.

Thanks to Neil for putting me on the right track.

 

[hungate]

fneily- did you ever discover the problem that reachaf was haveing? I am having the same problem. Just to recap. I have a tblEmployees, with fields FirstName, LastName, and the new field for the text box Worker.

I have a list box with a control source to the tblEmployees, LastName. The textbox is created, and bound to the field Worker. I have inserted the module. And I am having the same problem with the error. If I change the afterupdate event I will get a form that looks right however, it will not allow me to make selections. Please let me know what you think. Also if you would prefer to send a sample that would help. Thanks in advance.

t_hungate@yahoo.com