Summary

This year in Australia there was a new tax and guess what, invoices reports had to be changed significantly. This left software for two small business clients plus our own billing system in a situation where the invoice reports would have to be split into old and new. The solution that we came up with was to save all the old Invoices "as is" using the Access snapshot format. That way when a client rang up to inquire about an invoice, we could check it using the file manager. Very soon the process of running the Access report and doing a manual Save As (see figure 1) became tedious as did using the file manager. As removing repetition is the role of the programmer, I developed a generalised solution for opening Access reports directly into snapshot format that you can add to any Access report. This article describes the event programming that you need to add to your reports and forms to switch from soft to hard copy snapshot reports.

What Is A Snapshot ?

A snapshot is a defined by Microsoft as being "a portable Microsoft Access report". A report snapshot file should have a file extension of *.snp and it should always have the exact same layout and look as the report itself when it is in preview mode. This exact copy feature (see figure 2) sets it apart from the other ways that you can save a report such as transfer to Rich Text Format (Word 95) , HTML and Excel where the report can look vastly different to its original format .

Naturally once you save the report as a snapshot, you then need to view the file and for this you will use the Access Snapshot viewer. This viewer is free and can be downloaded from Microsoft. You can think of the viewer and additonal saving functionality as being a highly specialized alternative to the Adobe Acrobat PDF format technology that is available from Adobe. There is though one important difference. If you already own Microsoft Access 97 Service Pack 1 upwards, the software that you use to save the report is free and easy to install (especially if you own Access 2000). Adobe Acrobat unfortunately requires a new license for each PC that you wish to use to save Access reports to a PDF format. But the Adobe reader is free and thus the popularity of the format.

  << Click to view
Figure 1 - Saving a report to an external file

Download The Viewer - Read Some Comprehensive Examples

One of the best locations for reading about programming the snapshots and installing the snapshot viewer can be found at the following address. This address is also the address that you probably will provide your users with to download the snapshot viewer. There are also notes on this page as to how you can install the snapshot viewer from your Access 97 Compact Disk. Access 2000 is far easier as it automatically installs as soon as you open a *.snp file.

http://www.microsoft.com/accessdev/articles/snapshot.htm

The report that is saved as the snapshot is demonstrated in the snapshot viewer as show in figure 2. The snapshot viewer is a very simple interface that works in the same way the Report Preview works in Microsoft Access. The only difference is that the snapshot viewer will keep a stack of the open snapshots in memory that you can shuffle just like ordinary windows. It will also keep a list of previously opened snapshot files and allow you to simply send the snapshot as an email attachment. What it will definitely not allow you to do is edit the report in design view and / or change the data behind the report. This is good for read-only viewer of reports.

  << Click to view
Figure 2 - The snapshot reader provides an Interface identical to report preview for the invoice report

The Demonstration Database

As my clients and my own billing databases were a bit too specific, I decided to add the snapshot reporting software to the Northwind Order Entry Database that comes with the Access 97 Samples. This is provided for downloading in both Access 97 and 2000 formats. Figure 3 shows the Orders form that I have amended to now use Snapshots rather than ordinary Access reports. When the form opens, there are a list of instructions that are provided so that you can produce you first snapshot without learning too much about the application. These instructions are provided using the Balloon class object that was discussed by yours truly in a previous edition of Smart Access. The databases that I have provided link to the Northwind database automatically.

  << Click to view
Figure 3 - The Order Entry database for Northward has been altered to produce snapshot Invoice reports

Relative Referencing - A Lesson For Handling External Files

Over the last year or two I seem to be spending more and more time with external files such as Excel, Word and now snapshot files. The technique that I use for handling these external files would be very familiar to anyone who has been involved in web and HTML development. It is called "Relative referencing of directories and files" My Access adaptation of this technique is to store the files relative to the location of the database. Generally this will be a in a sub-directory under the main database. This way when you program everything using your "C:\MyDevelopmentDirPath\" directory and then move the solution to your clients "X:\LiveDirPath\" directory, the software will still work as the sub directories are in the same relative location.

