Modify the Report Based on Control Setting

In previous posts, you already know how to create simple reports using the Report Button provided in the Reports Group. This posting will modify the report that was created to be displayed according to our settings like in the picture below.

The picture above shows that when clicking the Preview command button, MS Access will display a dialog box that contains instructions to be executed. MS Access will then display the report in accordance with that instructions. For example, see below picture. When we choose report to be viewed by range, then related combo box controls will be on, and we are being asked to enter the given values in that combo boxes. After clicking View Report button, MS Access will display a report based on that given values.

Creating such above report involve a wide range of MS Access objects: table, query, form, report, and module. To make such a report, follow these long steps.

1. Using Blank Form, create a form described below:



2. Following are the related properties:
   1. Form properties:
      1. Caption = Preview Report of Chart of Account - Main Account
      2. Modal = Yes
      3. Auto Center = Yes
      4. Auto Resize = Yes
      5. Fit to Screen = Yes
      6. Moveable = No
      7. Border Style = Dialog
      8. Record Selector = No
      9. Navigation Button= No
   2. Using Use Control Wizards in Controls Group, click Option Group button (available in Control group as well) and enter:
      1. Labels names:
         1. View All
         2. View by Range:
         Click next to continue.
      2. Set View All as the default. Click next to continue.
      3. Set Value for View All is 1 and View By Range: is 2. Click next to continue.
      4. Click next to continue.
      5. Caption = View Mode and click Finish.
   3. Go to frame properties and set the following:
      1. Name = FrameViewMode
      2. Border Style = Transparent
      3. After Update = [Event Procedure] and type this procedure:

         Private Sub FrameViewMode_AfterUpdate()
          If Me.FrameViewMode = 2 Then
            Me.GroupName.Enabled = True
            Me.FromAccountCode.Enabled = True
            Me.ToAccountCode.Enabled = True
            Me.FromAccountName.Enabled = True
            Me.ToAccountName.Enabled = True
            Me.Form.Requery
          Else
            Me.GroupName.Enabled = False
            Me.FromAccountCode.Enabled = False
            Me.ToAccountCode.Enabled = False
            Me.FromAccountName.Enabled = False
            Me.ToAccountName.Enabled = False
          End If
         End Sub
         

   4. On the Tools tab, click Add Existing Fields to change the Task Pane to Field List, choose table tblMainAccount
   5. Drag field GroupName on to the form.
   6. Change the Task Pane back to Properties Sheet
   7. Click GroupName combo box and set the following properties:
      1. Control Source = blank
      2. Enabled = No
      3. After Update = [Event Procedure] and type this procedure:

          Private Sub GroupName_AfterUpdate()
           Me.FromAccountCode.Requery
          End Sub

      4. On Change = [Event Procedure] and type this procedure:

          Private Sub GroupName_Change()
           Me.FromAccountCode = Null
           Me.ToAccountCode = Null
          End Sub

   8. Create a combo box and set the properties as follow:
      1. Name = FromAccountCode
      2. Row Source = SELECT tblMainAccount.AccountCode, tblMainAccount.AccountName FROM tblMainAccount WHERE (((tblMainAccount.GroupName)=[Forms]![frmMainAccountDialog]![GroupName])) ORDER BY tblMainAccount.AccountCode;
      3. Row Source Type = Table/Query
      4. Bound Column = 1
      5. Column Count = 2
      6. Column Widths = 1";2"
      7. List Width = 3
      8. Limit to list = Yes
      9. Enabled = No
      10. After Update = [Event Procedure] and type this procedure:

          Private Sub FromAccountCode_AfterUpdate()
            Me.ToAccountCode.Requery
           End Sub

   9. Create same combo box above and set the properties as follow:
      1. Name = ToAccountCode
      2. Row Source = SELECT tblMainAccount.AccountCode, tblMainAccount.AccountName
         FROM tblMainAccount WHERE (((tblMainAccount.AccountCode)>=[Forms]![frmMainAccountDialog]![FromAccountCode]) AND ((tblMainAccount.GroupName)=[Forms]![frmMainAccountDialog]![GroupName])) ORDER BY tblMainAccount.AccountCode;
      3. Row Source Type = Table/Query
      4. Bound Column = 1
      5. Column Count = 2
      6. Column Widths = 1";2"
      7. List Width = 3
      8. Limit to list = Yes
      9. Enabled = No
   10. Create a text box and set the properties as follow:
       1. Name = FromAccountName
       2. Control Source = =[FromAccountCode].[column](1)
       3. Enabled = No
       4. Width = 2.7083"
   11. Create same text box above and set the properties as follow:
       1. Name = FromAccountName
       2. Control Source = =[FromAccountCode].[column](1)
       3. Enabled = No
       4. Width = 2.7083"
   12. Disable Use Control Wizards and create Button (Form Control), set the properties as follow:
       1. Name = Command1
       2. Caption = View Report
       3. Width = 1"
       4. On Click = [Event Procedure] and type this procedure:

          Private Sub Command1_Click()
              If Me.FrameViewMode = 2 Then
                  If IsNull(Me.FromAccountCode) Or 
           IsNull(Me.ToAccountCode) Then
                      MsgBox 
           "Either From Code or To Code is empty", vbExclamation, "Empty Field"
                      Exit Sub
                  End If
              End If
              Global_PreviewUnRefresh ("rptMainAccount")
           End Sub

   13. Create same button (Form Control) above and set the properties as follow:
       1. Name = Command0
       2. Caption = Cancel
       3. Width = 1"
       4. On Click = =Global_Close()
   14. Finally, go to Form properties and set the Record Source to blank.
3. Save form as frmMainAccountDialog and close it.
4. Open form frmMainAccount as in the picture below in design view.
   
5. On the most right side of , add another button like this    and set the properties as follows:
   1. Name = Preview
   2. Caption = Preview
   3. Picture = (image),  image name = Preview
   4. Width = 0.3333"
   5. On Click = =Global_OpenForm('frmMainAccountDialog')
6. Save and close frmMainAccount. The form is ready for used.