Multiselect List Box 1

[Zorro1265]

I have a multiselect listbox whose selections are then copied into a textbox using the following code.

Private Sub ListAvailable_Click()

Dim strItems As String
Dim intItem As Integer
For intItem = 0 To ListAvailable.ListCount - 1
If ListAvailable.Selected(intItem) Then
strItems = strItems & ListAvailable.Column(0, intItem) & ";" & _
ListAvailable.Column(1, intItem) & ";" & _
ListAvailable.Column(2, intItem) & ";"
End If
Next intItem
ListSelected.RowSource = ""
ListSelected.RowSourceType = "Value List"
ListSelected.RowSource = strItems


End Sub

This gives me 3 columns of data inthe textbox. I want to take each line in that text box and append it to a table along with the current ID on the form. Is this possible? I thought I saw something like this but with the search disabled I am stuck.

Thanks

 

[RickSpr]

You need to use DAO to do this, since you'll be adding multiple rows, presumably to a table other than the form's Record Source. If you're using Access 2000, go to the Tools>References dialog and check "Microsoft Data Access Objects 3.6".

Assumptions: MyTable is the table you're appending to. It has 4 fields: RecID, Data1, Data2, and Data3. Your form has a text box called txtRecID which contains the current ID.

In this example the code is attached to the Click event of a command button, rather than your list box. It doesn't make much sense to update the table in the Click event of a multi-select list box; if the user clicks one item, it will be added immediately. If the user then clicks a second item, it will also be added--and the first item, which is still selected, will be added again. Besides, if the user aims wrong, they won't have a chance to undo what they've done. It's better to let them select all the items, unselecting if they click the wrong one, and then click a single Update button to cause the records to be added at once.

Private Sub cmdUpdate_Click()
    Dim db As DAO.Database, rst As DAO.Recordset
    Dim varItem As Variant

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("MyTable", dbOpenDynaset)
    For Each varItem In Me!ListAvailable.ItemsSelected
        rst.AddNew
        rst!RecID = Me!txtRecID
        rst!Data1 = ListAvailable.Column(0, varItem)
        rst!Data2 = ListAvailable.Column(1, varItem)
        rst!Data3 = ListAvailable.Column(2, varItem)
        rst.Update
    Next varItem
    rst.Close
    Set rst = Nothing
    Set db = Nothing
End Sub

Notes: This code takes the record data from the list box, since it's easier than parsing the contents of the text box after you've built it. Also, I've used the ItemsSelected collection, rather than iterate through all the list items and test each one to see if it's selected. ItemsSelected is a collection that contains the selected row numbers.
Rick Sprague

 

[Zorro1265]

Thanks Rick,

My boss threw a wrench into things, is there a way that if a user changes there mine and comes back afterwards alters his ItemsSelected that it can replace the first entries in the table? I had origianlly thought of using an append and delete query but I dont know how to write one yet. What would you do?

Keith

 

[RickSpr]

It depends. If the user comes back again, will we be able to figure out what ID they used last time? And are we sure that that ID was only used by that one user? And do you mean that each time they make choices for an ID, it should replace all the previous choices for that ID?

I expect the answer is Yes to all three of these questions, and I'm going to assume it is in order to give you the following code fragment, which deletes all rows with a given RecID value. You could insert this after the "Set db = CurrentDb()" statement (line 5) in the procedure above.

    db.Execute "DELETE * FROM MyTable WHERE RecID = '" & Me!txtRecID & "';", dbFailOnError

But now I should point out that your users would be doing this update "blind". Unless you put in some tricky code here, your list box isn't going to show them which items they previously selected. They'll just have a list box with a bunch of items in it, and they'll have to consider each item again. That may be perfectly valid for your application, but it's a little unusual, so I thought I'd mention it. Rick Sprague

 

[Zorro1265]

My database is a report generator for medical testing. What happens is at the beginning of the test they may have on opinion and a few minutes later before they close the database they may have changed their mind and want to pick moderate instead of mild for something. Sinc they havent left the form or the current id will it still show in the list box? I will try this out in the morning and see if I can make it work. Thanks!!

 

[Zorro1265]

Rick
On the delete code I get a data type mismatch error.

db.Execute "DELETE * FROM MyTable WHERE RecID = '" & Me!txtRecID & "';", dbFailOnError

If I want to compare to one other criteria on the delete such as ListAvailable.Column(1, varItem)in the add code how would I do this? The data in colums 1 and 2 from the add code are references to two id numbers in a table, a category and phrase (from within that category). I thought if we can add the recid and the category it would delete just the category I want to change. Did that make any sense? You mention there may be a way to show the user the items they have picked before. How hard would that be? Thanks so much for all this help!

 

[member 37483]

Zorro, did you ever get the answer to when you were trying to make a multiselect that put the selections into different fields? If so please let me know. jo2hn_1999@yahoo.com