Dim varItem As Variant - But not working

[Wayne-I-M]

Hi

I have a small code that runs from an OnClick (TestButton)
TestList is a multi select list box
TestReport is a simple "hello" on an A4 sheet
TestList.Column(0) contains the record ID
TestList.Column(1) contains the names of clients


Private Sub TestButton_Click()
Dim varItem As Variant
For Each varItem In Me.TestList.ItemsSelected
'Copy the report and rename it'
DoCmd.CopyObject , "New Test Report" & " " & Format(Date, "short date") & "
" & Me.TestList.Column(1), acReport, "TestReport"
Next varItem
End Sub

If I select 3 items on the list it "should" rename TestReport to
New Test Report 27 / 06 / 08 John
New Test Report 27 / 06 / 08 Jim
New Test Report 27 / 06 / 08 Sally
etc

But it doesn't (doing something silly here I know)

What it does is count the number of selected items and then copy the report
that number of times (in the above case it would copy it 3 times) each time
with the name
New Test Report 27 / 06 / 08 Sally
But of course it will delete the report each time as you can't have more
than 1 report with the same name

Basically I need to run a code set on a selected item in a list then "after"
the code has run for this set, run the same code for the name item

So for the above I would end up with 3 re-named reports

Any ideas

 

[Clif McIrvin]

Hi

I have a small code that runs from an OnClick (TestButton)
TestList is a multi select list box
TestReport is a simple "hello" on an A4 sheet
TestList.Column(0) contains the record ID
TestList.Column(1) contains the names of clients


Private Sub TestButton_Click()
Dim varItem As Variant
For Each varItem In Me.TestList.ItemsSelected
'Copy the report and rename it'
DoCmd.CopyObject , "New Test Report" & " " & Format(Date, "short
date") & "
" & Me.TestList.Column(1), acReport, "TestReport"

the data you want is in varItem, not me.testlist.column(1)

(Isn't it?)

I've not worked with these (yet) but I'm pretty sure that's what I've
read. I sure recall reading that the .text value of a listbox is not
necessarily what you would first think, and definately will not contain
more than one value.

 

[Beetle]

You might try;

Private Sub TestButton_Click()
Dim varItem As Variant
For Each varItem In Me.TestList.ItemsSelected
'Copy the report and rename it'
DoCmd.CopyObject , "New Test Report" & " " & Format(Date, "short date") & "
" & Me.TestList.Column(1, varItem), acReport, "TestReport"
Next varItem
End Sub

 

[Wayne-I-M]

Perfect - many thanks