vrijdag 18 september 2015

Access: Smart Object Names Combined With Smart VBA Functions

Introduction

When I create object in Access I try to be as consistent as possible. For instance, when I create a table containing customers, I create names like this:

Object
Name
Table
tblCustomers
Form
frmCustomers
Query
qryCustomers
Command button
cmdCustomers

It is not just that I like the Hungarion Notation (invented by Simonyi K├íroly from Hungary), it also enables me to use smart functions while programming VBA.

Smart Functions

For instance when I create a command button which should lead me to the form frmCustomers, I call the command button cmdCustomers. After that I attach a function to the On Click event:


Because it refers to the active control, it will automatically transfer the name of the command (cmdCustomers) tot the function cmdCommandClick.

The VBA of the function cmdCommandClick looks like this:

Public Function cmdCommandClick(strFrmName As String)
     DoCmd.OpenForm "frm" & Mid(strFrmName, 4)
End Function

So, now I can easily copy such a command button, change the name and it will automatically go to the right form.

I created another function, FormOpenSource, to automatically attach the right Record Source to the form. This function is attached to the On Load event:


The VBA of the function FormOpenSource looks like this:

Public Function FormOpenSource(objFrm As Form)
    Dim tblName As String
    tblName = "qry" & Mid(objFrm.Name, 4)
    
    'use the query if there is one, otherwise the table
    If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then
        objFrm.RecordSource = "qry" & Mid(objFrm.Name, 4)
    Else
        objFrm.RecordSource = "tbl" & Mid(objFrm.Name, 4)
    End If
End Function

In this case the function would look for a query qryCustomers and if it does not exist, it will look for tblCustomers.

Just a couple of examples to make programming Access VBA easier and faster.
Een reactie plaatsen