Office Assistant And Msgbox Menus-3

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

Checkbox 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. 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 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 check boxes, this is a little more complex because of the following reasons:

1. The checkboxes can be either checked or unchecked, and their state must be validated. 

2. The user may select one or multiple options simultaneously. If multiple selections are not allowed, a validation check should enforce the limit of only one item. 

3. Conversely, if multiple selections are permitted, the program must handle and execute the corresponding actions for each selected option. 

4. In either scenario, it is essential 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 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 select a single item or allowing her to click Cancel.

• Once a valid selection is made, the selected option 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 its various 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.

1. Message Box using Office-Assistant
2. Message Box with Options Menu
3. Office Assistant with CheckBox Menu

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()")

• Network and Report Page Setup-3
• Network and Report Page Setup-2
• Network and Report Page Setup
• Filter by Character and Sort
• Animating Label on Search Success

Read More

Office Assistant And Msgbox Menus-2

Continued from Last Week. - Access 2003.

This article is a continuation of last week’s post, “Office Assistant and MsgBox Menus.” If you haven’t read that yet, I recommend going through it first before proceeding with this one.

Last week, we learned how to use the Office Assistant for MsgBox and saw how to create a menu and present it to the User, using the Labels Property of the Balloon Object.

Labels.Text Property

We have seen that we can display the Menu from Labels.Text Properties in three different ways by setting the Values of the Balloon Type Property:

1. Balloon Type = msoBalloonTypeButtons

   The User can click on any of these buttons to select one of the options presented.

2. Balloon Type = msoBalloonTypeBullets
3. Balloon Type = msoBalloonTypeNumbers

The second and third Balloon Type values display the Labels.Text in different visual styles, but they are not interactive—users cannot select them. These types are therefore best suited for situations where you only need to present information, rather than receive user input.

Let’s take a closer look at the Office Assistant's Balloon Object behavior when displaying a Message Box to understand how it works.

If you revisit the earlier examples, you’ll notice that when a message box created with the Office Assistant’s Balloon Object is displayed, you cannot interact with any other objects in the database. You must dismiss the Balloon first by responding to one of its options—such as clicking OK or Cancel—before you can continue.

This behavior is identical to that of the standard MsgBox() function in MS Access. However, the Office Assistant provides ways to overcome this restriction.

When a Balloon is displayed, you cannot, for instance, open a Form or Report to verify some information before clicking Yes or No, because the Mode property of the Balloon Object is set to its default value, msoModeModal.

The Mode Property of Message Balloon

I have not intentionally introduced the Mode property in the earlier examples to avoid overwhelming you with too many details at once. Now that you’re familiar with the basics, it will be easier to understand additional features associated with it.

As you’ve already seen, you can create stylish Message Boxes using the Balloon Object of the Office Assistant with just a few lines of VBA code—without even using this property.

The Mode property can be assigned one of three values, each controlling how the Balloon Object behaves when displayed.

Mode = msoModeModal (default).

This setting forces the user to respond to the Balloon before performing any other action—just like a standard MsgBox in MS Access. The user must click one of the available buttons (such as OK or Cancel) to dismiss the Balloon before continuing with any other task.

Mode = msoModeModeless.

This setting allows the user to access other database objects while the Balloon remains active. However, it also requires another property—Callback—to trigger a separate subroutine that can handle user interactions, evaluate selections or choices, perform the necessary actions, and finally close the Balloon object.

Mode = msoModeAutoDown.

This setting automatically dismisses the Balloon (MsgBox) if you click anywhere else, effectively ignoring it.

The purpose and behavior of the first and last Mode property values are straightforward. However, the second value: msoModeModeless requires special handling. When this mode is used, the Callback property must be set to the name of a valid subroutine; otherwise, the Balloon will fail to function properly. Leaving the Callback property empty (an empty string) will also cause an error.

Let’s now modify our earlier program to demonstrate how to use the Mode property set to msoModeModeless, along with the Callback property, to handle user selections from a menu displayed in the Balloon.

The Mode and Callback Property.

The Modified Code with Mode and CallBack Property Settings and the sample code for the required Subroutine MyProcess() is given below:

Public Sub Choices()
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Report Options"
    .Icon = msoIconAlertQuery
    .Button = msoButtonSetNone
    .labels(1).text = "Print Preview."
    .labels(2).text = "Print. "
    .labels(3).text = "Pivot Chart. "
    .BalloonType = msoBalloonTypeButtons
    .text = "Select one of  " & .labels.Count & " Choices? " 
    .mode = msoModeModeless
    .Callback = "myProcess"
    .Show
End With

End Sub

Sub MyProcess(bln As Balloon, lbtn As Long, lPriv As Long)
Assistant.Animation = msoAnimationPrinting
Select Case lbtn
    Case 1
        DoCmd.OpenReport "MyReport", acViewPreview
    Case 2
        DoCmd.OpenReport "MyReport", acViewNormal
    Case 3
        DoCmd.OpenReport "MyReport", acViewPivotChart
End Select
 bln.Close
End Sub

The Callback property is set with the Subroutine name myProcess. When the user clicks on one of the Options from the displayed MsgBox, the MyProcess() Subroutine is called by the Balloon and passes the required Parameter Values.

There are three parameters passed to the Subroutine when called:

1. The Balloon Object bln.
2. A Long Integer type value lbtn (BalloonTypeButton) identifying the Option clicked.
3. A Long Integer type value lPriv (Private) uniquely identifies the Balloon that is called the Sub-Routine, if there is more than one Balloon active in memory at the same time.

NB: There is no such thing as a collection of Balloon Objects. But you can create an Array of Variables with a Balloon Object, define different Property Settings for each of them, and the Show() method is run with their respective index numbers in Programs when you need them to appear.

 lbtn  The variable will have the value of the user's choice. This is tested in the Sub-Routine and runs the  Docmd.OpenReport  action within the Select Case End Select Statements.

The Assistant.Animation = msoAnimationPrinting line is placed in the Subroutine, rather than in the main Program as part of the Balloon Object Property setting, to animate the printing action only after the User makes a selection from the displayed Menu; otherwise, the printing animation will run before the selection of choices.

  bln.close  The statement dismisses the MsgBox.

Next week, we will learn how to use the CheckBoxes.Text Property Values of the Balloon Object to display a Menu with Checkboxes in a MsgBox.

• Network and Report Page Setup-2
• Network and Report Page Setup
• Filter by Character and Sort
• Animating Label on Search Success
• Form Background with Gradient Color

Read More

Office Assistant and MsgBox Menus

Office Assistant and MsgBox Menus - Access 2003.

In last week’s article, Color and Picture in Message Box, we explored several techniques to use the Office Assistant for displaying message boxes with formatted text. In this article, we will move beyond formatting. I believe many readers would now like to learn how this simple feature can be used to gather User responses— allowing them to choose from multiple options and trigger different actions based on their selections.

I have already discussed this concept earlier by creating MsgOK(), MsgYN(), and MsgOKCL() Functions, and others were created using the Office Assistant. These functions can be called from anywhere within your application — just like the built-in MsgBox() function — using only the message text, or both the message text and title as parameters.

My intention in designing these functions was to simplify the Office Assistant’s Balloon object use, which otherwise requires several property values to be manually set for each display.

However, during this simplification process, I’ve found that many readers may not fully understand the underlying method used within these functions.

The Links to those earlier posts are given below for reference:

1. Message Box with Office Assistant -Access 2003
2. Message Box with Options Menu - Access 2003
3. Office Assistant with CheckBox Menu - Access 2003.

In the example code presented in last week’s article, Color and Picture in Message Box, we explored several property values of the Balloon object of the Office Assistant that can be assigned before displaying a message box.

In this session, we will work directly with these properties so that you can clearly understand how each one functions. This approach will make it easier to grasp their behavior and usage, rather than passing values for them indirectly through the parameter list of a function definition.

Message Balloon Properties.

The following are some of these properties:

• Animation
• Icon
• Heading
• Text
• Balloon Type
• Button

Microsoft Office Object Library

Note:
If you have not yet attached the Microsoft Office Object Library to your database, please do so before trying out the examples given here. Follow the steps below:

1. Press Alt + F11 to open the VBA Code Window (or go to Tools → Macro → Visual Basic Editor).

2. From the Tools menu, select References.

3. In the Available References list, locate Microsoft Office Object Library and place a check mark beside it.

4. Click OK to close the dialog box.

Message Balloon Animation Constants.

The Animation property alone offers about thirty-five different choices, all defined as constants in the Microsoft Office Object Library. For reference, the constant values for the Balloon properties — Animation, Button, Icon, and Balloon Type — are listed below.

Animation	Icon
msoAnimationAppear msoAnimationBeginSpeaking msoAnimationCharacterSuccessMajor msoAnimationCheckingSomething msoAnimationDisappear msoAnimationEmptyTrash msoAnimationGestureDown msoAnimationGestureLeft msoAnimationGestureRight msoAnimationGestureUp msoAnimationGetArtsy msoAnimationGetAttentionMajor msoAnimationGetAttentionMinor msoAnimationGetTechy msoAnimationGetWizardy msoAnimationGoodbye msoAnimationGreeting msoAnimationIdle msoAnimationListensToComputer msoAnimationLookDown msoAnimationLookDownLeft msoAnimationLookDownRight msoAnimationLookLeft msoAnimationLookRight msoAnimationLookUp msoAnimationLookUpLeft msoAnimationLookUpRight msoAnimationPrinting msoAnimationRestPose msoAnimationSaving msoAnimationSearching msoAnimationSendingMail msoAnimationThinking msoAnimationWorkingAtSomething msoAnimationWritingNotingSomething	msoIconAlert msoIconAlertCritical msoIconAlertInfo msoIconAlertQuery msoIconAlertWarning msoIconNone msoIconTip
Button	BalloonType
msoButtonSetAbortRetryIgnore msoButtonSetBackClose msoButtonSetBackNextClose msoButtonSetBackNextSnooze msoButtonSetCancel msoButtonSetNextClose msoButtonSetNone msoButtonSetOK msoButtonSetOkCancel msoButtonSetRetryCancel msoButtonSetSearchClose msoButtonSetTipsOptionsClose msoButtonSetYesAllNoCancel msoButtonSetYesNo msoButtonSetYesNoCancel	msoBalloonTypeButtons msoBalloonTypeBullets msoBalloonTypeNumbers

The Animation and Icon properties are always set using one of the values listed above, depending on the message you want to convey to the User. By default, the OK button appears. If you need a different button or a group of buttons, then set the Button property values. The Balloon Type property is used in conjunction with the Labels property, which will be covered later in this article.

By going through these simple and straightforward examples, you will gain a better understanding of the Office Assistant, its methods, and how to incorporate them into your Applications.

We will now reproduce the code from last week’s article with minor adjustments and review it before applying changes for our new examples.

Public Sub MyMsgBox()
Dim strMsg As String
Dim strTitle As String

strTitle = "Assistant Test"
strMsg = "Wecome to MS-Access Tips and Tricks"

With Assistant.NewBalloon
    .Icon = msoIconAlertInfo
    .Animation = msoAnimationGetAttentionMajor
    .Heading = strTitle
    .text = strMsg
    .Show
End With

End Sub

Use the Above Code for a Demo Run.

Press Alt+F11 to display the VBA Editing Window. Select the Module option from the Insert Menu to create a new Standard VBA Module. Copy and Paste the above Code into the Module. Click anywhere in the Code and press F5 to run.

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

The Show() method in the example displays the Message Box after all the other property values have been set. The Text property defines the main body text of the Message Box, while the Heading property sets the title text in bold. The Animation property can be assigned one of the 35 options listed above, and we have set the Icon property to the Information type.

