Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Unsecured Database and Users Log

Introduction

When Data Entry or Editing actions are performed on important Tables in Databases a TimeStamp with User Name is normally saved in each record to mark that event. This is done through the Form Before Update Event Procedure. A sample procedure is given 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 function =Now() gives the Date/Time Stamp value and the Current User built-in function provides the User Name, from the current instance of the database opened in the Workstation. We are focusing on the usage of the CurrentUser() method.

The CurrentUser function can return the User Name value correctly from a secured database (from a database that is implemented with Microsoft Access Security features) shared on a Network. When each authorized User attempts to open an instance of the database in her Workstation she has to provide her authenticated User Name and Password before getting access to the database or its other Objects. Consequently, the values returned by the CurrentUser() Function will always point to the correct User.

The Admin User.

But, if the database is not implemented with Microsoft Access Security then the CurrentUser() function will always return the value Admin. Any user who opens an Unsecured Database in a network will be silently logged in by MS-Access as Admin User, as a member of the Admins Group Account, and will never prompt for any User Name or Password.

We are not going to explore the Microsoft Access Security issues involved here (I have already dedicated about nineteen Pages on this issue under the Microsoft Access Security topic on the Main Menu of this Site) but how to get the Network User Name and the Workstation Name correctly if we want to record those values, in an unsecured database shared on a Network?

Yes, there is a simple Trick that you can use to capture their Network User Names and Workstation IDs and record those values in the Table fields. No, we don't need any lengthy VBA program to do this. But, first, let us see from where we will get this information. We will try to find your own Network User ID and Workstation ID stored in your computer's memory.

Finding Computer Name

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

A lengthy list of Environmental Values is displayed in the DOS (Disk Operating System, the main driving force behind the Windows Operating System) Window. These are loaded into memory when you turn on the Computer or after Logoff/Logon actions and influence the smooth running of your data-to-day tasks. We are interested in only two values among them and look for the following Values:

COMPUTERNAME=your Computer Name

USERNAME=your Network User ID

These may not be in the nearby lines in the Environment Settings but can appear anywhere within the list, if necessary, use the Scroll Bar to move the list up or down, when you are sure you have spotted these values then type Exit and press the ENTER key to come out of the DOS window.

There is a built-in function ENVIRON() in MS-Access that we can use to capture these values from Memory and use it where we want them in our Database.

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 write two simple Functions with the Environ() Function and add them to our own Function Library in the Global Module. This will simplify the usage of this Function and we will only need to use the Function Name that we have defined, like Current User, to get these values 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 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 will get 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 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 Windows XP Command, its new Version is 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 popup 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 with 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:

Share:

Msaccess Report and Page Totals

Introduction

Ms-Access Functions Sum, Count, Avg, etc., cannot be used in Page Header or Footer Areas of Reports for creating Page-wise summary values. These will work in Report Group Header/Footer or in Report Header/Footer areas only.

That doesn't mean that you cannot do any calculations on Page Header/Footer Areas. You can write expressions to print useful information without using the above categories of functions.

For Example: When you design Reports with the built-in Report Wizard you have seen MS-Access uses expressions in the Page Footer Area to display Date/Time and Page Numbers.

The Report Wizard uses the Function =Now() in a TextBox to print Date and Time and the Expression ="Page " & [Page] & " of " & [Pages] to display Page Number information in the Page Footer area.

Even though we cannot use this method to calculate and print Page-wise Total Values, we can use it to print the Running Sum values on every page in Page Header and Page Footer Areas.

We don't have to struggle with any VBA Code for this.

The Targeted Readers of this website.

By going through the contents of this website one gets the general impression that if you want to do something in MS-Access then you must know and use VBA Code. This is not true. You can do plenty of task automation with Macros. The first Ms-Access Application that I have developed in 1997 for use in our Department is without a single line of VBA code and it is still in use. The only change that I have made to that Application is to convert it from MS-Access Version 2 to MS-Access97, later to MS-Access2000, and modified the Main Switch Board Form to give it a better look.

