March 2012 ~ LEARN MS-ACCESS TIPS AND TRICKS

Opening Specific Page of Pdf File

a.p.r. pillaiMarch 30, 2012pdf files, Utilities No comments

Introduction.

Opening an external file (such as Word, Excel, or Adobe PDF) from Microsoft Access is straightforward. You can use the Hyperlink tool (Ctrl+K) to browse for a file on disk and assign it as a hyperlink on a form. Another option is to open the Hyperlink tool from the Hyperlink Address property of a label, then browse for and select the desired file.

Once the hyperlink is set, clicking on it will open the file (Word, Excel, or PDF) starting with the first page, according to the document’s default view settings.

The Shell Command.

Another method used to open an external file is a DOS Command (Microsoft Disk Operating System) Shell in VBA. The Shell() command needs mainly two parameters (actually three values), as the syntax is shown below:

Call Shell(“<Parent Application> <file pathname>”, <window mode>)

The first parameter of the Shell() command has two segments, separated by a space.

A. First Parameter

The parent Application Path Name(C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe).
The pathname of the PDF file to open (C:\aprpillai\Documents\dosa.pdf).

B. Second Parameter

open window mode

The Sample Trial Run.

Let us try an example to open a PDF file: C:\aprpillai\Documents\dosa.pdf using the Shell() Command:

Open any one of your databases or create a new one.
Create a new Form with a Command Button on it.
Select the Command Button and open its Property Sheet (F4).
Change the Name property value to cmdRun and change the Caption Property value to Open PDF File.
Select the Event Tab of the Property Sheet and click on the On Click Property.
Click on the build (. . .) button at the right end of the property to open the VBA Module.

Copy and paste the following VBA Code overwriting the existing lines:

Private Sub cmdRun_Click() 
Dim strApplication As String 
Dim strFilePath As String 

strApplication = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe" 
strFilePath = "C:\aprpillai\Documents\dosa.pdf" 

Call Shell(strApplication & " " & strFilePath, vbNormalFocus) 

End Sub

Change the pathname of the PDF file to select a file from your disk with a few pages.
Save the Form with the name PDF_Open_Example.
Open the form in normal view and click on the Command Button to open the PDF file.

The above Sub-Routine opens the file selected with Normal Focus.

After opening the PDF file with page 1 on the top, type a different page number in the navigation control at the bottom of the document to jump to that page.

However, if we already know the page number we want to open, we can pass it as a parameter to the AcroRd32.exe program. For example:

..\AcroRd32.exe /A page=25 ..\dosa.pdf

This command will open the PDF file directly to page 25 instead of starting from the first page.

To demonstrate, let’s modify our earlier program by adding the page parameter (/A page=25) so that the PDF opens at the 5th page. The updated version of the program is shown below:

Private Sub cmdRun_Click()
Dim strApplication As String
Dim strFilePath As String

strApplication = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe /A page=25"
strFilePath = "C:\aprpillai\Documents\dosa.pdf"

Call Shell(strApplication & " " & strFilePath, vbNormalFocus)

End Sub

Note: Do not include spaces on either side of the equal sign in the parameter page=25. The /A switch must immediately follow the program name (AcroRd32.exe), with a space before specifying page=25.

In our example, the file dosa.pdf contains several recipes. To make navigation easier, we should be able to jump directly to a specific recipe with a single click. For this purpose, we can create a Combo Box on the form that lists all recipes, along with their corresponding page numbers and descriptions. By selecting a recipe from the list, the program can pass the correct page number to Acrobat Reader, allowing us to quickly display the chosen recipe.

A Sample Form.

An image of a sample form with the list of Dosa Recipes in a Combo Box is given below:

Right-click to open Large Image in New Tab/Window.

I will explain the second Combo box (Zoom Percentage) a little later.

Open the Form in Design View.
Select the Control Wizard tool to launch when you select the Combobox Tool.
Select the Combobox Tool and draw a Combobox on the Form.
Select the Radio Button on the Control Wizard, with the caption ‘I will type the values that I want and click Next.
Type 2 in the ‘Number of Columns’ control and press the Tab Key.
Type a similar list of topics, shown in the image above, from your PDF file with Page Number in the first column and Description in the second column. When finished, click Next.
Select the first column and click Next.
Type a suitable caption for the child label and click Finish.
Select the Combo Box; if it is deselected, then display the Property Sheet (F4).
Change the Name Property value to cboPage.
Display the VBA Module of the Form (Design -> Tools -> View Code or press ALT+F11).

