September 2009 ~ LEARN MS-ACCESS TIPS AND TRICKS

Filter Function Output In Listbox-2

a.p.r. pillaiSeptember 25, 2009Functions, List Boxes, msaccess controls 2 comments

Introduction

Last week, we explored the Filter() function with a simple example, demonstrating how it works. In that example, we assigned constant values directly to the source array elements to keep the VBA code straightforward and easy to follow.

Just as we can filter data on Forms by setting criteria in the Filter property, or use conditions in Queries to extract information from Tables, the Filter() function provides yet another powerful way to process data — but this time, directly from an Array.

By loading data from a Table or Query into an array, we can use the Filter() function to quickly generate results that match (or exclude) a specified pattern.

To understand this better, let’s try an example using the Employees table from the Northwind.mdb sample database. We’ll create a simple Address Book application that uses the Filter() function to find and display matching names or addresses in a List Box based on user input.

The following section outlines the User Interface (UI) design we plan to create and explains how users will interact with it to display information quickly on the Form.

For this experiment, we will design a Form containing the following controls:

A List Box to display the filtered results.
A Text Box for entering the search text.
A Check Box to toggle between matching and non-matching results.
A Command Button to execute the filter operation.

An image of the Form in Design View is shown below:

Right-click to open Large Image in New Tab/Window.

When the Form opens in Normal View, both the List Box and Text Box controls will be empty.

To display all records, the user can type ALL in the Text Box and click the Command Button. This action will load and display the names and addresses of all employees in the List Box.
Alternatively, the user can enter a word or phrase — such as part of a name or address — and click the Command Button to display only those records where the entered text appears anywhere within the name or address fields.

If the Matching Cases Check Box is selected, the filter will include only records that match the search text.
If it is not selected, the filter will instead display all records that do not match the entered text.

To provide the user with the functionality described above, we need two Subroutines in the Form’s Code Module and a User-Defined Function in a Standard Module that utilizes the Filter() function.

Form_Load Event:
When the Form opens, the first Subroutine runs from the Form_Load() event. It reads data — specifically the First Name, Last Name, and Address fields — from the Employees table. These values are combined into a single text string per record and stored in a single-dimensional array variable in memory. This array remains active for as long as the Form is open.
Command Button Click Event:
The second Subroutine runs when the user clicks the Command Button. It uses the Filter() function to extract matching entries from the source array based on the search text entered in the Text Box control.

The Filter() function returns all array elements that match (or, optionally, do not match) the specified search text and saves them into a target array variable (xTarget). The resulting data is then formatted and assigned to the Row Source property of the List Box, which displays the filtered results to the user.

Right-click to open Large Image in New Tab/Window.

The Address Book Project.

Let us prepare for the Address Book's Quick Find Project.

The Design Task

Import Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.
Open a new Form in Design View.
Select the List Box Control from the Toolbox and draw a List Box as shown in the design above.
While the List Box is in the selected state, display the Property Sheet (View -> Properties) and change the following Property Values as given below:
- Name = AddBook
- Row Source Type = Value List
- Width = 4.5"
- Height = 1.75"
- Font Name = Courier New
- Font Size = 10
Position the Child Label attached to the List Box above and change the Caption value to Address Book.
Draw a Text Box below the List Box. Change the Name Property value of the TextBox to xFind. Position the Child Label above the Text Box and change the Caption value to Search Text/ALL.
Create a Check-Box Control to the right of the Text Box. Change the Name Property of the Check-Box to MatchFlag. Change the Default Value Property to True. Change the Caption value of the child label of Matching Cases.
Create a Command Button to the right of the Check-Box control. Change the Name Property Value of the Command Button to cmdFilter and the Caption Property Value to Filter.
NB: Ensure that the Name Property Values of the above controls are the same as given above. This is important because we are referencing these names in Programs.
Display the Code Module of the Form (View ->Code).

Copy and paste the following Code into the Form Module:

The VBA Code

Option Compare Database
Option Explicit
Dim xSource() As Variant

Private Sub Form_Load()
Dim db As Database, rst As Recordset, J As Integer
Dim FName As String * 12, LName As String * 12, Add As String * 20

'Take the count of records
J = DCount("*", "Employees")

'redimension the array for number of records
ReDim xSource(J) As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenDynaset)
'load the name and addresses into the array
J = 0
Do Until rst.EOF
   FName = rst![FirstName]
   LName = rst![LastName]
   Add = rst![Address]
  xSource(J) = FName & LName & Add
