Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Office Assistant And Msgbox Menus-2

This is the continuation of last Week's Article: Office Assistant and MsgBox Menus. Readers may go through that Page first, if you have not already done so, before continuing.

Last week we have learned how to use Office Assistant for MsgBox in a simple way and seen how to create a Menu and present it to the User, with the Labels Property of the Balloon Object.

We have seen that we can display the Menu from Labels().Text Property in three different ways by setting the Values of BalloonType Property:

  1. BalloonType = msoBalloonTypeButtons

    The User can click on any of these Buttons to select one of the Options presented.

  2. BalloonType = msoBalloonTypeBullets
  3. BalloonType = msoBalloonTypeNumbers

The second and third BalloonType Values displays the Labels().Text in different styles but they cannot be selected by the User. So, we can use these options for different situations in Programs.

Let us look little closer to the overall behavior of the Balloon Object when you display MsgBox with Office Assistant, to understand it better.

If you go back and try those earlier examples, you can see that while a MsgBox with the Office Assistant's Balloon Object is displayed you cannot click or work with any other object in the database unless you dismiss the Balloon by responding to the actions you are suggested to do, like clicking on the OK or Cancel Button and so on. This is true in the case of the normal MsgBox() Function of MS-Access too. But, Office Assistant has some solution to this rigid behavior of Message Boxes.

When the Balloon is displayed, you cannot open a Form or Report to check something on it, before clicking the Yes or No Button on the MsgBox because the Mode Property of the Balloon Object is set with default value msoModeModal.

I have not introduced the Mode Property to you in the earlier examples to avoid overcrowding of usage rules. Once you are through with the basics it will be easier to understand other things associated with it better. You have already seen that you can create Message Boxes using Balloon Object of Office Assistant with few lines of Code and display them in style without using these properties.

The Mode Property can be set with three different values to control the behavior of the Balloon Object.

Mode = msoModeModal (default)

This value setting forces the User to dismiss the Balloon by responding to the suggested action before doing anything else, like normal MS-Access MsgBox. You have to click on one of the Buttons (if more than one is showing like OK and Cancel) on the MsgBox to dismiss the Balloon before you are allowed to do anything else.

Mode = msoModeModeless

This value setting allows the User to access other database objects, while the Balloon is active. But, this forces the use of another Property CallBack, to run separate Sub-Routine to do the testing of selection of choices or do different things based on the choice and finally to Close the Balloon Object.

Mode = msoModeAutoDown

This value setting dismisses the Balloon (MsgBox) automatically if you click somewhere else ignoring the Balloon.

The first and last Mode Property value setting and their usages are very clear. But, the second one (msoModeModeless) needs the CallBack Property Value set with the name of a valid Sub-Routine otherwise the Balloon will not work. You cannot load the CallBack Property with an empty string either because that will trigger an Error.

So let us see how we can re-write the earlier Program to understand the usage of Mode Property set with the value msoModeModeless and Callback Property with the name of a Sub-Routine to handle the selection of option displayed in the Menu.

The Modified Code with Mode and CallBack Property Settings and the sample Code for the required Sub-Routine 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 Sub-Routine name myProcess. When the User clicks on one of the Options from the displayed MsgBox the MyProcess() Sub-Routine is called by the Balloon and passes the required Parameter Values.

There are three parameters passed to the Sub-Routine 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 identifying the Balloon that called the Sub-Routine, if there are more than one Balloon active in memory at the same time.

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

The lbtn Variable will have the value of 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 Sub-Routine, 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.

The bln.close 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 Check-Boxes in MsgBox.

Share:

Office Assistant and MsgBox Menus

In last Week's Article: Color and Picture in Message Box we have seen a method to use Office Assistant quickly to display a Message Box with formatted text. We are not going to discuss further on the formatting part but I have a general feeling that Readers would like to know how this simple method can be used to obtain responses from Users, out of several options presented to them, and do different things based on their selection of choices.

I have already covered this topic by creating Functions like MsgOK(), MsgYN(), MsgOKCL() etc., with the use of Office Assistant. These can be called with only the Message Text Value alone or Message Text and Title Values as Parameters, from anywhere within the Application like the built-in Function MsgBox(). I made an attempt to simplify the usage of Office Assistant through the above mentioned Functions and others, which otherwise needs several property values to be passed to the Office Assistant's Balloon Object for displaying.

But, in the simplification process the method used in those Functions is not fully understood by several readers.

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

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

In the example Code presented in last week's Post: Color and Picture in Message Box we came across several Properties of the Balloon Object of Office Assistant that can be set with values before the Message Box is displayed.

We are going to work with these properties directly, so that it is easy to understand their usage, rather than passing values for them through the Parameter List in the function definition.

Following are some of these properties:

  • Animation
  • Icon
  • Heading
  • Text
  • BalloonType
  • Button