You can get tons of materials from the Internet on basic usage of MS-Access (or that was a general impression that I had when I started this Website) and quickly attain the basic knowledge on Table Design, Relationships Design, Queries, Forms, Reports, and Macros. Once you are through with the basics and when you are on the lookout for something different, advanced, interesting, and need a more exciting experience with MS-Access usage then this Website is primarily targeting Readers like you.

Once you are bored with the basic stuff and on the lookout for something better browse the contents on this website. Here, the VBA Code is the main driving force behind all the examples given on this Site.

Trial Run Without VBA Code

So, let us try out part of 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 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; from OrderID numbers between 10248 and 10300; so that the sample Report will have only about five or six pages in Portrait Mode.

  5. Click on the Product_ListQ Query and select a Report from 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 look like the sample image given below.

  9. Add the Red-colored TextBoxes 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 Over All.

    • 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 on 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 this Label's Name property value 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 try to display values into the other TextBoxes we have created for Running Sum, BF, and CF values without VBA Program.

    At a later stage, we will hide the Quantity field copied for calculating Running Sum but we will keep it visible now and use it for calculating values, on the other two controls BF and CF on 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 Over All.

    Each Quantity value is summed up in this control and the last line has the total so far value on the Page. If we put a reference in the QtyRunningSum Text Box in the CF Text Box in the Page Footer area, we can transfer this Value into 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 Text Box:

    =[QtyRunningSum]

  13. Now, preview the Report again and check whether the last total value is appearing in the Page Footer area in CF TextBox or not. Advance to the next page and check the value appearing 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 by now without reading further down.

    You have two choices to put the Value in this Control but both have a little problem taking care of to get 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 one is to put a reference in the QtyRunningSum Text Box, like =[QtyRunningSum]. This is also logically correct assuming that the BF control will take the total cumulative value from the previous page because the QtyRunningSum control in the Detail Section on the current page comes after the Page Header Section Format and Print Events.

    Report Line hiding Tricks tried earlier

    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:

  14. Choose one of the two options I have presented above and change the Control Source Property Value with either the expression =[CF] or with =[QtyRunningSum]. If you ask me, I prefer the first one because we are going to delete the QtyRunningSum TextBox when we use the Program for Page Total.

  15. 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 of the Detail Section of the current page. In other words, the Value in the BF control is equal to the cumulative value of the previous page plus the Quantity Value in the first line of the current page. This is because even after the Page Header/Footer control values undergo the Format and Print Events; the controls CF & BF internally keep on populating with changing Running Sum values. If this was not the case we could have easily calculated the Page Total value by finding the difference between CF & BF control values on the same page.

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

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

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

    • =[CF] - [Quantity]

      Or

    • =[QtyRunningSum] - [Quantity]

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

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

  18. 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 make a copy of this Report to our Page Total example. We will use VBA Code to calculate and update 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 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 have 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 Detail Section.

  5. Select Code from 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 global area of the Module. In the Detail Section Print Event Procedure, we are adding up the Quantity Values in both Variables. At the Page Footer Print Event Procedure we are updating the Page Total, CF TextBox values, and resetting the x_pagetotal Variable to zero. Here, we are checking whether the current Page Number is equal to the Last Page Number, if so then 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 check the control values there too.

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

NB: Here, I would like to remind you about the other serious side effect of this method which I have already explained earlier. Since the Program is depending on the Page by Page calculations to arrive at the correct Control Totals on each page, if you jump from one page to a distant page and skip one or more pages in between, by typing the Page Number in the Page Number Control, then these control values will not be correct.

If the Report is printed directly to the Printer, without Previewing and moving between pages as explained above, it will print with correct values on each page.

Share:

Detail and Summary from same Report

Introduction.

You don't have to design two different Reports; one for a Detail Listing of records with Group-wise Totals and another one for Group-wise Totals alone. We can play a small trick to get both outputs from the same Report depending on the User's choice.

Recommended reading before proceeding with this topic:

  1. Hiding Report Lines Conditionally
  2. Hiding Records and Group Footer Calculations
  3. Hiding Group Header/Footer and Detail Sections

The Report.MoveLayout Property.

For hiding Report Lines conditionally there are other methods too. For example, the following VBA Code (instead of the earlier simple method we have tried) can give you the same result for hiding Detail Section Report Lines:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  If [OrderID] = 10280 Or [OrderID] = 10297 Then
      Report.MoveLayout = False
      Report.NextRecord = True
      Report.PrintSection = False
  Else
      Report.MoveLayout = True
     Report.NextRecord = True
      Report.PrintSection = True
  End If

End Sub

We must set all the three Report-Property Values shown above in different combinations to get the same result. We have already explored the PrtDevMode, and PrtMIP Report Properties, and learned how to change Paper Size and Page Orientation, Margin Settings, and Column Settings through the  Program, while previewing or sending the Report to a Network Printer.

If you would like to know more details about the above Report-Property settings you may search VBA Help Documents. You can get the Help Document related to this topic quickly if you open any Code Module and Type Report.MoveLayout and press F1 while the cursor is next to the Text or in the Text.

We will continue with our new Trick. We will use a copy of last week's sample Report Order_Details2 for this experiment. If you already have a Report with Group-wise Sub-Totals you may use that too. If you are using your own Report then ensure that you are displaying the Group Item Value or Description along with the label Sub-Total on the left side of the sub-total value control in the Group Footer.

Sample Report Design View.

We will hide the Group Header and Detail Sections when the User opens the Report for Summary Preview or Print. A sample Report Image in Design View is given below.

We will replace the Code written for an earlier example, with a new Program so it is better to make a copy of that Report for our trial run now.

  1. Make a Copy of the Order_Details2 Report and Paste it with the name Order_Details3.

  2. Open the Report in Design View.

  3. Write the Expression =Sum([Quantity]) in the empty Text Box (we have removed this for our earlier example) in the CustomerID Group Footer below the Quantity field in the Detail Section.

  4. Write the same expression in the Report Footer empty Text Box to take Quantity Report level Total.

  5. Display the Code Module of the Report (View - - >Code).

  6. Delete the existing VBA Code from the Code Module (Class Module is the correct term for Report and Form Modules).

  7. Copy and Paste the following VBA Code into the Module and Save the Report.

The Report Class Module Code.

Option Compare Database
'Global declarations
Dim x_opt As Integer

Private Sub Report_Open(Cancel As Integer)
   If IsLoaded("MainSwitchBoard") Then
       x_opt = Forms![MainSwitchBoard]![Opt]
   Else
       x_opt = 1
   End If
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If x_opt = 2 Then
        Cancel = True
    Else
        Cancel = False
    End If
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    If x_opt = 2 Then
       Cancel = True
    Else
       Cancel = False
    End If
End Sub

As you can see; the above code is not that complicated to understand. We are reading the Report Option settings from the MainSwitchBoard in the Report_Open() Event Procedure and loading that value into a Global Variable x_opt (defined at the top of the Module below the Global default declaration Option Compare Database). Use IsLoaded() Function to check whether the MainSwitchboard Form is open or not before attempting to read the Value from the Option Group control from the Form.  Otherwise, the Report is open normally for a Detail print preview.

The IsLoaded() Function.

Copy the following Code for the IsLoaded() Function and paste it into a Global Module (Standard Module) of your Project and save it:

Public Function IsLoaded(ByVal strForm As String) As Boolean
'---------------------------------------------------------
'Checks through the Forms Collection and if the Form is
'loaded in Memory then Returns TRUE else FALSE
'---------------------------------------------------------
Dim varFrm As Form

On Error GoTo IsLoaded_Err

IsLoaded = False

For Each varFrm In Forms
  If varFrm.Name = strForm Then
   IsLoaded = True
  End If
Next

IsLoaded_Exit:
Exit Function

IsLoaded_Err:
IsLoaded = False
Resume IsLoaded_Exit
End Function

The IsLoaded() Function checks through the list of all Open Forms looking for the MainSwitchBoard Form and if it is open then returns TRUE otherwise FALSE.

