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 (Word, Excel, or Adobe .pdf file) from Microsoft Access is not a big issue. We can use the HyperLink tool (Ctrl+K) to browse and find a file on disk and set up a Hyperlink on a form. We can launch the Hyperlink tool from the Hyperlink Address Property of a Label, browse and find the external file on disk and set up a hyperlink on a label.

When we click on the Hyperlink the file (Ms-Word, Excel, or Adobe Acrobat pdf file) will open with the first page on top, and whatever default view configuration of the document is set.

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 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 on the navigation control, at the bottom of the document, to jump to that page.

But, if we know this information in advance then we can use the page number value as an open parameter to the AcroRd32.exe program (..\AcroRd32.exe /A page=25 ..\dosa.pdf) to open the pdf file with that page in view.

We will modify the above program with the addition of the Page parameter (..\AcroRd32.exe /A page=25) to jump to the 5th page of the pdf document. The modified program is given 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

NB: Don't leave any space on either side of the equal sign in the ‘page=25’ parameter. The /A switch must be given after the program name AcroRd32.exe, followed by a space and then page=25.

We have several recipes on the dosa.pdf file and we must be able to go to a particular one quickly with a single click. To make the task easy we must create a Combo box on the form with a list of all recipes (with page numbers and descriptions) so that we can select a particular recipe and jump to that quickly.

A Sample Form.

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

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 it 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 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 de-selected, 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.
As you can see from the header toolbar that the page in view is 7/51 and the document view is only about 60% (zoom=60) of its actual size of 100%. We can control the view size of a pdf document by specifying the Zoom parameter immediately after the Page parameter.
I have created a second Combo Box Control with the name cboZoom on the form, the image shown above, and Zoom Percentage values 50,60,70,80,90,100 & 120 so that one of these values can be selected along with the Page number to view the document in the desired size.

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 join parameter key names (page, zoom) and control values (page number and zoom percentage) together the result of it should form like the sample value shown above.

You may create a text box with the name cboZoom on the form and type the zoom percentage value (not to add the % symbol along with the value) and run it to test the above code.

NB: If you leave both controls (cboPage and cboZoom) empty the pdf file will open with the first page of the file on top and with a 100% zoom value.

Technorati Tags: Microsoft

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 on the same date or across 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 values in the Start Time and End Time look like it is not important, because both are the same, to calculate the time difference between them. We can subtract the Start Time value of 12:30:45 from the End Time value of 13:30:15 and will get the result of 00:59:30. But, if the end time is 01:30:15 past midnight, then how do we calculate the time difference?

00:00:00 Time.

But first, how the time changes to zero after 23:59:59 Hrs at night and how this value is held in the computer’s memory. With this basic knowledge working with the date and time values becomes very easy.

Everybody knows that a day equals 24 hours, 1 hour equals 60 Minutes, 1 Minute equals 60 Seconds, and further if we go to a smaller denomination 1-second equals 100 milliseconds. If you would like to find out how February gets 29 days (everybody knows about it) but why every 100th Year (like 1700, 1800,1900 or 2100) is not a Leap Year, how every 400th Year becomes Leap Year and why every 4000th year is not Leap Year, Click here to find out!

One Day in Seconds.

So, keeping aside the milliseconds part 1 Day = 86400 Seconds (24 x 60 x 60). In other words 1 Second = 1/86400 = 0.0000115740740741 Days. Exactly 1 second after midnight the computer’s internal time value will be 0.0000115740740741 and this value keeps adding to the time value at every second interval. This value can be divided by 100 to get the value equal to 1 millisecond and we could say that value is added to the time value at every millisecond interval. At 12:00 Noon the internal time value is 0.5 (half a day) and at 23:59:59 the internal time value will be 0.999988425926 (in days). Again this will reset to 0 at 12:00:00 midnight.

Date and Time Together.

To find out the time difference between time values spread across different dates the date value also should go along with the time value as shown in Example-2 above. The date value is also mapped in the computer’s memory as a continuous number starting 1 from 31-12-1899. If you create a Date/Time field in your Table having 0 as the default value then you will find the date appearing in that field as 30-12-1899, if not filled in with a date.

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 find out the difference between them. All you have to do is to 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 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 different dates. If the date is the same you will get the result in days something like 0.9999999. All we have to do is to 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 the form of a small function we can easily find the time difference in Hours, Minutes, and Seconds by using the Start & 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 have seen how to scan through a VBA Module (both Standard Module and Form/Report Modules – Class Modules) and insert Error Handler lines automatically, wherever they are found missing. Find the link to that article here.

I am sure that the Utility Program will help you to save time, otherwise, you spent on typing/copy-pasting and modifying hundreds of lines of error-trap code in your sub-routines or functions. The whole idea behind the error handler is to take care of unexpected errors and if necessary report them to the developer so that the logical error can be rectified once and for all. Besides that, the program should not break the code unexpectedly. If it is a minor issue the user can ignore it and continue to do what he/she is doing.

A particular Project will have hundreds of Sub-Routines/Functions in Standard Modules and Form/Report Modules. When the error message pops up, the message will carry the Error Number and Error Description. The message box’s title will carry the Function/Sub-Routine name if this is included in the title parameter of the MsgBox() Function. But, the user may not notice this valuable information to pass on to the programmer, so that he can go directly into that program and do what he needs to do to rectify the error.

A Common Error Handler.

A better approach to these kinds of issues is to create a common Error Handler Program and maintain an Error Log Text File on disk. When an error occurs in a Function/Sub-Routine call the common Function with the necessary parameters (Error Number, Error Description, Function/Sub-Routine Name, Module Name, Database Name). The common error handler program will display the error messages and write out the details into a Text File on Disk.

If you have several Microsoft Access-based Applications installed on the Local Area Network all of them can save the error log information into a single text file on the Server’s common location. The error log will carry the Date, Time, Module Name, and Database name, besides the normal error values Error Number and Error Description.

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

Error Message Info.

The error log entry has all the details (Date & Time of Error, Database Path, Module Name, Procedure name, etc., to pinpoint the location of the Error. Even if the user doesn’t bother to report the problem to the Administrator, the Administrator can periodically check this log file to monitor his application’s health.

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 into 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