rst.MoveNext
J = J + 1
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Private Sub cmdFilter_Click()
Dim x_Find As String, xlist As String, xTarget As Variant
Dim x_MatchFlag As Boolean, J As Integer

Me.Refresh
x_Find = Nz(Me![xFind], "")
x_MatchFlag = Nz(Me![MatchFlag], 0)

'if no search criteria then exit
If Len(x_Find) = 0 Then
  Exit Sub
End If
    'initialize list box
    xlist = ""
    Me.AddBook.RowSource = xlist
    Me.AddBook.Requery

If UCase(x_Find) = "ALL" Then
    'Take all values from the Source Array
    'Format it as listbox items
    For J = 0 To UBound(xSource())
      xlist = xlist & xSource(J) & ";"
    Next
Else    'Call the Filter Function
    xTarget = GetFiltered(xSource(), x_Find, x_MatchFlag)
    'format the returned values as list box items
    If Len(xTarget(0)) > 0 Then
        For J = 0 To UBound(xTarget)
            xlist = xlist & xTarget(J) & ";"
        Next
    End If
End If
    'remove the semicolon from
    'the end of the list box Value List
    If Len(xlist) > 0 Then
        xlist = Left(xlist, Len(xlist) - 1)
    End If
    'insert the list item string
    'and refresh the list box
    Me.AddBook.RowSource = xlist
    Me.AddBook.Requery

End Sub

Save the Form with the name Filter Form.
Copy and paste the following Function into a Standard Module and save the Module:
```
Public Function GetFiltered(ByRef SourceArray() As Variant, ByVal xFilterText As Variant, ByVal FilterType As Boolean) As Variant
    GetFiltered = Filter(SourceArray, xFilterText, FilterType)
End Function
```
Filter() Function and Filter Property of the Form.
We cannot use the Filter() Function in the Form Module because the function name clashes with the Form Property Filter.
We have placed the Filter() function inside a User-Defined Function named GetFiltered() within a Standard Module, along with the required parameters. This allows us to call it easily from the Form’s Module. The first parameter of the function is passed By Reference, enabling direct access to the source array values without creating a separate copy — improving both performance and efficiency.
The Demo Runs
Open the Filter Form in Normal View.
Enter the word ALL (in this case, the Matching Cases flag has no effect) in the Text Box control and click on the Filter Command Button.
This action will display the Name and Addresses of all Employees from the xSource() Array loaded from the Employees Table.
Enter the text Ave in the Text Box and ensure that the 'matching cases' check box is in the selected state.
Click on the Command Button.
This time you will find that only two employees (Nancy & Laura) names and addresses are filtered, and the word Ave is appearing in their Address Lines.
Clear the check mark from the 'matching-cases' check box, then click the Filter command button again.

Now, all items except those containing the word Ave will appear in the List Box.

If you review the code we added to the Form Module, you’ll notice that the xSource() array variable is declared in the global section of the module. This allows the same data to be accessed and reused across both Subroutines within the Form Module.

In the Form_Load() Event Procedure, we have declared three Variables as fixed-length String Type (see the declaration line given below).

Dim FName As String * 12, LName As String * 12, Add As String * 20

When we read employee names and Addresses into these Variables, the values will be left-justified inside the Variable, and the balance area of the declared size will be space-filled to the right. This method will space out items at a fixed distance from each other and properly align them when displayed.

It’s important to use a fixed-width font, such as Courier New, for the List Box display. This ensures that all text lines are properly aligned and easy to read. We’ve already configured this setting in Step 4 above.

If you click the Filter Command Button when the TextBox is empty, then the program terminates; otherwise, it calls the GetFiltered() Function and passes the parameter values.

The output Values are returned in the xTarget Array, and the next steps format the Value List and display it in the List Box.

Filter Function output in ListBox

a.p.r. pillaiSeptember 18, 2009Functions, List Boxes, msaccess controls 1 comment

Introduction

This FILTER is not related to a Query, SQL WHERE clause, or a Form’s Filter property. It is a built-in VBA function with a useful purpose: quickly filtering data from an array based on text matching.

We can use it to search across multiple fields of data from a Table, extract either matching or non-matching items, and display the results neatly in a List Box.

The Demo Run of the Filter.

But first, will experiment with a simple example to understand its usage. Copy and paste the sample VBA code given below into a Standard Module in your database:

