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 →

majicJack VOIP Non-Secure Signup!

I am in the process of getting rid of BELL as my phone supplier as the service, especially customer service, is simply garbage and their fees are ridiculous for what you get. So I’ve been exploring VOIP and over my vacation this summer I was able to tryout the majicJack first hand and was truly impressed.

So, back in Montreal, I went out and bought one, set it up and then went to register the account to get it operational. I went through screen after screen very quickly (the setup it very straightforward) and then selected an option to get a Canadian phone number rather than the default US number, then filled out another screen or two and went to finalize everything by paying the supplementary fee for the Canadian number and stopped dead in my tracks when I noticed that they where going to collect my credit card information over an unencrypted connection! No SSL! No HTTPS! Just regular HTTP, so everyone on the internet can grab my information!

I did notify them through their online chat, but I have no clue if it is being taken seriously.

For me, I’m using the default US number to test it out for 1 month, then I will decide if I’m keeping the unit or not. If I decide I am, I will attempt to get a Canadian number again, if there is no HTTPS then, then I will be returning the whole thing one way or another. You’d have to be nuts to provide any confidential information over an unsecured connection this day in age.

The Evolution of MS Access

I was having a discussion with fellow MVPs regarding the Linked Table Manager and its limitations and how IMHO Access truly hasn’t evolved in a long, long time.

Fellow MVP, Luke Chung, kindly enumerated a few improvements in recent versions, such as:

Reports to PDF
Totals row on datasheets
Filtering UI at the field and field header level including multi-value selects
Filterable Report View
Control anchoring/resizing in form design
Tabbed views
Navigation Pane
Search bar (Navigation Pane)
Date picker
Image controls with control source

My thoughts on the subject

It’s just that many improvements I find were possible long before it was incorporated by Microsoft, so I didn’t find it truly moved the application forward (generating PDFs, calendar, …). Thankfully, MVPs (long before my time) were kind enough to have provided solutions to many of these issues.

Then even when they did incorporate a solution, it was half-baked: Calendar, Ribbon, …

Take the calendar for instance. You can’t interact with it in VBA in any manner. It only goes month by month. There is no Time component. Why would they not have turned to Allen Browne, Stephen Lebans et al. and used one of their much better solutions (at the very least inspired themselves from them). UA also has a great Time Pieces sample. Why put in place such a limited tool thus making us implement something else anyways. Now, the first thing I do is run code to disable the date picker on any db I take over and put in place one of the above.

Take the Ribbon, ignoring the real-estate it uses in comparison to the old Command bars, they never offered a tool to develop it. Don’t get me wrong, Gunter’s app is great, but why should you need a 3rd party tools to create a menu for your database? Do you remember how easy it was to create Command Bars!

The attachment data type, really!? This is getting more people into trouble than anything else. Access simply was never meant to store external items.

Navigation Custom Grouping. There is no way to export the custom groupings and I’ve experienced it when all the Groups just vanished into thin air!? You don’t want to know how much time it took to recreate everything. Even then, You can’t rename object when in Custom Group mode, so you have to switch back to normal object mode, rename it, then return to Custom mode. Talk about turning in circles.

Multi-Value Fields! Enough said.

And on and on …

A lot of good intentions, but I just find they are incomplete. They start down a path but never properly complete the voyage.

Don’t get me wrong, I too appreciate the Report to PDF (although Stephen Lebans had resolved that issue long ago!), and I have a complete love/hate relationship with the Ribbon, but at the end of the day, I feel their (Access Dev Team’s) energies were improperly focused and focused on the wrong items in many instances. I would have much preferred seeing improvements to:

Linked Table Manager (scrollbar to see the full path, sortable, …)
SQL Editor (in dyer need to improvements, heck Notepad is more advanced!)
Charting (another element in dyer need of improvement)
VBE (this is another aspect that hasn’t moved forward in 20+ years and that could be improved in numerous ways, just look at SmartIndenter, Mz-Tools and the likes.)
Improved Packaging Wizard instead of eliminating it
Improved shortcut menus (now it’s a mess!)

Your Turn to Talk

So this post is to ask you, what are your thoughts on how Access has evolved?
Has it evolved?
Are you happy with where it is, compared to where it was, compared to where it could/should be 30 years later?
Are you happy with the improvements that were made or were there much more pressing area that needed attention and that were ignored?

There are no right or wrong answers, just curious what people think.

VBA – Automating Excel

One thing that any experienced Access developer will learn is that Automating Excel is an essential part of providing good reporting in Access. Although Access has great reporting tools for grouping/organizing data, it is horrible when it comes to charting when compared to Excel (there is simply no comparison). Throw into the mix that there are numerous cases where end-user would like to be able to perform filters … and you don’t want them playing around with the raw data, so exporting it to Excel makes everyone happy.

What one will also learn is that Access does provide a few techniques/tools to export tables/queries to Excel, but once again they are very primitive and lack some serious refinement.

So what can you do to Export data to Excel while enabling you, the developer, better control over the process and final output? It’s simple, automate the process yourself instead of relying on Access to do it for you using Excel Automation.

Below I will elaborate a basic framework of reusable functions that can simplify Excel automation and then I will give you a concrete example of its usage.

Typically, I create 2 standard modules: (i) Constants Declarations, (ii) framework of reusable functions, but nothing stops you from putting them all together in one module if you prefer.

The Constants Declaration

Obviously, you can add/remove constants as required by your specific needs, this is just an example of some of the common constants used.

Option Compare Database
Option Explicit

'Excel Constants used in various Excel functions, ...
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Const xlAutomatic = -4105
Public Const xlDiagonalDown = 5
Public Const xlDiagonalUp = 6
Public Const xlEdgeBottom = 9
Public Const xlEdgeLeft = 7
Public Const xlEdgeRight = 10
Public Const xlEdgeTop = 8
Public Const xlInsideHorizontal = 12
Public Const xlInsideVertical = 11
Public Const xlNone = -4142
Public Const xlContinuous = 1
Public Const xlDouble = -4119
Public Const xlExpression = 2
Public Const xlSolid = 1
Public Const xlThick = 4
Public Const xlThin = 2
Public Const xlUp = -4162
Public Const xlThemeColorDark1 = 1
Public Const xlThemeColorDark2 = 3
Public Const xlSortOnValues = 0
Public Const xlAscending = 1
Public Const xlSortNormal = 0
Public Const xlYes = 1
Public Const xlTopToBottom = 1
Public Const xlPinYin = 1
Public Const xlThemeFontMinor = 2
Public Const xlLandscape = 2
Public Const xlPortrait = 1
Public Const xlValues = -4163
Public Const xlPart = 2
Public Const xlByRows = 1
Public Const xlByColumns = 2
Public Const xlNext = 1
Public Const xlPrevious = 2
Public Const xlPie = 5
Public Const xlUnderlineStyleSingle = 2
Public Const xlUnderlineStyleNone = -4142
Public Const xlCenter = -4108
Public Const xlBottom = -4107
Public Const xlTop = -4160
Public Const xlContext = -5002

The basic framework of reusable functions

Option Compare Database
Option Explicit

Private Const sModName = "mod_MSExcel" 'For Error Handling

Public oExcel           As Object    'Excel Application Object
Public oExcelWrkBk      As Object    'Excel Workbook Object
Public oExcelWrSht      As Object    'ExcelWorksheet Object
Public bExcelOpened     As Boolean    'Was Excel already open or not


Public Sub LaunchExcel(Optional bVisible As Boolean = True)
On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
    
    If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oExcel = CreateObject("Excel.Application")
        bExcelOpened = False
    Else    'Excel was already running
        bExcelOpened = True
    End If

On Error GoTo Error_Handler
    oExcel.Visible = bVisible

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: LaunchExcel" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Sub AddExcelWrkBk(Optional ByVal sTmplt As String = "")
'Create a new workbook based on a template file
    On Error GoTo Error_Handler

    If sTmplt = "" Then
        Set oExcelWrkBk = oExcel.Workbooks.Add()
    Else
        'Technically should test for the existance of the file before trying to use it
        Set oExcelWrkBk = oExcel.Workbooks.Add(sTmplt)
    End If

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: AddExcelWrkBk" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Sub OpenExcelWrkBk(ByVal sWrkBk As String, Optional sPwd As Variant)
'Open an existing Excel Workbook
    On Error GoTo Error_Handler

    If IsMissing(sPwd) Then
        Set oExcelWrkBk = oExcel.Workbooks.Open(sWrkBk)
    Else
        Set oExcelWrkBk = oExcel.Workbooks.Open(sWrkBk, , , , sPwd)
    End If

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: OpenExcelWrkBk" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Sub CloseExcel(Optional bCleanupOnly As Boolean = True)
On Error GoTo Error_Handler
    
    If bCleanupOnly = False And bExcelOpened = False Then
        'oExcelWrSht.Close False
        'oExcel.ActiveWorkbook.Close False
        oExcel.Quit
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    Set oExcel = Nothing
    Exit Sub

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

Demo

Now let put it all together for a moment to see how it all works in one function to export a query. So let Rewrite my Export Records to Excel post using this reusable framework.

Function Export2Excel(ByVal sQuery As String)
    On Error GoTo Error_Handler
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim iCols           As Integer
    Dim iRows           As Integer
 
    Call LaunchExcel 'Start Excel
    oExcel.ScreenUpdating = False
    oExcel.Visible = False   'Keep Excel hidden until we are done with our manipulation
    Call AddExcelWrkBk    'Start a new workbook
    Set oExcelWrSht = oExcelWrkBk.Sheets(1)
 
    'Open our SQL Statement, Table, Query
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sQuery, dbOpenSnapshot)
    With rs
        If .RecordCount <> 0 Then
            'Get a proper record count
            .MoveLast
            iRows = .RecordCount
            .MoveFirst
            'Build our Header
            For iCols = 0 To rs.Fields.Count - 1
                oExcelWrSht.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
            Next
            'Do some basic formatting
            With oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
                                   oExcelWrSht.Cells(1, rs.Fields.Count))
                .Font.Bold = True
                .Font.ColorIndex = 2
                .Interior.ColorIndex = 1
                .horizontalAlignment = xlCenter
            End With
            oExcelWrSht.Range("A2").CopyFromRecordset rs 'Copy the data from our query into Excel
            oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
                              oExcelWrSht.Cells(iRows, rs.Fields.Count)).Columns.AutoFit    'Resize our Columns
            oExcelWrSht.Range("A1").Select  'Return to the top of the page
        Else
            MsgBox "There are no records returned by the specified queries/SQL statement.", vbCritical + vbOKOnly, "No data to generate an Excel spreadsheet with"
            GoTo Error_Handler_Exit
        End If
    End With
    
    'At this point you could save it and close it, or in this instance we're going to leave it open for our users to interact with it.