If the Report output option setting on the Main Switchboard is 1 then the Report will print normally with all Report Sections including the Group Header/Footer Sections.

If the option setting is 2 then the Format Event of Report CustomerID Group Header and Detail Section is canceled (these Sections are hidden) and shows only the Page Header/Footer, CustomerID Group Sub-Totals, and Report Total in Print Preview or Print.

The Report Option Group.

We will create a Report Option Group in the MainSwitchBoard Form (or you may create a new sample Form as shown below) to set and launch our Report by using one of the options provided for Detail and Summary Views.

  1. Open a new Form or your Main Switchboard (Control Screen) Form in Design View.

  2. Check whether the Control Wizard (with the magic wand icon) on the ToolBox is in the selected state, if not then select it.

  3. Select the Option Group Tool from the ToolBox.

  4. Draw a rectangle on the Form as shown above. The Options Group Wizard will open up.

  5. Type Detail Report, press TAB Key and Type Summary Report for two options, and click Next.

  6. Accept Detail Report as the default choice and click Finish.

  7. You may drag and position the Child Label attached to the Options Group as shown on the design above and change the Label Caption as Report Options.
  8. Click on the Option Group outer frame to select it and display the Property Sheet (View - - > Properties).

  9. Change the Name Property Value to Opt. (no dot at the end)

  10. Select the Command Button Tool from the ToolBox and draw a Command Button below the Option Group Control.

  11. Display the Property Sheet of the Command Button.

  12. Change the Caption Value to Report Preview.

  13. Set the Hyperlink SubAddress Property value to Report Order_Details3. Don't forget to leave a space between the word Report and your Report Name.

  14. Save the MainSwitchBoard Form.

  15. The Demo Run.

  16. Open it in a normal view. Click on the Report Preview Command Button to open the Report after setting the Detail Report or Summary Report Option in the Option Group Control.

NB: Don't forget to close the earlier Report Preview before attempting to open it for different Options.

Next, we will explore how to prepare and show Page Totals on each page of the Report.

Share:

Hiding Report Lines Conditionally-3

Continued from last two weeks' topics.

This is the continuation of two Articles published earlier on this Subject. You may go through them by following the links given below before continuing from here.

  1. Hiding Report Lines Conditionally
  2. Hiding Report Lines Conditionally-2.

We have learned how to hide Report items conditionally and calculate Sub-Totals and Report Footer Totals in Code by excluding the values of those records we have removed from the Report Detail Section. We have updated the calculated values in Group Footer and Report Footer Section Controls.

We did this by checking for specific values in the Report Source Data and by Canceling the Format Event of the Report.

These exercises we have done on the Detail Section alone earlier in the Report by hiding specific records. But, here our aim is to hide all the records, of a particular Customer Group, including the Customer Group Header, Group Footer, and Detail Sections.

Hiding Report Group Entries.

