Category Archives: MS Access Forms

MS Access Forms

Enhanced ControlTip for MS Access

I was trying to help in a forum discussion:

Creating A Dynamic Form To Replace Controltiptext?, Access 2013

and finally decided to create a proof of concept of my general idea for an Enhanced ControlTip.

MS Access Enhanced ControlTipOver the years and over the various editions of MS Access I have found ControlTips to be useful, but unreliable.  Under various conditions, they don’t seem to always display, or display promptly and then become frustrating.  So my basic idea was to replace the built-in ControlTip with a dynamic pop-up form which could display my text.

In the attached demo file below I have included three forms, each illustrating a slight twist on the general concept:

  • Display the Custom Control Tip when you move the mouse over each form control (F_Main_MouseMove)
  • Display the Custom Control Tip when each control gets the focus (F_Main_GotFocus)
  • Display the Custom Control Tip when you press a key sequence in each control (F_Main_Shortcut)

Features

By using such an approach we can avoid the native issues experienced with the built-in ControlTip property where it doesn’t display, or there are delays.

But beyond that, this approach offers other advantages, such as:

  • Rich Text so you can format the text being displayed to make it much more appealing and emphasize certain aspects.
  • You can format the underlying form to fit in with your overall database feel.
  • You could add a delay to display the Enhanced ControlTip as you see fit very easily.

At the end of the day, the Enhanced ControlTip is much more customizable and as a developer you can take it wherever you’d like!

Disclaimer/Notes:

If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

All code samples, download samples, links, ... on this site are provided 'AS IS'.

In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.

 

Download

Feel free to download a copy (unlocked accdb) by using the link provided below:

Download “MS Access Enhanced ControlTip” Enhanced_ControlTip_V1.zip – Downloaded 42058 times – 74.89 KB

Taking things Further

The next step would be to allow to possibly implement a WebBrowser Control and use pure HTML instead of RichText because it is more versatile. We could also explore making the pop-up form have a dynamic sizing to adapt to the ControlTip content. We could include a form caption and make it dynamically populated as part of the routine. And much more …

To be continued at a later date.

Adding Attachments to an Access Database

Why You Should Avoid Using Attachment Data Type in Microsoft Access

The Attachment data type, introduced in Access 2007, allows users to insert files which get embedded directly into databases. However, this feature comes with significant drawbacks:

Database Bloat

Using Attachment fields causes rapid database size increase. Each attached file adds its full size to the database, quickly turning a small database into a large one.

Performance Impact

Large databases resulting from Attachment fields can negatively affect performance, slowing down initial load times, queries and overall operations.

Complex Interaction

Programmatically managing Attachment fields requires specialized code, making it challenging to insert, extract, or manipulate attached files.

 
Continue reading

Validate E-mail Addresses

