Back to Top
Back to Top
Back to Top
Back to Top
Back to Top
 
 

 

Chapter 7: VBA Coding- Subroutines and Function Procedures

You can use VBA to do many of the following activities with the MS Word EMR Project:

  • Fill in and manipulate the underlying template document.

  • Send data to other templates, s.a. to print out prescriptions, letters, and medication lists.

  • Link/communicate to the MS Access back-end tables to pull data and to perform data processing.

  • Automate chart creation using the ActiveX Graph control.

  • Use the Windows API dialog boxes to gather information from the MS Word template environment.

  • Perform record optimizing tasks to keep your patient data same and rapidly accessible.

Why Use Procedures?


            Procedures exist for two main reasons: code organization and to allow for reuse throughout your program by other procedures. By breaking a large amount of code up into smaller pieces, it makes it easier to follow your code, understand, and to work with it.

            You can begin your coding by using the MS Word Macro Recorder which can help you perform routine tasks and occasionally to help you write part of your VBA application. An excellent discussion on this topic can be found here- http://office.microsoft.com/en-us/products/HA010192301033.aspx.  A typical macro produced by this method would look like this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/16/2007 by Alberto Andres Borges
'
Selection.InsertSymbol Font:="Symbol", CharacterNumber:=-3922, Unicode:= _
True
Selection.TypeText Text:=" "
End Sub

            Alternatively, you can open Access and use its control wizard to generate the vba code. For example, add a click button onto an Access form and instantly a control (button) wizard will pop up to help generate the vba code automatically.

Modules and Procedures and Their Scope

As stated in a previous chapter, a module is a container for procedures.  A procedure is a unit of VBA code enclosed either between the Sub and End Sub statement or between the Function and End Function statements.  

The following sub procedure closes a form:

Private Sub CommandButton2_Click()

Unload Me

frmSchedulter.Show

End Sub

Procedures in Visual Basic can have either private or public scope.  A procedure with private scope is only accessible to the other procedures in the same module; a procedure with public scope is accessible to all procedures in in every module in the template in which the procedure is declared.

Calling Sub Procedures and Function Procedures

There are two ways to call a sub procedure.  The following example shows how a sub procedure can be called by other sub procedures. 

Sub z(a)

    MsgBox a

End Sub

 

Sub x()

    Call z("ABC")

End Sub 

            In this demonstration Sub z procedure takes an argument (a) and display the argument value ("ABC") in a message box. The following example from the MWEP calls up a dialog box and sends off the URL of the folder needed:

Private Sub CommandButton7_Click()

On Error Resume Next

[PlsWt].Visible = True

XX = "welcome"

Call AutoMacros.BrowseFolder(szDialogTitle = "C:\")

‘***************[Other code]**********************

End Sub

 

Public Function BrowseFolder(szDialogTitle As String) As String

    Dim X As Long, bi As BROWSEINFO, dwIList As Long

    Dim szPath As String, wPos As Integer, XX As String

 

    With bi

        .hOwner = hWndAccessApp

        .lpszTitle = szDialogTitle

        .ulFlags = BIF_RETURNONLYFSDIRS

    End With

    

    dwIList = SHBrowseForFolder(bi)

    szPath = Space$(512)

    X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)

    

    If X Then

        wPos = InStr(szPath, Chr(0))

        BrowseFolder = Left$(szPath, wPos - 1)

    Else

        BrowseFolder = vbNullString

    End If

    frmSplashscreen.TextBox2 = szPath

    yy = frmSplashscreen.TextBox2 & "\zfilemds_Scheduler.mdb"

    frmSplashscreen.TextBox2 = yy

    conDBpath2 = yy

    Select Case XX

        Case "welcome"

        Case "handouts"

            Call ListBoxFill1

        Case "workups"

            Call ListBoxFill2

        Call ListBoxFill2

    End Select

End Function

 

 

            Notice how the folder displayed is now “C:\”.

            Note: Although you can call any procedure by simply referring to their procedure name, if there are procedures with duplicate names in different modules, you must refer to the procedures by including the module name, s.a. automacros in the sample above. In general, it is always good programming form to use the module name in all of your calls.

