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:
         view source

         print?

         01	Private Sub FrameViewMode_AfterUpdate()

         02	If Me.FrameViewMode = 2 Then

         03	Me.GroupName.Enabled = True

         04	Me.FromAccountCode.Enabled = True

         05	Me.ToAccountCode.Enabled = True

         06	Me.FromAccountName.Enabled = True

         07	Me.ToAccountName.Enabled = True

         08	Me.Form.Requery

         09

         Else

         10	Me.GroupName.Enabled = False

         11	Me.FromAccountCode.Enabled = False

         12	Me.ToAccountCode.Enabled = False

         13	Me.FromAccountName.Enabled = False

         14	Me.ToAccountName.Enabled = False

         15

         End If

         16

         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:
         view source

         print?

         1	Private Sub GroupName_AfterUpdate()

         2	Me.FromAccountCode.Requery

         3

         End Sub

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

         print?

         1	Private Sub GroupName_Change()

         2	Me.FromAccountCode = Null

         3	Me.ToAccountCode = Null

         4

         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:
          view source

          print?

          1	Private Sub FromAccountCode_AfterUpdate()

          2	Me.ToAccountCode.Requery

          3

          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:
          view source

          print?

          01	Private Sub Command1_Click()

          02	If Me.FrameViewMode = 2 Then

          03	If IsNull(Me.FromAccountCode) Or

          04	IsNull(Me.ToAccountCode) Then

          05

          MsgBox

          06	"Either From Code or To Code is empty", vbExclamation, "Empty Field"

          07

          Exit Sub

          08

          End If

          09

          End If

          10	Global_PreviewUnRefresh ("rptMainAccount")

          11

          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.