Public Function myFilter()
Dim X(7) As Variant, Y As Variant
Dim J as Integer, msg as String

X(0) = "Strawberry Milk"
X(1) = "Chocolates"
X(2) = "Milkshake"
X(3) = "Mango Juice"
X(4) = "Icecold Milk"
X(5) = "Apple Juice"
X(6) = "Buttermilk"
X(7) = "Vanilla Icecream"

'Extract all items containing the text "milk" from Array X()
'and save the output in Array Y()
Y = FILTER(x, "milk", True, vbTextCompare)
msg = ""
For J = 0 To UBound(Y)
   msg = msg & J + 1 & ". " & Y(J) & vbCr
Next

MsgBox msg

End Function

Click anywhere within the Code and press F5 to run the Code. The output of the function will be displayed in a MsgBox.

How It Works

Let us examine the above code closely. Variable X is dimensioned for eight elements and loaded the Array with text values.

The FILTER() Function in the statement Y = FILTER(X, "milk", True, vbTextCompare) extracts the items that match the search text milk from the Source Array of values from Variable X and saves the output as an array of Values into Variable Y.

The FILTER() Function accepts four parameters.

The first parameter X is the Array containing the Text Values.

The second parameter milk is the search text. It is compared against each item in the source array X. Whenever a match is found anywhere within an array element, that item is extracted and added as an element in the target array Y .

The third parameter value True will extract the matched items as output and save them in Variable Y. When this value is set to False, the output will be items that do not contain the search text milk.

The fourth parameter dictates the use of a specific comparison method, like Binary, Database, or Text Comparison. Here, we have used the Text Comparison method. The third and fourth parameters are Optional.

Try the above Code with different search text, like juice or Ice, etc.

If you look at the Variable declarations of the Code, you can see that we have declared the Variable Y as a simple Variant Type and not as an Array Variable.

The FILTER() function automatically resizes the target array based on the results of the filter operation. Because the number of matches can vary with each search, the output array can be of different sizes each time. To handle this dynamically, we use the UBound() function to determine the number of elements in the filtered array. This allows a For...Next loop to iterate through all matching items and format them for display in a form MsgBox or other output.

Important points:

The source array must be single-dimensional.
The search text can be multiple pieces of information, like first name, last name, and address. You should concatenate all relevant fields into a single string for each record and store them in a single-dimensional array.

The Filter() Function will not work in Code Modules of Form or Report.

Real Application Around Filter() Function

I have developed an Application around this Function for our Department Secretary to find Office Files with their location addresses (we have hundreds of them) that match a specific word or phrase in their Subject or Description Fields and display them in a List Box on a Form.

We will try a similar and simple example using employee data, using more than one field value, joined together as source Array contents, and display the Filter result in a List Box. I will give details of this example in the next Article.

In the meantime, you can experiment with using this function for your own tasks. Once I present my example, you can compare it with your approach and see the differences or improvements.

If you could do it differently, share the idea with me, so that I can learn something from you, too.

Dynamic ListBox ComboBox Contents

a.p.r. pillaiSeptember 12, 2009Combo Boxes, List Boxes, msaccess controls No comments

Introduction

Sometimes, you may want to display different sets of unrelated data in the same List Box — each with its own column layout — and switch between them at will. This switch could happen when the user clicks a button or triggers another event.

Normally, when creating a List Box or Combo Box in MS Access, you choose one of three standard Row Source Type options: Table/Query, Value List, or Field List. You then set other properties such as Column Count, Column Widths, and Bound Column manually.

However, there’s another, often overlooked option — you can assign a User-Defined Function to the Row Source Type property. This function can populate the List Box or Combo Box dynamically through VBA code, allowing full control over the data and its layout.

Interestingly, while it’s called a user-defined function, this feature is actually built into Microsoft Access. The documentation provides the structure, required parameters, and basic code template. All you need to do is copy the function, adapt it, and tailor it to suit your specific requirements.

You can view the details of this function directly from the Access Help system. To do this, place the insertion point in the Row Source Type property of a List Box or Combo Box control and press F1.

When the Help window opens, look for the hyperlink titled User-defined Function and click it. This will display detailed information about the function’s parameters and how each one works.

We’ll now examine the second example provided in that Help document. The VBA code shown below demonstrates how the function can be used with a List Box, giving us a clearer understanding of its structure and behavior.

Function ListMDBs(fld As Control, ID As Variant,  row As Variant, col As Variant,  code As Variant) As Variant
Static dbs(127) As String, Entries As Integer    
Dim ReturnVal As Variant

ReturnVal = Null
    Select Case code
        Case acLBInitialize
               ' Initialize.
            Entries = 0
            dbs(Entries) = Dir("*.MDB")
            Do Until dbs(Entries) = "" Or Entries >= 127
                Entries = Entries + 1
                dbs(Entries) = Dir
            Loop
            ReturnVal = Entries
        Case acLBOpen ' Open.
            ' Generate unique ID for control.
            ReturnVal = Timer
        Case acLBGetRowCount
            ' Get number of rows.
            ReturnVal = Entries
        Case acLBGetColumnCount
    ' Get number of columns.
            ReturnVal = 1
        Case acLBGetColumnWidth
   ' Column width.
   ' -1 forces use of default width.
            ReturnVal = -1
        Case acLBGetValue   ' Get data.
            ReturnVal = dbs(row)
        Case acLBEnd  ' End.
            Erase dbs
    End Select
    ListMDBs = ReturnVal
End Function

Copy the above code into a new Standard Module in your Database and save it.
Open a new Form in Design View and create a List Box on it.
Click on the List Box to select it.
Display the Property Sheet (View -> Properties).
Insert the Function name ListMDBs in the Row Source Type Property, overwriting the value Table/Query.
Save the Form.
Open the Form in the normal view.

A list of databases from the specified directory (check Tools -> Options -> General Tab for your default directory location) will appear in the List Box. A sample image of a ListBox is given below:

Right-click to open Large Image in New Tab/Window.

Taking Files-List From a Specific Folder

You can modify the following line in the program to list Word or Excel files either from the default folder or from a specific folder of your choice.

dbs(Entries) = Dir("*.xls")

dbs(Entries) = Dir("C:\My Documents\*.xls"). The dbs(Entries) = Dir in the subsequent calls uses the first call parameter value "C:\My Documents\*.xls" by default and populates the String Array dbs() in the Initialize step of the Program.

This section of the code can be customized and adapted for various purposes. After the initialization phase, the function is repeatedly called by the system to retrieve other values—such as the row count, column count, and more—that define the List Box’s property values, which are usually set manually during design time.

When the ColumnWidths property is assigned the value -1 in the program, it signals Access to retain the default settings specified manually in the Property Sheet. This feature is useful when displaying multiple columns of data with mixed column widths.

Finally, the following statements pass the List Box Source Values dbs(row) that we have created under the Initialize stage for displaying in the List Box:

Case acLBGetValue ' Get data.
ReturnVal = dbs(row)

The Row parameter holds the actual number of items loaded into the dbs() array, which was initially declared with 127 elements. This value is passed to the function through the Entries variable, as shown in the following segment of the code.

Case acLBGetRowCount ' Get the number of rows.
ReturnVal = Entries

The dbs variable is declared as a static, single-dimensioned array with 128 elements (indexed from 0 to 127) so that it retains its values during subsequent calls to the ListMDBs user-defined function. This persistence is important because MS Access repeatedly calls the function at different stages of the List Box population process. During each of these calls, the required parameter values are automatically passed by MS Access — you don’t need to provide them manually.

The first parameter represents the name of the List Box control. The second parameter, ID, helps the system distinguish this specific process from other similar ones that may be running. It is assigned a value based on the System Timer using the following statements:

Case acLBOpen ' Open.
' Generate a unique ID for control.
ReturnVal = Timer

The System Timer generates new values at millisecond intervals, ensuring that each user-defined function instance receives a unique identification value when multiple such functions are active simultaneously.

The Code parameter carries specific values used within the Select Case statements of the function. These values determine which property or action is being requested by MS Access, and the corresponding return values are then used to define or update the List Box property settings dynamically.

Using Table Record Field Values in a ListBox.

If you’ve understood, or at least have a general idea of, how this function defines the contents of a List Box or Combo Box, then we’re ready to move on to the interesting part — the trick I mentioned at the beginning of this article.

Second Example with Employees Table

We will create a copy of the above code and modify it to make a List Box that displays two columns — Employee Code and First Name — from the Employees table in the Northwind database.

Import the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.

Copy the following VBA Code into the Standard Module of your database and save the Module:

Function ListBoxValues(fld As Control, ID As Variant, row As Variant, col As Variant, code As Variant) As Variant
    Static xList(127, 0 To 1) As String, Entries As Integer
    Dim ReturnVal As Variant, k As Integer
    Dim db As Database, rst As Recordset, recCount As Integer

    ReturnVal = Null
    Select Case code
        Case acLBInitialize  ' Initialize.
            Set db = CurrentDb
            Set rst = db.OpenRecordset("Employees", dbOpenDynaset)
            Entries = 0
            Do Until rst.EOF
               For k = 0 To 1
                    xList(Entries, k) = rst.Fields(k).Value
               Next
               rst.MoveNext
               Entries = Entries + 1
            Loop
            rst.Close
            ReturnVal = Entries
        Case acLBOpen                    ' Open.
            ' Generate unique ID for control.
            ReturnVal = Timer
        Case acLBGetRowCount
         ' Get number of rows.
            ReturnVal = Entries
        Case acLBGetColumnCount
   ' Get number of columns.
            ReturnVal = 2
        Case acLBGetColumnWidth    ' Column width.
           ' -1 forces use of default width.
            ReturnVal = -1
        Case acLBGetValue                ' Get data.
            ReturnVal = xList(row, col)
        Case acLBEnd                        ' End.
            Erase xList
    End Select
    ListBoxValues = ReturnVal
End Function

Open the Form in Design View with the List Box that we created earlier.
Click on the List Box to select it.
Display the Property Sheet (View -> Properties).
Change the Column Widths Property Value to 0.5";1.5"

The following lines of Code say to use the values set in the Column Widths property without change:
Case acLBGetColumnWidth ' Column width -1 forces the use of the default width.

ReturnVal = -1
If the Column Widths property contains only a single value (for example, 1"), then all columns in a multi-column list will automatically use that same width. This may not look visually appealing when the column values vary in length. You can experiment with different width settings to better understand how they affect the appearance of your list.
Create a Command Button on the Form.
Ensure that the Command Button is in the selected state and display the Property Sheet.
Click on the On Click Property and select [EventProcedure] from the Drop Down control, and click on the Build (...) Button to open the VBA Module with the skeleton of the On Click Event Procedure.
Copy and paste the following lines of code in the middle of the Event Procedure.
```
Me.List40.RowSourceType = "ListMDBs"
Me.List40.Requery
```
Change the name of the List Box (the name in Bold Letters) to match the name of your own List Box.
Create another Command Button below the first one.
Repeat the Procedure in Steps 10 and 11 for the On Click Property of the second Command Button.
Copy and paste the following code in the middle of the On Click Event Procedure:
```
Me.List40.RowSourceType = "ListBoxValues"
Me.List40.Requery
```
Change the name of the List Box (the name in Bold Letters) in the Code to match the name of your own List Box.
Save and Close the Form.
Open the Form in Normal View.

Since you have already inserted the ListMDBs User Defined Function in the Row Source Type Property earlier, the list of databases will appear in the List Box first.
Click on the second Command Button to change the List Box contents to the Employees List.
A sample image of the List Box with Employee List is given below:
Right-click to open Large Image in New Tab/Window.
The Command Button click changes the List Box contents back to the Database List again.

It works for Combo Boxes in the same way. You may create a Combo Box control and run the same functions from the Row Source Type Property.

Don't forget to set the Default Value Property value 1; otherwise, the Combo Box may not show anything in its Text Box area before you select an item from the list.

Office Assistant And Msgbox Menus-3

a.p.r. pillaiSeptember 04, 2009Animations, Menus and Toolbars, msaccess animation No comments

After Clickable Menu Options - Access 2003.

After reviewing the earlier articles on this topic, I hope readers are now familiar with programming the Balloon Object of the Microsoft Office Assistant. You have seen that with just a few lines of customizable VBA code, you can display message boxes with formatted text—using colors, underlines, and even your favorite images. These balloons can also include menus to capture user responses, in addition to standard buttons like OK, Cancel, Yes, and No.

Since this article is the third part of the series, I recommend that new readers refer to the previous articles to learn these interesting and simple techniques in MS-Access before continuing. Links to those articles are provided below:

Color and Picture in MsgBox -Access 2003.
Office Assistant and MsgBox Menus - Access 2003.
Office Assistant and MsgBox Menus-2 - Access 2003.

Last week, we learned how to display Clickable Menu Options in a Balloon Object using Office Assistant. The Image of that example is given below.

We have displayed the Menu Options in the Message Box using the Labels Property of the Balloon Object.

Check Box Menu Options

In this section, we will learn how to display menu options with checkboxes in a balloon message box and how to capture and process user responses. You will also see how to execute specific actions based on the choices made. The example code and a sample image of the message box with the checkbox menu are provided below:

Public Function ChoicesCheckBox()
Dim i As Long, msg As String
Dim bln As Balloon, j As Integer
Dim selected As Integer, checked As Integer

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Select Data Output Option"
    .Checkboxes(1).text = "Print Preview."
    .Checkboxes(2).text = "Export to Excel."
    .Checkboxes(3).text = "Datasheet View."
    .Button = msoButtonSetOkCancel
    .text = "Select one of " & .Checkboxes.Count & " Choices?"
    i = .Show

    selected = 0
    If i = msoBalloonButtonOK Then
        'Validate Selection
        For j = 1 To 3
            If .Checkboxes(j).checked = True Then
                selected = selected + 1
                checked = j
            End If
        Next

       'If User selected more than one item
        'then re-run this program and force the
        'User to select only one item as suggested
        'in the message text.

        If selected = 0 or selected > 1 Then
           Call ChoicesCheckBox
        Else
            Select Case checked
                Case 1
                    Debug.Print .Checkboxes(checked).text
                Case 2
                    Debug.Print .Checkboxes(checked).text
                Case 3
                   Debug.Print .Checkboxes(checked).text
            End Select
        End If
    End If
End With

End Function

Like the Labels Property Array, the dimension of CheckBoxes can also be up to a maximum of five elements only.

In our earlier example, we did not use the OK or Cancel buttons alongside the label-based menu because the Balloon Button (msoBalloonTypeButtons), options were directly clickable. Clicking an item both registered the selection and dismissed the Office Assistant. The index number of the clicked item was returned as the response value, making it straightforward to check the selection and execute the corresponding action.

But in the case of check boxes, this is a little more complex because of the following reasons:

The checkboxes can be either checked or unchecked, and their state must be validated.
The user may select one or multiple options simultaneously. If multiple selections are not allowed, a validation check should enforce the choice of only one item.
Conversely, if multiple selections are permitted, the program must handle and execute the corresponding actions for each selected option.
In either scenario, it is essential to inspect the checked or unchecked state of each element in the CheckBox array to determine the validity of the menu selection.

Validating the Checked/Unchecked Items

In the example code given above, the User can select only one item at a time.

During the validation stage of the code, the first step is to check whether the user clicked the OK button. If she did, the program counts all the check-marked items and stores the total in the variable selected.
If selected is zero (the user clicked OK without selecting any option) or greater than one, the ChoicesCheckBox() function is called again. This refreshes the menu, clears any existing check marks, and displays it anew, forcing the user to make a valid selection of a single item or allowing her to click Cancel.
Once a valid selection is made, the related program executes the action corresponding to the user’s choice.
If multiple selections are permitted, the validation logic and action execution will differ, and the code must be written accordingly to handle all selected items.

The Balloon Object of the Office Assistant, introduced and explained in these articles, provides a solid foundation for learning the basics of this feature and understanding the use of its various properties.

However, duplicating and customizing this code across multiple programs for different needs is not advisable. Doing so increases the size of your database, reduces code flexibility, and is generally poor programming practice.

You may refer to the earlier published articles (links provided below), which demonstrate how to define public functions such as MsgOK(), MsgYN(), MsgOKCL(), and others using the Office Assistant. These functions simplify the use of this feature, allowing you to call them anywhere in your programs—just like the standard MsgBox() function—without duplicating code.

The function names themselves indicate the type of buttons that will appear in the message box when called, either with just a message text or with a message text and title.

For reference, a comparison between the above user-defined functions and the standard MsgBox() function is provided below. Note that the underscore ( _ ) character is used to indicate line continuation in the code. If you place all values on a single line, the underscore should not be used.

MS-Access MsgBox() usage	Office-Assistant-based User Defined Function usage
MsgBox "Welcome to Tips and Tricks"	MsgOK "Welcome to Tips and Tricks"
X = MsgBox("Shut Down Application", vbQuestion+vbDefaultButton2+vbYesNo, _"cmdClose_Click()")	X = MsgYN("Shut Down Application", _ "cmdClose_Click()")
X = MsgBox( "Click OK to Proceed or Cancel?", _ vbOKCancel+vbDefaultButton2+vbQuestion, _ "MonthEndProcess()")	X = MsgOKCL("Click OK to Proceed or Cancel?", _ "MonthEndProcess()")