List Box Concatenation????

N

Nick

I have a list box that has several "business rules", maybe 20, they are short
phrases such as " you have to do this when...", " you can't do this
when....". I have set the list box up so users can select multiple "rules"
which is what I need. Now my problem is that I need to take the "rules" that
the user selects from the list box and print them on a report I have already
created. A user on this site who was helping me said this could be a
concatenation, if it is not I am sorry. Any help would be greatly
appreciated. Thanks.
 
N

Nick

This article was helpful but I dont know if it is answering what I need,
maybe I am missing something. I want a user to be able to make multiple
selections on a list box on a form and then those selections, and those
selections only, will print on a report that I have created. Thanks for your
help.
 
J

John W. Vinson

I have a list box that has several "business rules", maybe 20, they are short
phrases such as " you have to do this when...", " you can't do this
when....". I have set the list box up so users can select multiple "rules"
which is what I need. Now my problem is that I need to take the "rules" that
the user selects from the list box and print them on a report I have already
created. A user on this site who was helping me said this could be a
concatenation, if it is not I am sorry. Any help would be greatly
appreciated. Thanks.
Click to expand...

A listbox is a display tool... not a data storage medium. I really don't think
that a Report can read (directly) the values selected in a listbox on a form.
You should almost surely have a Table into which the user's selections are
stored, and base the report on a query joining this table.

You could use a continuous Subform with a combo box to allow the user to
select multiple rules, each into its own record, or if you wish I can post
some VBA code which will push multiple selections from a listbox into a table.


John W. Vinson [MVP]
 
N

Nick

John -
Thanks for all the help. I thought that might be a problem with the list box
not being a data storage tool. If you could post the code that put multiple
selections into a talbe that would be very helpful. Thanks again for your
time.
 
J

John W. Vinson

John -
Thanks for all the help. I thought that might be a problem with the list box
not being a data storage tool. If you could post the code that put multiple
selections into a talbe that would be very helpful. Thanks again for your
time.
Click to expand...


You'll need to adapt this to your table and fieldnames of course.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, newly cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub



John W. Vinson [MVP]
 
Top