Back to Top

Table of Contents:

Calling Sub Procedures and Function Procedures

Populating Combo Box and List Box Controls

ADO vs DAO

Manipulating Data: Adding Records Using A Form

Manipulating Data: Editing Records Using A Form

Manipulating Data: Deleting Records Using A Form

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

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

Applying this Tutorial to Your EMR Project

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

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

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. 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. 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. 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

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

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

The format for the For… Next is:

For counter=startNumber to endNumber (Step increment)

One or more VB statements

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

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.