Case Example

The frmMerchandise form includes all the fields in the Merchandise table, which contains data about Nifty Lions products. Combo boxes have been created for the CategoryID and SupplierID fields. In this example, you'll perform various tasks, including adding a subform, to improve the form.


Copy frmMerchandise to the Clipboard. Paste the form in the Forms section of the Database window. Name the form frm[YourInitials]MerchandiseDetails.


Open the form in Design view.


Choose View, Form Header and Footer. Lengthen the header so that its height is 1".


In the toolbox, click the Label tool. (If the toolbox isn't visible, click the Toolbox button on the toolbar.) Drag the crosshairs to the Form Header and click 2 inches from the left and anywhere from the top. Type Merchandise Details and press Enter.


Open the label's property sheet. Set the Top property at 0.375. Make the label 2 inches wide. Increase the Font Size to 16. Edit the Height to 0.25.


Choose Edit, Select All. Press Shift and click the label in the header to deselect it. Move all the controls in the Detail section down so that the top control ItemID is 1 inch from the top. (The Detail section lengthens when you move the controls.) Click anywhere outside the selected controls to deselect them.


Delete the CategoryName label. Move the CategoryID combo box so that it is two rows of dots below the Detail selector and aligned at left with the labels. Increase the font size to 14. Increase the width to 1.125 inches. Double-click the handle in the upper-right corner to lengthen the control for a better fit.


Select the SupplierID combo box. On the Data tab of the property sheet, click in the Row Source property and click the triple dots at the end of the line. Edit the SQL statement so that the supplier names are alphabetized. Close the statement and click Yes to save your changes. Close the property sheet.


Select the SupplierStockNumber, PurchasePrice, UnitsInStock, and UnitsOnOrder text boxes. Drag and drop them so that the text boxes are 4.5 inches from the left and 1 inch from the top.


Reduce the length of SupplierStockNumber to 6 inches. Decrease the width of the form to 6 inches as well (see Figure 11.25).

Figure 11.25. The text boxes and labels need to be aligned, and the space between them must be made equal.


Align the UnitsOnOrder controls to the SupplierID controls so all four controls are the same distance from the top.


Select the four text boxes in the second column of controls. Choose Format, Vertical Spacing, Make Equal.


Choose Tools, Relationships to open the Relationships window.

The tblMerchandise table has a one-to-many relationship with tblOrderDetails through the MerchID field. Therefore, you can create a subform that shows order details for each product.


Close the Relationships window. Close the property sheet.


With the Control Wizards tool selected, click the Subform/Subreport wizard tool in the toolbox. Drag the crosshairs to the Detail section and click 3 inches from the top and 1.5 inches from the left.


In the first dialog box, choose Use Existing Tables and Queries. Click Next.


Open the Tables/Queries drop-down list and choose tblOrderDetails. Add all the fields from Available Fields to Selected Fields. Click Next.


Leave the current selections of Choose from a List and Show tblOrderDetails for Each Record in tblMerchandise Using MerchID. Click Next.


Name the subform frmOrderInfo subform. Click Finish.


Delete the label of the subform. Save your work.


In Form view, adjust the columns in the subform so that all column names are in view.


Apply an ascending sort to the Category combo box.


Inspect a few records to make sure the form is working properly (see Figure 11.26). Compare your form to frmMerchandiseDetails in NiftyLionsChap11End.mdb.

Figure 11.26. The completed form in Form view.

Hands-On Microsoft Access(c) A Practical Guide to Improving Your Access Skills
Hands-On Microsoft Access: A Practical Guide to Improving Your Access Skills
ISBN: 0321245458
EAN: 2147483647
Year: 2005
Pages: 169
Authors: Bob Schneider © 2008-2017.
If you may any questions please contact us: