This is the second part of the earlier Article: Drill-Down Inquiry Screen. If you have landed on this Page please read the first part of this Article by following the above Link.

I hope you have downloaded the sample database from the bottom of the first part of this Article and tried it out. Then you have a general idea, what we are going to explore further on this subject.

Here, we will design three small Forms, assemble them on the Main Form on a Tab Control and use few lines of Code to refresh the Main Form to update the contents of the Sub-forms before bringing them into view.

First, we will design the top layer of the Form for the Employee-wise Order Summary Information.

03_Employee_Summary Form

  1. Select the Query 03_Employee_Summary.
  2. Design a Tabular Form (continuous form) like the sample given below. You may use the Form Wizard from Insert Menu to create the Form quickly but it may insert background images and the controls may be created with shadows etc. In that case you have to modify the design to look like the sample given below.
  3. Select all the Data Fields together by clicking on the left side border of Detail Section or click in Detail Section somewhere and drag the mouse over all the controls to select them together.
  4. Display the Property Sheet (View – ->Properties).
  5. Change the following Property Values:
    • Enabled = No
    • Locked = Yes
    • Tab Stop = No
    • Back Color = 12632256
    • Special Effect = Raised
  6. Expand the Detail Section of the Form down to get enough room to draw a lengthy Command Button.
  7. Draw a Command Button as wide as the full length of all the Fields above, so that we can cover the fields by placing it over them.
  8. Display the Property Sheet of the Command Button.
  9. Change the following Property Values:
    • Name = cmd1
    • Transparent = Yes
    • Hyperlink SubAddress = #
  10. We must make the Command Button’s height same as of the Fields above. Click on the Command Button to select it, hold the Shift Key and click on any one of the Fields above to select it along with the Command Button. Alternatively you can click and drag over the Command Button and any one of the fields above.
  11. Select Format – -> Size and select Shortest, if Command Button’s height is more than the field, or select Tallest to make the Command Button as tall as the fields above.
  12. Drag the Transparent Command Button and place it over the Fields above.
  13. To make sure that the Transparent Command Button stays above all the data fields, select Format – – > Bring to Front.
  14. Now, reduce the Detail Section height, but there must be little gap above and below the Data Fields.
  15. Draw a Text Box in the Form Footer Section below in the same position of TORDERS field in the Detail Section and write the following expression in the Control Source Property:
    =Sum([TORDERS]) 
  16. Change the Caption of the child label to Total Orders.
  17. Create a Label at the Header of the Form and change the Caption value to EMPLOYEE-WISE ORDERS SUMMARY. Change the font size to 12.
  18. Display the Code Module of the Form ( View – -> Code), Copy and paste the following VBA lines into the Module:
    Private Sub cmd1_Click()
    Me.Parent.Refresh
    Me.Parent.Tabctl0.Pages(1).SetFocus
    End Sub
  19. Save the Form with the Name: 03_Employee_Summary.

04_Order_ListQ Form

  1. Select the Query 04_Order_ListQ and create a Tabular Form (continuous Form) as we did at the top.
  2. Change the design to look like the sample Image given below:
  3. Select all the fields as we did earlier and change the following Property Values:
    • Enabled = No
    • Locked = Yes
    • Tab Stop = No
    • Back Color = 16777215
    • Special Effect = Flat
  4. Follow Step-6 to 8 given above.
  5. Change the following Properties of the Command Button:
    • Name = cmdOrder
    • Transparent = Yes
    • Hyperlink SubAddress = #
  6. Follow Step-10 to 13 explained above. Reduce the height of the Detail Section without leaving gap above and below the fields.
  7. Create a Command Button at the Footer Section of the Form.
  8. Display the Property Sheet of the Command Button and change the following Property Values:
    • Name = cmdMain
    • Caption = Goto Main
  9. Expand the Header Section of the Form and drag the Field Headings down to get enough room to create a Heading to the Form.
  10. Add a Text Box above the Field Headings and write the following expression in the Control Source Property of the Text Box:
    = "Order List of " & [EmpName]
  11. Display the Code Module of the Form (View – – >Code), Copy and paste the VBA Code given below into the Module and save the Form with the name 04_Order_ListQ.
Private Sub cmdMain_Click()
    Me.Parent.Tabctl0.Pages(0).SetFocus
End Sub

Private Sub cmdOrder_Click()
   Me.Parent.Refresh
   Me.Parent.Tabctl0.Pages(2).SetFocus
End Sub

05_Order_DetailQ Form

.

  1. Select the Query 05_Order_DetailQ and Create a Tabular Form.

    Here, we don't need the Transparent Command Button. Change the design of the form to make it like the sample image given below.

    Order Detail Screen Image
  2. Create a Text Box in the Form Footer below the Quantity field and write the following expression in the Control Source Property:
    =Sum( [Quantity])
  3. Create another Text Box in the Form Footer below the ExtendedPrice Column and write the following expression in the Control Source Property:
    =Sum([ExtendedPrice])
  4. Create a Command Button in the Form Footer below the Text Boxes.
  5. Change the following Property Values of the Command Button.
    • Name = cmdBack
    • Caption = << Back
  6. Display the Code Module of the Form (View – -> Code), copy and paste the following lines into the Module:
    Private Sub cmdBack_Click()
        Me.Parent.TabCtl0.Pages(1).SetFocus
    End Sub
  7. Save the Form with the Name 05_Order_DetailsQ.

Now, we are ready for the Main Form Inquiry_Main to assemble all the three Sub-Forms on a Table Control and make the Tab Control invisible.