If you understood the method we have used in the last two examples, then you will have no difficulty understanding it here. The only difference here is to compare the Group Value (CustomerID) in all the three Sections (i.e. Group Header, Detail, and Group Footer Sections) Format Event Procedure to hide or show them rather than Report Detail Section alone as we did in the earlier example.

  1. Make a copy of the earlier Report (Order_Details) and save it with the name Order_Details2.
  2. Open the Report in Design View. We will make a small change in the Group Footer section.
  3. Click on the Label with the Caption Sub-Total to select it.
  4. Select the Change To option from Format Menu and select Text Box from the displayed options.

    The Label Control we have now changed to a Text Box to write an expression to display the CustomerID value of each Customer Group along with the earlier caption Sub-Total.

  5. Display the Property Sheet (View- - >Properties) of the Text Box.
  6. Write the following Expression in the Control Source Property of the Text Box.

    =[CustomerID] & " Sub-Total: "

    The result of this change will appear on the Report something like BSBEV Sub-Total: 123.

    CustomerID code will be enough for our example here, but when you design some Reports in your Projects then it should be something descriptive like the Customer Name rather than Customer Code.

    We will be using this Report for some other trick later, besides what we are going to do with it here, so take it seriously and you will know why it is so important.

    Implement Code Changes

  7. Display the Code Module of the Report (View - - > Code).
  8. Delete the existing Code from the Module.
  9. Copy and Paste the following Code into the Report Code Module and Save the Report.
    Option Compare Database
    Dim x_subtot As Long, x_gtot As Long
    
    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    If [CustomerID] = "BSBEV" Or [CustomerID] = "CENTC" Then
      Cancel = True
    Else
       Cancel = False
    End If
    
    End Sub
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If [CustomerID] = "BSBEV" Or [CustomerID] = "CENTC" Then
        Cancel = True
    Else
        Cancel = False
    End If
    
    End Sub
    
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
        If PrintCount = 1 Then
            x_subtot = x_subtot + [Quantity]
            x_gtot = x_gtot + [Quantity]
        End If
    End Sub
    
    Private Sub GroupFooter0_print(Cancel As Integer, PrintCount As Integer)
        If PrintCount = 1 Then
            [subtot] = x_subtot
            x_subtot = 0
        End If
    End Sub
    
    Private Sub ReportFooter_print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
        [gtot] = x_gtot
    End If
    End Sub
    
  10. Open the Report in Print Preview and check whether these Customer Group Header, Detail, and Group Footer Sections are hidden from the Report or not. Check the sample image given below.

From the VBA Code above, we have purposely removed the Code for Group Footer Format Event Procedure so that the Group Footer alone for the CustomerID BSBEV and CENTC will appear. We can now see the position of these Group items appearing on the Report and from where the Group Header and Detail Sections are hidden from our view.

Another point to note is that since we have Canceled the Format Event of the Detail Section of these Customer Groups the Print Event has not taken place in the Detail Section. Because of this, the Group Summary Totals for these two Customer Codes are shown as zeroes.

Copy the following code and paste it at the bottom of the Code Module and save the Report.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
     If [CustomerID] = "BSBEV" Or [CustomerID] = "CENTC" Then
           Cancel = True
     Else
           Cancel = False
     End If
End Sub

Note: If you are wondering how to name the Sub-Routine and Parameters related to each Section of the Report correctly then stop worrying. This (the Subroutine header line and the End Sub lines) will be written by MS-Access. Click on the Section Header/Footer of the Report in the design view, display the Property Sheet (View - - > Properties), set Event Procedure on the Format or Print Event Property, and Click on the build (. . .) Button to the right edge of the Property Sheet. Both the Sub-Routine Name (pointing to the correct Section of the Report) with the appropriate Parameter list and the End Sub lines will open up in the Code Module of the Report. You only need to write other lines between them manually.

Preview Report after Code Changes.

Open the Report again in Print Preview and this time you will find the Group Footer of these Customers is also hidden.

We are checking for the CustomerlD Code BSBEV and CENTC in all three sections of the Report in the Format Event Procedure to hide these Sections. Once the Format Action is prevented from happening; the Print Event doesn't take place and the Quantity Value of those items is not added to the Total.

If you look at the Code structure carefully, you can see that we are using the Format Event as well as Print Event Procedures for both Detail Section and Group Footer Sections. Format Action is used for hiding or displaying the Report contents based on the criterion and Print Action for Calculations or Updating the Group Total Controls. We have used only the Print Event Procedure for Report Footer Section.

Next, we will learn how to open the same Report in two different Modes, i.e., as a Detail Report and as a Summary Report.

Share:

Hiding Report Lines Conditionally-2

Continued from Last Week's Topic.

Last week, we started the topic of hiding Report Detail Section data lines and worked through a simple example as well. But, we have not yet seen how this action affects the normal calculations on the Report, like calculating Group-wise Sub-Totals or Report Footer Totals.

We have seen that we can do this with a simple VBA Code if we know how MS-Access works on the data Formatting and Printing actions on the Report before it shows the results to us.

Handling Summary Information.

This week we will work with a sample report to see how Group-level Sub-Totals are calculated by MS-Access when some of the group-level records or data lines are conditionally suppressed from showing on the Report with VBA Code.

