Selecting and Sending Reports Via E-Mail

[Guest]

Using WinXP, Access2K2

Copyright Notice:

' This database and all the code therein is © 1999-2002 Arvin Meyer
(e-mail address removed)
' You are free to use this code and this database in an application
' as long as you do not publish it without the author's permission.
' Additionally, you are required to include this copyright notice in the
application.

The following code returns a list of developed reports to be sent via
E-mail. Some of the reports use different filter by form methods to bring up
the desired report. I would prefer not to have all reports be seen by the
user because the filter forms must be opened first to not have errors
presented.

Let's presume out of 12 reports, only two are developed without the use of
filtering forms. Is there a way to limit the list to the two reports?

I have tried to "Hide" the reports; Tools, Options, View, and uncheck
'Hidden Objects'. That only hides them in the database window, but not from
the code below.

Code Start **********************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OF
Dim db As Database
Dim i As Integer
Dim contr As Container
Dim strRptList As String
Dim strRptName As String
Dim Length As Integer

Set db = CurrentDb()
Set contr = db.Containers("Reports")

strRptList = ""
For i = 0 To contr.Documents.Count - 1
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList & "; "
Length = Len(strRptName)
strRptList = strRptList & strRptName
Next i

Me!lstRpt.RowSource = strRptList

Exit_OF:
Exit Sub
Err_OF:
MsgBox Err & " " & Error, , "Report Open"
Resume Exit_OF

End Sub
Code End ***************************

Thanks for any suggestions.

 

[Arvin Meyer [MVP]]

One of the ways to hide objects in Access is to prefix them with "USys"

So naming a report "USysMyReport" will hide it in the database window unless
hidden and system objects are turned on in Tools >>> Options.

Knowing that, we can make an exception in the code below, and exclude any
report that has the first 4 letters = to usys:


For i = 0 To contr.Documents.Count - 1
If Left(strRptName,4) <> "USys" Then
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList & "; "
Length = Len(strRptName)
strRptList = strRptList & strRptName
End If
Next i

 

[Guest]

I certainly appreciate the suggestion. I tried your recommendation, adding
the lines of code, changing the names of the reports, and verifying that
'Hidden and System Objects were unchecked. Still, when the "E-Mail" code is
run, all files in the reports container appear in the list.

I also tried different variations of code line:

If Left(strRptName,4) <> "USys" Then

etc. . . . . i.e. If Left(strRptName,3) <> "rpt" Then

What happened with that line is 4 of the 5 reports with the 3 character
prefixes of "rpt" disappeared, except one. All other reports without "rpt"
were in the list. Trying the "USys" variation, any report with that appeared
no matter what other settings I had set in Options> View.

 

[Arvin Meyer [MVP]]

Whether you use USys or not. and special prefix that you use that is unique
to the hidden reports will work. If you named them "hdrptReportName", you
could keep from including them in a list by using Left(strRptName, 2) = "hd"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Using this line in the code: <If Left(strRptName, 3) <> "Que" Then>.

After trying different variations of prefixes in the report names, I found
that reports alphabetically before and the first report with "Que" in the
report name were listed. All other reports alphabetically after the last were
missing.

In the database windows for reports I have for example:

Address1
Address2
Dir1
Dir2
QueryByForm1
QueryByForm2
QueryByForm3
rptActive
rptDeceased
rptDonors

After the code runs, the list includes the first 5 reports. The remaining 5
reports are missing. If I change the characters in the line of code to "Add",
then 'Address1' is in the list and the remaining 9 reports are left out.

It would appear that when the code sees the first exception, then the loop
is broken and stops listing any more reports, but includes the alphabetically
first exception.

--
Jim Ory

Whether you use USys or not. and special prefix that you use that is unique
to the hidden reports will work. If you named them "hdrptReportName", you
could keep from including them in a list by using Left(strRptName, 2) = "hd"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I certainly appreciate the suggestion. I tried your recommendation, adding
the lines of code, changing the names of the reports, and verifying that
'Hidden and System Objects were unchecked. Still, when the "E-Mail" code
is
run, all files in the reports container appear in the list.

I also tried different variations of code line:

If Left(strRptName,4) <> "USys" Then