NB: If you have not already attached the Microsoft Office Object Library to your Database, to try out the examples given here, then do that by following the procedure given below.

  • Press Alt+F11 to display the VBA Code Window (or Tools - - >Macro- ->Visual Basic Editor).
  • Select References from Tools Menu.
  • Find Microsoft Office Object Library in the Available List and put a check mark to select it.
  • Click OK to close the Dialog Box.

The value for Animation alone has about thirty five different choices which are defined as Constants in the Microsoft Office Object Library. The constant values for Balloon Properties Animation, Button, Icon and BalloonType are given below for reference:

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

Animation and Icon properties are always set with one of the above values based on what we are trying to convey to the User. By default the OK button will appear. If any other Button or Buttons Group is required then the Button Property must be set with one of the above values. The BalloonType is used with Labels Property only. We will look into the Labels property at the later part of this Article.

I am sure when you go through these simple straightforward examples you will be better informed about the usage of Office Assistant, its methods and will start using them in your Applications.

I will reproduce the Code here with simple changes, which we have seen in last week's Article, and go through it before we make changes in them 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

Press Alt+F11 to display the VBA Editing Window. Select Module from Insert Menu to create a new Standard VBA Module. Copy and Paste the above Code into the Module. Click somewhere in the middle of the Code and Press F5 to Run it.

In the above example the .Show() method displays the Message Box after setting the other Property Values. The Text property value shows the body text of the Message Box and Heading is the Title Text in Bold letters. The Animation property can be set with one of the 35 different options given above. The Icon Property value we have changed to Information type here.

If we want to obtain the responses of the User to do different things then we need to bring in the Button Property into the Code. Let us say if the User needs to proceed with the report preparation process then she must click the OK Button otherwise Cancel Button. To evaluate the response received from the User and to take action accordingly we must write code for that further down in the Routine.

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

Compare the changes made in the new Code with the earlier one to find the difference.

If the User clicked the OK Button (-1 is returned in Variable R, Cancel=-2) then the ReportProcess Macro is run otherwise the Program ends doing nothing.

We can give different choices to the User in the form of a Menu with the use of Labels Property and perform the actions based on User's response. See the example code given below:

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 with appropriate changes before attempting to Run it.

The dimension of the Labels() Property Value can be up to a maximum of 5 only. The BalloonType Value msoBalloonTypeButtons allows the User to click on one of the Options to select it and the Index value of the item clicked is returned in Variable R. We have set the Value Button = msoButtonSetNone to remove the OK Button from appearing so that the User will click only on one of the Options displayed.

There are two more BalloonType Property values available: msoBalloonTypeBullets and msoBalloonTypeNumbers. These are not selectable, like msoBalloonTypeButtons, and used 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 BalloonType Property Changes are given below:

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

Share:

Color and Picture in Message Box

Image of a Microsoft Access Message Box, with the use of Office Assistant, is shown below. The Message Box pops up after the User changes the Appointment Date field value with a new date and the Message Alert asks the User to reconfirm whether she really needs to replace the earlier Date 20/07/2009 with the new value 25/07/2009 or not.

The Field Name text is underlined and field values (old and new) are displayed in different Color. Company Logo is displayed above the message text.

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}" of the Code to your .bmp image location.
  3. See that you have linked 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 Tools Menu.
    • Find Microsoft Office 11.0 Object Library (or whatever Version 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 F5 Key to Run the Code. You will find the above Message Box with Office Assistant displaying your Bitmap Image above the message text.

One important point to note here is that the visibility of the actual data displayed within the message text with underline and Color. It catches the eye of the User quickly and looks lot better than showing everything in one Color.

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

Message Box with Office Assistant

You may also go through the following Posts to learn more interesting ways to use 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

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) embedded in the Message String to format Underline and Color to the text. Old and New Values from the AppointmentDt field is 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 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 type of Codes used within curly brackets to format the message text:

{ul } ' stands for Underline
{cf } ' stands for Color Format and 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 text to 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 may copy the text string part of the above examples into the first program given 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 only difficulty you may experience while formatting the message text is to type all the codes in curly brackets and it is likely that you may make errors in balancing the opening or closing brackets. In such cases that part of the message text or the field values added to it will not appear in the Message Box when displayed.

There is an easy way out to this problem. Define all the above Color Codes as Global Constants in VBA Standard Module and use the Constants wherever we need them in the message text. You can declare the Color Values as Constants in a Standard Module like the following:

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 re-write the example-2 Code given above as:

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.

To display Company Logo above the Message Text (or below if you like) you can use two types of Images, Bitmap Image (.bmp) or 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 .wmf Image without the sizing_factor Value as well.

