ACCESS FEED: Re: [MS_AccessPros] Page Count Question

Selasa, 24 Juli 2012

Re: [MS_AccessPros] Page Count Question

Excellent idea! This will work fine...

Thanks,
Stan

----- Original Message -----
From: A.D. Tejpal
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, July 23, 2012 1:45 AM
Subject: Re: [MS_AccessPros] Page Count Question

Bill, Stan,

On further examination, it seems Stan's case pertains not to multiple reports (as treated in my earlier post), but to a common report, where, if the output happens to exceed two pages, a warning is needed so as to enable the user to store such print outs separately.

In that case, an interesting alternative could be considered that does not require prior opening of the report in preview mode.

Place an unbound blank text box named TxtLongBill with suitable back color (say purple) in report header section. This text box should look like a thin strip - for ready visual identification. Place the code given below, in report header's format event:

' Code in report's module
'====================================
Private Sub ReportHeader_Format( _
Cancel As Integer, FormatCount As Integer)
If Me.Pages > 2 Then
Me.TxtLongBill.Visible = True
MsgBox "Caution: This Bill Exceeds Two Pages"
Else
Me.TxtLongBill.Visible = False
End If
End Sub
'====================================

With the above arrangement, the user can carry on printing the bills in a normal manner (no preview needed). Whenever a particular bill happens to exceed 2 pages, a warning message will come up, alerting the user for appropriate action towards separate storage of such prints. Printing will resume after the user has closed the message box.

Simultaneously, such print outs (longer than two pages) will carry a purple strip at top, facilitating easy visual identification.

Note:
As mentioned earlier, the report should have a calculated text box with an expression that includes [Pages].

Best wishes,
A.D. Tejpal
------------

----- Original Message -----
From: A.D. Tejpal
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, July 23, 2012 10:06
Subject: Re: [MS_AccessPros] Page Count Question

Bill, Stan,

Thanks Bill!

If the number of reports is large and if it is desired to readily view separate lists of reports based upon their page count, we could consider expanding further on the lines indicated by Bill.

For example, let table T_Reports have fields RepName (text type), and PgCount (number type), apart from autonumber type primary key field. Sample subroutine named P_FillReportsTable() as given below (at the end of this post), will populate the table with the name and page count for each report.

Sample query Q_RepMoreThan2Pgs as given below, will show at a glance, all reports having more than two pages:

Q_RepMoreThan2Pgs
==================================
SELECT T_Reports.*
FROM T_Reports
WHERE (((T_Reports.PgCount)>2));
==================================

Similarly, sample query Q_RepUpTo2Pgs as given below, will show the other reports (<= 2 pages):

Q_RepUpTo2Pgs
==================================
SELECT T_Reports.*
FROM T_Reports
WHERE (((T_Reports.PgCount)<=2));
==================================

Note:
Each report should have a calculated text box with an expression that includes [Pages]. Otherwise, on opening in print preview, report's Pages property could return zero.

Best wishes,
A.D. Tejpal
------------

' Code in general module
'=================
Sub P_FillReportsTable()
Dim rs As DAO.Recordset
Dim obj As Object
Dim Rnm As String, Pgs As Long

With CurrentDb
' Clear table T_Reports
.Execute "DELETE FROM T_Reports;", dbFailOnError
Set rs = .OpenRecordset("T_Reports")

For Each obj In CurrentProject.AllReports
Rnm = obj.Name
DoCmd.OpenReport Rnm, acViewPreview, , , acHidden
Pgs = Reports(Rnm).Pages
DoCmd.Close acReport, Rnm, acSaveNo

With rs
.AddNew
!RepName = Rnm
!PgCount = Pgs
.Update
End With
Next
End With

rs.Close
Set rs = Nothing
Set obj = Nothing
End Sub
'====================

----- Original Message -----
From: Bill Mosca
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, July 23, 2012 07:42
Subject: RE: [MS_AccessPros] Page Count Question

Hi Stan

Glad I could point you in the right direction.

Regards,

Bill

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Stan Helmle
Sent: Sunday, July 22, 2012 4:11 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Page Count Question

Hi Bill,

I forgot about being able to use the report 'hidden'. That should work quite
nicely...

(I've been spending way too much time on an AS400)

Thanks,
Stan

----- Original Message -----
From: Bill Mosca
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Sunday, July 22, 2012 2:57 PM
Subject: RE: [MS_AccessPros] Page Count Question

Stan

You can use that technique of getting the page count by opening the bill in
preview first. That could be done with the report hidden. If it exceeds 2 pages
put its name in an array. Then close the bill and move to the next one.

But I bet A.D. comes up with a better solution. He always does when it comes to
report magic.

Regards,
Bill Mosca,
Founder, MS_Access_Professionals
That'll do IT <http://thatlldoit.com/> http://thatlldoit.com
MS Access MVP

<https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E>
https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Stan Helmle
Sent: Sunday, July 22, 2012 1:47 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: [MS_AccessPros] Page Count Question

I support an Access bill printing application that prints around 2500 bills per
day. Most are 2 page bills. However, we need to be able to determine which are
greater than 2 pages before printing starts. And, it is not easy to do this task
by merely counting line items to be printed. We use this technique, but it is
not 100% accurate.

I'm familiar with the Me.Page (If Me.Page > 2 Then ...) which we use during the
actual bill print to catch any bills that spill over to page 3. We then have to
sort through the printed bills to find them and pull them out.

I'm looking for any creative ideas on how we might better handle this task. I'm
always amazed at what this group comes up with.

Thanks,
Stan Helmle

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]