etc. . . . . i.e. If Left(strRptName,3) <> "rpt" Then

What happened with that line is 4 of the 5 reports with the 3 character
prefixes of "rpt" disappeared, except one. All other reports without "rpt"
were in the list. Trying the "USys" variation, any report with that
appeared
no matter what other settings I had set in Options> View.
--
Jim Ory

One of the ways to hide objects in Access is to prefix them with "USys"

So naming a report "USysMyReport" will hide it in the database window
unless
hidden and system objects are turned on in Tools >>> Options.

Knowing that, we can make an exception in the code below, and exclude any
report that has the first 4 letters = to usys:


For i = 0 To contr.Documents.Count - 1
If Left(strRptName,4) <> "USys" Then
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList & "; "
Length = Len(strRptName)
strRptList = strRptList & strRptName
End If
Next i

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
Using WinXP, Access2K2

Copyright Notice:

' This database and all the code therein is © 1999-2002 Arvin Meyer
(e-mail address removed)
' You are free to use this code and this database in an application
' as long as you do not publish it without the author's permission.
' Additionally, you are required to include this copyright notice in
the
application.

The following code returns a list of developed reports to be sent via
E-mail. Some of the reports use different filter by form methods to
bring
up
the desired report. I would prefer not to have all reports be seen by
the
user because the filter forms must be opened first to not have errors
presented.

Let's presume out of 12 reports, only two are developed without the use
of
filtering forms. Is there a way to limit the list to the two reports?

I have tried to "Hide" the reports; Tools, Options, View, and uncheck
'Hidden Objects'. That only hides them in the database window, but not
from
the code below.

Code Start **********************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OF
Dim db As Database
Dim i As Integer
Dim contr As Container
Dim strRptList As String
Dim strRptName As String
Dim Length As Integer

Set db = CurrentDb()
Set contr = db.Containers("Reports")

strRptList = ""
For i = 0 To contr.Documents.Count - 1
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList & "; "
Length = Len(strRptName)
strRptList = strRptList & strRptName
Next i

Me!lstRpt.RowSource = strRptList

Exit_OF:
Exit Sub
Err_OF:
MsgBox Err & " " & Error, , "Report Open"
Resume Exit_OF

End Sub
Code End ***************************

Thanks for any suggestions.

 

[Arvin Meyer [MVP]]

I can see that. Not testing the code, I assumed that was the place to put
the If Then statement. Obviously, you will need to move it further into the
loop. Try this:

If strRptList <> "" And Left(strRptName,3) <> "Que" Then strRptList =
strRptList & "; "
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Using this line in the code: <If Left(strRptName, 3) <> "Que" Then>.

After trying different variations of prefixes in the report names, I found
that reports alphabetically before and the first report with "Que" in the
report name were listed. All other reports alphabetically after the last
were
missing.

In the database windows for reports I have for example:

Address1
Address2
Dir1
Dir2
QueryByForm1
QueryByForm2
QueryByForm3
rptActive
rptDeceased
rptDonors

After the code runs, the list includes the first 5 reports. The remaining
5
reports are missing. If I change the characters in the line of code to
"Add",
then 'Address1' is in the list and the remaining 9 reports are left out.

It would appear that when the code sees the first exception, then the loop
is broken and stops listing any more reports, but includes the
alphabetically
first exception.

--
Jim Ory

Whether you use USys or not. and special prefix that you use that is
unique
to the hidden reports will work. If you named them "hdrptReportName", you
could keep from including them in a list by using Left(strRptName, 2) =
"hd"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I certainly appreciate the suggestion. I tried your recommendation,
adding
the lines of code, changing the names of the reports, and verifying
that
'Hidden and System Objects were unchecked. Still, when the "E-Mail"
code
is
run, all files in the reports container appear in the list.

I also tried different variations of code line:

If Left(strRptName,4) <> "USys" Then

etc. . . . . i.e. If Left(strRptName,3) <> "rpt" Then

What happened with that line is 4 of the 5 reports with the 3 character
prefixes of "rpt" disappeared, except one. All other reports without
"rpt"
were in the list. Trying the "USys" variation, any report with that
appeared
no matter what other settings I had set in Options> View.
--
Jim Ory


:

One of the ways to hide objects in Access is to prefix them with
"USys"

