Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Unsecured Database and Users Log

When Data Entry or Editing actions are performed on important Tables in Databases a Time-Stamp with User Name is normally saved on each record to mark that event. This is done through the Form's 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 from manual changes. The familiar function =Now() gives the Date/Time Stamp value and the CurrentUser built-in function provides the User's Name from the current instance of the database opened in User's Workstation. We are focusing on the usage of CurrentUser function.

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

But, if the database is not implemented with Microsoft Access Security then the CurrentUser function will always return the value Admin. Any user 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 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 into 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 UserID and WorkstationID stored in your computer's memory.

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

A lengthy list of Environment Values are 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 influences 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 UserID

These may not be in nearby lines but can appear anywhere within the list and if necessary use the Scroll Bar to move the list up or down and when you are sure you have spotted these values then type Exit and press 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 UserID 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 CurrentUser, 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 WorkstationID of the User from Memory.

These are also useful to monitor intrusions from third parties, or somebody not entrusted to maintain the database is opening it 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

Refer the following Posts for more advanced techniques like:

  1. Record the Open/Close events activity of Databases in a log Text File and send alerts to your machine in a popup message box, which you can turn it 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 taken place at 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

Share:

Msaccess Report and Page Totals

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

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 on 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 Internet on basic usage of MS-Access (or that was the 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 on the look out for something different, advanced, interesting and need more exciting experience with MS-Access usage then this Website is primarily targeting Readers like you.

The majority of the crowd may be on the other side now but I am sure they will be on the look out for something better all the time. That is why VBA Code is the main driving force behind all the examples given on this Site.

So, let us try out part of this experiment without VBA Code. We need a simple Report to do that.

  1. Import the following Tables from Northwind.mdb 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 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 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 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 Text Boxes and Labels to the Design of the Report as explained below:
    • Make a copy of the Quantity field in the Detail Section and position it to the right as shown.
    • Display the Property Sheet (View- ->Properties) of the copied control and change the Name Property value to QtyRunningSum.
    • Change the Running Sum Property Value to 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 PageTotal, 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 Carry 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 Program to change the Caption value to TOTAL at 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 are trying to display values into the other Text Boxes 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 summing up in this control and the last line has the total so far value on the Page. If we put a reference to 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 Text Box or not. Advance to the next page and check the value appearing there too.

    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 to take care of to get it right.

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

    The second one is to put a reference to 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.

    Read the following Articles where we have discussed the Format and Print Events of the Reports and to learn 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 Text Box when we use 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 on the Detail Section on 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 undergoes the Format and Print Events; the controls CF & BF internally keeps on populating with changed 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 from the first line running sum QtyRunningSum or previous Page CF Text Box Value we will get the BF Text Box 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 showing correctly. Move to the next page and check the values appearing there.

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

  1. Create a copy of the Product_List Report and name it as 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 Program at 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.
    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
    

    We have declared two Variables x_pagetotal and x_CF at the global area of the Module. At 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 PageTotal, CF Text Box values and resetting x_pagetotal Variable to zero. Here, we are checking whether the current Page Number is equal to the Last Page Number, if so then changes 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 in 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 below, 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

You don't have to design two different Reports; one for 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

For hiding of 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, PrtMIP Report Properties and learned how to change Paper Size and Page Orientation, Margin Settings and Column Settings through 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 at the left side of the sub-total value control in the Group Footer.

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 earlier example, with 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.
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). Using 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 Detail print preview.

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() Functions 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 cancelled (these Sections are hidden) and shows only the Page Header/Footer, CustomerID Group Sub-Totals and Report Total in Print Preview or Print.

We will create a Report Option Group on the MainSwitchBoard Form (or you may create a new sample Form as shown below) to set and launch our Report from there for Detail and Summary Options.

  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 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 Option 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 default choice and Click Finish.
  7. You may drag and position the Child Label attached to the Option 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. Open it in normal view. Click on the Report Preview Command Button to open the Report after setting 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 Option.

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

Share:

Hiding Report Lines Conditionally-3

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 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 on the Report by hiding specific record. But, here our focus is on hiding all records of a particular Customer Group including the Customer Group Header, Group Footer and Detail Sections.

If you understood the method we have used in the last two examples then you will have no difficulty in understanding it here. The only difference here is to compare the Group Value (CustomerID) in all the three Section's (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 on the Group Footer Section.
  3. Click on the Label with the Caption Sub-Total to select it.
  4. Select Change To 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.

  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 for Detail Section. Because of this the Group Summary Totals for these two Customer Codes are showing 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 design view, display the Property Sheet (View - - > Properties), set Event Procedure on the Format or Print Event Property, 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 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.

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

We are checking for the CustomerlD Code BSBEV and CENTC in all the 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 are 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 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 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

Last week, we have 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 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.

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 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 for 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 print out, it is only three pages long, so that we can compare the results when we hide the report lines with program.

    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.

  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.

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 difference in both VBA Codes. Last Week we have asked MS-Access to hide the Detail Section with the statement Report.Section(acDetail).Visible = False during the Report Format Event when the OrderIDs in the criterion are encountered.

In this example we have asked MS-Access to Cancel the Format action. 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 in 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 are left on the Report.

We will now proceed with 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 it directly in the Sub-Totals and Report Footer Total Text Boxes. Let us see how we do that.

  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.

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 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 the following Articles to learn more about Custom Menus and Toolbars.

    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 into the 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 List Box based Menu Option.

    DoCmd.OpenReport "myReport", acViewNormal

  3. To be on the safest 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:

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