multiple listbox value printing on report

[DIRENE]

I have a form with a listbox(simple) and a command button on it. That on the click button I have the code:

Dim strWhere
strWhere = ""
Dim intI As Integer
With Me!lstSupChoice
For intI = 0 To .ListCount - 1
If .Selected(intI) Then
If strWhere = "" Then
strWhere = "OPTIONS = '" & .ItemData(intI) & "'"
Else
strWhere = strWhere & " or OPTIONS = '" & .ItemData(intI) & "'"
End If
End If
Next intI
End With
DoCmd.OpenReport "SupReport", acViewPreview, , strWhere

Which pulls more the one option of to the report. Is there a way to get them to stay together like:

xlt trim
spare tire
blue

Right now their space out like

xlt trim


spare tire



blue


Thanks

irene

 

[SkipVought]

Hi,

Put the values in a lable.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DIRENE]

Can you tell me how to do that

Thanks
Irene

 

[SkipVought]

          Label1.Caption = ""
          For x = 0 to ListBox1.ListCount - 1
             If ListBox1.Selected(x) = True Then

               Lable1.Caption = Lable1.Caption & ListBox1.List(x) & vbCrLf

            End If

         Next x

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DIRENE]

Skip, I hate to be a pain but where would I but this code. Irene

 

[SkipVought]

I would guess...

Dim strWhere
strWhere = ""
Dim intI As Integer
With Me!lstSupChoice

    Me!Label1.Caption = ""

    For intI = 0 To .ListCount - 1
        If .Selected(intI) Then

            Me!Lable1.Caption = Me!Lable1.Caption & .List(intI) & vbCrLf
  

            If strWhere = "" Then
                strWhere = "OPTIONS = '" & .ItemData(intI) & "'"
                    Else
                strWhere = strWhere & " or OPTIONS = '" & .ItemData(intI) & "'"
            End If
        End If
    Next intI
End With
DoCmd.OpenReport "SupReport", acViewPreview, , strWhere

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DIRENE]

It stops me at Me!Label1.Caption = ""

 

[SkipVought]

Do you have a Label on your form named Label1?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DIRENE]

Yes and one on my report

it's now stoping at:

Me!Label1.Caption = Me!Label1.Caption & .List(intI) & vbCrLf

with a run-time out error 438
object does not support this property or method

 

[SkipVought]

When your code stops, hit the Debug button and using the Watch Window, what values are in

intI

and

Me!lstSupChoice.List(intI)

???

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DIRENE]

Ok Skip I have a

Me!lstSupChoice that in the window say Me!lstSupChoice=null

and intI window say intI=3

but I don't have a

Me!lstSupChoice.List(intI) any way in the code.

Irene

 

[SkipVought]

Irene,

Don't you have

With Me!lstSupChoice
...
  Me!Label1.Caption = Me!Label1.Caption & .List(intI) & vbCrLf

End with

THEN, you have an expression

Me!lstSupChoice.List(intI)

BTW, Me!lstSupChoice is an Listbox Object that should have a little + box next to it in the Watch Window when you view it in Debug.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DIRENE]

Skip this is my code on command button:


Private Sub btnViewSupReport_Click()
Dim strWhere
strWhere = ""
Dim intI As Integer
With Me!lstSupChoice

Me!Label1.Caption = ""

For intI = 0 To .ListCount - 1
If .Selected(intI) Then

Me!Label1.Caption = Me!Label1.Caption & .List(intI) & vbCrLf


If strWhere = "" Then
strWhere = "OPTIONS = '" & .ItemData(intI) & "'"
Else
strWhere = strWhere & " or OPTIONS = '" & .ItemData(intI) & "'"
End If
End If
Next intI
End With
DoCmd.OpenReport "SupReport", acViewPreview, , strWhere



End Sub

At I don't see that + in the watch window but on the form my listbox name is lstSupChoice


Irene

 

[SkipVought]

You have

Me!lstSupChoice.List(intI)

!!!

What is the Watch Window value???

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DIRENE]

I'm sorry Skip I just don't see it and when your say watch window Value. I'm thing that is when you are hovering over the line and if I hover over list.(intI) I get intI=1

 

[SkipVought]

intI, the list index, has a value of ONE

What value does the LISTBOX have???

Me!lstSupChoice.List(intI)

COPY this string, ADD in the Watch window and PASTE in the Watch Window Textbox, PLEASE!

Then tell me what the value is.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DIRENE]

Skip, Here is the watch window I really appreciate your help. I'll try what you tell me tomorrow because it's time for me to go home. Have a nice night. Irene

Watch : : Me!lstSupChoice.List(intI) : <Object doesn't support this property or method> : Variant/Integer : Form_frmO.btnViewSupReport_Click

 

[SkipVought]

I have done this with your code using a lable, listbox and button on a Userform

Works just fine!

The message in the Watch Window tells me that List is not a valid property for your listbox.

When you Add Me!lstSupChoice it should tell you that it is an Object of some kind. If not, then the problem is with Me!lstSupChoice.

Does Me!lstSupChoice do everything else in your button clock code?


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Problem is...

I am NOT real familiar with Access Form Objects. They behave differently than other VBA control objects.

BUT...

I did find a solution

Me!Label1.Caption = Me!Label1.Caption & .ItemData(intI) & vbCrLf

The key is the ItemData property of the Listbox.

Hope this makes it work for you.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DIRENE]

Skip, I'm sorry to say that this just copy everything from my listbox to my label on the form and I just what the ones I picked to go has one group to my report.

And in the Watch window I know get

Watch : : Me!lstSupChoice : <Out of context> : Empty : Form_frmO.btnViewSupReport_Click


But I do thank you for all your help.

I'll keep fooling around with it.

Irene