Saving Multiple Values in one field... 1

[smoker1]

Hello! I am using the MultiSelect Property in Access 95 to allow users to select multiple items in a bound list box.

I know that coding is necessary so that it combines the items into one field, but am unsure how to do it. I have reviewed the MS site, which gave an example that didn't work.

Can anyone assist? Thanks in advance!

--Angela

 

[Nannapaneni]

Dim i As Integer, tmpStr As String

For i = 0 To List1.ListCount - 1
If List1.Selected(i) = True Then
tmpStr = tmpStr & List1.List(i) & ","
End If
Next i

tmpStr = Mid(tmpStr, 1, Len(tmpStr) - 1)

 

[weinem01]

I've written some code that does exactly what you want. Just writing the values into a table is pretty simple. You can just use the listbox.ListCount property, which gives you the number of items selected. You can then use a 0 based count to identify which item was selected by using the listbox.itemdata(counter) to write the data into your table. The tricky part comes when the user deselects an item that was previously selected. There's also the matter of populating the listbox with the existing records when the form loads. The way that I addressed this problem was to create an array that contained the old values that were selected and then compared the newly selected values to determine if a selected item was a new addition to the table, or if an item that was previously selected was deselected. I copied this code out of a more reusable module and deleted the extra parts, so I won't guarentee that this code will run perfectly, but you can get the idea of the process for doing this using dao recordsets. It's from a database I designed to track patients and their symptoms for a clinical research project.

Hope this helps.

Matt Weiner

Private Sub Select_Old(boxname As String)

Dim TB As DAO.Recordset
Dim SQLstmt As String
Dim Item, count As Integer
Set currdb = CurrentDb
SQLstmt = "SELECT symptomName FROM tblsymptoms Where patientID = " & currpatient & ""
Erase strOldSymptom
count = symptombox.ListCount - 1

Set TB = currdb.OpenRecordset(SQLstmt)

Do While Not TB.EOF 'for each symptom that the patient has in the database
'Scroll through each member of symptom box, and if it was previously selected and is registered in the
'database, make it selected
For Item = 0 To count
If symptombox.ItemData(Item) = TB!SymptomName Then
symptombox.Selected(Item) = True
strOldSymptom(Item) = TB!SymptomName
End If
Next Item
TB.MoveNext
Loop
TB.Close
Set TB = Nothing

End Sub

Private Sub Update_Box
Dim i As Integer
Dim SQLstmt, strsearch As String
Dim TB As DAO.Recordset
Dim condition1, condition2 As Boolean

SQLstmt = "SELECT * from tblsymptoms where PatientID = " & currpatient & ""
counter = symptombox.ListCount - 1

Set TB = currdb.OpenRecordset(SQLstmt)

For i = 0 To counter

condition1 = symptombox.Selected(i) = True And strOldSymptom(i) = ""
condition2 = symptombox.Selected(i) = False And strOldSymptom(i) = symptombox.ItemData(i)

If condition1 Then 'The item was not selected, but now is
TB.AddNew
TB!PatientID = currpatient
TB!SymptomName = symptombox.ItemData(i)
TB.Update

ElseIf condition2 Then 'The user deselects an item that was previously selected
strsearch = "SymptomName = """ & strOldSymptom(i) & """"
TB.FindFirst (strsearch)
TB.Delete
End If
Next i
TB.Close
Set TB = Nothing
End Sub

 

[smoker1]

Thanks both for your assistance! I will try these suggestions.

--Angela

 

[weinem01]

The best way to do this would be to write a class module with the code that I included in my previous post and then dim a new object for each combobox. If you have any experience with class modules and object oriented programming, I would encourage you to do this - If I had to rewrite my code again, I would use objects. But, if you're just getting started programming and want a simple way, the above code should work.

Matt Weiner