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

Filter Function Output in ListBox-2.

Last week, we explored the Filter() function; a simple example demonstrates 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 the Large Image in a New Tab/Window.

When the Form opens in Normal View, both the ListBox and TextBox controls will be empty.

To display all records, the User can type ALL in the TextBox and click the CommandButton. This action will display the names and addresses of all employees in the ListBox.
Alternatively, the user can enter a word or phrase — such as part of a name or address — and click the CommandButton 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 Field values are joined 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 CommandButton. It uses the Filter() function to extract matching entries from the source array based on the search text entered in the TextBox 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 ListBox Row Source property, which displays the filtered results to the user.

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

The Address Book Project.

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

The Design Task.

Import the Employees Table from the 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 TextBox below the ListBox. Change the Name Property value of the TextBox to xFind. Position the Child Label above the TextBox and change the Caption value to Search Text/ALL.
Create a Check-Box Control to the right of the Text Box. Change the CheckBox Name Property 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 function parameter 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 names 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, the employees' (Nancy & Laura) Names and Addresses are filtered, and the word Ave is in their Address Lines.
Clear the check mark from the 'matching-cases' CheckBox, then click the Filter command button again.

Now, all items except those records having 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 way, the data can be accessed and reused across both Subroutines within the Form Module.

In the Form_Load() Event Procedure, we 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 employee names and addresses are read into these variables, the data is left-justified, with the unused portion of each field padded with spaces on the right. This approach maintains a consistent spacing between items and ensures proper alignment when they are 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 CommandButton 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

Filter function output in ListBox.

This FILTER is not related to a Query, SQL WHERE clause, or a Form’s Filter property. It is a powerful built-in VBA function that can filter data from an array based on text matching.

We can use it to search across multiple data fields of 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 function output will be displayed in a MsgBox.

How It Works

Let us examine the above code closely. Variable X is dimensioned for eight elements, and the Array is loaded 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 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 True parameter extracts the matched items and saves 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 specific comparison method: 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: 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 filter operation result. 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: 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 Forms or Reports.

Real Application Around the 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 ListBox on a Form.

We will try a similar example using employee data, with more than one field, joined together as source Array contents, and display the Filter result in a ListBox. 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 examine the differences or improvements.

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

Dynamic ListBox ComboBox Contents

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

Dynamic ListBox ComboBox Contents.

Sometimes, you may want to display different sets of unrelated data in the same ListBox — 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 ListBox or ComboBox 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.

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

Interestingly, although it is called a user-defined function, this feature is built into Microsoft Access. The documentation provides the function structure, required parameters, and a basic code template. All you need is to copy the Function and customize it to meet your specific requirements.

You can view the function details in the Access Help Documents. 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, locate the hyperlink titled User-defined Function and select it. This gives the function parameter details and explains each one's purpose.

We will examine the second example given in the Help document. The VBA code below shows how the function is used in a ListBox, which demonstrates its behaviour and structure.

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 add a ListBox.
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 the Large Image in a 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 code segment is customized and used for various purposes. After the initialization phase, the system repeatedly calls the function to retrieve additional values—such as the row count, column count, and other properties—that determine the ListBox’s property values, which are normally set manually at design time.

When the ColumnWidths Property is -1, Access retains the default settings manually specified in the Property Sheet. This feature is useful when displaying multiple columns of data with mixed column widths.

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

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 calls the function repeatedly at different stages of the ListBox 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 ListBox control name. 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 random values at millisecond intervals, ensuring that each function call receives a unique ID.

The Code parameter carries specific values evaluated in the SELECT CASE statements. These values identify the property or action by Microsoft Access, and the corresponding return values dynamically define or update the List Box property settings.

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 use the VBA Code with a few changes for a ListBox 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 ListBox 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 may run experiments with different width settings to better understand how they affect the List appearance.
Create a Command Button on the Form.
Ensure that the CommandButton is active 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 CommandButton below the first one.
Repeat the Procedure in Steps 10 and 11 for the On Click Property of the second CommandButton.
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 ListBox (the name in Bold Letters) in the Code to match the name of your own ListBox.
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 CommandButton to change the ListBox contents to the Employees List.
A sample image of the ListBox with the Employee List is given below:
Right-click to open the Large Image in a New Tab/Window.
The CommandButton clicks the ListBox contents back to the Database List.

The same procedure works in the ComboBox also. You may create a ComboBox control and run the same functions from the Row Source Type Property.

Don't forget to set the Default Value Property value to 1; otherwise, the ComboBox may not show anything in its TextBox 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 the Microsoft Office Assistant Balloon Object for MsgBox. 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, besides standard buttons like OK, Cancel, Yes, and No.

Since this article is the third part of the series, I recommend that new readers of this topic go through the earlier articles to learn these simple tricks 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.

Checkbox Menu Options.

In this section, we will learn how to display checkbox menu options 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. 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 is five CheckBoxes 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, making it straightforward to check the selection and execute the corresponding action.

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

The checkboxes can be checked or unchecked, and their state must be validated.
The user may select one or multiple options simultaneously. If multiple selections are not allowed, the validation check should enforce the limit 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 case, necessary to inspect the selected state of each element in the CheckBox array to determine the menu selection.

Validating the Checked/Unchecked Items

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

The first step checks 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 again, forcing the user to select a single item or allowing her to select Cancel.
It executes the valid selection 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 Office Assistant Balloon Object, introduced and explained in these articles, provides a solid foundation for learning the basic features and its properties.

However, duplicating and customizing this code across multiple programs for different needs is not advisable. Doing so increases the database size, 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 character 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()")