Early in my development career I performed very basic E-mail validation (see: http://www.devhut.net/2010/12/06/vba-validate-email-address/) but as I learnt more about coding, I learnt about the power of Regular Expressions.  Now several years ago I did make a small post on this site regarding Regular Expressions, but did not give any details or true examples to learn from.

So today, I am going to rectify that by providing below a sample E-mail validation function using a Regular Expression.

'---------------------------------------------------------------------------------------
' Procedure : ValidateEmail
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Validate whether or not the submitted e-mail address conforms to basic
'               RFC rules and thus is valide.  It returns True if the e-mail address
'               is valide, otherwise it returns False
' 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:
' ~~~~~~~~~~~~~~~~
' sEmail    : The e-mail address you wish to validate
'
' Usage:
' ~~~~~~
' ValidateEmail("someone@somewhere.com") -> will return True/False
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-09-29              Initial Release
' 2         2019-09-30              Update to fix bug with Null/Blank entries (Damian)
'                                   Update Copyright, Refs, error handling
'---------------------------------------------------------------------------------------
Public Function ValidateEmail(ByVal sEmail As Variant) As Boolean
    On Error GoTo Error_Handler
    Dim oRegEx          As Object
 
    If Not IsNull(sEmail) Then
        Set oRegEx = CreateObject("vbscript.regexp")
        oRegEx.Pattern = "^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$"
        ValidateEmail = oRegEx.Test(sEmail)
    Else
        ValidateEmail = True
    End If
 
Error_Handler_Exit:
    On Error Resume Next
    If Not oRegEx Is Nothing Then Set oRegEx = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ValidateEmail" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

So with this function you can then use the e-mail address’ Before Update event to perform validation similar to:

    If ValidateEmail(Me.EmailAddressControlName) = False Then
        MsgBox "The inputted e-mail does not seem to be a valid e-mail address.", vbCritical + vbOKOnly, "Invalid Entry"
        Cancel = True
    End If

VBA Programming a Form or Report with Intellisense

I was introduced, by fellow MVP Ben Clothier, to a really nice way to code when defining a Form/Report object when referring to an existing form/report so as to gain intellisense for that database object.  You’re probably saying to yourself: “What is he talking about”, but bare with me for just a moment because it will be worth it!

Say you have a form name Form1 with various controls on it, with various properties, …

Up until relatively recently, when I was coding this with reference to a form such a s this I might use code along the lines of:

Public Sub SomeFunctionName()
    Dim frm                   As Access.Form

    Set frm = Forms![Form1].Form
    With frm
        '...
    End With
    Set frm = Nothing
End Sub

Now the above example works just fine, but one has to go back and forth between the actual form and the VBE to copy/paste control names, … to code.

Where it becomes truly fun is that with a very small change in code, one can gain the benfit of intellisense for the designated database object. Observe the following code:

Public Sub SomeFunctionName()
    Dim frm                   As Form_Form1

    Set frm = Forms![Form1].Form
    With frm
        '... Now, frm possesses Intellisense relative to Form1, coding just became much easier!!!
    End With
    Set frm = Nothing
End Sub

by declaring the variable frm as the specific form (As Form_YourFormName) you intend to work with instead of a generic Access.Form, you now gain Intellisense in the VBE for the designated form. Now control names, events, … are a keystroke away. This greatly facilitates coding, reduces typos, …

Similarily for reports you can do something along the lines of:

Public Sub SomeFunctionName()
    Dim rpt                   As Report_Report1

    Set rpt = Reports![Report1].Report
    With rpt
        '... Now, rpt possesses Intellisense relative to Report1
    End With
    Set rpt = Nothing
End Sub

Try it out and see for yourself how it makes your coding life a little bit easier!

Display the Record Count of a Subform on a Main Form

I’ve seen this come up numerous times and for some reason it can cause some headaches the first time you try to set it up. So, I thought I’d simply post a little MS Access Demo Database to show you how it can be done. As it stands right now, the demo includes 3 different approaches that can be employed to display the record count of a given subform on the parent form (or any other location of your choice in reality).

SubForm Record Count

Approach 1

Use DCount() on the sub-form’s record source to determine the record count.

Approach 2

Use a Count() control on the subform and the main form simply reference it to display the value from the subform’s control.

Approach 3

Use VBA to determine the form’s RecordsetClone.RecordCount and display it as the caption value of a label control.

Continue reading

MS Access – VBA – Determine if a Form Exists or Not

I was writing a procedure for a client some time ago that interacted with a series of forms that came from a table, the problem being was that some of the specified forms no longer existed.  When faced with this we have 2 options:

  1. Use error trapping to trap this specific error and move on
  2. Create some function to validate if the form exists before executing the code in the first place

I chose option 2 and set out to create a simple function to do exactly that.  This is what I put together:

'---------------------------------------------------------------------------------------
' Procedure : FrmExist
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine whether or not a form exists in the current database
'             Returns True if it exists, False if it does not
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFrmName    Name of the form to search for
'
' Usage:
' ~~~~~~
' ? FrmExist("frm_Login")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-03-17              Initial Release
'---------------------------------------------------------------------------------------
Function FrmExist(sFrmName As String) As Boolean
    On Error GoTo Error_Handler
    Dim frm                   As Access.AccessObject

    For Each frm In Application.CurrentProject.AllForms
        If sFrmName = frm.Name Then
            FrmExist = True
            Exit For    'We know it exist so let leave, no point continuing
        End If
    Next frm

Error_Handler_Exit:
    On Error Resume Next
    Set frm = Nothing
    Exit Function

Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FrmExist" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Then I could easily perform checks an act accordingly with a single call to my function, doing something like:

If FrmExist("frm_Login") = True Then
    'Do something because the form was found
Else
    'Do something because the form was not found
End if

MS Access – VBA – List Continuous Forms

Once again this is a little procedure I created for my own purposes when trying to review databases so I can quickly identify certain types of forms. In this specific case it is used to list all the continuous forms.

'---------------------------------------------------------------------------------------
' Procedure : ListContinuousFrms
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Identify all the continuous form within a database
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-11-23              Initial Release
'---------------------------------------------------------------------------------------
Public Sub ListContinuousFrms()
    Dim frm                   As AccessObject
    Dim iCounter              As Long

    Application.Echo False
    For Each frm In CurrentProject.AllForms
        DoCmd.OpenForm frm.Name, acDesign
        If Forms(frm.Name).Form.DefaultView = 1 Then
            iCounter = iCounter + 1
            Debug.Print iCounter, frm.Name   '0=Single Form, 1=Continuous Forms, 2=Datasheet, 3=PivotTable, 4=PivotChart, 5=Split Form
        End If
        DoCmd.Close acForm, frm.Name, acSaveNo    'Close and don't save any changes
    Next frm
    Application.Echo True
    Set frm = Nothing
End Sub

As always, don’t forget to implement proper error handling.

I use this procedure, well a variation thereof, to identify which continuous form do not have an Alternate Back Color property set. I also use it to quickly apply various other properties to standardize forms looks, styles …

Finally a Fix for the Images not showing in Forms and Report for Access 2016

It took several months, but Microsoft finally has released an official fix for the issue of images not showing on forms and reports in MS Access 2016.

Microsoft Installer (.msi)-based edition of Office 2016

It can be downloaded from Microsoft’s site at:

https://support.microsoft.com/en-us/kb/3114379

Office 2016 Click-to-Run editions

It took a few extra days, but the fix has finally been ported to the Click-to-Run editions as well.

File -> Account -> Office Updates -> Update Now

I haven’t taken the time to test out either yet, but should it ever fail, you can always try one of the original workarounds proposed in: http://www.devhut.net/ms-access-missing-images-images-not-showing/, but hopefully this will be the end of this dreaded issue.

 

MS Access – Missing Images, Images not showing

UPDATE

There’s finally an official update from Microsoft to fix the problem, see: http://www.devhut.net/2015/12/08/finally-a-fix-for-the-images-not-showing-in-forms-and-report-for-access-2016/ for all the details and links.


There has been a MAJOR screw up and sadly we are seeing countless posts/questions regarding images not showing up in forms/reports.  Sadly, even several months after being flagged, we are still waiting upon an official fix from Microsoft!  🙁

Proposed Solution 1

One proposed solution is to set your database options (File -> Options -> Current Database)

12-5-2015 6-57-31 AM

and then reinsert all your images, but this will still not help if you are working with bmp images, so you’d need to ensure they are of another image format first.

Proposed Solution 2

Another fix has been to simply change the image’s Size Mode to Stretch or Clip and changing the Picture Type to Linked.  In many cases, it was as simple as that.

12-5-2015 7-03-34 AM

Below is the beginning of code to loop through all the forms and reports within a database and switch the properties in question.  The issue being that one way or another, you will still need to manually intervene since by switching them from Embedded to Linked, you will need to supply a path/filename.

'---------------------------------------------------------------------------------------
' Procedure : FixImages
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Try to start fixing MS' mess with images
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015-11-30              Initial Release
'---------------------------------------------------------------------------------------
Sub FixImages()
    On Error GoTo Error_Handler
    Dim DbO                   As AccessObject
    Dim DbP                   As Object
    Dim frm                   As Access.Form
    Dim rpt                   As Access.Report
    Dim ctl                   As Access.Control

    Set DbP = Application.CurrentProject

    'Review/Fix Forms
    Debug.Print "Processing Forms"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllForms
        Debug.Print vbTab & DbO.Name
        DoCmd.OpenForm DbO.Name, acDesign    'Open the form in design view so we can work with it
        Set frm = Forms(DbO.Name)
        With frm
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = acImage Then    'If an image control is found then apply our settings
                    Debug.Print vbTab & vbTab & ctl.Name
                    ctl.SizeMode = acOLESizeClip    'could also be acOLESizeStretch - https://msdn.microsoft.com/en-us/library/office/ff837281(v=office.15).aspx
                    ctl.PictureType = 1    '0 would be embedded - https://msdn.microsoft.com/en-us/library/office/ff197027(v=office.15).aspx
                End If
            Next
        End With
        DoCmd.Close acForm, DbO.Name, acSaveYes    'Close and save our changes
    Next DbO

    'Review/Fix Reports
    Debug.Print "Processing Reports"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllReports
        Debug.Print vbTab & DbO.Name
        DoCmd.OpenReport DbO.Name, acDesign    'Open the report in design view so we can work with it
        Set rpt = Reports(DbO.Name)
        With rpt
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = acImage Then    'If an image control is found then apply our settings
                    Debug.Print vbTab & vbTab & ctl.Name
                    ctl.SizeMode = acOLESizeClip    'could also be acOLESizeStretch - https://msdn.microsoft.com/en-us/library/office/ff837281(v=office.15).aspx
                    ctl.PictureType = 1    '0 would be embedded - https://msdn.microsoft.com/en-us/library/office/ff197027(v=office.15).aspx
                End If
            Next
        End With
        DoCmd.Close acReport, DbO.Name, acSaveYes    'Close and save our changes
    Next DbO
    
    Debug.Print "Operation Complete!"

Error_Handler_Exit:
    On Error Resume Next
    Set ctl = Nothing
    Set frm = Nothing
    Set rpt = Nothing
    Set DbO = Nothing
    Set DbP = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FixImages" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

What might also be useful is a simple procedure to quickly identify the problematic forms and reports. Below is such a procedure, it will loop through all the forms and reports looking for the specified control type; in our case acImage.

'---------------------------------------------------------------------------------------
' Procedure : IdentifyImageObjects
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Identify Forms/Reports with the specified control type
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Usage     : Call IdentifyImageObjects(acImage)
'           : Call IdentifyImageObjects(acLabel)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015-11-30              Initial Release
'---------------------------------------------------------------------------------------
Sub IdentifyImageObjects(sCtlType As AcControlType, Optional bLeaveOpen As Boolean = True)
    On Error GoTo Error_Handler
    Dim DbO                   As AccessObject
    Dim DbP                   As Object
    Dim frm                   As Access.Form
    Dim rpt                   As Access.Report
    Dim ctl                   As Access.Control
    Dim bCtlTypeFound         As Boolean

    Set DbP = Application.CurrentProject

    'Review/Fix Forms
    Debug.Print "Processing Forms"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllForms
        bCtlTypeFound = False
        DoCmd.OpenForm DbO.Name, acDesign    'Open the form in design view so we can work with it
        Set frm = Forms(DbO.Name)
        With frm
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = sCtlType Then    'If an image control is found then apply our settings
                    bCtlTypeFound = True
                    Exit For    'Stop the loop once we find 1 instance of the Control Type we are searching for
                End If
            Next
        End With
        If bCtlTypeFound = True Then
            Debug.Print vbTab & DbO.Name
            If bLeaveOpen = False Then DoCmd.Close acForm, DbO.Name, acSaveNo
        Else
            DoCmd.Close acForm, DbO.Name, acSaveNo
        End If
    Next DbO

    'Review/Fix Reports
    Debug.Print "Processing Reports"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllReports
        bCtlTypeFound = False
        DoCmd.OpenReport DbO.Name, acDesign    'Open the report in design view so we can work with it
        Set rpt = Reports(DbO.Name)
        With rpt
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = sCtlType Then    'If an image control is found then apply our settings
                    bCtlTypeFound = True
                    Exit For    'Stop the loop once we find 1 instance of the Control Type we are searching for
                End If
            Next
        End With
        If bCtlTypeFound = True Then
            Debug.Print vbTab & DbO.Name
            If bLeaveOpen = False Then DoCmd.Close acReport, DbO.Name, acSaveNo
        Else
            DoCmd.Close acReport, DbO.Name, acSaveNo
        End If
    Next DbO

    Debug.Print "Operation Complete!"

Error_Handler_Exit:
    On Error Resume Next
    Set ctl = Nothing
    Set frm = Nothing
    Set rpt = Nothing
    Set DbO = Nothing
    Set DbP = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: IdentifyImageObjects" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Proposed Solution 3

I also found an interesting statement (taken from: http://answers.microsoft.com/en-us/office/forum/office_365hp-access/embedded-images-disappeared-after-upgrade-to/2c84727f-89e8-48f8-9096-eb330379d4c6?page=1) in which user sLaeYa says:

If you export your Form:

  • Right Click on the Form in Navigation Pane
  • Select Export to XML File
  • Choose Destination
  • Select Data and Presentation

In the destination folder there is an images folder, you will find that the names of your main embedded image has changed to “Form_Name.BMP”

If you change the file name to the new one it starts working again.

I cannot confirm or deny this claim as I haven’t had the time to test it out yet.  Feel free to leave a comment should you have any more relevant information on the subject.

MS Access – VBA – Close All Open Forms

In some cases it may be useful to close all the forms within a database.

Why you might ask, one example would be to ensure you close all connection to the back-end prior to working with it.

'---------------------------------------------------------------------------------------
' Procedure : CloseAllOpenForms
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Close all the currently open Forms in the database
' 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:
' ~~~~~~
' ? CloseAllOpenForms
'   Returns -> True     => Closed all Forms successfully
'              False    => A problem occurred
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015-02-17              Initial Release
' 2         2023-02-09              Update variable naming, Error handler, copyright
' 3         2023-03-13              Changed approach as For each frm in Forms skips
'                                       objects
'---------------------------------------------------------------------------------------
Function CloseAllOpenForms() As Boolean
    On Error GoTo Error_Handler
    Dim i As Long

    For i = Application.Forms.Count - 1 To 0 Step -1
        DoCmd.Close acForm, Forms(i).Name, acSaveNo
    Next i
    
    CloseAllOpenForms = True

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

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

Then you can simply call it by doing

Call CloseAllOpenForms

OR

If CloseAllOpenForms Then
    'All Forms were successfully closed
    'Your code goes here
End if