So naming a report "USysMyReport" will hide it in the database window
unless
hidden and system objects are turned on in Tools >>> Options.

Knowing that, we can make an exception in the code below, and exclude
any
report that has the first 4 letters = to usys:


For i = 0 To contr.Documents.Count - 1
If Left(strRptName,4) <> "USys" Then
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList & ";
"
Length = Len(strRptName)
strRptList = strRptList & strRptName
End If
Next i

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
Using WinXP, Access2K2

Copyright Notice:

' This database and all the code therein is © 1999-2002 Arvin Meyer
(e-mail address removed)
' You are free to use this code and this database in an application
' as long as you do not publish it without the author's
permission.
' Additionally, you are required to include this copyright notice in
the
application.

The following code returns a list of developed reports to be sent
via
E-mail. Some of the reports use different filter by form methods to
bring
up
the desired report. I would prefer not to have all reports be seen
by
the
user because the filter forms must be opened first to not have
errors
presented.

Let's presume out of 12 reports, only two are developed without the
use
of
filtering forms. Is there a way to limit the list to the two
reports?

I have tried to "Hide" the reports; Tools, Options, View, and
uncheck
'Hidden Objects'. That only hides them in the database window, but
not
from
the code below.

Code Start **********************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OF
Dim db As Database
Dim i As Integer
Dim contr As Container
Dim strRptList As String
Dim strRptName As String
Dim Length As Integer

Set db = CurrentDb()
Set contr = db.Containers("Reports")

strRptList = ""
For i = 0 To contr.Documents.Count - 1
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList & "; "
Length = Len(strRptName)
strRptList = strRptList & strRptName
Next i

Me!lstRpt.RowSource = strRptList

Exit_OF:
Exit Sub
Err_OF:
MsgBox Err & " " & Error, , "Report Open"
Resume Exit_OF

End Sub
Code End ***************************

Thanks for any suggestions.

 

[Guest]

That certainly produced an unusual effect. Instead of eliminating the reports
from the list, the report names were appended to the alphabetically arranged
report just before the reports to be excluded.

So using the same example reports, the result for the list is:

Address1
Address2
Dir1
Dir2QueryByForm1QueryByForm2QueryByForm3
rptActive
rptDeceased
rptDonors


--
Jim Ory

I can see that. Not testing the code, I assumed that was the place to put
the If Then statement. Obviously, you will need to move it further into the
loop. Try this:

If strRptList <> "" And Left(strRptName,3) <> "Que" Then strRptList =
strRptList & "; "
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Using this line in the code: <If Left(strRptName, 3) <> "Que" Then>.

After trying different variations of prefixes in the report names, I found
that reports alphabetically before and the first report with "Que" in the
report name were listed. All other reports alphabetically after the last
were
missing.

In the database windows for reports I have for example:

Address1
Address2
Dir1
Dir2
QueryByForm1
QueryByForm2
QueryByForm3
rptActive
rptDeceased
rptDonors

After the code runs, the list includes the first 5 reports. The remaining
5
reports are missing. If I change the characters in the line of code to
"Add",
then 'Address1' is in the list and the remaining 9 reports are left out.

It would appear that when the code sees the first exception, then the loop
is broken and stops listing any more reports, but includes the
alphabetically
first exception.

--
Jim Ory

Whether you use USys or not. and special prefix that you use that is
unique
to the hidden reports will work. If you named them "hdrptReportName", you
could keep from including them in a list by using Left(strRptName, 2) =
"hd"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
I certainly appreciate the suggestion. I tried your recommendation,
adding
the lines of code, changing the names of the reports, and verifying
that
'Hidden and System Objects were unchecked. Still, when the "E-Mail"
code
is
run, all files in the reports container appear in the list.

I also tried different variations of code line:

If Left(strRptName,4) <> "USys" Then

etc. . . . . i.e. If Left(strRptName,3) <> "rpt" Then

What happened with that line is 4 of the 5 reports with the 3 character
prefixes of "rpt" disappeared, except one. All other reports without
"rpt"
were in the list. Trying the "USys" variation, any report with that
appeared
no matter what other settings I had set in Options> View.
--
Jim Ory


:

One of the ways to hide objects in Access is to prefix them with
"USys"

So naming a report "USysMyReport" will hide it in the database window
unless
hidden and system objects are turned on in Tools >>> Options.

Knowing that, we can make an exception in the code below, and exclude
any
report that has the first 4 letters = to usys:


For i = 0 To contr.Documents.Count - 1
If Left(strRptName,4) <> "USys" Then
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList & ";
"
Length = Len(strRptName)
strRptList = strRptList & strRptName
End If
Next i

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
Using WinXP, Access2K2

Copyright Notice:

' This database and all the code therein is © 1999-2002 Arvin Meyer
(e-mail address removed)
' You are free to use this code and this database in an application
' as long as you do not publish it without the author's
permission.
' Additionally, you are required to include this copyright notice in
the
application.

The following code returns a list of developed reports to be sent
via
E-mail. Some of the reports use different filter by form methods to
bring
up
the desired report. I would prefer not to have all reports be seen
by
the
user because the filter forms must be opened first to not have
errors
presented.

Let's presume out of 12 reports, only two are developed without the
use
of
filtering forms. Is there a way to limit the list to the two
reports?

I have tried to "Hide" the reports; Tools, Options, View, and
uncheck
'Hidden Objects'. That only hides them in the database window, but
not
from
the code below.

Code Start **********************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OF
Dim db As Database
Dim i As Integer
Dim contr As Container
Dim strRptList As String
Dim strRptName As String
Dim Length As Integer

Set db = CurrentDb()
Set contr = db.Containers("Reports")

strRptList = ""
For i = 0 To contr.Documents.Count - 1
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList & "; "
Length = Len(strRptName)
strRptList = strRptList & strRptName
Next i

Me!lstRpt.RowSource = strRptList

Exit_OF:
Exit Sub
Err_OF:
MsgBox Err & " " & Error, , "Report Open"
Resume Exit_OF

End Sub
Code End ***************************

Thanks for any suggestions.

 

[Arvin Meyer [MVP]]

You will have to experiment with the exact code and placement. Without
something to test it on, there's no way I can debug the problem. Did you try
setting a stop (break point) in the code and stepping through it to see
where it's causing the problem?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

That certainly produced an unusual effect. Instead of eliminating the
reports
from the list, the report names were appended to the alphabetically
arranged
report just before the reports to be excluded.

So using the same example reports, the result for the list is:

Address1
Address2
Dir1
Dir2QueryByForm1QueryByForm2QueryByForm3
rptActive
rptDeceased
rptDonors


--
Jim Ory

I can see that. Not testing the code, I assumed that was the place to put
the If Then statement. Obviously, you will need to move it further into
the
loop. Try this:

If strRptList <> "" And Left(strRptName,3) <> "Que" Then strRptList =
strRptList & "; "
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Using this line in the code: <If Left(strRptName, 3) <> "Que" Then>.

After trying different variations of prefixes in the report names, I
found
that reports alphabetically before and the first report with "Que" in
the
report name were listed. All other reports alphabetically after the
last
were
missing.

In the database windows for reports I have for example:

Address1
Address2
Dir1
Dir2
QueryByForm1
QueryByForm2
QueryByForm3
rptActive
rptDeceased
rptDonors

After the code runs, the list includes the first 5 reports. The
remaining
5
reports are missing. If I change the characters in the line of code to
"Add",
then 'Address1' is in the list and the remaining 9 reports are left
out.

It would appear that when the code sees the first exception, then the
loop
is broken and stops listing any more reports, but includes the
alphabetically
first exception.

--
Jim Ory


:

Whether you use USys or not. and special prefix that you use that is
unique
to the hidden reports will work. If you named them "hdrptReportName",
you
could keep from including them in a list by using Left(strRptName, 2)
=
"hd"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
I certainly appreciate the suggestion. I tried your recommendation,
adding
the lines of code, changing the names of the reports, and verifying
that
'Hidden and System Objects were unchecked. Still, when the "E-Mail"
code
is
run, all files in the reports container appear in the list.

I also tried different variations of code line:

If Left(strRptName,4) <> "USys" Then

etc. . . . . i.e. If Left(strRptName,3) <> "rpt" Then

What happened with that line is 4 of the 5 reports with the 3
character
prefixes of "rpt" disappeared, except one. All other reports without
"rpt"
were in the list. Trying the "USys" variation, any report with that
appeared
no matter what other settings I had set in Options> View.
--
Jim Ory


:

One of the ways to hide objects in Access is to prefix them with
"USys"

So naming a report "USysMyReport" will hide it in the database
window
unless
hidden and system objects are turned on in Tools >>> Options.

Knowing that, we can make an exception in the code below, and
exclude
any
report that has the first 4 letters = to usys:


For i = 0 To contr.Documents.Count - 1
If Left(strRptName,4) <> "USys" Then
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList &
";
"
Length = Len(strRptName)
strRptList = strRptList & strRptName
End If
Next i

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
Using WinXP, Access2K2

Copyright Notice:

' This database and all the code therein is © 1999-2002 Arvin
Meyer
(e-mail address removed)
' You are free to use this code and this database in an
application
' as long as you do not publish it without the author's
permission.
' Additionally, you are required to include this copyright notice
in
the
application.

The following code returns a list of developed reports to be sent
via
E-mail. Some of the reports use different filter by form methods
to
bring
up
the desired report. I would prefer not to have all reports be
seen
by
the
user because the filter forms must be opened first to not have
errors
presented.

Let's presume out of 12 reports, only two are developed without
the
use
of
filtering forms. Is there a way to limit the list to the two
reports?

I have tried to "Hide" the reports; Tools, Options, View, and
uncheck
'Hidden Objects'. That only hides them in the database window,
but
not
from
the code below.

Code Start **********************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OF
Dim db As Database
Dim i As Integer
Dim contr As Container
Dim strRptList As String
Dim strRptName As String
Dim Length As Integer

Set db = CurrentDb()
Set contr = db.Containers("Reports")

strRptList = ""
For i = 0 To contr.Documents.Count - 1
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList & ";
"
Length = Len(strRptName)
strRptList = strRptList & strRptName
Next i

Me!lstRpt.RowSource = strRptList

Exit_OF:
Exit Sub
Err_OF:
MsgBox Err & " " & Error, , "Report Open"
Resume Exit_OF

End Sub
Code End ***************************

Thanks for any suggestions.

 

[Guest]

No I haven't set break points in the code. I'm still a novice at programming
VBA. And still trying to learn to make a normalized database. You could say
I'm still stumbling around Access.

I appreciate that you share your code with others and thank you for helping
me on this problem. I'm sure your time is valuable. I may just remove the
option to email reports from the program, but allow them to pick through the
Email addresses. They can add the reports when their email program is running.
--
Jim Ory

You will have to experiment with the exact code and placement. Without
something to test it on, there's no way I can debug the problem. Did you try
setting a stop (break point) in the code and stepping through it to see
where it's causing the problem?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

That certainly produced an unusual effect. Instead of eliminating the
reports
from the list, the report names were appended to the alphabetically
arranged
report just before the reports to be excluded.

So using the same example reports, the result for the list is:

Address1
Address2
Dir1
Dir2QueryByForm1QueryByForm2QueryByForm3
rptActive
rptDeceased
rptDonors


--
Jim Ory

I can see that. Not testing the code, I assumed that was the place to put
the If Then statement. Obviously, you will need to move it further into
the
loop. Try this:

If strRptList <> "" And Left(strRptName,3) <> "Que" Then strRptList =
strRptList & "; "
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
Using this line in the code: <If Left(strRptName, 3) <> "Que" Then>.

After trying different variations of prefixes in the report names, I
found
that reports alphabetically before and the first report with "Que" in
the
report name were listed. All other reports alphabetically after the
last
were
missing.

In the database windows for reports I have for example:

Address1
Address2
Dir1
Dir2
QueryByForm1
QueryByForm2
QueryByForm3
rptActive
rptDeceased
rptDonors

After the code runs, the list includes the first 5 reports. The
remaining
5
reports are missing. If I change the characters in the line of code to
"Add",
then 'Address1' is in the list and the remaining 9 reports are left
out.

It would appear that when the code sees the first exception, then the
loop
is broken and stops listing any more reports, but includes the
alphabetically
first exception.

--
Jim Ory


:

Whether you use USys or not. and special prefix that you use that is
unique
to the hidden reports will work. If you named them "hdrptReportName",
you
could keep from including them in a list by using Left(strRptName, 2)
=
"hd"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
I certainly appreciate the suggestion. I tried your recommendation,
adding
the lines of code, changing the names of the reports, and verifying
that
'Hidden and System Objects were unchecked. Still, when the "E-Mail"
code
is
run, all files in the reports container appear in the list.

I also tried different variations of code line:

If Left(strRptName,4) <> "USys" Then

etc. . . . . i.e. If Left(strRptName,3) <> "rpt" Then

What happened with that line is 4 of the 5 reports with the 3
character
prefixes of "rpt" disappeared, except one. All other reports without
"rpt"
were in the list. Trying the "USys" variation, any report with that
appeared
no matter what other settings I had set in Options> View.
--
Jim Ory


:

One of the ways to hide objects in Access is to prefix them with
"USys"

So naming a report "USysMyReport" will hide it in the database
window
unless
hidden and system objects are turned on in Tools >>> Options.

Knowing that, we can make an exception in the code below, and
exclude
any
report that has the first 4 letters = to usys:


For i = 0 To contr.Documents.Count - 1
If Left(strRptName,4) <> "USys" Then
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList &
";
"
Length = Len(strRptName)
strRptList = strRptList & strRptName
End If
Next i

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
Using WinXP, Access2K2

Copyright Notice:

' This database and all the code therein is © 1999-2002 Arvin
Meyer
(e-mail address removed)
' You are free to use this code and this database in an
application
' as long as you do not publish it without the author's
permission.
' Additionally, you are required to include this copyright notice
in
the
application.

The following code returns a list of developed reports to be sent
via
E-mail. Some of the reports use different filter by form methods
to
bring
up
the desired report. I would prefer not to have all reports be
seen
by
the
user because the filter forms must be opened first to not have
errors
presented.

Let's presume out of 12 reports, only two are developed without
the
use
of
filtering forms. Is there a way to limit the list to the two
reports?

I have tried to "Hide" the reports; Tools, Options, View, and
uncheck
'Hidden Objects'. That only hides them in the database window,
but
not
from
the code below.

Code Start **********************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OF
Dim db As Database
Dim i As Integer
Dim contr As Container
Dim strRptList As String
Dim strRptName As String
Dim Length As Integer

Set db = CurrentDb()
Set contr = db.Containers("Reports")

strRptList = ""
For i = 0 To contr.Documents.Count - 1
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList & ";
"
Length = Len(strRptName)
strRptList = strRptList & strRptName
Next i

Me!lstRpt.RowSource = strRptList

Exit_OF:
Exit Sub
Err_OF:
MsgBox Err & " " & Error, , "Report Open"
Resume Exit_OF

End Sub
Code End ***************************

Thanks for any suggestions.

 

[Arvin Meyer [MVP]]

OK, so now you get to learn a little programming.

To set a breakpoint, all you need do is click on the left gray bar in the
code window. If you do this immediately after the Dim statements (it doesn't
work there) the code stops. Then Press F8 and it will go 1 line, F8 it will
run another line, and so forth. If you hover the mouse over the variables on
the previous line, you'll see all the values. If right before the line that
states: Next i you add a temporary line of code:

Debug.Print strRptList

at every iteration of the loop it will print the values of strRptList in the
debug (immediate) window.

Keep this up and I'll have you hooked on Access in no time.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

No I haven't set break points in the code. I'm still a novice at
programming
VBA. And still trying to learn to make a normalized database. You could
say
I'm still stumbling around Access.

I appreciate that you share your code with others and thank you for
helping
me on this problem. I'm sure your time is valuable. I may just remove the
option to email reports from the program, but allow them to pick through
the
Email addresses. They can add the reports when their email program is
running.
--
Jim Ory

You will have to experiment with the exact code and placement. Without
something to test it on, there's no way I can debug the problem. Did you
try
setting a stop (break point) in the code and stepping through it to see
where it's causing the problem?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

That certainly produced an unusual effect. Instead of eliminating the
reports
from the list, the report names were appended to the alphabetically
arranged
report just before the reports to be excluded.

So using the same example reports, the result for the list is:

Address1
Address2
Dir1
Dir2QueryByForm1QueryByForm2QueryByForm3
rptActive
rptDeceased
rptDonors


--
Jim Ory


:

I can see that. Not testing the code, I assumed that was the place to
put
the If Then statement. Obviously, you will need to move it further
into
the
loop. Try this:

If strRptList <> "" And Left(strRptName,3) <> "Que" Then strRptList =
strRptList & "; "
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
Using this line in the code: <If Left(strRptName, 3) <> "Que" Then>.

After trying different variations of prefixes in the report names, I
found
that reports alphabetically before and the first report with "Que"
in
the
report name were listed. All other reports alphabetically after the
last
were
missing.

In the database windows for reports I have for example:

Address1
Address2
Dir1
Dir2
QueryByForm1
QueryByForm2
QueryByForm3
rptActive
rptDeceased
rptDonors

After the code runs, the list includes the first 5 reports. The
remaining
5
reports are missing. If I change the characters in the line of code
to
"Add",
then 'Address1' is in the list and the remaining 9 reports are left
out.

It would appear that when the code sees the first exception, then
the
loop
is broken and stops listing any more reports, but includes the
alphabetically
first exception.

--
Jim Ory


:

Whether you use USys or not. and special prefix that you use that
is
unique
to the hidden reports will work. If you named them
"hdrptReportName",
you
could keep from including them in a list by using Left(strRptName,
2)
=
"hd"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
I certainly appreciate the suggestion. I tried your
recommendation,
adding
the lines of code, changing the names of the reports, and
verifying
that
'Hidden and System Objects were unchecked. Still, when the
"E-Mail"
code
is
run, all files in the reports container appear in the list.

I also tried different variations of code line:

If Left(strRptName,4) <> "USys" Then

etc. . . . . i.e. If Left(strRptName,3) <> "rpt" Then

What happened with that line is 4 of the 5 reports with the 3
character
prefixes of "rpt" disappeared, except one. All other reports
without
"rpt"
were in the list. Trying the "USys" variation, any report with
that
appeared
no matter what other settings I had set in Options> View.
--
Jim Ory


:

One of the ways to hide objects in Access is to prefix them with
"USys"

So naming a report "USysMyReport" will hide it in the database
window
unless
hidden and system objects are turned on in Tools >>> Options.

Knowing that, we can make an exception in the code below, and
exclude
any
report that has the first 4 letters = to usys:


For i = 0 To contr.Documents.Count - 1
If Left(strRptName,4) <> "USys" Then
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList
&
";
"
Length = Len(strRptName)
strRptList = strRptList & strRptName
End If
Next i

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message
Using WinXP, Access2K2

Copyright Notice:

' This database and all the code therein is © 1999-2002 Arvin
Meyer
(e-mail address removed)
' You are free to use this code and this database in an
application
' as long as you do not publish it without the author's
permission.
' Additionally, you are required to include this copyright
notice
in
the
application.

The following code returns a list of developed reports to be
sent
via
E-mail. Some of the reports use different filter by form
methods
to
bring
up
the desired report. I would prefer not to have all reports be
seen
by
the
user because the filter forms must be opened first to not have
errors
presented.

Let's presume out of 12 reports, only two are developed
without
the
use
of
filtering forms. Is there a way to limit the list to the two
reports?

I have tried to "Hide" the reports; Tools, Options, View, and
uncheck
'Hidden Objects'. That only hides them in the database window,
but
not
from
the code below.

Code Start **********************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OF
Dim db As Database
Dim i As Integer
Dim contr As Container
Dim strRptList As String
Dim strRptName As String
Dim Length As Integer

Set db = CurrentDb()
Set contr = db.Containers("Reports")

strRptList = ""
For i = 0 To contr.Documents.Count - 1
strRptName = contr.Documents(i).Name
If strRptList <> "" Then strRptList = strRptList &
";
"
Length = Len(strRptName)
strRptList = strRptList & strRptName
Next i

Me!lstRpt.RowSource = strRptList

Exit_OF:
Exit Sub
Err_OF:
MsgBox Err & " " & Error, , "Report Open"
Resume Exit_OF

End Sub
Code End ***************************

Thanks for any suggestions.