Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Opening Specific Page of Pdf File

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 setup 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.

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

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

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

A.  First Parameter

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

B.  Second Parameter

  1. open window mode

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

  1. Open any one your databases or create a new one.
  2. Create a new Form with a Command Button on it.
  3. Select the Command Button and open it's Property Sheet (F4).
  4. Change the Name property value to cmdRun and change the Caption Property value to Open PDF File.
  5. Select the Event Tab of the Property Sheet and click on the On Click Property.
  6. Click on the build (. . .) button at the right end of the property to open the VBA Module.
  7. 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
    
  8. Change the pathname of the pdf file to select a file from your disk with few pages.
  9. Save the Form with the name PDF_Open_Example.
  10. 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 open parameter to 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 Page parameter (..\AcroRd32.exe /A page=25) to jump to the 5th page of the pdf document. 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 ‘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 description) so that we can select a particular recipe and jump to that quickly.

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) little later. 

  1. Open the Form in Design View.
  2. Select the Control Wizard tool to launch it when you select the Combobox Tool.
  3. Select the Combobox Tool and draw a Combobox on the Form.
  4. Select the Radio Button, on the Control Wizard, with the caption ‘I will type the values that I want’ and click Next.
  5. Type 2 in the ‘Number of Columns’ control and press Tab Key.
  6. Type a similar list of topics, shown in the image above, from your pdf file with Page Number in first column and Description in the second column, when finished click Next.
  7. Select the first column and click Next.
  8. Type a suitable caption for the child label and click Finish.
  9. Select the Combo Box, if it is de-selected, then display the Property Sheet (F4).
  10. Change the Name Property value to cboPage.
  11. Display the VBA Module of the Form (Design - - > Tools - - > View Code or press ALT+F11).
  12. 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
  13. Close the VBA Module, Save the Form and open it in normal view.
  14. Select one of the item from the Combobox with a larger page number.
  15. Click on the Command Button to open the pdf file displaying the selected page. Check the following imge for a sample view of the dosa.pdf file:

    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 it's 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, 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 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 100% zoom value.

Technorati Tags:
Share:

Calculating Time Difference

How do we calculate difference of time in Hours, Minutes and Seconds between two time period? 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, looks like it is not important, because both are same, to calculate the time difference between them. We can subtract the Start Time value 12:30:45 from the End Time value 13:30:15 and will get the result 00:59:30.  But, if the end time is 01:30:15 past mid-night then how do we calculate the time difference?

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

Everybody knows that a day is equals to 24 hours, 1 hour equals to 60 Minutes, 1 Minute equals to 60 Seconds and further if we go to a smaller denomination 1 second equals to 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 become Leap Year and why every 4000th year is not Leap Year, Click here to find out!

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. 

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 also mapped in 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 default value then you will find the date appearing in that field as 30-12-1899, if not filled-in with a date.

The date 21-03-2012 equals to 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 other.  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 the date is same or different dates.  If the date is same you will get the result in days something like 0.9999999.  All we have to do is to convert this 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 these information into the form of a small function we can easily find the time difference in Hours, Minutes and Seconds  by passing the Start & End Date/Time Values to the Function as parameters.

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:
Share:

Centralized Error Handler and Error Log

In an earlier article with 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 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 it 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 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 better approach to these kind 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 not only display the error message but also write out the details into a Text File on Disk.

If you have several Microsoft Access based Applications installed on Local Area Network all of them can save the error log information into a single text file on 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:

The error log entry have all the details (Date & Time of Error, Database Path, Module Name, Procedure name etc. to pin-point 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 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 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

When the above program runs into error it calls the BugHandler() Program and passes the Module Name and Database Path as 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

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

This method will write out the details of errors from your databases into a common place 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 out.

Technorati Tags: ,
Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts