Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / February 2007

Tip: Looking for answers? Try searching our database.

Selecting and Sending Reports Via E-Mail

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jim Ory - 21 Feb 2007 17:52 GMT
Using WinXP, Access2K2

Copyright Notice:

' This database and all the code therein is © 1999-2002 Arvin Meyer
arvinm@datastrat.com
' 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.

Signature

Jim Ory

Arvin Meyer [MVP] - 23 Feb 2007 02:41 GMT
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

Signature

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

> Using WinXP, Access2K2
>
[quoted text clipped - 55 lines]
>
> Thanks for any suggestions.
Jim Ory - 23 Feb 2007 17:20 GMT
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.
Signature

Jim Ory

> One of the ways to hide objects in Access is to prefix them with "USys"
>
[quoted text clipped - 72 lines]
> >
> > Thanks for any suggestions.
Arvin Meyer [MVP] - 23 Feb 2007 22:23 GMT
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"
Signature

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
[quoted text clipped - 95 lines]
>> >
>> > Thanks for any suggestions.
Jim Ory - 24 Feb 2007 12:24 GMT
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.

Signature

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
[quoted text clipped - 98 lines]
> >> >
> >> > Thanks for any suggestions.
Arvin Meyer [MVP] - 25 Feb 2007 00:16 GMT
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 & "; "
Signature

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>.
>
[quoted text clipped - 147 lines]
>> >> >
>> >> > Thanks for any suggestions.
Jim Ory - 25 Feb 2007 13:48 GMT
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

Signature

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
[quoted text clipped - 153 lines]
> >> >> >
> >> >> > Thanks for any suggestions.
Arvin Meyer [MVP] - 26 Feb 2007 01:00 GMT
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?
Signature

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
[quoted text clipped - 190 lines]
>> >> >> >
>> >> >> > Thanks for any suggestions.
Jim Ory - 26 Feb 2007 11:47 GMT
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.
Signature

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
[quoted text clipped - 194 lines]
> >> >> >> >
> >> >> >> > Thanks for any suggestions.
Arvin Meyer [MVP] - 27 Feb 2007 01:12 GMT
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.
Signature

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
[quoted text clipped - 228 lines]
>> >> >> >> >
>> >> >> >> > Thanks for any suggestions.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.