LEARN MS-ACCESS TIPS AND TRICKS: Drill-Down Inquiry Screen-2

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

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 of the shape of things to come.

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

Select the Query 03_Employee_Summary.

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.

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.

Display the Property Sheet (View - ->Properties).

Change the following Property Values:

Enabled = No

Locked = Yes

Tab Stop = No

Back Color = 12632256

Special Effect = Raised

Expand the Detail Section of the Form down to get enough room to draw a lengthy Command Button.

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.

Display the Property Sheet of the Command Button.

Change the following Property Values:

Name = cmd1

Transparent = Yes

Hyperlink SubAddress = #

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.

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.

Drag the Transparent Command Button and place it over the Fields above.

To make sure that the Transparent Command Button stays above all the data fields, select Format - - > Bring to Front.

Now, reduce the Detail Section height, but there must be little gap above and below the Data Fields.

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])

Change the Caption of the child label to Total Orders.

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.

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

Save the Form with the Name: 03_Employee_Summary.

04_Order_ListQ Form

Select the Query 04_Order_ListQ and create a Tabular Form (continuous Form) as we did at the top.

Change the design to look like the sample Image given below:

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

Follow Step-6 to 8 given above.

Change the following Properties of the Command Button:

Name = cmdOrder

Transparent = Yes

Hyperlink SubAddress = #

Follow Step-10 to 13 explained above. Reduce the height of the Detail Section without leaving gap above and below the fields.

Create a Command Button at the Footer Section of the Form.

Display the Property Sheet of the Command Button and change the following Property Values:

Name = cmdMain

Caption = Goto Main

Expand the Header Section of the Form and drag the Field Headings down to get enough room to create a Heading to the Form.

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]

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.

Select the Query 05_Order_DetailQ and Create a Tabular Form.

Create a Text Box in the Form Footer below the Quantity field and write the following expression in the Control Source Property:

=Sum( [Quantity])

Create another Text Box in the Form Footer below the ExtendedPrice Column and write the following expression in the Control Source Property:

=Sum([ExtendedPrice])

Create a Command Button in the Form Footer below the Text Boxes.

Change the following Property Values of the Command Button.

Name = cmdBack

Caption = << Back

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

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

Select the Parameter Table Date_Param, select Form from Insert Menu and select Design View from the displayed List.

Select the Tab Control Tool from the Toolbox and draw a Tab Control on the Detail Section of the Form.

Check the sample image given below. The Tab Control will have only two pages when created but we need one more page.

Click on the Tab Control to select it and select Tab Control Page from Insert Menu.

Page

Caption

Page1, Page2

Page3

Summary, Orders

Order Detail

Sub-Forms

03_Employees_Summary, 04_Order_ListQ

05_Order_DetailQ

Position the Database Window with the Forms Tab active and the Inquiry_Main Form with the Tab Control side by side.

Drag and drop the 03_Employee_Summary Form on the Tab Control Page1.

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.

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.

Left

Width

Height

Right-click on the Sub-Form and select Copy from the displayed Shortcut Menu.

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.

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.

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.

Follow Step-9 to 12 above to bring in the 05_Order_DetailQ Form into the third Page of the Tab Control.

Display the Field List (View - ->Field List), if it is not visible.

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.

Create a Command Button to the right of EndDate field and change its property values as given below:

Name = cmdRefresh

Caption = <<Refresh Screen

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

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

Create a heading on top of the Form with the Caption Value SALES PERFORMANCE INQUIRY.

Create a Command Button below the Tab Control and change the following property values:

Name = cmdQuit

Caption = Quit

Tab Control

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

Caution:

Save

File

Back Style = Transparent

Multirow = No

Style = None

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

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: