A more elegant VBA solution ?

[Wayne-I-M]

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)

We send out many e mail reports each Monday morning to major client showing
the status of their group bookings (we operate flights and tours to Europe on
their behalf)

Follow the question – I decided to automate this whole process using a
simple scheduler application.

The code I have works

Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list

***********************
BUT – There MUST be a better way to filter the report than setting the value
of a text box then using that to filter the report.
***********************

I have tried lots of “stuff†to filter the reports from a column in the list
box – no luck. If there isn’t way then it won’t matter as this system works.
It just “looks†wrong and as if it could be improved.

I have tried setting the bound column to 0 and using
TestList.Column(0, varItem) - this doesn’t work
I have tried just using the bound column (TestList)
This desn't work either


Has anyone an idea of how to filter a report from a multi select list ?
without it looking like a school project





This is the code - note the 3rd section ‘Set value of filter box’
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)


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") & "
" & TestList.Column(1, varItem), acReport, "Old Test Report"

‘Set value of filter box’
Me.TestTextBox = Me.TestList.Column(0, varItem)

‘Open the new report’
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date") & " "
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden

‘E Mail the new Report’
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""

‘Close the new report’
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date") &
" " & TestList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)

‘Go to next item on list’
Next varItem

End Sub

 

[Jeff Boyce]

I probably don't fully understand your situation, so take this with a
grain...

If I had a report (template) that I wanted to custom-title for each
"customer", I might build a table that held each customerID and the title to
use.

Then I'd build a query that joined the dataset to the customer-title table
and filter it down to the single customer. The report would run, fill in
the "title" and use the customer's data. Next customer? New Title! At
least it saves copying/renaming/using/deleting.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)

We send out many e mail reports each Monday morning to major client
showing
the status of their group bookings (we operate flights and tours to Europe
on
their behalf)

Follow the question - I decided to automate this whole process using a
simple scheduler application.

The code I have works

Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list

***********************
BUT - There MUST be a better way to filter the report than setting the
value
of a text box then using that to filter the report.
***********************

I have tried lots of "stuff" to filter the reports from a column in the
list
box - no luck. If there isn't way then it won't matter as this system
works.
It just "looks" wrong and as if it could be improved.

I have tried setting the bound column to 0 and using
TestList.Column(0, varItem) - this doesn't work
I have tried just using the bound column (TestList)
This desn't work either


Has anyone an idea of how to filter a report from a multi select list ?
without it looking like a school project





This is the code - note the 3rd section 'Set value of filter box'
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)


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") &
"
" & TestList.Column(1, varItem), acReport, "Old Test Report"

'Set value of filter box'
Me.TestTextBox = Me.TestList.Column(0, varItem)

'Open the new report'
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date") & "
"
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden

'E Mail the new Report'
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking
summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details
up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""

'Close the new report'
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date")
&
" " & TestList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)

'Go to next item on list'
Next varItem

End Sub

 

[Klatuu]

If you are using a Multi Select List box and interating through the
ItemsSelected collection, you can use the Where argument of the OpenReport
method to filter the report based on the column value. Now, as I am sure you
are aware, a query will not understand a column reference in the criteria.
If the report were being printed directly, you could use the Where argument
of the OpenReport method, but that will not work when you are using the
SendObject to Email it. Then you can use a hidden text box on your form as
the query criteria and populate the text box in the loop before you send the
email:

Dim varItem As Variant

With Me.MyListBox
For Each VarItem In .ItemsSelected
Me.txtClientFilter = .ItemData(varItem)
'Do the send stuff here
Next VarItem
EndWith

If you need to use a column other than the bound column, say the 3rd column,
it would be:
Me.txtClientFilter = .Column(2, varItem)


--
Dave Hargis, Microsoft Access MVP

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)

We send out many e mail reports each Monday morning to major client showing
the status of their group bookings (we operate flights and tours to Europe on
their behalf)

Follow the question – I decided to automate this whole process using a
simple scheduler application.

The code I have works

Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list

***********************
BUT – There MUST be a better way to filter the report than setting the value
of a text box then using that to filter the report.
***********************

I have tried lots of “stuff†to filter the reports from a column in the list
box – no luck. If there isn’t way then it won’t matter as this system works.
It just “looks†wrong and as if it could be improved.

I have tried setting the bound column to 0 and using
TestList.Column(0, varItem) - this doesn’t work
I have tried just using the bound column (TestList)
This desn't work either


Has anyone an idea of how to filter a report from a multi select list ?
without it looking like a school project





This is the code - note the 3rd section ‘Set value of filter box’
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)


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") & "
" & TestList.Column(1, varItem), acReport, "Old Test Report"

‘Set value of filter box’
Me.TestTextBox = Me.TestList.Column(0, varItem)

‘Open the new report’
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date") & " "
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden

‘E Mail the new Report’
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""

‘Close the new report’
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date") &
" " & TestList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)

‘Go to next item on list’
Next varItem

End Sub

 

[Wayne-I-M]

Hi Jeff

Thanks for the ideas - the problem we (I) have is the number of reports can
go from only around 50 to up to 2500 (per week).

For this reason I have just one report - I filter this and send it out.
It's just simpler to have it that way - I have used a table with report
titles in the past but (maybe me just being lazy) it's just seems simpler to
have just a single template and filter it due to the varying number of group
clients

I know it's not normally admited but we even have a few clients from america
(but don't tell anyone else)

--
Wayne
Manchester, England.

I probably don't fully understand your situation, so take this with a
grain...

If I had a report (template) that I wanted to custom-title for each
"customer", I might build a table that held each customerID and the title to
use.

Then I'd build a query that joined the dataset to the customer-title table
and filter it down to the single customer. The report would run, fill in
the "title" and use the customer's data. Next customer? New Title! At
least it saves copying/renaming/using/deleting.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)

We send out many e mail reports each Monday morning to major client
showing
the status of their group bookings (we operate flights and tours to Europe
on
their behalf)

Follow the question - I decided to automate this whole process using a
simple scheduler application.

The code I have works

Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list

***********************
BUT - There MUST be a better way to filter the report than setting the
value
of a text box then using that to filter the report.
***********************

I have tried lots of "stuff" to filter the reports from a column in the
list
box - no luck. If there isn't way then it won't matter as this system
works.
It just "looks" wrong and as if it could be improved.

I have tried setting the bound column to 0 and using
TestList.Column(0, varItem) - this doesn't work
I have tried just using the bound column (TestList)
This desn't work either


Has anyone an idea of how to filter a report from a multi select list ?
without it looking like a school project





This is the code - note the 3rd section 'Set value of filter box'
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)


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") &
"
" & TestList.Column(1, varItem), acReport, "Old Test Report"

'Set value of filter box'
Me.TestTextBox = Me.TestList.Column(0, varItem)

'Open the new report'
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date") & "
"
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden

'E Mail the new Report'
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking
summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details
up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""

'Close the new report'
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date")
&
" " & TestList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)

'Go to next item on list'
Next varItem

End Sub

 

[Wayne-I-M]

As you can see from the code i have used the coumns to provide variables BUT
- and I really don't understand this.

Setting the SomeControl.column (0, varItem)
"[RecordID]=[Forms]![MyTestForm]![TestTestBox].Column(0, varItem)"
or
"[RecordID]=Me.TestTestBox.Column(0, varItem)"
Does not work

But this does
Me.TestTextBox = Me.TestList.Column(0, varItem)

I have not a clue why this would be so. All the column iterations work -
except for 0 ??

I have just looked up "iterations" Ha Ha it's my English word for today

Do you have any idea why Me.TestList.Column(0, varItem) will work to set the
value of another control but not as the filter ??

--
Wayne
Manchester, England.

If you are using a Multi Select List box and interating through the
ItemsSelected collection, you can use the Where argument of the OpenReport
method to filter the report based on the column value. Now, as I am sure you
are aware, a query will not understand a column reference in the criteria.
If the report were being printed directly, you could use the Where argument
of the OpenReport method, but that will not work when you are using the
SendObject to Email it. Then you can use a hidden text box on your form as
the query criteria and populate the text box in the loop before you send the
email:

Dim varItem As Variant

With Me.MyListBox
For Each VarItem In .ItemsSelected
Me.txtClientFilter = .ItemData(varItem)
'Do the send stuff here
Next VarItem
EndWith

If you need to use a column other than the bound column, say the 3rd column,
it would be:
Me.txtClientFilter = .Column(2, varItem)


--
Dave Hargis, Microsoft Access MVP

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)

We send out many e mail reports each Monday morning to major client showing
the status of their group bookings (we operate flights and tours to Europe on
their behalf)

Follow the question – I decided to automate this whole process using a
simple scheduler application.

The code I have works

Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list

***********************
BUT – There MUST be a better way to filter the report than setting the value
of a text box then using that to filter the report.
***********************

I have tried lots of “stuff†to filter the reports from a column in the list
box – no luck. If there isn’t way then it won’t matter as this system works.
It just “looks†wrong and as if it could be improved.

I have tried setting the bound column to 0 and using
TestList.Column(0, varItem) - this doesn’t work
I have tried just using the bound column (TestList)
This desn't work either


Has anyone an idea of how to filter a report from a multi select list ?
without it looking like a school project





This is the code - note the 3rd section ‘Set value of filter box’
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)


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") & "
" & TestList.Column(1, varItem), acReport, "Old Test Report"

‘Set value of filter box’
Me.TestTextBox = Me.TestList.Column(0, varItem)

‘Open the new report’
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date") & " "
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden

‘E Mail the new Report’
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""

‘Close the new report’
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date") &
" " & TestList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)

‘Go to next item on list’
Next varItem

End Sub

 

[Klatuu]

Setting the SomeControl.column (0, varItem)

"[RecordID]=[Forms]![MyTestForm]![TestTestBox].Column(0, varItem)"
or
"[RecordID]=Me.TestTestBox.Column(0, varItem)"
Does not work

No, because text boxes do not have columns.

Me.TestTextBox = Me.TestList.Column(0, varItem)

This is correct. You are assigning the value of the first column in the
list box of the current row to a text box. That is what I posted as a
solution.

Do you have any idea why Me.TestList.Column(0, varItem) will work to set the
value of another control but not as the filter ??

It is because Columns is a collection, not a specific property. It is also
why even the bound column of a multi select list box will not return a value.
It has no value of its own. Its values are all contained in the Columns
collection. But, a combo box or a single select list box will return a value
based on the Bound Column of the current row.
--
Dave Hargis, Microsoft Access MVP

As you can see from the code i have used the coumns to provide variables BUT
- and I really don't understand this.

Setting the SomeControl.column (0, varItem)
"[RecordID]=[Forms]![MyTestForm]![TestTestBox].Column(0, varItem)"
or
"[RecordID]=Me.TestTestBox.Column(0, varItem)"
Does not work

But this does
Me.TestTextBox = Me.TestList.Column(0, varItem)

I have not a clue why this would be so. All the column iterations work -
except for 0 ??

I have just looked up "iterations" Ha Ha it's my English word for today

Do you have any idea why Me.TestList.Column(0, varItem) will work to set the
value of another control but not as the filter ??

--
Wayne
Manchester, England.

If you are using a Multi Select List box and interating through the
ItemsSelected collection, you can use the Where argument of the OpenReport
method to filter the report based on the column value. Now, as I am sure you
are aware, a query will not understand a column reference in the criteria.
If the report were being printed directly, you could use the Where argument
of the OpenReport method, but that will not work when you are using the
SendObject to Email it. Then you can use a hidden text box on your form as
the query criteria and populate the text box in the loop before you send the
email:

Dim varItem As Variant

With Me.MyListBox
For Each VarItem In .ItemsSelected
Me.txtClientFilter = .ItemData(varItem)
'Do the send stuff here
Next VarItem
EndWith

If you need to use a column other than the bound column, say the 3rd column,
it would be:
Me.txtClientFilter = .Column(2, varItem)


--
Dave Hargis, Microsoft Access MVP

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)

We send out many e mail reports each Monday morning to major client showing
the status of their group bookings (we operate flights and tours to Europe on
their behalf)

Follow the question – I decided to automate this whole process using a
simple scheduler application.

The code I have works

Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list

***********************
BUT – There MUST be a better way to filter the report than setting the value
of a text box then using that to filter the report.
***********************

I have tried lots of “stuff†to filter the reports from a column in the list
box – no luck. If there isn’t way then it won’t matter as this system works.
It just “looks†wrong and as if it could be improved.

I have tried setting the bound column to 0 and using
TestList.Column(0, varItem) - this doesn’t work
I have tried just using the bound column (TestList)
This desn't work either


Has anyone an idea of how to filter a report from a multi select list ?
without it looking like a school project





This is the code - note the 3rd section ‘Set value of filter box’
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)


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") & "
" & TestList.Column(1, varItem), acReport, "Old Test Report"

‘Set value of filter box’
Me.TestTextBox = Me.TestList.Column(0, varItem)

‘Open the new report’
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date") & " "
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden

‘E Mail the new Report’
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""

‘Close the new report’
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date") &
" " & TestList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)

‘Go to next item on list’
Next varItem

End Sub

 

[Wayne-I-M]

Excellent answer - I truleys have learned something new about access today -
thank you

Looks like I will stay with my hidden text box provideing the filter criteria.

Many thank

--
Wayne
Manchester, England.

Setting the SomeControl.column (0, varItem)
"[RecordID]=[Forms]![MyTestForm]![TestTestBox].Column(0, varItem)"
or
"[RecordID]=Me.TestTestBox.Column(0, varItem)"
Does not work

No, because text boxes do not have columns.

Me.TestTextBox = Me.TestList.Column(0, varItem)

This is correct. You are assigning the value of the first column in the
list box of the current row to a text box. That is what I posted as a
solution.

Do you have any idea why Me.TestList.Column(0, varItem) will work to set the
value of another control but not as the filter ??

It is because Columns is a collection, not a specific property. It is also
why even the bound column of a multi select list box will not return a value.
It has no value of its own. Its values are all contained in the Columns
collection. But, a combo box or a single select list box will return a value
based on the Bound Column of the current row.
--
Dave Hargis, Microsoft Access MVP

As you can see from the code i have used the coumns to provide variables BUT
- and I really don't understand this.

Setting the SomeControl.column (0, varItem)
"[RecordID]=[Forms]![MyTestForm]![TestTestBox].Column(0, varItem)"
or
"[RecordID]=Me.TestTestBox.Column(0, varItem)"
Does not work

But this does
Me.TestTextBox = Me.TestList.Column(0, varItem)

I have not a clue why this would be so. All the column iterations work -
except for 0 ??

I have just looked up "iterations" Ha Ha it's my English word for today

Do you have any idea why Me.TestList.Column(0, varItem) will work to set the
value of another control but not as the filter ??

--
Wayne
Manchester, England.

If you are using a Multi Select List box and interating through the
ItemsSelected collection, you can use the Where argument of the OpenReport
method to filter the report based on the column value. Now, as I am sure you
are aware, a query will not understand a column reference in the criteria.
If the report were being printed directly, you could use the Where argument
of the OpenReport method, but that will not work when you are using the
SendObject to Email it. Then you can use a hidden text box on your form as
the query criteria and populate the text box in the loop before you send the
email:

Dim varItem As Variant

With Me.MyListBox
For Each VarItem In .ItemsSelected
Me.txtClientFilter = .ItemData(varItem)
'Do the send stuff here
Next VarItem
EndWith

If you need to use a column other than the bound column, say the 3rd column,
it would be:
Me.txtClientFilter = .Column(2, varItem)


--
Dave Hargis, Microsoft Access MVP


:

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)

We send out many e mail reports each Monday morning to major client showing
the status of their group bookings (we operate flights and tours to Europe on
their behalf)

Follow the question – I decided to automate this whole process using a
simple scheduler application.

The code I have works

Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list

***********************
BUT – There MUST be a better way to filter the report than setting the value
of a text box then using that to filter the report.
***********************

I have tried lots of “stuff†to filter the reports from a column in the list
box – no luck. If there isn’t way then it won’t matter as this system works.
It just “looks†wrong and as if it could be improved.

I have tried setting the bound column to 0 and using
TestList.Column(0, varItem) - this doesn’t work
I have tried just using the bound column (TestList)
This desn't work either


Has anyone an idea of how to filter a report from a multi select list ?
without it looking like a school project





This is the code - note the 3rd section ‘Set value of filter box’
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)


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") & "
" & TestList.Column(1, varItem), acReport, "Old Test Report"

‘Set value of filter box’
Me.TestTextBox = Me.TestList.Column(0, varItem)

‘Open the new report’
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date") & " "
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden

‘E Mail the new Report’
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""

‘Close the new report’
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date") &
" " & TestList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)

‘Go to next item on list’
Next varItem

End Sub

 

[Klatuu]

Good idea, but you don't have to open the form in design view. You just set
the criteria in the query to reference the hidden text box.
--
Dave Hargis, Microsoft Access MVP

Excellent answer - I truleys have learned something new about access today -
thank you

Looks like I will stay with my hidden text box provideing the filter criteria.

Many thank

--
Wayne
Manchester, England.

Setting the SomeControl.column (0, varItem)
"[RecordID]=[Forms]![MyTestForm]![TestTestBox].Column(0, varItem)"
or
"[RecordID]=Me.TestTestBox.Column(0, varItem)"
Does not work

No, because text boxes do not have columns.

Me.TestTextBox = Me.TestList.Column(0, varItem)

This is correct. You are assigning the value of the first column in the
list box of the current row to a text box. That is what I posted as a
solution.

Do you have any idea why Me.TestList.Column(0, varItem) will work to set the
value of another control but not as the filter ??

It is because Columns is a collection, not a specific property. It is also
why even the bound column of a multi select list box will not return a value.
It has no value of its own. Its values are all contained in the Columns
collection. But, a combo box or a single select list box will return a value
based on the Bound Column of the current row.
--
Dave Hargis, Microsoft Access MVP

As you can see from the code i have used the coumns to provide variables BUT
- and I really don't understand this.

Setting the SomeControl.column (0, varItem)
"[RecordID]=[Forms]![MyTestForm]![TestTestBox].Column(0, varItem)"
or
"[RecordID]=Me.TestTestBox.Column(0, varItem)"
Does not work

But this does
Me.TestTextBox = Me.TestList.Column(0, varItem)

I have not a clue why this would be so. All the column iterations work -
except for 0 ??

I have just looked up "iterations" Ha Ha it's my English word for today

Do you have any idea why Me.TestList.Column(0, varItem) will work to set the
value of another control but not as the filter ??

--
Wayne
Manchester, England.



:

If you are using a Multi Select List box and interating through the
ItemsSelected collection, you can use the Where argument of the OpenReport
method to filter the report based on the column value. Now, as I am sure you
are aware, a query will not understand a column reference in the criteria.
If the report were being printed directly, you could use the Where argument
of the OpenReport method, but that will not work when you are using the
SendObject to Email it. Then you can use a hidden text box on your form as
the query criteria and populate the text box in the loop before you send the
email:

Dim varItem As Variant

With Me.MyListBox
For Each VarItem In .ItemsSelected
Me.txtClientFilter = .ItemData(varItem)
'Do the send stuff here
Next VarItem
EndWith

If you need to use a column other than the bound column, say the 3rd column,
it would be:
Me.txtClientFilter = .Column(2, varItem)


--
Dave Hargis, Microsoft Access MVP


:

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)

We send out many e mail reports each Monday morning to major client showing
the status of their group bookings (we operate flights and tours to Europe on
their behalf)

Follow the question – I decided to automate this whole process using a
simple scheduler application.

The code I have works

Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list

***********************
BUT – There MUST be a better way to filter the report than setting the value
of a text box then using that to filter the report.
***********************

I have tried lots of “stuff†to filter the reports from a column in the list
box – no luck. If there isn’t way then it won’t matter as this system works.
It just “looks†wrong and as if it could be improved.

I have tried setting the bound column to 0 and using
TestList.Column(0, varItem) - this doesn’t work
I have tried just using the bound column (TestList)
This desn't work either


Has anyone an idea of how to filter a report from a multi select list ?
without it looking like a school project





This is the code - note the 3rd section ‘Set value of filter box’
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)


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") & "
" & TestList.Column(1, varItem), acReport, "Old Test Report"

‘Set value of filter box’
Me.TestTextBox = Me.TestList.Column(0, varItem)

‘Open the new report’
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date") & " "
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden

‘E Mail the new Report’
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""

‘Close the new report’
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date") &
" " & TestList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)

‘Go to next item on list’
Next varItem

End Sub

 

[Wayne-I-M]

What is the reason for using the query to act as the filter. Is this not
doing the "job" twice. The unbound box contents can be sent to the report
without using the query so would this not add another section into the
process that is ot really needed.

1
OnClick
Set value of text box
Run report based on text box as filter

2
OnClick
Set value of text box
Set value of criteria in query
Run report based on query criteria

I know it only one extra "bit" and the report is being opened anyway - but ...

Is there a reason for this extra section baring in mind that sometime we are
running up to 2,500 reports to different clients so this extra section will
add to the application



--
Wayne
Manchester, England.

Good idea, but you don't have to open the form in design view. You just set
the criteria in the query to reference the hidden text box.
--
Dave Hargis, Microsoft Access MVP

Excellent answer - I truleys have learned something new about access today -
thank you

Looks like I will stay with my hidden text box provideing the filter criteria.

Many thank

--
Wayne
Manchester, England.

Setting the SomeControl.column (0, varItem)
"[RecordID]=[Forms]![MyTestForm]![TestTestBox].Column(0, varItem)"
or
"[RecordID]=Me.TestTestBox.Column(0, varItem)"
Does not work

No, because text boxes do not have columns.

Me.TestTextBox = Me.TestList.Column(0, varItem)

This is correct. You are assigning the value of the first column in the
list box of the current row to a text box. That is what I posted as a
solution.

Do you have any idea why Me.TestList.Column(0, varItem) will work to set the
value of another control but not as the filter ??

It is because Columns is a collection, not a specific property. It is also
why even the bound column of a multi select list box will not return a value.
It has no value of its own. Its values are all contained in the Columns
collection. But, a combo box or a single select list box will return a value
based on the Bound Column of the current row.
--
Dave Hargis, Microsoft Access MVP


:

As you can see from the code i have used the coumns to provide variables BUT
- and I really don't understand this.