Procedure Parameters and Arguments

            At this point, I will refer you to read the MSDN article “Procedure Parameters and Arguments”, located here- http://msdn.microsoft.com/en-us/library/2ch70h3t(VS.80).aspx . It presents an in-depth discussion on passing parameters.

Procedure Events

            Events are particular occurrences in the lifetime of an open object brought up by code that allows you to perform actions in specific moments. 

            Here is a listing of the various procedure events available:

Initialize

The initialize event occurs when a UserForm is first opened by calling up the show method in your code and also if you manipulate a property of the form or a property of an object on the form such as a checkbox before the form has been loaded.

This event will fire before the UserForm is visible. This event is useful for setting up items on the UserForm, the caption of a widow, and other general set of duties such as initializing drop-down lists. 

Click and DblClick

These events far when the user clicks or double clicks on the UserForm itself. It will not fire if the user clicks on objects on the UserForm. In the MSEP these events are not used.

MouseDown and MouseUp

These events fire when the mouse button is pressed down and when it is released, respectively. These events have parameters that specify information about the mouse at the time that its button is pressed.  In the MSEP these events are not used.

MouseMove

This event fires whenever the UserForm has focus (an active form) and the mouse is moved anywhere over the form. It fires as often as several times a second. In the MSEP these events are not used.

KeyPress

This event fires whenever the UserForm has focus (an active form) and the user presses a regular keyboard character. KeyPress only supports the ANSI character set (standard characters such as letters and numbers, as well as other regular keys such as Enter and Delete). In the MSEP these events are not used.

KeyDown and KeyUp

Similar to the Mouse events above, these events fire when the user presses or releases a key on the keyboard. In the MSEP these events are not used.

Resize and Layout

These two events serve similar purposes, the adjustment of your form to fit changes in size. The Resize event fires whenever the size of the form itself is changed while the layout event fires when the size of one or more controls change, such as the label control which can grow to fit its current contents.  In the MSEP these events are not used.

QueryClose

This event fires when the form is asked to close but before it actually closes. It allows your code determine if you should let the user close the UserForm. If the end user clicks on the cancel parameter of this event, it will set it equal to True, cancelling the closing of the UserForm. The other parameter value, CloseMode, which is transferred, lets it know why the form was being asked to close. The four values are discussed at length here -- http://msdn.microsoft.com/en-us/library/aa445535(VS.60).aspx

Terminate

The opposite of the initialize event, this event fires when the form is unloaded. The code goes here, rose at the last moment of UserForms’ existence. You cannot use any code that affects the visual appearance of the UserForm as it will no longer be visible event fires.  It will not work when the UserForm is hidden instead of unloaded.

Populating Combo Box and List Box Controls

            There are 4 major methods of populating these objects.

  • The simplest way, and the way that works with small lists, looks like this:

 With ComboBox15
        .AddItem "frmHyperlinksHandouts"
        .AddItem "frmHyperlinksWorkups"
        .AddItem "frmReferral"
        .AddItem "frmScheduler"
        .AddItem "frmSplashscreen"
        .AddItem "frmTheGenlLetter"
End With

            If you have a multiple-column list, use semicolons to delimit the strings for each column (for example, "John Doe;11/10/1955;male" for a three-column list). If the Item argument contains fewer strings than columns in the control, items will be added starting with the left-most column. If the Item argument contains more strings than columns in the control, the extra strings are ignored.

            To remove an item, use the
RemoveItem method.

  • Slightly larger recordsets can be managed by this method, in which the recordset is populated by a static group of values:

Private Sub UserForm_Initialize()

Dim myArray As Variant

     myArray = Split("white|black|oriental|mixed|Indian|middle eastern|Indian")

     ListBox1.List = myArray

End Sub

        Or alternatively:

Private Sub UserForm_Initialize()

cboYear1.List() = Array("2008", "2007", "2006", "2005", "2004", "2003", _

"2002", "2001", "2000", "1999", "1998", "1997", "1996", "1995", "1994", _

"1993", "1992", "1991", "1990", "1989", "1988", "1987", "1986", "1985", "1984")

