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

Saturday, January 24, 2009

Drill-Down Inquiry Screen-2

Continuation of Last Week's Post.

This is the second part of the earlier Article: Drill-Down Inquiry Screen. If you have landed straight on this page, then please visit the above link and go through the first part of this article before continuing..

I hope you have downloaded the sample database from the first part of this article published last week and tried it out. Then you have a general idea of what we are going to explore further on this topic.

Here, we will design three small Forms, assemble them on the Main Form on a Tab Control, and use a 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 and other settings. In that case, you have to modify the design to look like the sample given below.

Click to Enlarge
Select all the Data Fields together by clicking on the left side border of the Detail Section or click on the 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 the 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 the 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 a little gap above and below the Data Fields.
Draw a Text Box in the Form Footer Section below in the same position of the 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:

Click to Enlarge

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

Here, we don't need the Transparent Command Button. Change the design of the form to make it like the sample image given below.
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 TextBoxes.
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 to Enlarge

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.
The 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.
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 to how the Information is displayed on the Sub-Form. Check whether the Form and Tab control size need size increase or decrease is necessary or not. Leave some gap between the Tab control and the top of the Detail Section of the Form to insert a few TextBoxes 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 of 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
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 the same. If not, then change it.
Follow Step-9 to 12 above to bring in the 05_Order_DetailQ Form to 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 fields 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 the EndDate field and change its property values as given below:
- Name = cmdRefresh
- Caption = <<Refresh Screen
The Date Fields we have created and another two TextBoxes, 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.
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
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.
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

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:
- Allow Edits = Yes
- Allow Deletions = 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
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 a reference, find the mistake and correct it.

LEARN MS-ACCESS TIPS AND TRICKS

Saturday, January 24, 2009