Access – VBA – Complex String Parsing / Parsing OpenArgs and Tags

It can be very helpful at times to be able to use a form/report’s OpenArgs or a control’s Tag property to pass along settings, say a control’s visibility.

So you could set a control’s tag = “True” and then apply this “setting” by simply doing

Me.ControlName.Visible = Me.Tag

In a more complex example tag = “visible=True” and then your code would like

If (InStr(Me.Tag, "visible") > 0 Then Me.ControlName.Visible = Replace(Me.Tag, "visible=", "")

What About Handling Multiple Settings?

That why I’m writing today. I’ve seen the question numerous times and it all just comes down to 2 things:

  • Following an input standard when create the tag/openargs/…
  • Creating a simple parsing function

Both are illustrated in my function below

Continue reading →

Access – Minimize/Maximize Navigation Pane/Shutter Bar

Ever wanted to minimize/close the Navigation Pane/Shutter Bar? Not hide it completely, just make it minimize/close/collapse. Below is a simple sub that enables you to do so.

'---------------------------------------------------------------------------------------
' Procedure : Nav_Collapse
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Close/Minimize the main navigation pane/shutter bar
' 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
'
' Usage:
' ~~~~~~
' Call Nav_Collapse
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2019-01-11              Initial Release
'---------------------------------------------------------------------------------------
Public Sub Nav_Collapse()
    On Error GoTo Error_Handler

    DoCmd.SelectObject acModule, , True
    DoCmd.Minimize

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Nav_Collapse" & 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 Sub

Continue reading →

Access 365 Runtime Released

For those that had been waiting, Access 365 Runtime is available for download. Refer to Download and install Office 365 Access Runtime and remember 32-bit is still the recommended version to use!

Please note that this is intended for Office365 users who’s subscription does not include or did not install Access. It is not to be used by Office 2019 users.

Access – Let’s Talk Colors

Just a quick post to discuss colors in Access.

Colors can be an asset to designers, if used correctly, to greatly enhance your application(s), no doubt!

But how can we define colors is the question I thought I’d quickly touch upon today.

All too often I see in code people defining thing along the lines of

'Set the font color of a control
Me.ControlName.ForeColor = vbRed

where developer use one of the predefined ColorConstants, which include:

  • vbBlack
  • vbBlue
  • vbCyan
  • vbGreen
  • vbMagenta
  • vbRed
  • vbWhite
  • vbYellow

There are 2 major issues with the above (excluding the use of vbBlack and vbWhite): the choices are very limited and the colors are very aggressive (not colors I’d recommend to use as they are hard to look at, so not a good user experience).

Continue reading →

VBA – Move a File from one Directory to Another

Here is a function to move a file from one folder to another.

'---------------------------------------------------------------------------------------
' Procedure : FSO_File_Move
' Author    : Daniel Pineault, CARDA Consultants Inc.
'                Based off of an original Sub (moveCurrent) by Hans Vogelaar
' Website   : http://www.cardaconsultants.com
' Purpose   : Move a file from one directory to another
' 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
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile         : File with extension to move
' sPathSource   : Path where the file is currently housed (Source path)
' sPathDest     : Path where you would like to move the file to (Destination path)
' bAutomaticOverwrite : If the file already exists in the Destination folder, should
'                           it automatically be overwritten?
' bDisplayErrMsg: Whether or not the function should display errors or silently fail
'
' Usage:
' ~~~~~~
' FSO_File_Move("vbs.vbs", "C:\Test\From\", "C:\Test\To\")
' FSO_File_Move("vbs.vbs", "C:\Test\From\", "C:\Test\To\", False, False)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015-11-28              Initial Release
' 2         2019-01-07              Added function header
'                                   Updated error handler
'                                   Added bDisplayErrMsg input variable
' 3         2021-11-28              Name bug fix thanks to Blair Baker
'---------------------------------------------------------------------------------------
Public Function FSO_File_Move(ByVal sFile As String, _
                             ByVal sPathSource As String, _
                             ByVal sPathDest As String, _
                             Optional bAutomaticOverwrite As Boolean = True, _
                             Optional bDisplayErrMsg As Boolean = True) As Boolean
    On Error GoTo Error_Handler
    Dim oFSO                  As Object
    Dim sSourceFile           As String
    Dim sDestFile             As String
    Dim sMsg                  As String
    Dim answer                As Integer

    'Ensure properly formatted paths were supplied, adjust as req'd
    If Right(sPathSource, 1) <> "\" Then sPathSource = sPathSource & "\"
    If Right(sPathDest, 1) <> "\" Then sPathDest = sPathDest & "\"
    'Build fully qualified path\filenames for the move process
    sSourceFile = sPathSource & sFile
    sDestFile = sPathDest & sFile

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    'Ensure the supplied paths (Source/Destination) actually exist
    If oFSO.FolderExists(sPathSource) = False Then
        If bDisplayErrMsg = True Then
            sMsg = "Source path '" & sPathSource & "' does not exist."
            Debug.Print sMsg
            Call MsgBox(sMsg, vbInformation Or vbOKOnly, "FSO_File_Move Error")
        End If
        FSO_File_Move = False
        GoTo Error_Handler_Exit
    End If
    If oFSO.FolderExists(sPathDest) = False Then
        If bDisplayErrMsg = True Then
            sMsg = "Destination path '" & sPathDest & "' does not exist."
            Debug.Print sMsg
            Call MsgBox(sMsg, vbInformation Or vbOKOnly, "FSO_File_Move Error")
        End If
        FSO_File_Move = False
        GoTo Error_Handler_Exit
    End If
    'Ensure the File to be copied actually exists
    If oFSO.FileExists(sSourceFile) = False Then
        If bDisplayErrMsg = True Then
            sMsg = "Source file '" & sSourceFile & "' does not exist."
            Debug.Print sMsg
            Call MsgBox(sMsg, vbInformation Or vbOKOnly, "FSO_File_Move Error")
        End If
        FSO_File_Move = False
        GoTo Error_Handler_Exit
    End If

    'Check to see if the file already exist in the Destination folder
    If oFSO.FileExists(sDestFile) = True Then
        If bAutomaticOverwrite = False Then
            GoTo Error_Handler_Exit
        Else
            Kill sDestFile
        End If
    End If
    oFSO.MoveFile sSourceFile, sDestFile
    FSO_File_Move = True

Error_Handler_Exit:
    On Error Resume Next
    If Not oFSO Is Nothing Then Set oFSO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FSO_File_Move" & 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

Access – VBE – Retrieve Procedure Text

Here’s a bit of an odd ball, but I was helping out in a forum discussion in which the user needed to be able to display the code behind procedures. He was trying to create some type of teaching tool.

Now there are different ways to approach such a thing and he could have copy/pasted each procedure into a table, but that creates duplication (which is never a good thing), creates maintenance since he’ll have to update entries as code is change, …

I found the question intriguing and set out to find a way to simply read the information from the VBA project. Building on some of my other VBE code, below is my solution

Continue reading →

MS Access – Can Shrink Not Working

If you’ve ever created a report and set a control’s Can Shrink property to Yes and then ran the report only for the item to be displayed with no data present then this post is for you.

The entire issue lies not with the control, but with it’s label. By having a label, Access assumes that it should be displayed regardless of the associated text control’s value. Therefore, Control with label = always display.

Continue reading →