Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Event Trapping Summary On Datasheet

Introduction

How do we use Event Procedures like LostFocus(), GotFocus() on Datasheet view?

How to display Summation of numeric values on Datasheet view?

For answering both questions, we need a sample Table and a Datasheet Form.

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

  • Order_Details
  • Products

We require the Products table also, because it has a reference in the ProductID field of Order_Detail Table in a Combo Box. We don't want to leave the ProductID field empty on the Order_Detail Table, without the Products Table.


Design a Datasheet Form

  1. To Create a Datasheet Form, click on the Order_Detail Table, select Form from Insert Menu and select Autoform: Datasheet from the displayed list of options in the Formwizard.

    A Datasheet form will be created and will open the Table contents in Datasheet View.

  2. Select Save As. . . from File Menu and give the name Order Details for the Form. The Datasheet Form will be saved, but it will not be closed.
  3. Select Design View from View Menu to change the Form in Design View mode.

The Form in Design View looks like the image given below or may be different depending on your version of MS-Access.

Doesn't matter how the Fields are arranged on the Form, whether in row format or Column Format the data will be always displayed in Datasheet format. The placement of Fields will not affect the way the data displayed on the Datasheet but the Tab Order does.


The Tab Order of Controls

Let us try and find out how the Tab Order of Fields influences the Datasheet View.

  1. Change the Form into Datasheet View (View - - > Datasheet View) and check the order of fields appearing in there.
  2. Change the View into Design mode again and re-arrange the ProductID and UnitPrice fields by switching their places.
  3. Change the View back to Datasheet and inspect the data field order.

    There will not be any change in the Order of Fields displayed from previous View. If you move the Cursor using Tab Key then it moves in the same order of the fields' placement as you have seen earlier before switching the fields.

  4. Change the Form back to Design View again.
  5. Select Tab Order. . . from View menu. Click at the left border of the UnitPrice field on the Tab Order Control, click and drag it up and place below the OrderID field.

    Tip: You can click on the Auto Order Command Button to re-arrange the Tab Order according to the field placements on the Form.

  6. Open the Form in normal view now and check the change of field placements.

The Unbound Text Box

We will add one Unbound Text Box on the Form to calculate the Extended Price after adjusting Discounts of each item.

  1. Open the Form in design view, if you have closed it.
  2. Drag the Form Footer Section down to get more room to place another Text Box below, or you can place it to the right of the Discount Field too.
  3. Create a Text Box and write the formula =(1-[Discount])*[UnitPrice]*[Quantity] in it.
  4. While the Text Box is still in selected state display the Property Sheet (View - -> Properties).
  5. Change the Format Property value to Currency format. Change the Name Property value to Extended Price.
  6. Open the Form in normal view and check the newly added Text control's heading on the top. It will be something like Text10:.

    In Datasheet View of Forms MS-Access uses the Caption of the Child Labels attached to the Text Boxes as Field headings. We have changed the Name Property of the Text Box to Extended Price, but that is ignored here.

  7. Now, change the Form into design view and delete the Child Label attached to the Extended Price Text Box.
  8. Change to Datasheet view again and check the field name appearing on the top, it will be Extended Price now.

Datasheet Event Procedure

  1. To try an Event Procedure on the Datasheet view, copy and paste the following VBA Code into the Form's Code Module (View - -> Code to display the Code Module of the Form) and save the Form with the Code.
    Private Sub UnitPrice_LostFocus()
    Dim newUnitPrice As Double, msg As String
    Dim button As Integer
    
    button = vbQuestion + vbYesNo + vbDefaultButton2
    
    If Me![UnitPrice] = Me![UnitPrice].OldValue Then
       msg = "Replace UnitPrice: " & Me![UnitPrice].OldValue & vbCr & vbCr
       msg = msg & "with New Value: " & Me![UnitPrice]
    
       If MsgBox(msg, button, "UnitPrice_LostFocus()") = vbNo Then
            Me![UnitPrice] = Me![UnitPrice].OldValue
        End If
    End If
    
    End Sub
    

    We will attempt to trap the change in the UnitPrice field and will ask the User to confirm whether to retain the change or Cancel it.

  2. Open the Form in datasheet view and make some change in the UnitPrice Field and leave the Field by pressing Tab Key or Enter key.

A Message Box will appear asking for permission to retain the change or to cancel it.

Datasheets can be programmed with Event Procedures (Field level or Form level) for validation checks and display of information.

