ACC2: Form and Report Questions and Answers (114809)

MORE INFORMATION

1. Q. Why is the data sorted in my query but not in my report?
   
   A. Reports create their own internal queries to present the data. If you want to have the data in your report presented in a particular order, you must explicitly set the sort order in the Sorting And Grouping dialog box. To do this, open the report in Design view, then choose Sorting And Grouping from the View menu.
2. Q. How can I keep a group of records together on a report?
   
   A. The new KeepTogether property for groups in Microsoft Access version 2.0 gives you the ability to keep groups of like information together. This property is available in the Sorting And Grouping dialog box for reports. Using this property, you can keep an entire group together (including the group header, all records, and the group footer), or keep the group header with the first record.
   
   For more information about sorting and grouping, please see the Microsoft Access "User's Guide," version 2.0, Chapter 22, "Sorting and Grouping Data."
3. Q. Why is every other page of my report blank, and how can I correct this problem?
   
   A. This problem occurs when the total width of your report exceeds the width of the paper specified in the Print Setup dialog box. For example, blank pages print if your report form is 8 inches wide and your left and right margins are 1 inch wide for a total width of 10 inches, and if the paper size specified in the Print Setup dialog box is only 8.5 inches wide.
   
   Using this example, if controls (such as text boxes) extend beyond 8.5 inches, the controls are printed on a second page. Otherwise, you receive a warning message stating that some pages may be blank. Blank pages generated after the warning are not counted in the total pages of your report.
   
   For additional information about preventing blank pages, see 95920.
4. Q. What is an event procedure? Can I still call an Access Basic function from my form or report?
   
   A. Event procedures are Access Basic functions that respond to events (such as a mouse click) that occur on forms and reports. Event procedures are stored in form or report modules attached to the form or report, and are incorporated into the form or report's design structure. You can also call an Access Basic function stored in a separate module from an event property.
   
   For more information about event procedures, order the fax or mail copy of this script.
   
   
   • Event procedures are private procedures--only the form or report to which an event procedure is connected can call or use that event procedure. If you want another form or report to use the same procedure, make the procedure a global function by storing it in a module. Functions stored in modules are available to all forms and reports.
   • Since event procedures are private, or locally scoped, you can use the Me property to refer to the form or report, rather than using the full syntax (such as Forms![form name]...) to refer to the form or report.
   • Form and report modules are loaded only when the form or report is open. If you want to compile the procedure or search in the module, you must first open the form or report.
   To call an Access Basic function from a property, enter the following in the property:

   =MyFunctionName()

   NOTE: The equal sign and parentheses are required.
   
   To create an event procedure, choose the Build button in the property sheet, choose the Code Builder, and write the code for that event. When you complete and close the code, you will see [Event Procedure] displayed in the property sheet, which indicates that the event has code written for it.
   
   For more information about writing and using event procedures, see the Microsoft Access "Building Applications" manual, version 2.0, Chapter 3, "Introduction to Access Basic," and Chapter 5, "Access Basic Fundamentals."
5. Q. How can I print a page number that includes the full number of pages in the report on each page of a report?
   
   A. You can use the Page and the Pages properties to create a "Page X of Y" expression. The following example, used as a text box's ControlSource property, will print a "Page X of Y" page number on each page of the report:
   

             ="Page " & Page & " of " & Pages
   						

6. Q. Why do I get the message "#Error" in some controls on my form or report? How do I reference a control on a subform or subreport?
   
   A. Microsoft Access displays the "#Error" message in a field or text box when it cannot find necessary information, execute an expression, or store a value within the field's defined limits. "#Error" is not the only possible error message for these conditions. For additional information about troubleshooting "#Error" messages, see 112103.
7. Q. Do form validation rules override table validation rules? When are the validation rules on a form evaluated?
   
   A. In Microsoft Access 2.0, table validation rules are always enforced, no matter how you add or edit data. Form validation rules do not override table validation rules, but can be used in addition to table validation rules.
   
   When you enter or edit data in a form and then move the insertion point to a different field or record, the form validation rule is evaluated first. If the data passes this validation test, the table validation is evaluated. Therefore, you can use form validation to refine table validation.
   
   Since validation rules are evaluated only when data is added or edited, you may want to use the new Required property to force users to enter a value. The Required property prevents users from tabbing out of and not changing a field.
   
   For additional information about the Required property, search for "Required" then "Required Property" using the Microsoft Access Help menu.
8. Q. When is it appropriate to use an exclamation point (!) versus a period (.) for identifying objects and properties in an expression?
   
   A. Use an exclamation point before anything you specifically name, such as the name of a form or a control on a form, and use a period before anything Microsoft Access names, such as a property.
   
   In Microsoft Access version 1.x, you can use a period to reference a field name, such as "MyTable.Name" to refer to the Name field in the table MyTable. If, however, you use this same expression in Microsoft Access 2.0, you refer to the Name property, rather than the field called Name. To prevent this, use "MyTable!Name" in Microsoft Access 2.0.
9. Q. How do I turn off the ControlWizards?
   
   A. ControlWizards help you create list boxes, combo boxes, option groups, and command buttons. Once you are comfortable working with these controls, you may want to turn off the ControlWizards instead of having to cancel them each time you add one of these controls. To turn off the ControlWizards, either choose ControlWizards from the View menu to clear the check mark, or choose the ControlWizards button in the toolbox to toggle the button off.
10. Q. How can I check for duplicate records immediately after I enter a value in a primary key field?
    
    A. Normally, Microsoft Access does not check the values in primary key fields for duplicates until you move to the next record. If you want to check for duplicate values immediately after entering a value in a primary key field, use a macro in the field's AfterUpdate property. The macro should use the DLookup() function to check for duplicates and then display an appropriate message.
    
    For additional information about checking for duplicate primary keys, see 102527.