Copy and paste the following VBA Code into the Module, overwriting the existing code:

Private Sub cmdRun_Click()
Dim ReaderPath As String
Dim pdfFilePath As String
Dim PageNumber As Integer
Dim strOpenPDF As String

PageNumber = Nz(Me![cboPage], 1)' Get user selected page number, if empty then take 1 as default

ReaderPath = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe /A " & "page=" & PageNumber 
pdfFilePath = "C:\aprpillai\Documents\dosa.pdf" 'change the path to match your file location

strOpenPDF = ReaderPath & " " & pdfFilePath
Call Shell(strOpenPDF, vbNormalFocus)

End Sub

Close the VBA Module, save the Form, and open it in normal view.
Select one of the items from the Combobox with a larger page number.
Click on the Command Button to open the PDF file displaying the selected page. Check the following image for a sample view of the dosa.pdf file:

The Zoom Parameter.
From the header toolbar, you can see that the current view shows page 7 of 51, with the document opened at about 60% zoom of its actual size. This zoom level can also be controlled programmatically. By specifying the Zoom parameter immediately after the Page parameter, we can open the PDF document to both the desired page and zoom percentage.
To demonstrate this, I created a second Combo Box control on the form, named cboZoom, which contains a list of zoom percentage values: 50, 60, 70, 80, 90, 100, and 120. By selecting one of these values along with the page number, the PDF document can be opened not only at the correct page but also at the preferred zoom level for easier viewing.

The modified Code with the addition of Zoom Parameter is given below:

Private Sub cmdRun_Click()
Dim ReaderPath As String
Dim pdfFilePath As String
Dim PageNumber As Integer
Dim intZoom As Integer
Dim strOpenPDF As String

PageNumber = Nz(Me![cboPage], 1)
intZoom = Nz(Me![cboZoom], 100)

ReaderPath = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe /A " & quot;page=" & PageNumber & "&zoom=" & intZoom
pdfFilePath = "C:\aprpillai\hostgator\dosa.pdf"

strOpenPDF = ReaderPath & " " & pdfFilePath
Call Shell(strOpenPDF, vbNormalFocus)
End Sub

The Page parameter and Zoom parameter values must be joined with an & symbol, and there should not be any spaces on either side of the equal (=) sign:

..\AcroRd32.exe /A page=7&zoom=60 C:\aprpillai\Documents\dosa.pdf

When you combine the parameter key names (page, zoom) with the control values (page number and zoom percentage), the result should be the sample value shown earlier.

To test this, you can place a Text Box on the form with the name cboZoom. Enter a zoom percentage value in this control (note: do not include the % symbol), then run the code to confirm that it works.

Important: If both controls—cboPage and cboZoom—are left empty, the PDF file will open by default with the first page on top and at 100% zoom.

Technorati Tags: Microsoft

IIF vs Switch function in MS-Access
Preparing Rank List
Restoring Disabled Full Menus in Access 2007
Running Workgroup Admin in Access 2007
Apply the Filter to the Table directly

Calculating Time Difference

a.p.r. pillaiMarch 21, 2012Functions, Time Difference No comments

Introduction.

How do we calculate the difference in time in Hours, Minutes, and Seconds between two time periods? The time period can be within the same date or across different dates.

Example-1:

Start Time: 21-03-2012 12:30:45

End Time: 21-03-2012 13:30:15

Example-2:

Start Time: 21-03-2012 12:30:45

End Time: 24-03-2012 15:15:15

In the first example, the date portion of the Start Time and End Time is irrelevant when calculating the difference between them. For instance, subtracting the start time of 12:30:45 from the end time of 13:30:15 gives a result of 00:59:30. However, if the end time occurs past midnight, such as 01:30:15, how should we calculate the time difference?

00:00:00 Time.

First, let’s consider how time resets after 23:59:59 at night and returns to 00:00:00. Understanding how this transition is stored in a computer’s memory makes it much easier to work with date and time values.

We all know the basic units: a day has 24 hours, an hour has 60 minutes, a minute has 60 seconds, and a second can be further divided into 1000 milliseconds.

If you’re curious about how February gets 29 days, why every 100th year (such as 1700, 1800, 1900, or 2100) is not a leap year, why every 400th year is a leap year, and why every 4000th year is not a leap year, [click here to find out].

One Day in Seconds.

Setting aside milliseconds for the moment, one day equals 86,400 seconds (24 × 60 × 60). Put another way, one second equals 1/86,400, or 0.0000115740740741 days. Exactly one second after midnight, the computer’s internal time value becomes 0.0000115740740741, and this value increments with every passing second.

If we divide this value by 1000, we get the fraction that represents one millisecond, meaning the time value can also be incremented at millisecond intervals. At 12:00 noon, the internal time value is 0.5 (half a day), and at 23:59:59, it reaches 0.999988425926 (in days). When the clock rolls over to 00:00:00 midnight, the value resets to 0.

Date and Time Together.

To calculate the time difference between values that span different dates, the date component must be considered along with the time value, as shown in Example 2 above. In the computer’s memory, the date is stored as a continuous serial number beginning with 1 for 31-12-1899. For instance, if you create a Date/Time field in a table with a default value of 0, the field will display the date as 30-12-1899 until an actual date is entered.

Date and Time Number.

The date 21-03-2012 equals the date number 40989, and the Start Time (Example-2) in memory will be 40989.521354166700; the number after the decimal point is the time value. Since the Date and Time values held in memory are real numbers, it is easy to calculate the difference between them. All you have to do is subtract one number from another. This works with Date/Time Values on the same day or on different dates.

Let us find out the time difference between the date and time values in Example 2:

StartTime = #21-03-2012 12:30:45# = 40989.521354166700

This is the kind of value (Current Date and Time) returned with the function Now(). You can format this value as date alone (dd-mm-yyyy) or time alone ("hh:nn:ss") or both combined - format(now(),”dd-mm-yyyy hh:nn:ss”).

Use ? format(StartTime,”0.000000000000”) to display the Date/Time value as a real number from the StartTime Variable in the Debug Window.

EndTime = #24-03-2012 15:15:15# = 40992.635590277800

Difference = (EndTime - StartTime) = 3.114236111112 days

So, the result value we get in days doesn’t matter if the date is the same or a different date. If the date is the same, you will get the result in days, like 0.9999999. All we have to do is convert these days into Hours, Minutes, and Seconds.

Total_Seconds = Difference x 86400 (difference in days converted into total seconds) = 269070.000000077 Seconds

Hours = Int(Total_Seconds / 3600) = 74 Hrs.

Minutes = int((Total_Second MOD 3600)/60) = 44 Min.

Seconds = Total_Seconds MOD 60 = 30 sec.

If we assemble all this information into a small function, we can easily find the time difference in Hours, Minutes, and Seconds by using the Start and End Date/Time Values in the Function as parameters.

The Hours/Minutes Function.

Copy and paste the following Function Code into the Standard Module and save it:

Public Function HrsMin(ByVal startDate As Date, ByVal endDate As Date) As String
Dim diff As Double
Dim difHrs As Integer
Dim difMin As Integer
Dim difSec As Integer
diff = (endDate - startDate) * 86400
difHrs = Int(diff / 3600)
difMin = Int((diff Mod 3600) / 60)
difSec = (diff Mod 60)
HrsMin = Format(difHrs, "00") & ":" & Format(difMin, "00") & ":" & Format(difSec, "00")
End Function

You may call the Function HrsMin() directly from the Debug Window, as shown below, to test the code:

? HrsMin(#21-03-2012 12:30:45#,#24-03-2012 15:15:15#)

Result: 74:44:30

You may call the function from a Textbox in the Form like:

= HrsMin([StartDate],[EndDate])

Or from the Query Column like:

HMS:HrsMin([StartDate],[EndDate])

Or you may run it from VBA Code:

strHMS = HrsMin(dtSDate,dtEDate)

Technorati Tags: Functions

Earlier Post Link References:

Centralized Error Handler and Error Log

a.p.r. pillaiMarch 11, 2012Error Handler, Utilities No comments

Introduction.

In an earlier article on the VBA Utility program, we explored how to scan through a VBA module—whether a standard module or a form/report class module—and automatically insert missing error-handling lines. You can find the link to that article here.

This utility program can save you considerable time that would otherwise be spent typing, copying and pasting, and modifying hundreds of lines of error-trapping code in your subroutines and functions. The main purpose of an error handler is to manage unexpected errors and, when necessary, report them to the developer so that the underlying issue can be permanently fixed. At the same time, the program should not terminate abruptly. If it is a minor issue, the user should be able to dismiss the error and continue working without interruption.

A typical project may contain hundreds of subroutines and functions across standard modules and Form/Report modules. When an error occurs, the message typically includes the error number and description. If the procedure name is included in the MsgBox() function’s title parameter, it will also appear in the message box title. However, users often overlook this important detail, which could otherwise help the developer quickly locate the exact procedure where the error occurred and resolve it efficiently.

A Common Error Handler.

A more effective approach to handling such issues is to create a centralized error handler and maintain an error log Text File on disk. Whenever an error occurs in a function or subroutine, the common error handler can be called with the necessary parameters, like the Error number, Description, procedure name, module name, and database name. The handler will both display the error message to the user and record the details in a log file.

If several Microsoft Access applications are running on a Local Area Network, all of their error log information can be saved to a single shared text file on the Server. Each log entry will include details like the date, time, module name, and database name, along with the usual error number and description, creating a consolidated and traceable error history.

A Text file image with sample error log entries is given below:

Right-click to open Large Image in New Tab/Window.

Error Message Info.

Each error log entry contains all the essential details—such as the date and time of the error, database path, module name, and procedure name—to precisely identify where the error occurred. Even if users choose not to report problems, the administrator can periodically review the log file to monitor the application’s overall health and address issues proactively.

The Trial Run.

The following sample data processing program, DataProcess(), attempts to open the input table Table_1, but the table doesn’t exist (got deleted or renamed by mistake), and the program runs into an error:

Public Function DataProcess()
Dim db As Database, rst As Recordset, x
On Error GoTo DataProcess_Error

Set db = CurrentDb
Set rst = db.OpenRecordset("Table_1", dbOpenDynaset)

Do While Not rst.EOF
 x = rst.Fields(0).Value
Loop
rst.Close

DataProcess_Exit:
Exit Function

DataProcess_Error:
BugHandler Err, Err.Description, "DataProcess()", "Module4", CurrentDb.Name
Resume DataProcess_Exit
End Function

Common Error Handler Info and Log File.

When the above program runs into an error, it calls the BugHandler() Program and passes the Module Name and Database Path as the last two parameters in addition to Error Number, Error Description, and Program name. The VBA Code of BugHandler() main program is given below:

Public Function BugHandler(ByVal erNo As Long, _
                           ByVal erDesc As String, _
                           ByVal procName As String, _
                           ByVal moduleName As String, _
                           ByVal dbName As String)
On Error GoTo BugHandler_Error
Dim logFile As String
Dim msg As String

'Error Log text file pathname, change it to the correct path
'on your Local Drive or Server Location
logFile = "c:\mdbs\bugtrack\acclog.txt"

'Open log file to add the new error log entry
Open logFile For Append As #1
  'write the log details to log file
  Print #1, Now() & vbCr
  Print #1, "Database : " & dbName & vbCr
  Print #1, "Module   : " & moduleName & vbCr
  Print #1, "Procedure: " & procName & vbCr
  Print #1, "Error No.: " & erNo & vbCr
  Print #1, "Desc.    : " & erDesc & vbCr
  Print #1, String(80, "=") & vbCr
  Close #1

msg = "Procedure Name: " & procName & vbCr & "Error : " & erNo & " : " & erDesc
  MsgBox msg, , "BugHandler()"

BugHandler_Exit:
Exit Function

BugHandler_Error:
MsgBox Err & " : " & Err.Description, , "BugHandler()"
Resume BugHandler_Exit
End Function

The Library Database.

You can save the above code in a common Library Database, where you have saved your own common library functions, so that they can be attached to your Projects.

This method will write out the details of errors from your databases into a commonplace, accessible to you all the time. When an error is reported by the User, you can directly check the details of it without asking the user to spell it out.

Technorati Tags: Databases, Error Handler