Inquiry_Main Form

  1. Select the Parameter Table Date_Param, select Form from Insert Menu and select Design View from the displayed List.
  2. Select the Tab Control Tool from the Toolbox and draw a Tab Control on the Detail Section of the Form.
  3. Check the sample image given below. The Tab Control will have only two pages when created but we need one more page.
  4. Click on the Tab Control to select it and select Tab Control Page from Insert Menu.

    You may select each Page of the Tab Control, display their Property Sheet and Change the Caption Property value of Page1, Page2 and Page3 as Summary, Orders and Order Detail respectively, if needed. It is used for reference purposes only and will not use those names anywhere or display them either.

    Next step is to drag and drop the Sub-Forms (03_Employees_Summary, 04_Order_ListQ and 05_Order_DetailQ) one by one on the Tab Control Pages.

  5. Position the Database Window with the Forms Tab active and the Inquiry_Main Form with the Tab Control side by side.
  6. Drag and drop the 03_Employee_Summary Form on the Tab Control Page1.
  7. You may delete the child label attached to the Sub-Form. Re-size the Sub-Form and Tab Control to display the contents properly on the Screen. You may save the Form with the name Inquiry_Main and open it in Normal View to check as how the Information is displayed on the Sub-Form. If necessary increase or decrease the size of the Form and Tab Control. Leave some gap between the Tab control and the top of the Detail Section of the Form to insert few Text Boxes for StartDate and EndDate fields and two more Text Boxes for control purposes. We need space for a heading above these controls as well.
  8. When you are satisfied with the Sub-Form dimension for the design, click on the Sub-Form and display the Property Sheet. Take a piece of paper and note down the following Property Values for resizing and positioning the other two forms we are going to insert into Page2 and Page3 of the Tab Control.
    • Top
    • Left
    • Width
    • Height
  9. Right-click on the Sub-Form and select Copy from the displayed Shortcut Menu.
  10. Select Tab Control Page2, press and hold Shift Key, right-click on the Tab Control Page2 and select Paste from the displayed menu.

    The pasted control will be an empty form displaying the Source Object Name of the copied Form with white background.

  11. Display the Property sheet of the Form and change the Source Object Property value to 04_Order_ListQ. After this change the Form will appear on the Tab Control Page2.
  12. Change the dimension property values to the same value you have noted down. Since, you have copied the Form (no drag and drop from Database Window); you need to change only the Top and Left Property Values, Width and Height values will be same. If not then change it.
  13. Follow Step-9 to 12 above to bring in the 05_Order_DetailQ Form into the third Page of the Tab Control.
  14. Display the Field List (View – ->Field List), if it is not visible.
  15. Drag and drop StartDate and EndDate field above the Tab Control, create labels above and left and change their Caption values as shown on the design above.
  16. Create a Command Button to the right of EndDate field and change its property values as given below:
    • Name = cmdRefresh
    • Caption = <<Refresh Screen

    The Date Fields we have created and another two Text Boxes, which we are going to create, will be referenced in the Queries we have created earlier to filter the data for the Forms to display. Even though this refresh action is not necessary, as we do that before we display the results, it is an additional feature to refresh the changes manually by the User.

  17. Create a Text Box to the right of the Command Button and display its property sheet and change the following Property Values:
    • Name = EID
    • Control Source = =[03_Employee_Summary].Form!EmployeeID
    • Visible = No
  18. Create another Text Box below the earlier one and change the property values as given below.
    • Name = OID
    • Control Source = =[04_Order_ListQ].Form!OrderID
    • Visible = No
  19. Create a heading on top of the Form with the Caption Value SALES PERFORMANCE INQUIRY.
  20. Create a Command Button below the Tab Control and change the following property values:
    • Name = cmdQuit
    • Caption = Quit

    Now, we are going to make the Tab Control disappear, well the Tab Control will not look like a Tab Control after the change, to be exact.

  21. Click on the Tab Control, display the Property sheet and change the following property values:

    Caution: Immediately after you change the Property values make a point to click the Save Toolbar Button or select Save from File Menu to save the changes. There is a tendency to lock-up the Form in Office2000, if you attempt to do anything else.

    • Back Style = Transparent
    • Multirow = No
    • Style = None
  22. Display the Code Module of the Form, copy and paste the following few lines of Code into the Module:
    Private Sub cmdQuit_Click()
      DoCmd.Close acForm, Me.Name
    End Sub
    
    Private Sub cmdRefresh_Click()
       Me.Refresh
    End Sub
    
    Private Sub EndDate_LostFocus()
    Me.Refresh
    End Sub
    
    Private Sub Form_Load()
    Me.TabCtl0.Pages(0).SetFocus
    End Sub
  23. We will finish the design by setting the Property Values of the Inquiry_Main Form. Display the Property Sheet of the Form and change the following Property Values:
    • Allow Edits = Yes
    • Allow Deletiions = No
    • Allow Additions = No
    • Data Entry = No
    • Scroll Bars = Neither
    • Record Selectors = No
    • Navigation Buttons = No
    • Dividing Lines = No
    • Auto Re-size = Yes
    • Auto Center = Yes
    • Pop up = Yes
    • Modal = No
    • Border Style = Dialog
    • Control Box = Yes
    • Min Max Buttons = None
    • Close Button = Yes
    • Allow Design Changes = Design View Only
  24. Save the Form, open it in Normal View and try out your creation.

Note: If you face any difficulty in running your design normally, use the downloaded database as reference, find the mistake and correct it.

StumbleUpon Toolbar