Error_Handler_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    oExcel.Visible = True   'Make excel visible to the user
    oExcel.ScreenUpdating = True
    Call CloseExcel
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Export2Excel" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

What else can be done

Pretty much anything.

Above is a simplistic example. I have reusable functions to apply borders the same way, I have reusable function to create charts, … this way with a single call I can automate complex tasks and simplify my overall code and provide me with just one location to edit should the need arise.

MS Access – VBA – Import Listing of Sub-Directories Into A Table

I was recently asked how one could modify the code found in my MS Access – VBA – Import Directory Listing Into A Table post to get a listing of sub-directories/folders instead of a listing of files. The solution is pretty straight forward, as you can see for yourself below:

'---------------------------------------------------------------------------------------
' Procedure : ListSubDirectories
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Create a listing of directories within the specified directory and
'               append them to a table
' 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:
' ~~~~~~~~~~~~~~~~
' sDirectory: Full path of the root directory to extract a listing of subdirectories
'               from including trailing \
'
' Usage:
' ~~~~~~
' Call ListSubDirectories("c:\")
' Call ListSubDirectories("c:\users\")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-07-06              Initial Release - Answer website question
'---------------------------------------------------------------------------------------
Public Sub ListSubDirectories(sDirectory As String)
    On Error GoTo Error_Handler
    Dim db                    As Database
    Dim sSQL                  As String
    Dim MyFolder              As String

    Set db = CurrentDb

    MyFolder = Dir$(sDirectory & "*", vbDirectory)
    Do While MyFolder <> ""
        If GetAttr(sDirectory & MyFolder) And vbDirectory Then
            Debug.Print MyFolder
            sSQL = "INSERT INTO [YourTableName] (YourTableFieldName) VALUES('" & MyFolder & "');"
            db.Execute sSQL, dbFailOnError
            'dbs.RecordsAffected 'could be used to validate that the
            'query actually worked
        End If
        MyFolder = Dir$
    Loop

Error_Handler_Exit:
    On Error Resume Next
    Set db = Nothing
    Exit Sub

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

I hope this helps someone!

PHP – MPDF – Displaying Foreign Characters Properly

I was performing some work to generate PDFs for a client from a system that could have multiple languages (primarily English and French). Now out of the box, MPDF worked perfectly with English content, but when I tried generating PDFs with French content I would get blanks or ?.

Once again, I will skip over all the various things I tried, but the final solution (in my case at least) was to used the iconv() function to convert the string encoding so MPDF was happy with it.

So, my code became:

$PDFContent= iconv("cp1252", "UTF-8", $PDFContent);

Now combine that with my issue from my previous post PHP – MPDF – HTML contains invalid UTF-8 character(s) and we get:

$PDFContent = iconv("cp1252", "UTF-8", $PDFContent);
$PDFContent = mb_convert_encoding($PDFContent, 'UTF-8', 'UTF-8');
$mpdf->WriteHTML($PDFContent);

So now, with 2 lines of code, we deal with the error and ensured that French accents get handled properly when generating PDFs. You have to love MPDF and PHP!

Upgrade mdb file format to accdb?

Based upon a recent discussion between Access MVPs, I thought it could be valuable information to share with the Access community at large. Below is the summary of our discussion on the subject.

Developers with older database which were created using the mdb file format often wonder if they should be upgrading to the newer accdb file format.

I do believe, as a general rule, the old adage “Do not fix what isn’t broken”. That being said, 2013+ no longer support the ’97 and prior versions of mdb and no one knows for how much longer Microsoft will continue to support the latter version of the mdb file format. Since the accdb file format has been around now for also a decade, it might be a good time to consider it.

So let’s ponder the question for a minute and see if there are any compelling reasons to upgrade file formats.

Continue reading →

PHP – MPDF – HTML contains invalid UTF-8 character(s)

I was doing some PHP work for a client which involved using MPDF to generate a PDF on the fly. On my test server, everything worked perfectly. Yet, when I ran the code on the production server, I kept getting:

HTML contains invalid UTF-8 character(s)

I won’t bore you with the various iterations I tried, but the final solution was to insert

$PDFContent = mb_convert_encoding($PDFContent, 'UTF-8', 'UTF-8');

prior to actually writing the content to the PDF file. So you code becomes something along the lines of:

$PDFContent = mb_convert_encoding($PDFContent, 'UTF-8', 'UTF-8');
$mpdf->WriteHTML($PDFContent);