Now you might have to be a little careful here as to which Access database that you use to undertake your relative referencing. If you use the front-end / software database for the relative path, it is possible that the software database could have been moved to a local drive rather than the network drive. This would result in your orders being stored on local hard drives. That is not a situation that you want to get into So what I do when the databases are on a network is to store the snapshot and other files relative to the backend database. Specifically for this exercise, I have stored all the snapshots in the "\orders" subdirectory. You should now setup an \orders subdirectory under the directory that holds the Northwind.mdb database.

Relative Referencing - The Essential Source Code

To manage relative referencing, I use the following 3 layers of software. Firstly there is a local function and a constant that I will use throughout the database. It is necessary to use a particular function for this database to ascertain the relative path as it is possible that you may want to change the location at some stage in the future. The sample code is as follows

Public Const gSnapFileType = ".snp"

Public Function SnapDirPath()

  ' Subroutine to use in this application database to
  ' return the path of the backend database

  SnapDirPath = GetLinkedPath_FX("Orders") & "orders"

End Function

Now the function that I use to find out the directory path of the linked Orders table is shown in code snippet below. Here the connect property of a single table in DAO TableDefs container is parsed using the following functions

Function GetLinkedPath_FX(tableName) As Variant

Dim conPath As Variant
Const linkedID = ";DATABASE="

On Error Resume Next
conPath = CurrentDb.TableDefs(tableName).Connect
If InStr(conPath, linkedID) > 0 Then
  GetLinkedPath_FX = GetDBPath_FX(Mid(conPath, Len(linkedID) + 1))
Else
  GetLinkedPath_FX = Null
End If

End Function

For those of you who happened to read Edition 4 of the Smart Access Ezine will notice that the routine Mike Gunderloy provided for finding the database path has been modified slightly below to now include an optional parameter for passing a database file path in a string. In this case the file path would be the connect (link) property of the table definition. E.g.

;DATABASE=C:\MSOFFICE97\OFFICE\Samples\Northwind.mdb

And using the Mid function to strip out the unnecessary ";DATABASE=" substring, we now can return the database path of the table in the backend database using the amended GetDBPath function.

Function GetDBPath_FX(Optional DbPathStr) As String

Dim strPath As String
Dim intLastSlash As Integer

If IsMissing(DbPathStr) Then
  strPath = CurrentDb.NAME
Else
  strPath = DbPathStr
End If

For intLastSlash = Len(strPath) To 1 Step -1
  If Mid(strPath, intLastSlash, 1) = "\" Then
    Exit For
  End If
Next intLastSlash

GetDBPath_FX = left(strPath, intLastSlash)

End Function

Changing Your Report From Softcopy To Hardcopy

The amount of code that you will need to add to your reports to make them save as snapshots is really very small as shown below. And the place to locate the code is always under the Activate event of the Report.

Private Sub Report_Activate()

  Dim snapFile As String, reportOpen As Boolean

  snapFile = SnapDirPath & Me!OrderID & gSnapFileType
  reportOpen = makeSnapShot_FX(Me.NAME, snapFile)

End Sub

Naturally there is more to this than is obvious at first glance. First it is necessary to define the relative path to the snapshot file using the SnapDirPath routine discussed above. Secondly and very importantly, you need will need a unique reference for the file in the same way as a unique key defines a record. This unique reference should allow the report to be easily identified using the file manager as you may wish to open the snapshot reports using the file manager.

The actual generation of the snapshot is handled through a general function called makeSnapShot_FX that can be imported into any Access software database. The first part of the module relates to the way that software will handle the user asking for a replacement report to generated. It does this using the vb Dir function to test that the file actually exists. If it does, then the user is given the option of replacing the report. In certain situations, you may wish to make this replacement happen in a more secure manner.

Public Function makeSnapShot_FX(reportName As String, snapFilePath As String) As Boolean

Dim openNow, buttonStyle
buttonStyle = vbYesNo + vbCritical + vbDefaultButton2

If Len(Dir(snapFilePath)) > 0 Then
  openNow = MsgBox("Are you sure that you want " & _
  "to replace the snapshot", buttonStyle, _
  "File already exists")
Else
  openNow = vbYes
End If

The second section generates the all important report snapshot and as you can see, it is all handled with the OutputTo method of the DoCmd object. You will probably be familiar with this with the output formats of RTF, Excel and text. What you will find unusual though is that you will need to use the constant "Snapshot Format" to export the file to a snapshot. Its not the unusual Integer constant that you might be used to but it works. Also most importantly note that I close the actual report before it opens in preview mode immediately after the snapshot is created.

If openNow = vbYes Then
  DoCmd.OutputTo acOutputReport, reportName, _
   "Snapshot Format", snapFilePath, True

  DoCmd.CLOSE acReport, reportName
  makeSnapShot_FX = True

The final section of the function handles the options when the user has tried to send a report to a snapshot that already exists. The prompts that I have catered for are more suited to a power user as the user can choose either to view the existing snapshot, view the actual report or just cancel. Before adopting this approach, consider if your users should be allowed to replace existing reports. In this case, previewing the document is handled by not doing anything as the report is about to become visible anyway.

Else
  makeSnapShot_FX = False
  buttonStyle = vbYesNoCancel + vbInformation

  openNow = MsgBox("Would you like to see the" & _
   " report as a snapshot document {Yes} " & vbCrLf 
   & vbCrLf & "OR preview the Access Report {No}",
  buttonStyle, "Open or Preview the Document")
  Select Case openNow

    Case vbYes
      Application.FollowHyperlink snapFilePath
      DoCmd.CLOSE acReport, reportName
    Case vbCancel
      DoCmd.CLOSE acReport, reportName
  End Select

End If

End Function

The really neat bit of code that I have used here is to use the "followHyperlink" method of the application object to open the existing snapshot report. This is the method that I now use to open any external file because it opens the file using the program that is associated with that file type (just like file manager). A great example of this is text files which now open in your favourite editor rather than in the notepad application.

And Finally Setting Up The User Interface For Snapshots

The following code is the simplest that you are ever going to read in Smart Access. The code refers to the two buttons at the bottom of the form in Figure 3. The report is opened as follows (with no tricks bar the Order filter).

DoCmd.OpenReport "InvoiceSnapshot", acPreview, , _
"[OrderID]=" & [OrderID]

The "Preview Existing Order" command button actually has no code at all under the button. The use of the hyperlink property of the command button means that the Hard Coded Invoice for the current record will be opened using the hyperlink feature that is programmed using the following code under the the Current event of the form.

cmdViewSnapshot.HyperlinkAddress = SnapDirPath & Me!OrderID & gSnapFileType

Note that there is a lot of other code under the form and in the database that was included in the initial Microsoft sample application. You will not need this to add a snapshot report to your application. Just search the visual basic in the database for Garry Robinson to find the code that I added to the database.

I hope this example of snapshot reporting helps you in the search for the paperless office that has often been promised and rarely delivered.

Garry Robinson

  Your Sample Database Is Called   "SnapshotOrders2000.zip"

Sample database is suited to all versions of Access

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

 

Author Bio.

Garry Robinson runs GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues,  visit his companies popular web site at  http://www.vb123.com/  or sign up for his Access email newsletter by sending a blank email to  tips@vb123.com  The web site features many Access resource sand software  that are used by  more than 10,000 readers a month. To find out about Garry’s book which is called “Real World Microsoft Access Database Protection and Security”, point your browser to http://www.vb123.com/map/. You can find Garry’s contact details at  … www.gr-fx.com