To obtain the User’s response and take different actions accordingly, we need to use the Button property in the code. For example, if the User must proceed to the report preparation process, they would click the OK Button; otherwise, they might click Cancel. We can then evaluate the response from the Balloon Object and write further code in the routine to handle each scenario appropriately.

Modified VBA Code

Let us see how we can do this with changes to the above Code. The modified program is given below:

Public Sub MyMsgBox ()
Dim strMsg As String
Dim strTitle As String
Dim R As Long

Title = "Assistant Test"
msgTxt = "Proceess Weekly Reports...?"

With Assistant.NewBalloon
.Icon = msoIconAlertQuery
    .Animation = msoAnimationGetAttentionMajor 
 .Button = msoButtonSetOkCancel
    .Heading = strTitle
    .text = strMsg 
 R = .Show
End With

If R = -1 Then
'User Clicked OK Button
    DoCmd.RunMacro "ReportProcess"
End If
End Sub

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

Compare the changes made in the new code with the earlier version to identify the differences.

If the user clicks the OK button (which returns -1 in the variable R; Cancel returns -2 when the Report Process Macro is executed. Otherwise, the program ends without performing any action.

We can also present different choices to the user as a menu using the Labels property and execute actions based on the user’s selection. An example of such code is provided below:

MsgBox-Based Menu Choices.

Public Sub Choices()
Dim R As Long
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Report Options"
    .Icon = msoIconAlertQuery
    .Button = msoButtonSetNone
    .labels(1).text = "Print Preview."
    .labels(2).text = "Print."
    .labels(3).text = "Pivot Chart."
    .BalloonType = msoBalloonTypeButtons
    .text = "Select one of " & .labels.Count & " Choices?"
    R = .Show
End With

Select Case R
    Case 1
        DoCmd.OpenReport "MyReport", acViewPreview
    Case 2
        DoCmd.OpenReport "MyReport", acViewNormal
    Case 3
        DoCmd.OpenReport "MyReport", acViewPivotChart
End Select

End Sub 

 Note: You may copy and paste the code into the VBA Module and modify it before attempting to run it.

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

The Labels() property can hold a maximum of 5 items using the Balloon Type value: msoBalloonTypeButtons. The user can click on one of the options to make a selection. The index of the clicked item is returned in the variable R.

In this example, we set Button = msoButtonSetNone to remove the default OK button, ensuring that the user must select one of the options displayed.

Balloon Type Bullets and Numbers.

Two additional Balloon Type property values are available: msoBalloonTypeBullets and msoBalloonTypeNumbers. Unlike msoBalloonTypeButtons These are not selectable by the user and are intended only for displaying information.

Compare the following Code with the earlier one to see the difference in changed Property Values.

Public Sub InfoDisplay()
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Reminder"
    .Icon = msoIconAlertInfo
    .labels(1).text = "MIS Reports."
    .labels(2).text = "Trial Balance."
    .labels(3).text = "Balance Sheet."
    .BalloonType = msoBalloonTypeBullets
    .text = "Monthly Reports for User Departments"
    .Button = msoButtonSetOK
    .Show
End With

Sample Images with Balloon Type Property Changes are given below:

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

We will continue this discussion next week to explore more ways to use the Office Assistant with Labels and Checkboxes.

• Network and Report Page Setup
• Filter by Character and Sort
• Animating Label on Search Success
• Form Background with Gradient Color
• MS Access and Reference Library

Read More

Color and Picture in Message Box

Color and Picture in Message Box - Access 2003

The image below shows a Microsoft Access Message Box displayed using the Office Assistant. This Message Box appears after the user changes the Appointment Date field to a new value. The alert prompts the user to reconfirm the change.

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

The field name is underlined, and the old and new values are displayed in different colors. Additionally, the company logo appears above the message text.

A Quick Demo

Want to find out quickly how this works?

1. Copy the following VBA Code into a Standard Module in your Database:
   

   Public Function MyAssistant()
   Dim msg As String
   Dim AppointmentDt As Date
   Dim AppointmentDt2 As Date
   Dim logo As String
   
   AppointmentDt = #7/20/2009#
   AppointmentDt2 = #7/25/2009#
   logo = "{bmp D:\Images\CoLogo.bmp}"
   
   msg = logo & vbCr & "Existing {ul 1}Appointment Date:{ul 0}{cf 252} " & AppointmentDt & "{cf 0}" & vbCr & vbCr
   msg = msg & "Replace with {ul 1}New Date:{ul 0}{cf 249} " & AppointmentDt2 & "{cf 0}...?"
   
   With Assistant.NewBalloon
       .Icon = msoIconAlertQuery
       .Animation = msoAnimationGetAttentionMinor
       .Heading = "Appointment Date"
       .text = msg
       .Show
   End With
   
   End Function

2. If you have a small Bitmap File (bmp) somewhere on your machine, then change the path in this line, logo = "{bmp D:\Images\CoLogo.bmp}" in the code to your .bmp image location.

3. See that you have linked the Microsoft Office Object Library File to your Database. If you are not sure how to do it, then do the following:

   • Press Alt+F11 to display the VBA Editing Window (or Tools -> Macro -> Visual Basic Editor), if it is not already visible.

   • Select References from the Tools Menu.

   • Find Microsoft Office 11.0 Object Library (or whatever version is available) in the Available List and put a check mark to select it.

   • Click OK to close the Dialog Box.

4. Click somewhere in the middle of the above VBA Code you have pasted into your Database.

5. Press the F5 Key to Run the Code. You will find the above Message Box, with Office Assistant displaying your Bitmap Image above the message text.

The Enhancement Makes the Difference.

One important point to note here is that the actual data visibility within the message text is underlined and colored. It catches the user's eye quickly and a lot better than showing everything in one Color.

Note: Readers who have not yet learned how to use the Office Assistant with MS-Access Message Boxes may read the following Post and copy the Programs presented there in your Library Database or Project. You can download a sample Database with the Code from there, too.

Message Box using the Office Assistant.

You may also go through the following Posts to learn more interesting ways to use the Office Assistant with Message Boxes:

1. Message Box with Options Menu
2. Office Assistant with Check-Box Menu
3. Selection of Office Assistant

The AppointmentDt_LostFocus() Event Procedure that displays the above Message Box is given below:

Private Sub AppointmentDt_LostFocus()
Dim msg As String

If Me![AppointmentDt].OldValue <> Me![AppointmentDt].Value Then
    msg = AsstLogo & vbCr & "Existing {ul 1}Appointment Date:{ul 0}{cf 252} " & Me![AppointmentDt].OldValue & "{cf 0}" & vbCr & vbCr
    msg = msg & "Replace with {ul 1}New Date:{ul 0}{cf 249} " &  Me![AppointmentDt].Value & "{cf 0}...?"

    If MsgYN(msg, "AppointmentDt_LostFocus()") = vbNo Then
        Me![AppointmentDt].Value = Me![AppointmentDt].OldValue
    End If
    Me.Refresh
End If

End Sub

Text Formatting Codes.

If you check the above message text formatted and stored in the String Variable msg. You can see that it uses certain code values within {} (curly brackets) in the Message String to format the Underline and Color of the text. Old and New Values for the AppointmentDt field are joined with the message text to show them in color.

At the beginning of the message text, I have used the word AsstLogo to add the Company Logo above the Message Text. I will explain this after we check the other codes and usage.

The MsgYN() User Defined Function we have created for displaying a Message Box with Yes and No Option Buttons with the Office Assistant, and learned about other options in the earlier Articles mentioned above.

There are only two types of Codes used within curly brackets to format the message text:

{ul } ' stands for Underline
{cf } ' stands for Color Format and is used with 16 different Color Numbers.

{ul 1} turns On the underline and {ul 0} turns it Off.

{cf 0} (Black Color) is used to change the color of the text to the normal message color.

Example-1: MsgOK "Hi {ul 1}Michael{ul 0}, Welcome to Tips and Tricks."

Result: Hi Michael, Welcome to Tips and Tricks.

Example-2: MsgOK "Hi {cf 250}{ul 1}Michael{ul 0}{cf 0}, Welcome to Tips and Tricks."

Result: Hi Michael, Welcome to Tips and Tricks.

You can copy the text string part of the above examples into the first program above and try them out to find out.

You can use any of the following 16 Color Numbers in the {cf } format string:

Color Codes:

• Black: 0
• Dark Red: 1
• Dark Green: 2
• Dark Yellow: 3
• Dark Blue: 4
• Dark Magenta: 5
• Dark Cyan: 6
• Light Gray: 7
• Medium Gray: 248
• Red: 249
• Green: 250
• Yellow: 251
• Blue: 252
• Magenta: 253
• Cyan: 254
• White: 255

The main challenge when formatting the message text is typing all the codes within curly brackets correctly, as it’s easy to make mistakes in balancing the opening and closing brackets. If this happens, part of the message text or the inserted field values may not appear in the Message Box.

A simple solution is to define all the color codes as Global Constants in a standard VBA module and use these constants wherever needed in the message text. You can declare the color values as constants in a standard module like this:

Declaring Global Constants.

Constant declaration Examples:

Public Const AsstUlOn as String = "{ul 1}"

Public Const AsstUlOff as String = "{ul 0}"

Public Const AsstGreen as String = "{cf 250}"

Public Const AsstBlack as String = "{cf 0}"

Then we can rewrite the example 2 Code above.

Example-2:

MsgOK "Hi " & AsstGreen & AsstUlOn & "Michael" & AsstUlOff & AsstBlack & ", Welcome to Tips and Tricks. "

No curly brackets or color codes to memorize. If you know the color name, then you can format the message quickly, the way you want it.

Valid Image Formats.

To display the Company Logo above the Message Text (or below if you like), you can use two types of Images, a Bitmap Image (bmp) or a Windows Meta File (wmf) Image.

Bitmap Image usage: {bmp ImagePath}

Windows Meta File usage: {WMF ImagePath sizing_factor}

The sizing_factor determines the width of the WMF file displayed and is omitted for Bitmap Files. You can use the WMF image without the sizing_factor value.

The complete Global Constant declarations for the above color values are provided below. You can copy them into a standard module in your Common Library Database to use across multiple applications on your network, or into a single database to test them before implementing them in other applications.

Text Formatting Constant Declarations

Public Const AsstLogo As String = "{bmp D:\Images\CoLogo}"
Public Const AsstUlon As String = "{ul 1}"
Public Const AsstUloff As String = "{ul 0}"
Public Const AsstBlack As String = "{cf 0}"
Public Const AsstDarkRed As String = "{cf 1}"
Public Const AsstDarkGreen As String = "{cf 2}"
Public Const AsstDarkYellow As String = "{cf 3}"
Public Const AsstDarkBlue As String = "{cf 4}"
Public Const AsstDarkMagenta As String = "{cf 5}"
Public Const AsstDarkCyan As String = "{cf 6}"
Public Const AsstLightGray As String = "{cf 7}"
Public Const AsstMediumGray As String = "{cf 248}"
Public Const AsstRed As String = "{cf 249}"
Public Const AsstGreen As String = "{cf 250}"
Public Const AsstYellow As String = "{cf 251}"
Public Const AsstBlue As String = "{cf 252}"
Public Const AsstMagenta As String = "{cf 253}"
Public Const AsstCyan As String = "{cf 254}"
Public Const AsstWhite As String = "{cf 255}"

You can modify the image path on the first line to point to a bitmap image stored in a network’s common folder accessible to all your application users. This allows all your MS-Access applications on the network to use the same image.

Network Server Location Mapping.

Use the Image Path Name in UNC format (like \\ServerName\FolderName\ImageName.bmp) so that if the Disk Drive mapping is changed to a different letter, like K: or J:, etc., the programs will not lose contact with the image on the Network Path.

Instead of adding the AsstLogo Constant along with the message text every time, like:

Msg = AsstLogo & vbCr & "Hi Michael,... " It is better if you add it to the main program code so that it is used automatically for all the messages before they are displayed. See the modified code segment in the main program given above:

Public Function MyAssistant()
With Assistant.NewBalloon
    .Icon = msoIconAlertQuery
    .Animation = msoAnimationGetAttentionMinor
    .Heading = "Appointment Date"
 .text = AsstLogo & vbCr & msg
    .Show
End With
End Function

Modify the above Code segment of the Main Program you have copied from the earlier Article: Message Box uses Office Assistant to use the Company Logo in all your Message Boxes.

Displaying Greetings on Special Occasions.

During special occasions, such as Christmas or Valentine's Day, you can replace the company logo with themed images to delight and surprise your application users.

Check the sample Message Boxes with Christmas Images:

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

Or the Image of the King of Pop Music:

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

Note: These features work only in Access 2003 or earlier versions.

Image Source: Britannica Encyclopedia.

• Filter by Character and Sort
• Animating Label on Search Success
• Form Background with Gradient Color
• MS-Access and Reference Library
• InputBox and Simple Menus

Read More

Microsoft Excel Power in MS-Access

Microsoft Excel Power in MS Access.

Sometimes, Microsoft Access users need report data exported to Excel for further analysis and custom work. In such cases, you can create a macro using the OutputTo command or the VBA DoCmd.OutputTo method to export data from a Table or Query and automatically open it in Excel.

The sample Macro command and parameter settings are illustrated in the image below:

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

The same action in Visual Basic Code is given below:

Function xPort2XL()
    DoCmd.OutputTo acTable, "Products", "MicrosoftExcelBiff8(*.xls)", "C:\My Documents\Products.xls", True, "", 0
End Function

Both can be run from a Command Button. Click on the Main Switch Board.

Imagine if users could perform all the tasks they normally do in Microsoft Excel—such as creating formulas, formatting cells, preparing charts, printing reports, and more—directly from within Microsoft Access. That would provide a completely different and seamless experience. They could even save a copy of their work as an independent Excel workbook outside Access whenever needed.

The Design Task.

Only a few things are involved to implement it in your Projects.

1. Open a new Form in Design View in your Database.

2. Expand the Detail Section of the Form large enough for the size of the Worksheet you would like to display on the Form.

3. Display the Toolbox (View -> Toolbars -> Control Toolbox), if it is not visible.

4. Select the Control Wizards Tool Button (the button with a magic wand icon) to activate it.

5. Select the Unbound Object Frame Tool from the Toolbox.

6. Draw a rectangle large enough to accommodate the Worksheet size you need. Leave about a quarter of an inch of space on all four sides of the Form. A sample image is given below for reference:

   

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

   This action will display a list of Objects that can be inserted into the Unbound Object Frame.

7. See that the Create New Radio Button is selected and Select Microsoft Excel Worksheet from the displayed Object Type List, and click the OK Command Button.

   An Excel Worksheet will be inserted into the Unbound Object Frame area on the Form. 

   If you look at the Menus and Toolbars above, you can see that all of them have been changed into Microsoft Excel Menus and Toolbars now. You can see the Formula Bar and the active cell address in the Formula Bar. Type some expression in the Formula Bar to save it in the active cell if you want to try now.

   But we are now in the Design View of the Form. First, we must save the Form with an appropriate name, and then open the Form in Normal View for use.

   Unlocking Worksheet for Normal Use.

   Before saving the Form, we must change a few Property Values of the Unbound Object Frame (the housing frame of the Worksheet Object) that were drawn initially. The Unbound Object Frame will be in a Disabled and Locked state in the normal Form View. We must modify these properties to work with the Worksheet in the normal view of the Form.

8. Click outside the Worksheet on the Detail Section of the Form to deactivate the Worksheet.

   The Unbound Object Frame with the Worksheet will be in a selected state (if it is not, click on it again to select it), showing the sizing controls on all four sides and corners. Now the Menus and Toolbars have changed back to MS-Access.

9. Select View -> Properties to display the Property Sheet of the Unbound Object Frame and change the following Property Values as shown below:

   • Enabled = Yes

   • Locked = No

10. Close the Property Sheet.

    NB: You may modify the look of your Form with a Title on the Header Section of the Form and change the Property settings of the Form to remove the Record Selector, Navigation Buttons,  Dividing Lines, etc. Add a Command Button at the Form Footer and attach a Macro to close the Form.

11. Select Close from the File Menu and save the Form with the name XlWorkBook.

    Preparing Worksheet for Normal Operations.

12. Open the XlWorkBook Form in Normal View.

    When you open the Form, the Unbound Object Frame will be in the selected state, showing the Excel Worksheet Grid and the sizing controls, as we saw earlier in Design View.

13. Right-click somewhere on the Control to display the Shortcut Menu.

14. Select Edit from the Worksheet Object Option displayed. You can double-click on the Unbound Object Frame to get the same result.

    The Worksheet becomes active now. Menus and Toolbars are now changed to Excel, and the Formula-Bar is showing up above the Form.

    A Few Issues with the Worksheet need correction.

    I understand that working with this worksheet may feel uncomfortable because only a limited area is visible, the scrollbars may not appear correctly, or the worksheet tabs are hidden. Don’t worry—these are all temporary issues that can be fixed quickly.

    Before addressing that, you might wonder how to bring data from an Access table or query into this worksheet. You cannot link a table from the same database directly, nor can you use Excel’s Import Data option via ODBC for this purpose—it’s either ineffective or not recommended for users.

    The simplest solution is to open the table or query in Datasheet View, select the rows, columns, or the entire dataset you need, copy it to the clipboard, and paste it directly into the worksheet.

15. Minimize the xlWorkBook Form, and the Access Menu is back.

16. Open a Table or Query in Datasheet View (this can be done through a macro for Users).

17. Click on the left top corner of Rows and Columns to highlight the entire Table or Query contents (or select a few rows or Columns).

18. Select Copy from the Edit Menu.

19. Minimize the Datasheet View and maximize the xlWorkBook Form.

20. Click on the top left corner of the Worksheet area where you would like to paste the data.

21. Select Paste from the Edit Menu. The copied contents will be pasted onto the Worksheet.

    You can close and reopen the Form, and all the changes you made earlier will be preserved and displayed.

    NB: The normal Excel Paste Special... Menu Options are not available here, but you can get them when the Excel Workbook is open in a different mode.

    You can work with the data as you will do in a worksheet, to add another Worksheet, write a formula, do calculations, create Charts, and so on. There are certain things that you cannot do here (in this state of the Form), like mark an area of the worksheet as Print Area, or do Page Setup changes, or Print Preview of the Worksheet. That doesn't mean that we cannot do these things at all.

    Worksheet EDIT or OPEN Mode.

    These drawbacks can be solved by opening the Worksheet in a different Mode.

22. Click on the Form's detail Section outside the Unbound Object Frame to deactivate the Worksheet.

23. Right-click on the Unbound Object Frame and select Open (earlier we had selected Edit) from the Worksheet Object Menu.

The Worksheet will open in a standard Microsoft Excel window, allowing you to perform any special analysis. You can add additional worksheets, write formulas, create charts, and more.

You can select specific areas of the worksheet as a Print Area, adjust Page Setup, use Print Preview, or print the worksheet. All standard Paste Special options are available for use.

Any changes you make can be updated directly on the MS-Access Form by selecting the Update option from the File menu. There’s no need to locate the Excel file on your disk. If desired, you can also save a copy of the workbook as a regular Excel file using Save Copy As from the File menu.

Once your work is complete, select Close & Return to xlWorkBook: Form to return to the MS-Access Form, ensuring that all your changes are saved.

Isn't it something different to work with Excel from within MS-Access?

1. Roundup Function of Excel in MS-Access
2. Proper Function of Excel in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy-Paste Data From Excel to Access 2007
7. Microsoft Excel-Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel- 2
11. Opening an Excel Database Directly
12. Create Excel, Word Files from Access

Read More

Unsecured Database and Users Log

Unsecured Database and User Log.

When performing data entry or editing important database tables, it is common practice to save a timestamp and user name for each record to mark the event. This is typically implemented using the form's Before Update event procedure. A sample procedure is provided below:

Private Sub Form_BeforeUpdate(Cancel As Integer)
     Me![EditedBy] = CurrentUser
     Me![EditedDt] = Now
End Sub

These fields will be added to the Data Entry/Editing Form from the Table, but will be kept hidden or disabled to prevent manual changes. The familiar =Now() function retrieves the current date and time, while the CurrentUser() Function provides the username of the person currently logged into the database. In this context, we are focusing on the usage of the CurrentUser() function.

The CurrentUser() function returns the correct username in a secured database—that is, a database configured with Microsoft Access security features and shared on a network. When an authorized user opens an instance of the database on their workstation, they must enter their authenticated username and password to gain access. Consequently, the value returned by CurrentUser() will always correctly identify the logged-in user.

The Admin User.

However, if the database is not secured with Microsoft Access Security, the CurrentUser() function will always return the value Admin. Any user who opens an unsecured database on a network is automatically logged in by MS-Access as the Admin user, a member of the Admins group, without being prompted for a user name or password.

We will not delve into Microsoft Access Security issues here (I have already dedicated about nineteen pages to this topic under the “Microsoft Access Security” section on the Main Menu of this site). Instead, we will focus on how to capture the network user name and workstation name correctly when working with an unsecured database shared on a network.

Fortunately, there is a simple trick to capture the network user ID and workstation ID and record these values in your table fields. No lengthy VBA program is required. But first, let’s see where this information is stored and how you can retrieve your own network user ID and workstation ID from your computer’s memory.

Finding Computer Name.

1. Select Run from the Start Menu.
2. Type Cmd and click OK. A DOS window will open up.
3. Type SET and press the ENTER key.

A long list of environmental variables will appear in the DOS (Disk Operating System) window, the core system underlying the Windows Operating System. These variables are loaded into memory when you start your computer or after logging off and back on, and they play an important role in the smooth execution of your day-to-day tasks.

However, for our purpose, we are interested in only two specific values from this list. Look for the following entries:

COMPUTERNAME=your Computer Name

USERNAME=your Network User ID

These two entries may not appear close to each other in the list of Environment Settings — they can be located anywhere within it. If necessary, use the scroll bar to move up or down through the list until you find them. Once you’ve confirmed their presence, type EXIT and press ENTER to close the DOS window.

In Microsoft Access, there is a built-in function called Environ() that allows you to retrieve these values directly from memory and use them anywhere in your database — such as in forms, reports, or VBA procedures.

Example-1: X = ENVIRON("USERNAME")

This will bring the Network User ID of the User from memory.

Example-2: X = ENVIRON("COMPUTERNAME")

This will get the WorkstationId (Computer Name) from the Environment String that we have seen in Memory.

In fact, we can simplify the use of the Environ() function by creating two small, reusable functions and adding them to a global module in our Function Library. Once defined, these functions can be used anywhere in our application — just like the built-in CurrentUser() function — to retrieve the Network User Name and Workstation (Computer) Name directly from memory.

Since CurrentUser is a built-in function, we will use something different that we can memorize easily. We can even use the Parameter Values of the Environ() function as our own function names.

Add the following Functions in the Global Module (Standard Module) of your Function Library Database or in the shared database itself:

Public Function UserName() As String
    UserName = Environ("UserName")
End Function

Public Function ComputerName() As String
    ComputerName = Environ("ComputerName")
End Function

After adding these Functions to the Global Module, you can call them from wherever you want, like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next
      If Err then
        Cancel = True
      Else
       Me![EditedBy] = UserName
       Me![EditedDt] = Now
      End IF
End Sub

OR

If Msgbox("Hi, " & UserName & ", Shutdown Application..?", vbQuestion+vbDefaultButton2+vbYesNo,"Shut Down")=vbYes then
     docmd.Quit acQuitSaveAll
End If 

Similarly, the ComputerName() Function gets the Workstation ID of the User from Memory.

Monitoring Intrusions.

These are also useful to monitor unauthorized use of third parties, or somebody not entrusted to maintain the open database,  from the Network Drive, by sending an Alert Message to you from the Startup Screen or the Main Switch Board Form's Open Event Procedure, like the example given below:

Private Sub Form_Open(Cancel As Integer)
       Call Shell("NET SEND YourNetworkID from: " & ComputerName & " User: " & UserName & " opened: " & Currentdb.Name & " at: " & Now())
End Sub

Note: NET SEND was a Windows XP Command; its new Version is the MSG Command.

Refer to the following Posts for more advanced techniques:

1. Record the Open/Close events and activity of Databases in a log Text File and send alerts to your machine in a pop-up message box, which you can turn on or off as you need them.

   Database Open/Close Event Alerts.

2. Sending Alerts to other User Workstations with useful information that they need to know about, immediately after processing activities take place on one side of the User Groups.

   Sending Alerts to Workstations.

3. How many users are currently online using a particular database, and how to communicate with them by sending Alerts to their Workstations?

   Who is online?

4. Send E-Mails to remote Users with attachments of important Reports in Snapshot Format.

   Automated E-Mail Alerts

Earlier Post Link References:

• Form Background with Gradient Color
• MS-Access and Reference Library
• InputBox and Simple Menus
• Change Form Modes On User Profile
• Positioning POP-UP Forms

Read More

Msaccess Report and Page Totals

MS Access Report and Page Totals.

When designing reports in Microsoft Access, you might have noticed that functions like Sum, Count, or Avg don’t work on the Page Header or Page Footer sections. These aggregate functions only operate in the Report Header/Footer or Group Header/Footer areas — not on individual pages.

That might sound limiting, but there’s a workaround. You can still use simple expressions in the Page Header or Footer to display useful, dynamic information — no VBA code required.

For example, when you build a report using the Report Wizard, Access automatically adds expressions in the Page Footer to show the current date, time, and page numbers.

• The expression =Now() prints the date and time.

• The formula "Page " & [Page] & " of " & [Pages] displays the page numbering.

While these expressions can’t calculate page-wise totals, you can use them to show running sum values on each page — both in the header and footer. It’s a simple, elegant way to make your reports more informative without writing a single line of VBA.

The Targeted Readers of this website.

By going through this website, one might get the impression that to work with MS Access, you must know and use VBA code. This is not true. Many tasks can be automated using Macros. The first MS Access application I developed in 1997 for our Audit Department didn’t contain a single line of VBA code—and it’s still in use today. The only changes I’ve made to that application over the years were converting it from MS Access Version 2 to MS Access 97, then to MS Access 2000, and giving the main switchboard form a facelift.

You can find plenty of material on the Internet covering the basics of MS Access (or at least that was my impression when I started this website). With these resources, you can quickly learn the essentials—Table Design, Relationships, Queries, Forms, Reports, and Macros. Once you’ve mastered the basics and are ready for something more advanced, interesting, and exciting, this website is designed especially for you.

When you’re ready to move beyond the basics, explore the contents here. VBA code is the main driving force behind all the examples presented on this site.

Trial Run Without VBA Code.

Therefore, let us try this experiment without the VBA Code. We need a simple Report to do that.

1. Import the following Tables from the Northwind sample database (C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb):

   • Order Details
   • Products

2. Open a new Query in Design View, do not select a Table or Query from the displayed list, and click Close.

3. Select SQL View from the View Menu to display the SQL Editing Window of the Query.

4. Copy and paste the following SQL String and save the Query with the name Products_ListQ:

   SELECT [Order Details].OrderID, Products.ProductName, [Order Details].Quantity
   FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
   WHERE ((([Order Details].OrderID) Between 10248 And 10300));
   

   We have selected only a few records from the Order Details Table, with OrderID numbers between 10248 and 10300, so that the sample Report will have only a few pages in Portrait Mode.

5. Click on the Product_ListQ Query and select a Report from the Insert Menu.

6. Select Auto Report: Tabular from the displayed list and click OK to create the Report.

7. Save the Report with the name Products_List.

8. Open the Report in Design View and change the design to match the sample image given below.

   

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

9. Add the Red-colored text boxes and Labels to the Design of the Report as explained below:

   • Make a copy of the Quantity field in the Detail Section and position it to the right as shown.

   • Display the Property Sheet (View ->Properties) of the copied control and change the Name Property value to QtyRunningSum.

   • Change the Running Sum Property Value to Overall.

   • Create a Text Box on the Page Header of the Report and change the Name property value of the Text Box to BF (stands for Brought Forward), the Border Color Property Value to 128, and the Font Weight Property Value to Bold.

   • Change the Caption value of the Child Label to B/F:.

   • Create a Text Box on the Page Footer of the Report, change the Name Property Value to Page-Total, Border Color Property Value to 128, and the Font Weight Property Value to Bold.

   • Change the Caption of the attached Child Label to Page Total, as shown in the sample design above.

   • Create another Text Box to the right, change the Name Property Value to CF (stands for carrying Forward), Border Color Property Value to 128, and the Font Weight Property Value to Bold.

   • Change the Name Property Value of the child label to lblCF and the Caption property value to C/F:.

   We have changed the Label's Name property to lblCF so that we can address it in the Program to change the Caption value to TOTAL on the Last Page of the Report. At that point, it is not appropriate to show the label as C/F: (Carry Forward). We will do this with the Program while creating Page Totals on the Report.

   Before that, we will display values in the other TextBoxes created for Running Sum, BF, and CF values without a VBA Program.

   At a later stage, we will hide the Quantity field copied to calculate the Running Sum, but keep it visible now, and use it for calculations on the other two controls, BF and CF, in the Page Header and Page Footer Sections of the Report, respectively.

10. Save the Report.

11. Open the Report in Print Preview and check how the Values are appearing in the copied Quantity Field with the Running Sum Property Value set to Overall All.

    Each Quantity value is summed up in this control, and the last line has the running sum value on the Page. If we put a reference to the QtyRunningSum TextBox in the CF TextBox in the Page Footer area, we can transfer this Value to that Control, showing the cumulative total value at the end of each page.

12. Open the Report in design view and write the following expression in the Control Source Property of the CF TextBox:

    =[QtyRunningSum]

13. Now, preview the Report again and check whether the last total value is appearing in the Page Footer area in the CF TextBox or not. Advance to the next page and check the value showing there, too.

    The next step is to create the Page Header Control BF Value. If you are smart, then you might have already made the change in the BF control. 

    You have two choices to put the Value in this Control, but both have a problem getting it right.

    The first choice is to put a reference to the CF TextBox control in the BF TextBox, like =[CF], to bring the Total Value from the previous page to the current Page Header.

    The second option is to place a reference in the QtyRunningSum text box, such as =[QtyRunningSum]. This approach is also logically correct, assuming that the BF control retrieves the cumulative total from the previous page. This is because the QtyRunningSum control in the Detail section of the current page is processed after the Page Header section’s Format and Print events.

    Read the following Articles where we have discussed the Format and Print Events of the Reports, and learn a few other Tricks we have tried earlier:

    • Hiding Report Lines Conditionally
    • Hiding Report Lines Conditionally-2
    • Hiding Report Lines Conditionally-3
    • Detail and Summary of the same Report

14. Report Line hiding Tricks tried earlier.

15. Choose one of the two options mentioned above and set the Control Source property to either =[CF] or =[QtyRunningSum]. Personally, I prefer the first option, as we’ll be deleting the QtyRunningSum text box when we implement the program for the page total.

16. Open the Report in Print Preview and check the Page Header Section BF Text Box Value.

    In both cases, you will get the same result: the starting running sum value of the first line in the Detail section of the current page. In other words, the value in the BF control equals the cumulative total from the previous page plus the quantity value of the first line on the current page. This happens because even after the Page Header/Footer control values go through the Format and Print events, the CF and BF controls continue to update internally with the changing running sum values. If this were not the case, we could have easily calculated the page total by simply taking the difference between the CF and BF control values on the same page.

    So, if we subtract the first line Quantity value of the first line running sum QtyRunningSum or the previous Page CF Text Box Value, we will get the BF TextBox value correctly.

17. This is the reason why we depend on VBA Code to solve the Page Total problem.

18. Change the expression in the BF Text Box in the Page Header Section as follows:

    • =[CF] - [Quantity]

      Or

    • =[QtyRunningSum] - [Quantity]

    We don't want the QtyRunningSum to display now, and we will hide it from the Detail Section.

19. Click on the QtyRunningSum Text Box, display the property sheet, and change the Visible property value to No.

20. Save the Report and open it in Print Preview.

Now, the Page Header Text Box BF and Page Footer Text Box CF Values are shown correctly. Move to the next page and check the values appearing there.

We will take a copy of this Report to our Page Total example. We will use VBA Code to calculate and update the Page Total and CF values on the new Report.

New Report with VBA Code.

1. Create a copy of the Product_List Report and name it Product_List2.

2. Open the Report in Design View.

3. Modify the expression in the Control Source Property of the BF Text Box in the Page Header as =[CF].

   We will calculate and update only the CF Text Box Value through the Program in the Page Footer Section.

   Since we are not depending on the Running Sum value, we don't have to worry about the side effect we experienced in the earlier method. The correct value will be picked from the previous page control CF Text Box.

4. Delete the QtyRunningSum Text Box from the Detail Section.

5. Select Code from the View Menu to display the Class Module of the Report.

6. Copy and paste the following Code into the Class Module, save and close the Report.

   The Report Class Module VBA Code.

   Option Compare Database
   'gobal declarations
   Dim x_pagetotal As Long, x_CF As Long
   
   Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   If PrintCount = 1 Then
      x_pagetotal = x_pagetotal + [Quantity]
      If Retreat = False Then
            x_CF = x_CF + [Quantity]
      End If
   End If
   End Sub
   
   Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
   If PrintCount = 1 Then
       [PageTotal] = x_pagetotal
       x_pagetotal = 0
       [CF] = x_CF
   End If
   If [Page] = [Pages] Then
       Report.Controls("lblCF").Caption = "TOTAL:"
   End If
   End Sub
   

   Review of Code.

   We have declared two variables, x_pagetotal and x_CF, in the Module's global area. In the Detail Section Print event procedure, we add the quantity values to both variables. In the Page Footer Print event procedure, we update the page total and the CF text box values, then reset the x_pagetotal variable to zero. At this point, we need to check whether the current page number is the same as the last page number. If it is, we change the lblCF label caption to “TOTAL:”.

7. Open the Report in Print Preview and check the Page Total, BF, and CF Text Box Values. Move to the next page, and the control values are there.

The Report image with cut Sections of the Page Header/Footer areas showing the Control Totals on page number three is given below.

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

Caution: I would like to remind you of another important side effect of this method, which I have explained earlier. Since the program relies on page-by-page calculations to generate the correct control totals on each page, jumping to a distant page by typing the page number in the Page Number control—and skipping one or more pages in between—will result in incorrect control values.

If the report is printed directly to the printer, without previewing or navigating between pages as described above, it will print with the correct values on each page.

• MS-Access and Reference Library
• InputBox and Simple Menus
• Change Form Modes On User Profile
• Positioning POPUP Forms
• Synchronized Floating Popup Form

Read More