For this example, we need three data Tables from C:\Program Files\Microsoft Office\Office11\Samples\NorthWind.mdb sample Database.

  1. Import the following Tables into your database:

    • Orders
    • Order Details
    • Products
  2. Open a new Query and display its SQL Window.

  3. Copy and Paste the following SQL String into the SQL Editing Window and Save the Query with the Name Order_DetailsQ:

    SELECT Orders.CustomerID,
       [Order Details].OrderID,
       Products.ProductName,
     [Order Details].Quantity
    FROM Orders INNER JOIN ([Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID) ON Orders.OrderID = [Order Details].OrderID
    WHERE ((([Order Details].OrderID) Between 10248 And 10300) AND ((Left([CustomerID],1)) Between "A" And "L"));
    

    We have used conditions in the Query to select only a few records, enough to limit the number of Pages on the Report to three so that we can check the accuracy of values appearing in Group-wise Sub-Totals and Report Footer Total easily.

  4. Click on the Order_DetailsQ query to select it and select Report from Insert Menu.

  5. Select Report Wizard from the displayed list of options and click OK.

  6. Select all fields from the Available Fields list and move them to the Selected Fields list and click Next Command Button.

  7. The CustomerID field is already selected for Grouping Levels. If any other Field is appearing on the Report sample View then click the < Button to remove it and select CustomerID Field from the Field List and click > Button to use it as Group Level and click Next.

  8. Click on the Summary Options Command Button and put a Check Mark under the Sum option in Quantity Field, click OK, and then click Finish to create the Report.

  9. Save the Report with the name Order_Details.

    Open the Report in Print Preview and check the Sub-Totals and Report Footer Total. The Report will look like the sample image given below. If you have access to a Printer then take a printout, it is only three pages long so that we can compare the results when we hide the report lines with the program.

    Sample Report Image.

    We are going to hide data lines of OrderID Numbers 10280 (three records) and 10297 (two records) from Customer Groups BERGS and BLONP respectively. Let us see what happens to the Sub-Totals and Report Totals when these five lines are prevented from showing up on the Report.

  10. Open the Report in Design View.

  11. Display the Code Module (View --> Code) of the Report.

  12. Copy and Paste the following Code into the Module, Save and close the Report.

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If [OrderID] = 10280 Or [OrderID] = 10297 Then
          Cancel = True
    Else
          Cancel = False
    End If
    End Sub

    Read the earlier Article with the Title: hiding-report-lines-Conditionally to learn how to use a parameter table to add several OrderIDs to filter, instead of Constant values in the If...Then statement.

    Print Preview of Report.

  13. Open the Report in Print Preview.

  14. Check the Sub-Totals of CustomerIDs BERGS and BLONP with the Print out you have taken earlier.

    Even though we have removed three entries from the first Customer and two from the second there is no change in the Group Sub-Totals or Report Footer Total. The Code only prevents the report from showing the data lines on the Report but MS-Access did not exclude these entries from Summary Totals. The Label appearing above the Summary Totals with the text Summary for CustomerID = BERGS (7 records) is also misleading. First thing first, we will eliminate this label before we take care of the Summary Totals. We don't want to add more lines in the Program to update this Label value also.

  15. Open the Report in Design View and delete the Text Box with the expression that shows the above Summary message.

Hiding Report Detail Section or Cancel Report Line Formatting

Before we proceed further with corrective actions for showing Sub-Totals and Report Total I want to draw your attention to the above code that we have used for this example.

If you have tried last week's example, you will see some differences in both VBA Codes. In last week's method that we used, based on the selected OrderID criteria we kept the Detail Section hidden, with the statement Report.Section(acDetail).Visible = False, during the execution of the Report line Format Event.

In this example, we have asked MS-Access to Cancel the Report Line Format action, rather than hiding the Detail Section. As far as the output is concerned both these actions have the same result.

You can run another experiment by running the same Code under the Print Event Procedure and see what difference it makes. I will give the code below. You may Copy and Paste it into the Report Module overwriting the earlier Code.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If [OrderID] = 10280 Or [OrderID] = 10297 Then
   Cancel = True
Else
      Cancel = False
End If
End Sub

Open the Report in Print Preview and inspect the area of the records of OrderIDs we have used in the criterion. Even though the data lines are suppressed from appearing on the Report the empty space of those Report Lines is left of the Report.

We will now proceed to the Sub-Total and Report Footer Total calculation part. That's right; we have to Calculate the Quantity Totals excluding the Values of Orders 10280 and 10297 and write them directly in the Sub-Totals and Report Footer Total TextBoxes. Let us see how we do that.

Performing Summary Calculations.

  1. Open the Report in Design View.

  2. Remove the expression =Sum([Quantity]) from the Text Box Control Source Property in the CustomerID Group Footer and in the Report Footer Sections.

  3. Click on the Group Footer Sub-Total Text Box and display its Property Sheet (View --> Properties).

  4. Change the Name Property Value to SubTotal.

  5. Similarly, change the Name Property Value of Page Footer Text Box to GTotal.

  6. Copy and Paste the following Code into the Report Module, replacing the earlier Code.

    'global declarations
    Dim x_SubTotal As Long, x_GTotal As Long
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If [OrderID] = 10280 Or [OrderID] = 10297 Then
        Cancel = True
    Else
        Cancel = False
    End If
    End Sub
    
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
        x_SubTotal = x_SubTotal + [Quantity]
        x_GTotal = x_GTotal + [Quantity]
    End If
    End Sub
    
    Private Sub GroupFooter0_print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
        [SubTotal] = x_SubTotal
        x_SubTotal = 0
    End If
    End Sub
    
    Private Sub ReportFooter_print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
        [GTotal] = x_GTotal
    End If
    End Sub
  7. Save the Report, open it in Print Preview, and check whether the Sub-Totals and Report Footer Totals are appearing now correctly or not.

In the above code, we have prevented the Report from showing the record lines in the Format Event and the Calculation is done in the Print Event of the Report. We are checking whether the PrintCount parameter Value is 1 or not, to correctly calculate the Totals. The Print Event can take place more than once if you move from a Page to an earlier Page in the Retreat Event.

The Report Summary Calculation Issues.

So far everything went on well, but there is a major problem with this method and it is important that the User must be aware of this to avoid undesirable results.

If you jump directly to a page (say from page 2 to the Last Page by typing the Page Number in the Report Page control below in Preview) then our line by line Calculation on every Page fails and the Report Footer Totals will be wrong. So, if the user is not aware of this issue and jumps through the Report Pages in Print Preview, before printing the Report, then we are in the soup.

To work around this problem we can use any of the following methods:

  1. Use Custom Menus and Tool Bars in your application and use separate options for Printing and Previewing the Report.

    Refer to the following Articles, to learn more about Custom Menus and Toolbars.


    A Different Approach.

    I recommend the above method rather than the second one given below.

  2. Disable Print Command Buttons from File Menu and from Report Preview Menu while previewing this particular Report and enable them again when Preview is closed. To do this, add the following Code to the VBA Module of the Report:
    Private Sub Report_Close()
       CommandBars("File").Controls("Print...").Enabled = True
       CommandBars("Print Preview").Controls("Print").Enabled = True
    End Sub
    
    Private Sub Report_Open(Cancel As Integer)
       CommandBars("File").Controls("Print...").Enabled = False
       CommandBars("Print Preview").Controls("Print").Enabled = False
    End Sub

    If you are using MS-Access2000 then use the same Control name Print in both lines of Code. The above code is written for MSAccess2003.

    To send the Report directly to the Printer you may use either a Print Macro or the following line of code to run from a Command Button Click Event or from a ListBox-based Menu Option.

    DoCmd.OpenReport "myReport", acViewNormal

  3. To be on the safe side use this method on Reports having only 2 pages.

Next, we will examine how to hide Group level information like Group-Header, Detail, and Group-Footer Sections with Code.

Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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