End Sub

        The year list is often used, and can be easily be made dynamic through this method by some simple extra coding:

Private Sub UserForm_Initialize()

Dim ii As Long, iii As Long, yy As String, Xx As String, myArray As Variant

iii = Year(Date) - 3

yy = ""

ii = 0

While ii < 5

Xx = iii + ii

If Len(yy) < 2 Then

yy = Xx

Else

yy = yy & "|" & Xx

End If

ii = ii + 1

Wend

End Sub

myArray = Split(yy)

ListBox1.List = myArray

End Sub

 

            If you wish to use the ListBox1.List method, but have 2 rows, your code should be changed as such:

Dim i As Integer
rst.Open "SELECT [ZipCode], [County] FROM tblISOCOuntryCodes ORDER BY [ZipCode];", _
         , adOpenStatic
rst.MoveFirst
i = 0
With Me.ListBox1
    .Clear
    Do
        .AddItem
        .List(i, 0) = rst![ZipCode]
        .List(i, 1) = rst![County]
        i = i + 1
        rst.MoveNext
    Loop Until rst.EOF
End With

  • To populate a combo box or list box control with external data, s.a. from MS Access using DAO you first need to make sure that you have a reference to the Microsoft DAO 3.6 Object Library. Your code will look like something like this:

Set db = OpenDatabase(conDBpath2)
' Retrieve the recordset

Set rs = db.OpenRecordset("SELECT Persmd.[PERSONALMD], Persmd.[GROUP NAME], Persmd.PLNAME, Persmd.PFNAME, Persmd.PSTREET, Persmd.PCITYSTATEZIP, Persmd.PWPHONE, Persmd.PhPHONE, Persmd.PFPHONE, Persmd.EMAIL, Persmd.autono, Persmd.PSALUTATION, Persmd.TitlePhrases, Persmd.DEANO FROM Persmd ORDER BY Persmd.PERSONALMD;")

' Determine the number of retrieved records

With rs
     .MoveLast
     NoOfRecords = .RecordCount
     .MoveFirst

' Set the number of Columns = number of Fields in recordset

ComboBox1.ColumnCount = .Fields.Count - 13

' Load the ListBox with the retrieved records

ComboBox1.Column = .GetRows(NoOfRecords)

' Cleanup

.Close

db.Close

End With

tt = "4.6 in"
aa = "4.6 in"
ComboBox1.ColumnWidths = tt
ComboBox1.ListWidth = aa

         There are many different ways to do this, but this is just one that I use most. To understand the code better, you need to review and understand the main properties of the combo box and list box controls are as such:

Property Type Description
Control source The combo box can be bound to any field of the source table attached to the form or remain unbound. The control source property contains the field name or expression to be used as the source for the bound control. For an unbound control, this field is empty.
Row Source type and Row Source The value for Row Source property is dependent on the Row Source type property. There can be three types of inputs for Row Source type property.
  1. Table/query: In this option the combo box is populated by a table or query. The related value for the Row Source property is the name of the table or query. A Query Builder can be used to generate a new SQL Select Statement as the Row Source.
  2. Field list: In this option the combo box is populated by the list of fields of the specified table or query. In the Row Source property the name of the table/query is to be specified.
  3. Value list: In this option the values displayed will have to be specified or hard coded. Each individual option of the list of values will have to be typed in separated by a semi colon in the Row Source property.
Column count This indicates the number of columns displayed in the combo box.
Column heads This Yes/No field decides whether the columns displayed will have headings or not.
Column width Give the width of each column in inches, separated by a semi colon. If any column is to be included in the query but hidden from view, enter zero as column width.
Bound column This contains the name of the control with which the combo box is bound.
List rows This indicates the number of rows displayed in the drop down list at a time.
List width This indicates the width of the drop down portion of the combo box. If it is set to auto, the width is adjusted automatically.
Limit to list This is a Yes/No field. When set to Yes the user cannot enter any value other than those in the drop down list. A No setting is used to allow the user to enter values other than the list.
Auto expand This property expands text if leading characters match one of the listed options.
Validation rule Enter the relevant expression to validate the data entered in this control. If no validation is required, leave it blank.
Validation text The text explanation accompanying a validation rule.

            Examples of the row source include the following:

  • To populate a combo box or list box control with external data using ADO you first need to make sure that you have a reference to the Microsoft ActiveX Data Objects 2.x Library. This method is ideal  if you wish to link into an SQL back-end. Your code will look like something like this:

Private Sub UserForm_Initialize()
    On Error GoTo UserForm_Initialize_Err
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=C:\Databases\PatientDatabase.mdb"
    rst.Open "SELECT DISTINCT [ChartNo] FROM Patients ORDER BY [ChartNo;",,adOpenStatic
    rst.MoveFirst
    With Me.ComboBox1
        .Clear
        Do
            .AddItem rst![Chartno]
            rst.MoveNext
        Loop Until rst.EOF
    End With
UserForm_Initialize_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
UserForm_Initialize_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume UserForm_Initialize_Exit
End Sub

ADO vs DAO

Data Access Objects (DAO) is the library that was designed for MS Access, the library Access itself uses. It has been present since inception, and is still the best library to use if you store your data in Access tables and write VBA code in Access.  The DAO library was designed to not only work with Jet databases but by using ODBC and respective drivers, can connect to other types of databases and work with those data sets as well in a heterogeneous environment.  The DAO library handles database connections, data sets, record locking and transactions.  It also can manipulate the Jet Database Engine Object and can handle Jet's User-Level security.

ActiveX Data Objects (ADO) is a generic library to handle database connections, data sets, record locking and transactions.  It has several features that are unavailable in DAO, such as disconnected recordsets, but one may never need these features.

Both of them comprise a set of objects to help programmers query and manipulate databases (relatively) easily.  For example, they allow the programmer to retrieve data from a database in the form of a recordset, to navigate within such a retrieved recordset, and to add, modify and delete records. They are in many respects very similar.  ADO is the newer technology, has a lot more bells and whistles, and was introduced with great fanfare a few years ago, first becoming available in Access with Access 2000.  However,  many Access programmers consider that, for working with Access/Jet databases, DAO remains the superior technology. Both Access 2000 and Access 2002 have the ADO library set as the default  data access library -- which causes problems to this day -- but thankfully,  Microsoft saw the wisdom of setting the DAO library as the default library
for data access, so this is what newly created databases in Access 2003 and  pre-Access 2000 versions have.Some programmers prefer to reverse this as our very first step in a new Access database i.e. remove the reference to ADO and add a reference to DAO!  You will read in some textbooks that Microsoft is planning on phasing out DAO, or that DAO is dead- but don’t believe it. It’s still evolving and Office 12 will have a new , more robust, version of DAO.

Those who work with more robust database engines, e.g. SQL Server, Oracle, or MySQL, often like some of ADO's features that are not present in DAO, although it is certainly perfectly possible to work entirely in DAO even with a server database. However, if working in an Access ADP, you don't really have much choice, ADO is the only real option.

You should also be aware that, much trumpeted as it was at it's launch, ADO
is now obsolete!  It has been superseded by the entirely different ADO.Net. However, ADO.Net is not, at this time, available to Access developers.

In summary, if you are planning to develop in Access, using Access tables, forms, and  VBA code, learn DAO. If you only need enough programming  skills to connect to external non-Jet data sources, then ADO is fine.

Manipulating Data: Adding Records Using A Form

            Here is the code to add a record to an underlying Access table:

Set db = OpenDatabase(conDBpath2)

Set rs = db.OpenRecordset("SELECT SCHETRES.* FROM SCHETRES WHERE [SCHETRES].[SCHDATE] " & " = # " & dd & " #;")

    With rs

    If .RecordCount = 0 Then

    .AddNew

    !schDate = dd

    !notes1 = "."

    !notes2 = "."

    !notes3 = "."

    !notes4 = "."

    !notes5 = "."

    .Update

End If

    .Close

End With

db.Close

Manipulating Data: Editing Records Using A Form

Here is the code to edit then update a record to an underlying Access table: 

Set db = OpenDatabase(conDBpath2)