Displaying of Summary Information

Method-1

We will attempt to answer the second Question we have raised on top of this page.

  1. Open the Order_Details Form in Design View.
  2. Drag the Form Footer Section down to get enough room to place two Text Boxes. Create two Text Boxes in the Form Footer Section.
  3. Write the formula =Sum([Quantity]) in the first Text Box.
  4. Display the Property Sheet of the Text Box and change the Name Property value to TOTALQTY.
  5. Write the formula =Sum((1-[Discount])*[UnitPrice]*[Quantity]) in the second Text Box.
  6. Change the Name Property Value to TOTALVALUE.

When we open the Order_Details Form in Datasheet View it will calculate the Summary V
alues in TOTALQTY and TOTALVALUE Text Boxes on the Footer of the Form, but we must do something to display it.

The first thought that comes into one's mind is to create a MsgBox and display the results in it on some Event Procedure of the Form. Besides, changes may takes place on the records and they should reflect in the result summary values and we must be able to refresh the change before displaying it again.

We will implement this method before we settle down with a better one.

  1. Copy and paste the following Code into the Form's Code Module and save the Form:
    Private Sub Form_DblClick(Cancel As Integer)    
    Dim msg As String
         Me.Refresh
        msg = "Total Quantity = " & Me![TOTALQTY] & vbCr & vbCr
        msg = msg & " | Total Value = " & Format(Me![TOTALVALUE], "Currency")
    
         MsgBox msg
    End Sub
    
  2. Open the Form in Datasheet View.
  3. Double-Click on the Record Selector at the left border of the Form.

    A Message Box pops up with the Summary Values from the Text Boxes in the Form Footer Section.

  4. Make some changes on the Quantity/UnitPrice Fields and try Step-3 again. The change of Value will reflect on the Message Box.
  5. You can filter the Data on ProductID or on OrderID by Right-Clicking on these fields and selecting Filter by Selection or other Options available on the displayed Shortcut Menu and by executing Step-3 to get the Summary of selected records.

Method-2

After trying out the above method your response may be something like "Yah.. it serves the purpose, but it doesn't give the impression of a sophisticated method. After all it takes so many clicks and pop-up Message Boxes". I agree with you too.

With a small change of the above Code we can make the results the way you like it, I hope!.

  1. Open the Form in Design View.
  2. Display the Property Sheet (View - - > Properties).
  3. Select the Mouse Move Property and select Event Procedure from the drop down control.
  4. Click on the build (. . .) button at the right side of the Property to open the Form's Code Module.
  5. Cut the Code lines from within the Form_DblClick() Event Procedure:
    Private Sub Form_DblClick(Cancel As Integer)
    
    End Sub
    

    Leave the above lines alone and paste the Code into the Form_MouseMove() Event Procedure.

  6. Change the line that reads:

    MsgBox msg

    to

    Me.Caption = msg

    After the change the Code will look like the following:

    Private Sub Form_DblClick(Cancel As Integer)
    
    End Sub
    
    Private Sub Form_MouseMove(button As Integer, Shift As Integer, X As Single, Y As Single)
    Dim msg As String
        Me.Refresh
        msg = "Total Quantity = " & Me![TOTALQTY] & vbCr & vbCr
        msg = msg & " | Total Value = " & Format(Me![TOTALVALUE], "Currency")
        Me.Caption = msg
    End Sub
    
  7. Open the Form in Datasheet View and move the Mouse over into the data area by crossing the Field Headings or the Record Selectors at the left side.
  8. Check the Title Area of the Datasheet View and you will find the Summary information is displayed there. A sample image is given below:

Now you can try changing the field values or filtering the data and moving the Mouse over the Field Headings or Record Selectors at the left to get the result on the Title Bar instantly. No Message Boxes or Double-Clicks and what you say about that?

Share:

3 comments:

  1. Keep posting stuff like this i really like it

    ReplyDelete
  2. [...] the form Caption area. If you want to learn more tricks with Datasheet, check the following Link: Event Trapping Summary on Datasheet __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]

    ReplyDelete
  3. […] you go through the following link you will have enough idea and required vba code to try out them: Event Trapping Summary On Datasheet | LEARN MS-ACCESS TIPS AND TRICKS You can double-click on a record (or use some other Event) on the datasheet to run an event […]

    ReplyDelete

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captur...

Labels

Blog Archive

Recent Posts