MS Access Forum / General 2 / February 2007
Selecting and Sending Reports Via E-Mail
|
|
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.
|
|
|