Set rs = db.OpenRecordset("SELECT ScheSec.* FROM ScheSec WHERE_ [SCHESEC].[autono] = " & [autono1] & ";")

    With rs

    .Edit

    ![LNAME] = IIf(IsNull([LNAME]), ".", [LNAME])

    ![FNAME] = IIf(IsNull([FNAME]), ".", [FNAME])

    ![HPhone] = IIf(IsNull([HPhone]), ".", [HPhone])

    ![WPhone] = IIf(IsNull([WPhone]), ".", [WPhone])

    .Update

    .Close

    End With

db.Close

Manipulating Data: Deleting Records Using A Form

            Here is the code to delete a record to an underlying Access table:

Set db = OpenDatabase(conDBpath2)

Set rs = db.OpenRecordset("SELECT ScheSec.* FROM ScheSec WHERE [SCHESEC].[autono] = " & [autono1] & ";")

    With rs

    .Delete

    .Close

    End With

End If

db.Close

Arrays allow you to manipulate data in a way in which multiple responses can lead to different recordset outcomes.  We will now cover various array declarations:

Working With Arrays: If… End If and Select Case… End Select

  1.   The format for the If… End If is:

If (condition) Then

    One or more VBA statements

End If

        Example:

If Len(ptn) < 2 Then

MsgBox "Please select a patient!", vbInformation

Exit Sub

End If

 

  1.   The format for the Select Case… End Select is:

Select Case (condition)

      Case (condition 1)

           One or more VBA statements

      Case (conditioni 2)

           One or more VBA statements

      Case (condition etc)

           One or more VBA statements

      Case Else

           One or more VBA statements

End If

            Example:

Select Case [Combobox2].Value

      Case "frmHyperlinks"

            frmHyperlinks.Show

      Case "frmPrescriptions"

            frmPrescriptions.Show

      Case "frmReferral"

             frmReferral.Show

      Case "frmTheGenlLetter"

             frmTheGenlLetter.Show

      Case Else

            Call AutoMacros.Hospital_Notes

End Select

Working With Loop Structures: Do While… Loop and For… Next

  1. The format for the For… Next is:

For counter=startNumber to endNumber (Step increment)

      One or more VB statements

Next

        Examples:

(a)   With Application.FileSearch
      Set db = OpenDatabase(conDBpath2)
      Set rs = db.OpenRecordset("WordDocuments", dbOpenDynaset)
      If .Execute() > 0 Then
           MsgBox "There were " & .FoundFiles.Count & " file(s) found."
           For I = 1 To .FoundFiles.Count
                rs.AddNew
                rs![Link] = .FoundFiles(I)
                rs![DocName] = Dir(rs![Link]) '.FileName
                rs![PathDate] = FileDateTime(rs![Link]) '.LastModified
                rs.Update
           Next I
      Else
           MsgBox "There were no files found."
      End If
      End With

 

(b)   For I = 0 To I = ii - 1

    .Font.Name = "times new roman"

    .Font.Bold = False

    .Shading.BackgroundPatternColor = wdColorWhite

    .Font.Color = wdColorBlack

 Next I

 

  1.   The Do While… Loop formats are:

a)    Do While condition

          Block of one or more VBA statements

      Loop

 

b)    Do Until condition
          Block of one or more VB statements
      Loop

           Examples:

a)         The above example will read a table cell until the end of the elements has been encountered (EOF= “end of file”).         

      Do While Not rs.EOF 

       objTable.Cell(CurrentRow,1).Range.Text=rs("MD1") & " (" & rs("Reason") & ")"      

       objTable.Cell(CurrentRow,2).Range.Text=rs("Script")

       counter =counter+1

rs.MoveNext

CurrentRow = CurrentRow + 1

Loop

Applying this Tutorial to Your EMR Project

          At this point, you should:

  • Set up various UserForm_Initialize() subroutines, adding in local variables, and set up the code to populate Comboboxes and ListBoxes.
  • Add code to your UserForm objects, s.a. the clickbuttons.

 

 

 

 

 

 

Go to Main Bibliography Page

 

Go to the Main Bibliography Page

 

Back to Top
Back to Top
Back to Top
Back to Top
Back to Top
Back to Top
Back to Top