Setting the SomeControl.column (0, varItem)
"[RecordID]=[Forms]![MyTestForm]![TestTestBox].Column(0, varItem)"
or
"[RecordID]=Me.TestTestBox.Column(0, varItem)"
Does not work

But this does
Me.TestTextBox = Me.TestList.Column(0, varItem)

I have not a clue why this would be so. All the column iterations work -
except for 0 ??

I have just looked up "iterations" Ha Ha it's my English word for today

Do you have any idea why Me.TestList.Column(0, varItem) will work to set the
value of another control but not as the filter ??

--
Wayne
Manchester, England.



:

If you are using a Multi Select List box and interating through the
ItemsSelected collection, you can use the Where argument of the OpenReport
method to filter the report based on the column value. Now, as I am sure you
are aware, a query will not understand a column reference in the criteria.
If the report were being printed directly, you could use the Where argument
of the OpenReport method, but that will not work when you are using the
SendObject to Email it. Then you can use a hidden text box on your form as
the query criteria and populate the text box in the loop before you send the
email:

Dim varItem As Variant

With Me.MyListBox
For Each VarItem In .ItemsSelected
Me.txtClientFilter = .ItemData(varItem)
'Do the send stuff here
Next VarItem
EndWith

If you need to use a column other than the bound column, say the 3rd column,
it would be:
Me.txtClientFilter = .Column(2, varItem)


--
Dave Hargis, Microsoft Access MVP


:

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)

We send out many e mail reports each Monday morning to major client showing
the status of their group bookings (we operate flights and tours to Europe on
their behalf)

Follow the question – I decided to automate this whole process using a
simple scheduler application.

The code I have works

Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list

***********************
BUT – There MUST be a better way to filter the report than setting the value
of a text box then using that to filter the report.
***********************

I have tried lots of “stuff†to filter the reports from a column in the list
box – no luck. If there isn’t way then it won’t matter as this system works.
It just “looks†wrong and as if it could be improved.

I have tried setting the bound column to 0 and using
TestList.Column(0, varItem) - this doesn’t work
I have tried just using the bound column (TestList)
This desn't work either


Has anyone an idea of how to filter a report from a multi select list ?
without it looking like a school project





This is the code - note the 3rd section ‘Set value of filter box’
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)


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") & "
" & TestList.Column(1, varItem), acReport, "Old Test Report"

‘Set value of filter box’
Me.TestTextBox = Me.TestList.Column(0, varItem)

‘Open the new report’
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date") & " "
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden

‘E Mail the new Report’
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""

‘Close the new report’
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date") &
" " & TestList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)

‘Go to next item on list’
Next varItem

End Sub

 

[a a r o n . k e m p f]

anything with that sort of volume should be moved to Reporting
Services.

Seriously.

-Aaron

Hi Jeff

Thanks for the ideas - the problem we (I) have is the number of reports can
go from only around 50 to up to 2500 (per week).  


For this reason I have just one report - I filter this and send it out.  
It's just simpler to have it that way - I have used a table with report
titles in the past but (maybe me just being lazy) it's just seems simplerto
have just a single template and filter it due to the varying number of group
clients

I know it's not normally admited but we even have a few clients from america
(but don't tell anyone else)

--
Wayne
Manchester, England.

I probably don't fully understand your situation, so take this with a
grain...

If I had a report (template) that I wanted to custom-title for each
"customer", I might build a table that held each customerID and the title to
use.

Then I'd build a query that joined the dataset to the customer-title table
and filter it down to the single customer.  The report would run, fill in
the "title" and use the customer's data.  Next customer?  New Title!  At
least it saves copying/renaming/using/deleting.

JOPO  (just one person's opinion)

Jeff Boyce
Microsoft Office/Access MVP

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)
We send out many e mail reports each Monday morning to major client
showing
the status of their group bookings (we operate flights and tours to Europe
on
their behalf)
Follow the question - I decided to automate this whole process using a
simple scheduler application.
The code I have works
Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list
***********************
BUT - There MUST be a better way to filter the report than setting the
value
of a text box then using that to filter the report.
***********************
I have tried lots of "stuff" to filter the reports from a column in the
list
box - no luck.  If there isn't way then it won't matter as this system
works.
It just "looks" wrong and as if it could be improved.
I have tried setting the bound column to 0 and using
TestList.Column(0, varItem)  -  this doesn't work
I have tried just using the bound column (TestList)
This desn't work either
Has anyone an idea of how to filter a report from a multi select list?
without it looking like a school project
This is the code - note the 3rd section 'Set value of filter box'
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)
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") &
"
" & TestList.Column(1, varItem), acReport, "Old Test Report"
'Set value of filter box'
Me.TestTextBox = Me.TestList.Column(0, varItem)
'Open the new report'
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date")& "
"
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden
'E Mail the new Report'
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking
summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details
up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""
'Close the new report'
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date")
&
" " & TestList.Column(1, varItem)
'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)
'Go to next item on list'
Next varItem
End Sub

 

[Wayne-I-M]

Yeah Arron you are proberbly right.

But as each report take less than a second to filter and another 3 seconds
to e mail its not a problem. I run these reports over night so they are done
and ready to send when the admin staff arrrive and switch on the outlook - I
did try ClickYes but didn't like the general idea so I deleted it.

Thanks for the advice though - I will keep it mind if the report volumn gets
much bigger


--
Wayne
Manchester, England.

anything with that sort of volume should be moved to Reporting
Services.

Seriously.

-Aaron

Hi Jeff

Thanks for the ideas - the problem we (I) have is the number of reports can
go from only around 50 to up to 2500 (per week).


For this reason I have just one report - I filter this and send it out.
It's just simpler to have it that way - I have used a table with report
titles in the past but (maybe me just being lazy) it's just seems simpler to
have just a single template and filter it due to the varying number of group
clients

I know it's not normally admited but we even have a few clients from america
(but don't tell anyone else)

--
Wayne
Manchester, England.

I probably don't fully understand your situation, so take this with a
grain...

If I had a report (template) that I wanted to custom-title for each
"customer", I might build a table that held each customerID and the title to
use.

Then I'd build a query that joined the dataset to the customer-title table
and filter it down to the single customer. The report would run, fill in
the "title" and use the customer's data. Next customer? New Title! At
least it saves copying/renaming/using/deleting.

JOPO (just one person's opinion)

Jeff Boyce
Microsoft Office/Access MVP

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)

We send out many e mail reports each Monday morning to major client
showing
the status of their group bookings (we operate flights and tours to Europe
on
their behalf)

Follow the question - I decided to automate this whole process using a
simple scheduler application.

The code I have works

Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list

***********************
BUT - There MUST be a better way to filter the report than setting the
value
of a text box then using that to filter the report.
***********************

I have tried lots of "stuff" to filter the reports from a column in the
list
box - no luck. If there isn't way then it won't matter as this system
works.
It just "looks" wrong and as if it could be improved.

I have tried setting the bound column to 0 and using
TestList.Column(0, varItem) - this doesn't work
I have tried just using the bound column (TestList)
This desn't work either

Has anyone an idea of how to filter a report from a multi select list ?
without it looking like a school project

This is the code - note the 3rd section 'Set value of filter box'
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)

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") &
"
" & TestList.Column(1, varItem), acReport, "Old Test Report"

'Set value of filter box'
Me.TestTextBox = Me.TestList.Column(0, varItem)

'Open the new report'
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date") & "
"
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden

'E Mail the new Report'
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking
summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details
up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""

'Close the new report'
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date")
&
" " & TestList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)

'Go to next item on list'
Next varItem

End Sub

 

[So Sorry For Poor Aaron]

Aaron, he head done been depecrated.

 

[Klatuu]

If you were using the OpenReport method, there would be no reason to put any
filter in the report's record source query, it could be done with the Open
Report's Where argument.
In this case you are using SendObject, so the only way to filter the report
is by filtering the report's record source query. The issue at hand is that
you can't use a list box column reference in a query. Also, because it is a
multi select list box, even putting something like
Forms!MyForm!MyListBox will not work because a Multi Select List box will
return Null from this reference.

So, the solution is to programmatically populate a text box with the value
of the list box's current row. Then when the report is run, its record
source query will be filtered based on what is in the text box. There really
is no unneeded section. You don't run the query, you just run the SendObject.
--
Dave Hargis, Microsoft Access MVP

What is the reason for using the query to act as the filter. Is this not
doing the "job" twice. The unbound box contents can be sent to the report
without using the query so would this not add another section into the
process that is ot really needed.

1
OnClick
Set value of text box
Run report based on text box as filter

2
OnClick
Set value of text box
Set value of criteria in query
Run report based on query criteria

I know it only one extra "bit" and the report is being opened anyway - but ...

Is there a reason for this extra section baring in mind that sometime we are
running up to 2,500 reports to different clients so this extra section will
add to the application



--
Wayne
Manchester, England.

Good idea, but you don't have to open the form in design view. You just set
the criteria in the query to reference the hidden text box.
--
Dave Hargis, Microsoft Access MVP

Excellent answer - I truleys have learned something new about access today -
thank you

Looks like I will stay with my hidden text box provideing the filter criteria.

Many thank

--
Wayne
Manchester, England.



:

Setting the SomeControl.column (0, varItem)
"[RecordID]=[Forms]![MyTestForm]![TestTestBox].Column(0, varItem)"
or
"[RecordID]=Me.TestTestBox.Column(0, varItem)"
Does not work

No, because text boxes do not have columns.

Me.TestTextBox = Me.TestList.Column(0, varItem)

This is correct. You are assigning the value of the first column in the
list box of the current row to a text box. That is what I posted as a
solution.

Do you have any idea why Me.TestList.Column(0, varItem) will work to set the
value of another control but not as the filter ??

It is because Columns is a collection, not a specific property. It is also
why even the bound column of a multi select list box will not return a value.
It has no value of its own. Its values are all contained in the Columns
collection. But, a combo box or a single select list box will return a value
based on the Bound Column of the current row.
--
Dave Hargis, Microsoft Access MVP


:

As you can see from the code i have used the coumns to provide variables BUT
- and I really don't understand this.

Setting the SomeControl.column (0, varItem)
"[RecordID]=[Forms]![MyTestForm]![TestTestBox].Column(0, varItem)"
or
"[RecordID]=Me.TestTestBox.Column(0, varItem)"
Does not work

But this does
Me.TestTextBox = Me.TestList.Column(0, varItem)

I have not a clue why this would be so. All the column iterations work -
except for 0 ??

I have just looked up "iterations" Ha Ha it's my English word for today

Do you have any idea why Me.TestList.Column(0, varItem) will work to set the
value of another control but not as the filter ??

--
Wayne
Manchester, England.



:

If you are using a Multi Select List box and interating through the
ItemsSelected collection, you can use the Where argument of the OpenReport
method to filter the report based on the column value. Now, as I am sure you
are aware, a query will not understand a column reference in the criteria.
If the report were being printed directly, you could use the Where argument
of the OpenReport method, but that will not work when you are using the
SendObject to Email it. Then you can use a hidden text box on your form as
the query criteria and populate the text box in the loop before you send the
email:

Dim varItem As Variant

With Me.MyListBox
For Each VarItem In .ItemsSelected
Me.txtClientFilter = .ItemData(varItem)
'Do the send stuff here
Next VarItem
EndWith

If you need to use a column other than the bound column, say the 3rd column,
it would be:
Me.txtClientFilter = .Column(2, varItem)


--
Dave Hargis, Microsoft Access MVP


:

I answered a question from someone a few days ago and it got me to thinking
(always a bad idea)

We send out many e mail reports each Monday morning to major client showing
the status of their group bookings (we operate flights and tours to Europe on
their behalf)

Follow the question – I decided to automate this whole process using a
simple scheduler application.

The code I have works

Copy the template report and rename it to Client Name / Date / etc
Filter the report to show just their bookings
Open the report (hidden dialog)
E mail it to them
Close the hidden dialog
Delete the new report
Do the same for each client on the list

***********************
BUT – There MUST be a better way to filter the report than setting the value
of a text box then using that to filter the report.
***********************

I have tried lots of “stuff†to filter the reports from a column in the list
box – no luck. If there isn’t way then it won’t matter as this system works.
It just “looks†wrong and as if it could be improved.

I have tried setting the bound column to 0 and using
TestList.Column(0, varItem) - this doesn’t work
I have tried just using the bound column (TestList)
This desn't work either


Has anyone an idea of how to filter a report from a multi select list ?
without it looking like a school project





This is the code - note the 3rd section ‘Set value of filter box’
And the 4th section ("[RecordID]=[Forms]![MyTestForm]![TestTestBox]",
acHidden)


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") & "
" & TestList.Column(1, varItem), acReport, "Old Test Report"

‘Set value of filter box’
Me.TestTextBox = Me.TestList.Column(0, varItem)

‘Open the new report’
DoCmd.OpenReport "New Test Report" & " " & Format(Date, "short date") & " "
& TestList.Column(1, varItem), acViewPreview, "",
"[RecordID]=[Forms]![MyTestForm]![TestTestBox]", acHidden

‘E Mail the new Report’
DoCmd.SendObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem), "RichTextFormat(*.rtf)",
TestList.Column(2, varItem), , "", "Booking Summery", "Your booking summery
and detail are attached." & Chr(13) + Chr(10) & "The report gives details up
to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM")", False, ""

‘Close the new report’
DoCmd.Close acReport, "New Test Report" & " " & Format(Date, "short date") &
" " & TestList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "New Test Report" & " " & Format(Date, "short
date") & " " & TestList.Column(1, varItem)

‘Go to next item on list’
Next varItem

End Sub