MS Access – Print Individual PDFs of a Report

Here’s another common request.

Say you have a contacts report and like to print each client’s individual report separately as a PDF. How can this be done exactly? A concrete example might be a billing database in which you have an Invoice report (which would be comprised of all the records) and you’d like to print each invoice separately. Or just the outstanding invoices… you get the idea.

Generating PDFs on the Hard Drive

Approach 1 – Individual Reports

The General Concept

The general concept is pretty straightforward:

  1. Defined a recordsource that you’re going to use to filter the report to get those records you seek
  2. Open the report filtered based on the above
  3. Print the report as a PDF
  4. Close the report
  5. Repeat for each record in 1

Continue reading →

VBA – Is PC Accessible? Can The Server/PC/… be PINGed?

Doorbell

A recent discussion between MVPs made me retrieve an old piece of code I had and thought it might serves others.

Certain code can rely on external servers/PC/… (think of table relinking, external attachments, …) and if they fail to connect, we often get errors that do not properly reflect the real issue. As such, it make sense, prior to trying to work with an external component, that we first validate that we can communicate with it. In networks where PINGing is enabled, the following function will permit you to determine if the component is accessible or not.

'---------------------------------------------------------------------------------------
' Procedure : PC_IsAccessible
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine if a PC is reacheable by validating if it can be PINGed
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
' Refs      : https://docs.microsoft.com/en-us/previous-versions/windows/desktop/wmipicmp/win32-pingstatus
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sHost     : IP address or Name of the PC to ping against
'
' Output
' ~~~~~~
' The function returns a boolean value
'   True    = PC could be reached successfully
'   False   = PC could not be reached
'
' Usage:
' ~~~~~~
' PC_IsAccessible("192.168.0.1")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-01-11              Initial Release
' 2         2019-05-05              Header Updated
'                                   Code cleanup
'                                   Error Handler Updated
'---------------------------------------------------------------------------------------
Function PC_IsAccessible(sHost As String) As Boolean
    On Error GoTo Error_Handler
    Dim oWMI                  As Object
    Dim oPingStatuses         As Object
    Dim oPingStatus           As Object
    Dim sSQL                  As String

    Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
    sSQL = "SELECT * FROM Win32_PingStatus WHERE Address='" & sHost & "'"
    Set oPingStatuses = oWMI.ExecQuery(sSQL)
    For Each oPingStatus In oPingStatuses
        If oPingStatus.StatusCode = 0 Then
            PC_IsAccessible = True
            Exit For
        End If
    Next

Error_Handler_Exit:
    On Error Resume Next
    If Not oPingStatus Is Nothing Then Set oPingStatus = Nothing
    If Not oPingStatuses Is Nothing Then Set oPingStatuses = Nothing
    If Not oWMI Is Nothing Then Set oWMI = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: PC_IsAccessible" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

VBA- Beware of Flawed Math

I thought I’d pass along a bug brought to my attention by ‘Jason’ regarding an issue with using the Int() function.

Now there has always been an issue with calculations due to floating point issues, which I’m not going to even attempt to get into in this post. There are far greater minds that have elaborated on this subject in the past. That said, whenever you need to perform any sort of calculation, you are best to always use currency or decimal data types.

Back to today’s post, the issue with the Int() function. Int() is used to return the whole number part from a number. So,

? Int(1)
 1 
? Int(5.25)
 5 
? Int(13.65)
 13

The Flaw!

In an Access Query

In a query
SELECT 40*0.3 AS Expr1, Int(12) AS Expr2, Int(40*0.3) AS Expr3;
Results in
12 | 12 | 12

In an Excel WorkSheet

=INT(40*0.3)
Results in
12

BUT In VBA (the bug)

Continue reading →

Determine the Extension of the Back-End File

I was revisiting some old code that I use to perform a compact and repair to keep things optimized for some of my clients and thought I’d share a useful function which I use to determine whether the file extension of a linked-table back-end file so I can name the compacted file appropriately.

I sometime obfuscate Access database files by switching the file extension and this enable me to create more versatile code that can handle such situations. Obviously, this is also useful in situation in which you may have both mdb and accdb files, so you can distinguish them from one another.

Not really rocket science, just extract the table definition connection and parse out the extension, but here’s a function so you don’t need to reinvent the wheel.

Continue reading →

Access – How to Purchase Standalone Version and Not Office 365

This is another thread I see come up from time to time in various forum; people wanting to buy Access, but being unable to figure out Microsoft’s website to actually do so!

Microsoft has gone out of their way so you basically can never find a direct link. They want everyone on Office 365! Even when you try to go on Access page and then click buy, you get pushed to Office 365.

Searching their site is no better, you don’t find it. Access doesn’t even show up in it’s list of products, even when you take the time to go on their “See all Office products” page. Access is simply not important to Microsoft, not enough to include in the list, not important enough to include on their “complete” listing page.

Continue reading →

An Expert Amongst Us, Congratulation Gustav Brock!

Experts-Exchange Distinguished Expert
I wanted to officially congratulate fellow Access MVP Gustav Brock for his recent (as in today, 2019-03-12) Experts-Exchange Distinguished Expert award. Gustav is simply brilliant, but more importantly, he freely shares this knowledge with us all on a daily basis and helps us all grow!

If you haven’t read any of his articles, I’d recommend you do, refer to https://www.experts-exchange.com/searchResults.jsp?searchTerms=Gustav+Brock&rtid=30&searchSubmit= or check out his GitHub account where he’s shared a few Gems, see https://github.com/GustavBrock.

Congratulations Gustav and Thank you for everything you do to help us all!

VBA – Turn Off The Monitor

Once again, helping someone out in a forum with a question, I put together the following to turnoff the PC monitors. It turn all the monitors off. As per the usual, I’m posting it here should it be able to help someone else out.

'32-bit declaration
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, _
                                                                        ByVal wMsg As Long, _
                                                                        ByVal wParam As Long, _
                                                                        ByVal lParam As Any) As Long
'64-bit declaration
'Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As LongPtr, _
'                                                                                ByVal wMsg As Long, _
'                                                                                ByVal wParam As LongPtr, _
'                                                                                lParam As Any) As LongPtr

'---------------------------------------------------------------------------------------
' Procedure : MonitorPower
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Turn On/Off the monitor
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' Req'd     : SendMessage API Declaration
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bMontiorOn: True -> Turn monitor on
'             False -> Turn monitor off
'
' Usage:
' ~~~~~~
' Call MonitorPower
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2018-12-14              Initial Release (Forum help)
'---------------------------------------------------------------------------------------
Public Function MonitorPower(Optional bMontiorOn As Boolean = False)
'REF: https://docs.microsoft.com/en-us/windows/desktop/menurc/wm-syscommand
    Const WM_SYSCOMMAND = &H112
    Const SC_MONITORPOWER = &HF170&
    Const MONITOR_ON = -1&
    Const MONITOR_OFF = 2&

    On Error GoTo Error_Handler

    If bMontiorOn = False Then
        SendMessage Application.hWndAccessApp, WM_SYSCOMMAND, SC_MONITORPOWER, MONITOR_OFF
    Else
        SendMessage Application.hWndAccessApp, WM_SYSCOMMAND, SC_MONITORPOWER, MONITOR_ON
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: MonitorPower" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Also note, that the screen will turn back on when the user moves the mouse or touches the keyboard making MonitorPower(True) somewhat unnecessary, but it can still be useful if used through code. It does not activate the screensaver, nor does it lock the PC. The only thing it does is power off the screen, so the screen goes black until the PC receives some form of input from the user, just like what happens through the power saving settings of a PC.

VBA – Secure an Excel Workbook

While trying to help someone out in a forum, I developed the following procedure that can be called to secure an Excel workbook with a password, which will be required by users when trying to open the file, and thought it might be of use to other.

Continue reading →