The complete Global Constant declarations for above Color values are given below. You may copy them into the Standard Module of your Common Library Database to use across your Applications on the Network or into one of your Databases to try them out before implementing it in your other Applications.

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 may modify the Image Path on the first line to point it to the Bitmap Image on the Network's common folder accessible to your Application Users so that all your MS-Access Applications on the Network can use this image.

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 loose 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 to all the messages before they are displayed. See the modified Code segment on 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 with Office Assistant to use the Company Logo in all your Message Boxes.

During special Occasions like Christmas or Valentines Day you can replace the Company Logo Image with other images suitable to these occasions to give surprises to your Application Users.

Check the sample Message Boxes with Christmas Images:

Or the Image of King of Pop Music:

Image Source: Britanica Encyclopedia

Share:

Microsoft Excel Power in MS-Access

There are times that Microsoft Access Application Users ask for Report Data exported into Excel so that they can work on it and do their own customized analysis. In this case we can create a Macro with the Macro Command OutputTo or Visual Basic Docmd to export the Data from a Table or Query and open the Data in Excel automatically.

The sample Macro Command and Parameter setting will look like the image given below:

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 from the Main Switch Board.

But, if the User is able to do whatever he/she does in Microsoft Excel, like writing expressions for Calculations, Formatting, Chart Preparations, Printing and so on from within Microsoft Access itself that will be a different experience altogether, right?. They can even save a copy of whatever they do from within Access as an independent Excel Workbook outside MS-Access, if needed.

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 to 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 Tool Box.
  6. Draw a rectangle large enough to accommodate the size of the Worksheet you need leaving about quarter of an Inch space on all four sides on the Form. A sample image is given below for reference:

    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 OK Command Button.

    An Excel Worksheet will be inserted into the Unbound Object Frame area on the Form. The Excel Worksheet will be in active state.

    If you look at the Menus and Toolbars above you can see that all of them are changed into Microsoft Excel Menus and Toolbars now. You can see the Formula Bar and the active Cell Address to the left of the Formula Bar. You can 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 Design View of the Form. First we must save the Form with an appropriate name and open the Form in Normal View for use.

    Before saving the Form we must change few Property Values of the Unbound Object Frame (the housing frame of the Worksheet Object) that we have drawn initially. The Unbound Object Frame will be in Disabled and Locked state in normal view of the Form. These properties we must modify to work with the Worksheet in 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 selected state (if it is not then click on it again to select it) showing the sizing controls on all four sides and corners. Now the Menus and Toolbars 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 and Dividing Lines etc. Add a Command Button at the Form Footer and attach a Macro to Close the Form.

  11. Select Close from File Menu and save the Form with the name XlWorkBook.
  12. Open the XlWorkBook Form in Normal View.

    When you open the Form the Unbound Object Frame will be in selected state showing the Excel Worksheet Grid and the sizing controls, as we have seen them 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 now changed to Excel and the Formula-Bar is showing up above the Form.

    I know you don't feel comfortable to work with this Worksheet because you have only limited area of the worksheet available, or the Scrollbars are not showing up properly to move to a wider area of the Worksheet or the Worksheet Tab is not visible etc. But, these are all temporary issues and can be solved in no time.

    Before going on to that how do we bring the data from Access Table or Query into this Worksheet. You cannot link a Table of this Database to the Worksheet and you cannot import the contents of a Table or Query into this worksheet either. There is an option Import Data in Data Menu of Excel to get external data through ODBC Connection, but this will not work for bringing data from the same database or cannot be recommended for Users.

    There is only one easy way that I could find, that is open the Table or Query in Datasheet View, Copy the required portion (Rows or Columns) or whole contents to the Clipboard and Paste it 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 few rows or Columns).
  18. Select Copy from Edit Menu.
  19. Minimize the Datasheet View and Maximize the xlWorkBook Form.
  20. Click on the left top corner of the Worksheet area where you would like to paste the data into.
  21. Select Paste from Edit Menu. The copied contents will be pasted on to the Worksheet.

    You can close and open the Form again and the data on the Form will have all the changes that you have made before closing it.

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

    You can work with the data as you will do in a regular Worksheet, like add another Worksheet, write 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.

    These draw backs 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 have selected Edit) from the Worksheet Object Menu.

The Worksheet will be opened in a normal Microsoft Excel Application Window and you can do whatever analysis you want to do here. You may add more Worksheets in the workbook, write formula, create Charts and so on.

Here, you can mark a selected area of the Worksheet as Print Area, change Page Setup, Print Preview or Print the Worksheet. The normal Paste Special. . . menu options are available here.

You can update these changes on the MS-Access Form by selecting Update option on the File Menu. You don't have to search for the Excel File on disk later. You can even save a copy of this Workbook as a regular Workbook by selecting the Option Save Copy As . . . from File Menu, if needed.

When your work is complete in Excel select the option Close & Return to xlWorkBook: Form to come back to MS-Access Form saving whatever work you have done.

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

Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts