OveKrview of Solutions
How to Create the Forms in Solutions
About Solutions
Add All To List
Edit Products
Employee Sales Dialog Box
Enter Or Edit Products
Find Customers
Limit List Contents
Reattach NWIND
Sales Totals
Show Sales
Solutions
Solutions Intro
Startup
How to Create the Reports in Solutions
Customer Phone List
Employee Sales
Invoice
Sales Averages
Sales Letter
Shipping Log
$#Overview of Solutions
Solutions is a self-documenting application that presents a collection of the forms, reports, and interface elements most commonly used to create applications. Many examples use features new to this version of Microsoft Access.
Who Should Use Solutions
You'll find the examples in Solutions more useful if you already know how to create queries, forms, reports, and macros and are familiar with the information on creating applications in Part 1, "Connecting the Dots," and Chapter 5, "Access Basic Fundamentals," in Building Applications.
Tips for Using Solutions
All the examples in this application are available from the Solutions form, which is displayed when you open the Solutions database. On the Solutions form, first select a category of examples in the upper list box. To open a sample form or report, double-click the example in the lower list box, or select the example and choose the OK button. Solutions automatically displays the sample form or report and its Highlights topic from Show Me, the Solutions Help system.
The Show Me Highlights topic identifies the techniques used to create the form or report. Click the technique you're interested in, and then follow the step-by-step instructions to see how the technique was applied to the form or report.
Tips for Using Show Me
The Show Me topic for a form or report is automatically displayed when the check box at the bottom of the Solutions form is selected. To display Show Me for a form in Form view or a report in Print Preview, press F1 or click the Show Me button on the toolbar.
The main features of Show Me are similar to those of Microsoft Access Help:
To use a keyword or phrase to look for a topic, click the Search button in the Show Me window.
To print a topic, choose Print Topic from the File menu in the Show Me window.
To keep the Show Me window on top, choose Always On Top from the Help menu in the Show Me window.
$#Highlights of the Limit List Contents Form
$#
The Limit List Contents form shows how to use an option group to limit the values displayed in a list box. This technique makes the Limit List Contents form work:
Displaying only the products in the selected category.
You can use the values of one control to determine the values displayed in a list. If the group of determining values is stable, such as the categories in the Northwind database, you can display them in an option group. For example, when you select a category in the Limit List Contents form, only products in that category are displayed.
Overview
Include the Category ID field in the underlying query for the Select Product list box. Set the criteria for Category ID to an expression that limits the list to those products whose Category ID matches the Category ID selected in the Select Category option group. Requery the product list after you select a category.
Objects Used in this Example
| Type | Name | |
| Table | Products | |
| Query | Limit Product List 2 | |
| Form | Limit List Contents | |
Step by Step 1 Create the Limit Product List 2 query. Table |
Field (Sort) | Criteria |
| Products | Product Name (Ascending) | -- |
| Product ID | -- | |
| Category ID | Forms![Limit List Contents]![Select Category] | |
2 Design the Limit List Contents form. a) Set the following form properties. Property |
Setting | |
| RecordSource | Products | |
| Caption | Limit List Contents | |
| DefaultView | Single Form | |
| ViewsAllowed | Form | |
| ShortcutMenu | No | |
| ScrollBars | Neither | |
| RecordSelectors | No | |
| NavigationButtons | No | |
| AutoCenter | Yes | |
b) Create an unbound option group, and set its Name property to Select Category. c) For each category in the option group, add an option button, and type the category name in each label. d) Set the OptionValue property for each option button to a Category ID. For example, the Category ID for Beverages is 1, so you should set the OptionValue property of the Beverages option button to 1. e) Set the DefaultValue of the option group to 1. f) Create an event procedure for the AfterUpdate property of the option group. g) In the Declarations section of the Form.Limit List Contents module, type: Option Explicit h) Create an unbound list box, and set its properties as follows. Property |
Value | |
| Name | Select Product | |
| RowSourceType | Table/Query | |
| RowSource | Limit Product List 2 | |
| ColumnCount | 2 | |
| ColumnWidths | ;0 in | |
| BoundColumn | 2 | |
Cross References For information on |
See | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating forms | User's Guide, Chapter 15, "Designing Forms," Chapter 16, "Customizing Forms" | |
| Creating queries | User's Guide, Chapter 11, "Designing Select Queries" | |
|
AfterUpdate Event Procedure for the Select Category Option Group
Sub Select_Category_AfterUpdate ()
' Requery Select Product list box.
Me![Select Product].Requery
End Sub
$#
Highlights of the Add All To List FormThe Add All To List form shows how to add "(all)" to the list in a list box or combo box. This technique makes the Add All To List form work:
$#Add "(all)" to a List
When you use a list box or combo box to enter selection criteria, you might want to be able to specify all records. On the Add All To List form, the list in the Select Customer combo box includes "(all)."
Overview
Create a function that adds "(all)" to the list in a combo box or list box. Create a list box or combo box, and set its RowSourceType property to the name of the function.
Objects Used in this Example
| Object | Name |
| Form | Add All To List |
| Module | Add All To List |
Step by Step 1 Create the Add All To List module. a) In the Declarations section of the module, type: Option Explicit b) Create the AddAllToCompanyList function. 2 Create the Add All To List form. a) Set the following form properties. Property |
Setting |
| RecordSource | Customers |
| Caption | Add All To List |
| DefaultView | Single Form |
| ViewsAllowed | Form |
| ShortcutMenu | No |
| ScrollBars | Neither |
| RecordSelectors | No |
| AutoCenter | Yes |
b) Add a form header and footer to the form. Set the Height property of the form footer to 0. c) Create a combo box in the form header, and set its properties as follows. Property |
Setting |
| Name | Select Customer |
| RowSourceType | AddAllToCompanyList |
| RowSource | -- |
| ColumnCount | 2 |
| ColumnWidths | 3 in;0 in |
| BoundColumn | 2 |
| LimitToList | Yes |
| AfterUpdate | [Event Procedure] |
d) In the detail section of the form, add text boxes for the Customer ID, Company Name, Contact Name, Phone, and Fax fields. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
| Creating forms | User's Guide, Chapter 15, "Designing Forms," and Chapter 16, "Customizing Forms" |
|
AddAllToCompanyList () Function
Function AddAllToCompanyList (MyComboBox As Control, ID As Long, Row As Long, Col As Long, Code As Integer) As Variant
' Add "(all)" to list of company names.
Static MyDB As Database, MyRecordset As Recordset
Dim X As Long
Select Case Code
' Initialize.
Case LB_INITIALIZE
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyRecordset = MyDB.OpenRecordset("SELECT [Company Name], [Customer ID] FROM Customers ORDER BY [Company Name];")
AddAllToCompanyList = True
' Open.
Case LB_OPEN
AddAllToCompanyList = Timer
' Determine number of rows.
Case LB_GETROWCOUNT
MyRecordset.MoveLast
AddAllToCompanyList = MyRecordset.RecordCount
' Set number of columns.
Case LB_GETCOLUMNCOUNT
AddAllToCompanyList = 2
' Set column width.
Case LB_GETCOLUMNWIDTH
AddAllToCompanyList = -1
' Get data.
Case LB_GETVALUE
' If (all) row, set bound column to asterisk.
If Row = 0 Then
If Col = 0 Then
AddAllToCompanyList = "(all)"
Else
AddAllToCompanyList = "*"
End If
Else
' Otherwise, move to next available row and fill it.
MyRecordset.MoveFirst
For X = 1 To Row - 1
MyRecordset.MoveNext
Next X
If Col = 0 Then
AddAllToCompanyList = MyRecordset.[Company Name]
Else
AddAllToCompanyList = MyRecordset.[Customer ID]
End If
End If
' Close recordset.
Case LB_END
MyRecordset.Close
End Select
End Function
$#
AfterUpdate Event Procedure for the Select Customer Combo Box
Sub Select_Customer_AfterUpdate ()
' Return record(s) that match value selected in Select Customer combo box.
If Me![Select Customer].Column(1) = "*" Then
DoCmd ShowAllRecords
Else
DoCmd ApplyFilter, "[Customer ID] = Forms![Add All To List]![Select Customer]"
End If
End Sub
$#
Highlights of the Reattach NWIND FormThe Reattach NWIND form makes the Reattach NWIND module and its Show Me topic available for viewing. This technique makes reattaching tables at startup work:
$#
Attach Tables at StartupWhen you use multiple databases for an application, you can ensure that connection information for attached tables is updated when the application is started. The AutoExec macro in the Solutions database runs code that refreshes the attachments to tables in NWIND.MDB.
Overview
Create a function that updates the attachments to external tables. Create an AutoExec macro that runs the code.
Objects Used in this Example
| Object | Name | |
| Macro | AutoExec | |
| Module | Reattach NWIND | |
Step by Step 1 Create the Reattach NWIND module. a) Create the AreTablesAttached function. b) Create the GetMDBName function. c) Create the GetMDBName2 function. d) Create the StringFromSz function. 2 Create an AutoExec macro with the following action. Condition |
Action | Arguments |
| Not AreTablesAttached() | DoMenuItem | Menu Bar: Database Menu Name: File Command: Close Database |
This action should be the first one in your AutoExec macro. You can add other actions after this one; for example, you can add an OpenForm action that displays a startup form or a main form. Cross References For information on |
See | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating macros | User's Guide, Chapter 24, "Macro Basics" | |
$# AreTablesAttached() Function Function AreTablesAttached () As Integer ' Update connection information in attached tables. ' ' Number of attached tables for progress meter. Const MAXTABLES = 8 Const NONEXISTENT_TABLE = 3011 Const NWIND_NOT_FOUND = 3024 Const ACCESS_DENIED = 3051 Const READ_ONLY_DATABASE = 3027 Dim TableCount As Integer Dim FileName As String, SearchPath As String, Temp As String Dim ReturnValue As Variant, AccDir As String, I As Integer Dim MyTable As TableDef Dim MyDB As Database, MyRecords As Recordset Set MyDB = DBEngine.Workspaces(0).Databases(0) AreTablesAttached = True ' Continue if attachments are broken. On Error Resume Next ' Open attached table to see if connection information is correct. Set MyRecords = MyDB.OpenRecordset("Products") ' Exit if connection information is correct. If Err = 0 Then MyRecords.Close Exit Function End If ' Initialize progress meter. ReturnValue = SysCmd(SYSCMD_INITMETER, "Attaching tables", MAXTABLES) ' Get name of directory where MSACCESS.EXE is located. AccDir = SysCmd(SYSCMD_ACCESSDIR) ' See if NWIND.MDB is in default location, <Access directory>\sampapps. ' If not, use <Access directory> as starting place for OpenFile dialog. Temp = Dir$(AccDir & "sampapps\.") SearchPath = AccDir & IIf(Temp = "", "", "sampapps\") If (Dir$(SearchPath & "nwind.mdb") = "") Then FileName = GetMDBName() ' Display Open File dialog. FileName = Trim(FileName) If FileName = "" GoTo Exit_Failed ' User pressed Cancel. Else FileName = SearchPath & "nwind.mdb" End If ' Loop through all tables, reattaching those with nonzero-length Connect strings. TableCount = 1 ' Initialize TableCount for status meter. For I = 0 To MyDB.TableDefs.Count - 1 Set MyTable = MyDB.TableDefs(I) If MyTable.Connect <> "" Then MyTable.Connect = ";DATABASE=" & FileName Err = 0 MyTable.RefreshLink If Err <> 0 Then If Err = NONEXISTENT_TABLE Then MsgBox "File '" & filename & "' does not contain required table '" & MyTable.SourceTableName & "'", 16, "Can't Run Solutions" ElseIf Err = NWIND_NOT_FOUND Then MsgBox "You can't run Solutions until you locate NWIND.MDB", 16, "Can't Run Solutions" ElseIf Err = ACCESS_DENIED Then MsgBox "Couldn't open " & filename & " because it is read-only or it is located on a read-only share.", 16, "Can't Run Solutions" ElseIf Err = READ_ONLY_DATABASE Then MsgBox "Can't reattach tables because SOLUTION.MDB is read-only or is located on a read-only share.", 16, "Can't Run Solutions" Else MsgBox Error, 16, "Can't Run Solutions" End If AreTablesAttached = False GoTo Exit_Final End If TableCount = TableCount + 1 ReturnValue = SysCmd(SYSCMD_UPDATEMETER, TableCount) End If Next I GoTo Exit_Final Exit_Failed: MsgBox "You can't run Solutions until you locate NWIND.MDB", 16, "Can't Run Solutions" AreTablesAttached = False Exit_Final: ReturnValue = SysCmd(SYSCMD_REMOVEMETER) End Function
|
GetMDBName() Function
Private Function GetMDBName () As String
' Return path of NWIND.MDB chosen by user in OpenFile dialog box.
' (This function works in conjunction with GetMDBName2 and StringFromSz to
' display a File-Open dialog that prompts user for location of NWIND.MDB.
' It uses code found in WZLIB.MDA.)
Const OFN_SHAREAWARE = &H4000
Const OFN_PATHMUSTEXIST = &H800
Const OFN_HIDEREADONLY = &H4
Dim ofn As wlib_GetFileNameInfo
' Fill ofn structure, which is passed to wlib_GetFileName.
ofn.hWndOwner = 0
ofn.szFilter = "Databases (*.mdb)|*.mdb|All(*.*)|*.*||"
ofn.NFilterIndex = 1
ofn.szTitle = "Where is NWIND.MDB?"
ofn.Flags = OFN_SHAREAWARE Or OFN_PATHMUSTEXIST Or OFN_HIDEREADONLY
ofn.szDefExt = "mdb"
' Call wlib_GetFileName function and interpret results.
If (GetMDBName2(ofn, True) = False) Then
GetMDBName = StringFromSz(ofn.szFile)
Else
GetMDBName = ""
End If
End Function
$#
GetMDBName2() Function
Private Function GetMDBName2 (gfni As wlib_GetFileNameInfo, ByVal fOpen As Integer) As Long
' This function acts as a cover to MSAU_GetFileName in MSAU200.DLL.
' wlib_GetFileName terminates all strings in gfni structure with nulls and
' then calls DLL version of function. Upon returning from MSAU200.DLL, null
' characters are removed from strings in gfni.
Dim lRet As Long
gfni.szFilter = RTrim$(gfni.szFilter) & Chr$(0)
gfni.szCustomFilter = RTrim$(gfni.szCustomFilter) & Chr$(0)
gfni.szFile = RTrim$(gfni.szFile) & Chr$(0)
gfni.szFileTitle = RTrim$(gfni.szFileTitle) & Chr$(0)
gfni.szInitialDir = RTrim$(gfni.szInitialDir) & Chr$(0)
gfni.szTitle = RTrim$(gfni.szTitle) & Chr$(0)
gfni.szDefExt = RTrim$(gfni.szDefExt) & Chr$(0)
lRet = wlib_MSAU_GetFileName(gfni, fOpen)
gfni.szFilter = StringFromSz(gfni.szFilter)
gfni.szCustomFilter = StringFromSz(gfni.szCustomFilter)
gfni.szFile = StringFromSz(gfni.szFile)
gfni.szFileTitle = StringFromSz(gfni.szFileTitle)
gfni.szInitialDir = StringFromSz(gfni.szInitialDir)
gfni.szTitle = StringFromSz(gfni.szTitle)
gfni.szDefExt = StringFromSz(gfni.szDefExt)
GetMDBName2 = lRet
End Function
$#
$#
StringFromSz() Function
Private Function StringFromSz (szTmp As String) As String
' If string terminates with nulls, return a truncated string.
Dim ich As Integer
ich = InStr(szTmp, Chr$(0))
If ich Then
StringFromSz = Left$(szTmp, ich - 1)
Else
StringFromSz = szTmp
End If
End Function
The Solutions Intro form shows how to create a form that users can turn off when they don't need to use it any more. This technique makes the Solutions Intro form work:
Creating a form that users can turn off.
$#Create a Form That Users Can Turn Off
You can display a form that provides introductory information about your application at startup and which users can choose not to display again. For example, the Solutions Intro form is displayed after the Startup form and before the Solutions main menu form every time you start the Solutions sample application. If you decide you don't want it displayed any more, you can select the check box at the bottom of the form.
Overview
Create a table with one Yes/No field. Create a form that has the following controls: labels that display information about your application, a check box bound to the Yes/No field, and a command button that closes the form. Create a main menu form. Create a startup form. In the OnTimer property of the startup form, modify the event procedure so that it uses the value in the Yes/No field to determine if the Solutions Intro form or the main menu form should be opened.
Objects Used in This Example
| Object | Names |
| Table | Options |
| Form | Solutions, Solutions Intro, Startup |
Step by Step 1 Create the Options table. a) Create a field named Hide Solutions Intro, and set its data type to Yes/No. (You don't need to create a primary key for the table.) b) Switch to Datasheet view, and type No in the Hide Solutions Intro field. 2 Create the Solutions Intro form. a) Set the following form properties. Property |
Setting |
| RecordSource | Options |
| Caption | Solutions Intro |
| DefaultView | Single Form |
| ViewsAllowed | Form |
| DefaultEditing | Can't Add Records |
| ShortcutMenu | No |
| ScrollBars | Neither |
| RecordSelectors | No |
| NavigationButtons | No |
| AutoCenter | Yes |
| BorderStyle | Thin |
| MinButton | No |
| MaxButton | No |
| OnClose | [Event Procedure] |
b) Create labels to display information about the application. c) Create a check box. In the Caption property of its attached label, type: Don't display this form again. Then set the following properties for the check box. Property |
Setting |
| Name | Hide Solutions Intro |
| ControlSource | Hide Solutions Intro |
| DefaultValue | 0 |
d) Create a command button, and set the following properties. Property |
Setting |
| Name | OK |
| Caption | OK |
| Default | Yes |
| OnClick | [Event Procedure] |
3 Create the Solutions form. 4 Create the Startup form. 5 Modify the event procedure of the OnTimer property of the Startup form. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
| Creating forms | User's Guide, Chapter 15, "Designing Forms," and Chapter 16, "Customizing Forms" |
|
Close Event Procedure for the Solutions Intro Form
Sub Form_Close ()
' Open Solutions form.
DoCmd OpenForm "Solutions"
End Sub
$#
Click Event Procedure of the OK Command Button
Sub OK_Click ()
On Error GoTo Err_OK_Click
' Close form. This code created by Command Button Wizard.
DoCmd Close
Exit_OK_Click:
Exit Sub
Err_OK_Click:
MsgBox Error$
Resume Exit_OK_Click
End Sub
$#
Timer Event Procedure of the Startup Form
The text and code you need to add to the event procedure to open the Solutions Intro form or the Solutions form are displayed in red.
Sub Form_Timer ()
' Open Solutions Intro form or Solutions form.
' Close startup form.
'
Dim MyDB As Database
Dim MyRecordset As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyRecordset = MyDB.TableDefs("Options").OpenRecordset()
' Use value in Hide Solutions Intro field in Options table to determine
' which form to open.
If MyRecordset.Fields("Hide Solutions Intro").Value = 0 Then
DoCmd OpenForm "Solutions Intro"
Else
DoCmd OpenForm "Solutions"
End If
' Close recordset.
MyRecordset.Close
' Close startup form.
DoCmd Close A_FORM, "Startup"
End Sub
$#
Highlights of the Sales Averages ReportThe Sales Averages report shows how to present multiple facts for each row heading in a crosstab report. This technique makes the Sales Averages report work:
$#
Create a Multiple-Fact Crosstab ReportYou can create a crosstab report that prints multiple facts for each row heading. For example, the Sales Averages report lists the minimum, average, and maximum monthly sales for each salesperson.
Overview
Use a select query as the report's record source, and create a crosstab layout by designing a multiple-column report. Create an unbound text box in the Last Name header to use as a flag to determine which row heading to print. Create an event procedure in the OnFormat property of the Last Name header that uses the NextRecord property to print the row headings in two columns. Create an event procedure in the OnFormat property of the Shipped Date header that uses the Month function and Left property to verify that the correct data prints in each column. When there is no data for a column, use the NextRecord property to prevent advancing to the next record, and use the PrintSection property to prevent printing the next section.
Objects Used in this Example
| Object | Names | ||
| Table | Employees, Orders | ||
| Query | Order Subtotals, Sales Averages | ||
| Report | Sales Averages | ||
Step by Step 1 Import the Order Subtotals query from NWIND.MDB, if you haven't already done so. 2 Create the Sales Averages query. Table/Query |
Field/Expression | Total | Criteria |
| Employees | Last Name First Name |
Group By Group By |
-- -- |
| Orders | Order ID Shipped Date Year: Format([Shipped Date], "yyyy") |
Group By Group By Expression |
-- -- "1993" |
| Order Subtotals | Subtotal | Group By | -- |
3 Design the Sales Averages report. a) Set the following sorting and grouping properties. Field/Expression |
Sort Order | Group Properties | |
| Last Name | Ascending | Group Header: Yes Group Footer: Yes Group On: Each Value Group Interval: 1 Keep Together: No |
|
| Shipped Date | Ascending | Group Header: Yes Group Footer: No Group On: Month Group Interval: 1 Keep Together: No |
|
b) Set the Width property of the report to 9.7813 inches. c) Set the following Print Setup options. Option |
Setting | ||
| Orientation | Landscape | ||
| Margins | Left: 0.4000 in Right: 0.4000 in Top: 1 in Bottom: 1 in |
||
| Items Across: | 14 | ||
| Column Spacing | 0.1 in | ||
| Item Size | Width: 0.6 in Height: 0 in |
||
| Item Layout | Horizontal | ||
d) In the page header, paste the labels for the type of currency and the names of the months. e) In the Last Name header, create the following text boxes, one below the other. Name |
ControlSource | ||
| Salesperson Last Name | =[Last Name]&"," | ||
| First Name | First Name | ||
f) In the Last Name header, create the following labels on top of the text boxes you created in Step 3e. Name |
Caption | ||
| L - Minimum | Minimum: | ||
| L - Average | Average: | ||
| L - Maximum | Maximum: | ||
g) In the Last Name header, create the following text boxes. Name |
Other Properties | ||
| Print What | ControlSource: -- Visible: No |
||
| Column Width | ControlSource: =(0.6+0.1)*1440 Format: General Number Visible: No |
||
| Left Margin | ControlSource: =0.4*1440 Format: General Number Visible: No |
||
h) In the Shipped Date header, create the following text boxes, one below the other. For all three, set the Format property to Standard, and the DecimalPlaces property to 0. Name |
ControlSource | ||
| Month Minimum | =Min([Subtotal]) | ||
| Month Average | =Avg([Subtotal]) | ||
| Month Maximum | =Max([Subtotal]) | ||
i) Set the following section properties. Section |
Property | Setting | |
| Page header | OnFormat | [Event Procedure] | |
| Last Name header | KeepTogether OnFormat |
Yes [Event Procedure] |
|
| Shipped Date header | KeepTogether OnFormat |
Yes [Event Procedure] |
|
| Detail section | Height | 0 in | |
| Last Name footer | NewRowOrCol Height |
After Section 0 in |
|
| Report footer | Height | 0 in | |
j) In the Declarations section of the Report.Sales Averages module, type: Option Explicit Cross References For information on |
See | ||
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | ||
| Creating queries | User's Guide, Chapter 10, "Query Basics," and Chapter 11, "Designing Select Queries" | ||
| Creating reports | User's Guide, Chapter 20, "Report Basics," and Chapter 21, "Designing Reports" | ||
| Setting sorting and grouping properties | User's Guide, Chapter 22, "Sorting and Grouping Data" | ||
$# Format Event Procedure for the Page Header Sub PageHeader1_Format (Cancel As Integer, FormatCount As Integer) ' Set Print What text box to 0 at top of page. Me![Print What] = 0 End Sub
|
Format Event Procedure for the Last Name Header
Sub GroupHeader2_Format (Cancel As Integer, FormatCount As Integer)
' Print information in row headings in correct order.
'
' Print Salesperson Last Name and First Name text boxes,
' hide Minimum, Average, and Maximum labels,
' set Print What text box to -1, and don't advance to next record.
If Me![Print What] = 0 Then
Me![Salesperson Last Name].Visible = True
Me![First Name].Visible = True
Me![L - Minimum].Visible = False
Me![L - Average].Visible = False
Me![L - Maximum].Visible = False
Me![Print What] = -1
Me.NextRecord = False
' Hide Salesperson Last Name and First Name text boxes,
' print Minimum, Average, and Maximum labels,
' and set Print What text box to 0.
Else
Me![Salesperson Last Name].Visible = False
Me![First Name].Visible = False
Me![L - Minimum].Visible = True
Me![L - Average].Visible = True
Me![L - Maximum].Visible = True
Me![Print What] = 0
End If
End Sub
$#
Format Event Procedure for the Shipped Date Header
Sub GroupHeader3_Format (Cancel As Integer, FormatCount As Integer)
' Print data in correct column.
'
' Don't advance to next record or print next section.
If Me.Left < Me![Left Margin] + (Month(Me![Shipped Date]) + 1) * Me![Column Width] Then
Me.NextRecord = False
Me.PrintSection = False
End If
End Sub
$#
Highlights of the Customer Phone ListThe Customer Phone List shows how to identify the first and last companies printed on each page. This technique makes the Customer Phone List work:
$#Print the First and Last Entries on a Page in the Page Header
Just as dictionaries make words easy to find, your reports can make data easy to find by identifying the first and last entries on a page. On the Customer Phone List, the first company name on a page is printed on the left side of the page header, and the last company name is printed on the right.
Overview
Create a report that sorts data by company name. In the page footer, create a text box for page numbers that uses the Pages property in its expression (to force two-pass reporting). Create an event procedure for the OnFormat property of the page footer that, on the first pass, puts the customer name in the last record on the page into a global array. Create an event procedure for the OnFormat property of the report footer that, on the first pass, puts the last record in the recordset into the array. Create an event procedure for the OnFormat property of the page header that, on the second pass, fills in the First Entry and Last Entry text boxes.
Objects Used in this Example
| Object | Name | |
| Table | Customers | |
| Report | Customer Phone List | |
Step by Step a) Set the report's RecordSource property to Customers. b) Add a report header and footer. Set the Height properties of both sections to 0. c) In the Sorting And Grouping box, select Company name in the Field/Expression column, and set Sort Order to Ascending. d) In the detail section, create the Company Name, Contact Name, and Phone text boxes. Cut their attached labels, and paste them in the page header. e) Set the KeepTogether property of the detail section to Yes. f) Create text boxes in the following sections and delete their attached labels. Section |
Name | ControlSource |
| Page header | First Entry | -- |
| Last Entry | -- | |
| Page footer | Page Number | =Page & " of " & Pages & " Pages" |
g) Make the Company Name, First Entry, and Last Entry text boxes the same width. h) In the Declarations section of the Report.Customer Phone List module, declare variables. i) Create event procedures for the following sections. Section |
Property | Setting |
| Page header | OnFormat | [Event Procedure] |
| Page footer | OnFormat | [Event Procedure] |
| Report footer | OnFormat | [Event Procedure] |
Cross References For information on |
See | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating reports | User's Guide, Chapter 20, "Report Basics," and Chapter 21, "Designing Reports" | |
| Setting sorting and grouping properties | User's Guide, Chapter 22, "Sorting and Grouping Data" | |
|
Declarations Section of the Report.Customer Phone List Module
Option Explicit
' Array for last record on each page and last record in recordset.
Dim gLast$()
' Flag for number of passes through report.
Dim gLastPage%
$
#Format Event Procedure for the Page Header
Sub PageHeader0_Format (Cancel As Integer, FormatCount As Integer)
' During second pass, fill in First Entry and Last Entry text boxes.
If gLastPage% = True Then
Reports![Customer Phone List].[First Entry] = Reports![Customer Phone List]![Company Name]
Reports![Customer Phone List].[Last Entry] = gLast$(Reports![Customer Phone List].Page)
End If
End Sub
$#
Format Event Procedure for the Page Footer
Sub PageFooter2_Format (Cancel As Integer, FormatCount As Integer)
' During first pass, enter last record in report's dynaset into array.
If Not gLastPage% Then
ReDim Preserve gLast$(Reports![Customer Phone List].Page + 1)
gLast$(Reports![Customer Phone List].Page) = Reports![Customer Phone List].[Company Name]
End If
End Sub
$#
Format Event Procedure for the Report Footer
Sub ReportFooter4_Format (Cancel As Integer, FormatCount As Integer)
Dim MyDB As Database
Dim MyRS As Recordset
' Set flag after first pass has been completed.
gLastPage% = True
' Open recordset for report.
Set MyDB = DBEngine.WorkSpaces(0).Databases(0)
Set MyRS = MyDB.OpenRecordset("Customers")
' Move to last record in recordset.
MyRS.MoveLast
' Enter last record into array.
gLast$(Reports![Customer Phone List].Page) = MyRS.[Company Name]
End Sub
$#
Highlights of the Edit Products FormThe Edit Products form shows how to create a form in which you can edit existing records but not enter new ones. These techniques make the Edit Products form work:
This technique enhances the Edit Products form:
$#Prevent Users from Automatically Moving to Another Record
On an edit-only form, you might want to prevent users from automatically advancing to another record. On the Edit Products form, for example, you can't advance to another record in the usual ways, such as by pressing tab or enter in the last field, by using the navigation buttons, or by using menu commands or toolbar buttons.
Overview
Remove the navigation buttons from the form. Create a transparent command button, and use an event procedure in its OnEnter property that uses the SetFocus method to move the focus to the Supplier ID combo box. Place the command button at the end of the tab order.
Objects Used in this Example
| Type | Name |
| Table | Products |
| Form | Edit Products |
Step by Step 1 Design the Edit Products form. a) Set the following form properties. Property |
Value |
| RecordSource | Products |
| Caption | Edit Products |
| DefaultView | Single Form |
| ViewsAllowed | Form |
| DefaultEditing | Can't Add Records |
| ShortcutMenu | No |
| ScrollBars | Neither |
| RecordSelectors | No |
| NavigationButtons | No |
| AutoCenter | Yes |
b) In the detail section, add the fields from the Products table. Use combo boxes for the Supplier ID and Category ID fields and a check box for the Discontinued field. Use text boxes for all the other fields. c) Set the Enabled property for all controls in the detail section to No. To prevent the labels from flashing when the controls are enabled, separate the attached labels from the controls by cutting the labels and then pasting them. d) Because it's a Counter field, make the Product ID text box display-only by setting the following properties. Property |
Value |
| BackColor | (same color as Detail section's BackColor) |
| BorderStyle | Clear |
| FontWeight | Bold |
2 In the detail section, create the Go To Supplier ID command button. a) Set its properties and create an event procedure as follows. Property |
Value |
| Name | Go To Supplier ID |
| Transparent | Yes |
| Enabled | No |
| OnEnter | [Event Procedure] |
b) To prevent users from inadvertently clicking the command button, hide it behind the Supplier ID combo box. (Make the command button the same height as the combo box, put it on top of the combo box, and then choose Send To Back from the Format menu.) 3 In the Declarations section of the Form.Edit Products module, type: Option Explicit 4 Edit the tab order for the form. Make sure that Supplier ID is the first field in the list and that Go To Supplier ID and Product ID are the last two fields in the list. Tip To move from record to record on this form, you enter search criteria. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
| Creating forms | User's Guide, Chapter 15, "Designing Forms," and Chapter 16, "Customizing Forms" |
|
Enter Event Procedure for the Go To Supplier ID Command Button
Sub Go_To_Supplier_ID_Enter ()
' Go to Supplier ID combo box to prevent advancing
' to another record.
Me![Supplier ID].SetFocus
End Sub
$#
Create a Toolbar and Menu That Include Only Editing TasksYou can control the kinds of tasks that users perform on a form by creating a custom toolbar and menu. For example, on the Edit Products form, the custom toolbar and menu include buttons and commands for editing tasks only.
Overview
Create a custom toolbar and menu. Create an event procedure for the form's OnActivate property that uses the ShowToolbar action to display the custom toolbar. Create an event procedure for the form's OnDeactivate property to hide the custom toolbar. Set the form's MenuBar property to the macro group for the menu.
Objects Used in this Example
| Object | Name | |||
| Form | Edit Products | |||
| Macro | Edit Products Menu Bar, Edit Products Menu Bar_File, Edit Products Menu Bar_File | |||
Step by Step 1 Create the Edit Products form. 2 Create a custom toolbar. a) Create a toolbar, and name it Edit Products Toolbar. b) Include these built-in buttons on the toolbar: Cut |
Copy | Paste | Undo | Undo Current Record |
Note The custom toolbars for the forms in Solutions also include the Design View and Form View buttons so that you can easily switch between views while you're looking at the examples. In most cases, you won't include these buttons on custom toolbars you create for your applications. c) To display the custom toolbar and hide the built-in toolbar, create event procedures for the following form properties. Property |
Setting | |||
| OnActivate | [Event Procedure] | |||
| OnDeactivate | [Event Procedure] | |||
3 Create a custom menu. a) Use the Menu Builder to create a menu bar named Edit Products Menu Bar. Use the Form menu bar as a template, and remove all menus and commands except the ones listed in the following table. Menu |
Command | |||
| File | Close | |||
| Edit | Undo Undo Current Field Cut Copy Paste |
|||
Note The custom menu bar in the Edit Products form also includes the View, Window, and Help menus so that you can switch easily between views and use the regular features of the Form View menu. b) Set the form's MenuBar property to the macro group, Edit Products Menu Bar. Cross References For information on |
See | |||
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |||
| Creating menus | Building Applications, Chapter 2, "Structuring a User Interface" | |||
| Creating toolbars | User's Guide, Appendix B, "Working with Toolbars" | |||
|
Activate Event Procedure for the Edit Products Form
Sub Form_Activate ()
' Hide built-in Form View toolbar.
' Show Edit Products Toolbar.
DoCmd ShowToolbar "Form View", A_TOOLBAR_NO
DoCmd ShowToolbar "Edit Products Toolbar, A_TOOLBAR_YES
End Sub
$#
Deactivate Event Procedure for the Edit Products Form
Sub Form_Deactivate ()
' Hide Edit Products Toolbar.
' Show built-in Form View toolbar.
DoCmd ShowToolbar "Edit Products Toolbar, A_TOOLBAR_NO
DoCmd ShowToolbar "Form View", A_TOOLBAR_YES
End Sub
$#
Limit the Contents of One List Based on the Value Selected in AnotherIf a list contains numerous items, you might find the list easier to use if you can limit the number of items displayed at one time. For example, instead of displaying all the products in the Northwind database, the Select Product list on the Edit Products form displays only those products in the category you pick in the Select Category list.
Overview
Create an event procedure for the AfterUpdate property of the Select Category combo box that uses the Requery method to requery the Select Product combo box. Include the Category ID field in the underlying query for the Select Product combo box. Set the criteria for the field to an expression that limits the list to those products whose Category ID matches the Category ID of the value in the Select Category combo box.
Objects Used in this Example
| Type | Names | |
| Table | Categories, Products | |
| Query | Category List, Limit Product List | |
| Form | Edit Products | |
| Module | Enable Controls | |
Step by Step 1 Create the Category List query. Table |
Field (Sort) | |
| Categories | Category Name (Ascending) | |
| Category ID | ||
2 Create the Limit Product List query. Table |
Field (Sort) | Criteria |
| Products | Product Name (Ascending) | -- |
| Product ID | -- | |
| Category ID | Forms![Edit Products]![Select Category] | |
3 Create the Enable Controls module, if you haven't already done so. a) In the Declarations section, type: Option Explicit b) Create the EnableControls function. 4 Modify the Edit Products form. a) Add a form header and footer. Set the Height property of the form footer to 0. b) Create the Select Category combo box in the form header. Set its properties and create an event procedure as follows. Property |
Value | |
| Name | Select Category | |
| RowSourceType | Table/Query | |
| RowSource | Category List | |
| ColumnCount | 2 | |
| ColumnWidths | ;0 in | |
| BoundColumn | 2 | |
| LimitToList | Yes | |
| AfterUpdate | [Event Procedure] | |
c) Create the Select Product combo box in the form header. Separate the attached label by cutting and pasting it (so that it doesn't flash when Select Product is enabled and disabled). Set the properties of the Select Product combo box as follows. Property |
Value | |
| Name | Select Product | |
| RowSourceType | Table/Query | |
| RowSource | Limit Product List | |
| ColumnCount | 2 | |
| ColumnWidths | ;0 in | |
| BoundColumn | 2 | |
| LimitToList | Yes | |
| Enabled | No | |
Tip On an edit-only form, you can prevent users from inadvertently making changes to the wrong record by disabling the controls in the detail section while they enter search criteria and enabling the controls after they find the record. Cross References For information on |
See | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating forms | User's Guide, Chapter 16, "Customizing Forms" | |
| Creating queries | User's Guide, Chapter 11, "Designing Select Queries" | |
|
EnableControls() Function
Function EnableControls (WhichSection As String, State As Integer) As Integer
' Enable or disable controls in specified section of form.
Dim MyForm As Form
Dim MyControl As Control
Dim X As Integer, SelectedSection As Integer
' Use active form. If no form is active, exit
' function without displaying error message.
On Error Resume Next
Set MyForm = Screen.ActiveForm
If Err Then
EnableControls = False
On Error GoTo 0
Exit Function
End If
' Define valid values for WhichSection.
Select Case UCase$(WhichSection)
Case "FORM HEADER"
SelectedSection = 1
Case "PAGE HEADER"
SelectedSection = 3
Case "DETAIL"
SelectedSection = 0
Case "PAGE FOOTER"
SelectedSection = 4
Case "FORM FOOTER"
SelectedSection = 2
Case Else
MsgBox "Invalid argument", , "EnableControls"
EnableControls = False
Exit Function
End Select
' Set state for all controls in specified section.
For X = 0 To MyForm.Count - 1
Set MyControl = MyForm(X)
If MyControl.Section = SelectedSection Then
On Error Resume Next
MyControl.Enabled = State
On Error GoTo 0
End If
Next X
EnableControls = True
End Function
$#
$#
AfterUpdate Event Procedure for the Select Category Combo Box
Sub Select_Category_AfterUpdate ()
' Enable and requery Select Product combo box.
' Disable controls in detail section.
Dim Tmp As Variant
Me![Select Product].Enabled = True
Me![Select Product].Requery
Tmp = EnableControls("Detail", False)
End Sub
A fast way to find a record is to select a value from a list. For example, the Edit Products form finds the record for the product you select in the Select Product combo box.
Tip On an edit-only form, you can prevent users from inadvertently making changes to the wrong record by disabling the controls in the detail section while they enter search criteria and enabling the controls after they enter it.
Overview
Create an event procedure for the AfterUpdate property of the Select Product combo box that uses the ApplyFilter command to find the record and a custom function to enable each control in the detail section.
Objects Used in this Example
| Type | Name |
| Table | Products |
| Form | Edit Products |
| Module | Enable Controls |
Step by Step 1 Create the Select Category and Select Product combo boxes. 2 Create the Enable Controls module, if haven't already done so. a) In the Declarations section, type: Option Explicit b) Create the EnableControls function. 3 Create an event procedure for the AfterUpdate property of the Select Product combo box. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
|
AfterUpdate Event Procedure for the Select Product Combo Box
Sub Select_Product_AfterUpdate ()
' Find record for product selected in Select Product combo box.
' Enable controls in detail section and disable the Product ID text box.
' Go to Supplier ID combo box.
Dim Tmp As Variant
DoCmd ApplyFilter , "[Product ID] = Forms![Edit Products]![Select Product]"
Tmp = EnableControls("Detail", True)
Me![Product ID].Enabled = False
Me![Supplier ID].SetFocus
End Sub
$#
Requery a List After You Move to Another RecordWhen you change a value in a field that affects the values in a list, it's essential to update, or requery, the list so that it contains the most current data. For example, when you move to another record on the Edit Products form, the Select Product combo box is requeried.
Overview
Create an event procedure for the AfterUpdate property of the form that uses the Requery method to requery the Select Product combo box.
Object Used in this Example
| Type | Name |
| Form | Edit Products |
Step by Step Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
|
AfterUpdate Event Procedure for the Edit Products Form
Sub Form_AfterUpdate ()
' Requery Select Product combo box.
Me![Select Product].Requery
End Sub
$
#Change the Color of a Control and Display a Message Based on the Control's ValueWhen the value in a control meets certain criteria, you can flag it by changing its color and displaying a message beside it. On the Edit Products form, for example, when the units in stock fall below the reorder level and there are no units on order, the text of the Units In Stock text box changes to red, and a message is displayed beside it.
Overview
Create a label to display the message. Create an event procedure for the OnCurrent property of the form that uses the text box's ForeColor property to set the color of the text and the label's Visible property to display the message. Create event procedures that run the Form_Current sub in the AfterUpdate properties of the Units In Stock, Units On Order, and Reorder Level controls.
Object Used in this Example
| Type | Name | |
| Form | Edit Products | |
Step by Step 1 Create a label beside the Units In Stock text box. Set its properties as follows. Property |
Setting | |
| Name | L - Low Stock | |
| Caption | Units in stock are below the reorder level. | |
| Visible | No | |
| ForeColor | 128 | |
2 Create event procedures for the form and controls as follows. Form/Control |
Property | Setting |
| Edit Products form | OnCurrent | [Event Procedure] |
| Units In Stock text box | AfterUpdate | [Event Procedure] |
| Units On Order text box | AfterUpdate | [Event Procedure] |
| Reorder Level text box | AfterUpdate | [Event Procedure] |
Cross References For information on |
See | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating forms | User's Guide, Chapter 15, "Designing Forms" | |
|
Current Event Procedure for the Edit Products Form
Sub Form_Current ()
' If value in Units In Stock text box is less than value in Reorder
' Level text box and value in Units On Order text box is 0, display
' value in Units In Stock in red and display L - Low Stock label.
If Me![Units In Stock] < Me![Reorder Level] And Me![Units On Order] = 0 Then
Me![Units In Stock].ForeColor = 128
Me![L - Low Stock].Visible = True
' Otherwise, display value in Units In Stock text box in black
' and hide L - Low Stock label.
Else
Me![Units In Stock].ForeColor = 0
Me![L - Low Stock].Visible = False
End If
End Sub
$#
AfterUpdate Event Procedure for the Units In Stock Text Box
Sub Units_In_Stock_AfterUpdate ()
' Run event procedure in Form_Current sub.
Form_Current
End Sub
$#
AfterUpdate Event Procedure for the Units On Order Text Box
Sub Units_On_Order_AfterUpdate ()
' Run event procedure in Form_Current sub.
Form_Current
End Sub
$#
AfterUpdate Event Procedure for the Reorder Level Text Box
Sub Reorder_Level_AfterUpdate ()
' Run event procedure in Form_Current sub.
Form_Current
End Sub
$#
Highlights of the Employee Sales ReportThe Employee Sales report shows how to create a crosstab report with dynamic column headings. These techniques make the Employee Sales report work:
The technique that enhances the report is:
$#Create a Crosstab Report with Dynamic Column Headings
You can vary the number of columns you print in a crosstab report depending on criteria you enter when you print it. For example, in the Employee Sales report, not every employee has sales each week, so the report prints columns for only those employees who had sales for the specified time period. This report also calculates totals for each row and column.
Overview
Create a crosstab query to use as the record source for the report. In the report design, create unbound text boxes in the page header (for column headings), in the detail section (for the row heading and crosstab values), and in the report footer (for column totals). In the OnFormat properties of the page header and detail section, create event procedures that fill the text boxes with the column headings and crosstab values and hide the unused text boxes. In the OnPrint property of the detail section, create an event procedure that calculates the row total, and adds it to the column-total array. In the OnPrint property of the report footer, create an event procedure that fills the text boxes with the column totals.
Objects Used in this Example
| Object | Names | |
| Table | Employees, Orders, Products | |
| Query | Employee Sales, Order Details Extended | |
| Report | Employee Sales | |
Step by Step 1 Import the Order Details Extended query from NWIND.MDB, if you haven't already done so. 2 Create Employee Sales, a crosstab query. Table/Query |
Field | Properties |
| Employees table | Last Name | Total: Group By Crosstab: Column Heading |
| Products table | Product Name | Total: Group By Crosstab: Row Heading |
| Order Details Extended query | Order Amount: Extended Price | Total: Sum Crosstab: Value |
| Orders table | Shipped Date | Total: Where Criteria: Between #1/2/93# And #1/16/93# |
Note If you look at the criteria for the Shipped Date field in the Employee Sales query, you'll see that it references controls on the Employee Sales Dialog Box. The steps to modify the query are covered in Set Report Criteria in a Dialog Box. 4 Design the Employee Sales report. a) Set the report's RecordSource property to Employee Sales. b) Add a report header and footer. Create a title for the report in the report header. c) Create 11 unbound text boxes in the page header. These text boxes will display the column headings. Set the Name property of the leftmost text box to Head1, the Name property of the next text box to Head2, and so on through Head11. d) Create 11 unbound text boxes in the detail section. (Create one text box for the row heading, nine text boxes for the maximum number of salespeople at Northwind Traders, and one text box for the row total.) Set the Name property of the leftmost text box to Col1, the Name property of the next text box to Col2, and so on through Col11. Set the Format property of text boxes Col2 through Col11 to Standard. e) Create 11 text boxes in the report footer. Set the Name property of the leftmost text box to Tot1 and its ControlSource property to: ="Totals:" Set the Name property of the remaining text boxes, which display the column totals, to Tot2 through Tot11. Set the Format property of text boxes Tot2 through Tot11 to Standard. f) In the General section of the Report.Employee Sales module, declare the variables,and create the InitVars sub and xtabCNulls function. g) Set properties and create event procedures as described in the following table. Report or Section |
Property | Setting |
| Report | OnOpen | [Event Procedure] |
| OnClose | [Event Procedure] | |
| Report header | OnFormat | [Event Procedure] |
| Page header | OnFormat | [Event Procedure] |
| Detail section | KeepTogether | Yes |
| OnFormat | [Event Procedure] | |
| OnPrint | [Event Procedure] | |
| OnRetreat | [Event Procedure] | |
| Report footer | OnPrint | [Event Procedure] |
Cross References For information on |
See | |
| Creating crosstab queries | User's Guide, Chapter 12, "Advanced Queries" | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating reports | User's Guide, Chapter 20, "Report Basics," and Chapter 21, "Designing Reports" | |
|
Declarations Section of the Report.Employee Sales Module
Option Compare Database 'Use database order for string comparisons
Option Explicit
' Constant for maximum number of columns Employee Sales query would
' create plus 1 for a Totals column.
Const TOTCOLS = 11
' Variables for database object and recordset.
Dim RptDB As Database
Dim RptRS As Recordset
' Variables for number of columns and row and report totals.
Dim IColCnt As Integer
Dim RgColTot(1 To TOTCOLS) As Long
Dim RptTotal As Long
$#
InitVars () Sub
Sub InitVars ()
Dim i As Integer
' Initialize RptTotal variable.
RptTotal = 0
' Initialize array that stores column totals.
For i = 1 To TOTCOLS
RgColTot(i) = 0
Next i
End Sub
$#
xtabCnulls () Function
Function xtabCnulls (MyVal As Variant)
' Test if a value is null.
If IsNull(MyVal) Then
' If MyVal is null, set MyVal to 0.
xtabCnulls = 0
Else
' Otherwise, return MyVal.
xtabCnulls = MyVal
End If
End Function
$#
Open Event Procedure for the Report
Sub Report_Open (Cancel As Integer)
' Create underlying recordset for report.
'
Dim i As Integer
Dim MyQuery As QueryDef
' Set database variable to current database.
Set RptDB = DBEngine.Workspaces(0).Databases(0)
' Open QueryDef.
Set MyQuery = RptDB.QueryDefs("Employee Sales")
' Open Recordset.
Set RptRS = MyQuery.OpenRecordset()
' Set a variable to hold number of columns in crosstab query.
IColCnt = RptRS.Fields.Count
End Sub
$#
Close Event Procedure for the Report
Sub Report_Close ()
' Close recordset.
RptRS.Close
End Sub
$#
Format Event Procedure for the Report Header
Sub ReportHeader3_Format (Cancel As Integer, FormatCount As Integer)
' Move to first record in recordset at beginning of report
' or when report is restarted. (A report is restarted when
' you print it from Print Preview window, or return
' to a previous page while previewing.)
RptRS.MoveFirst
' Initialize variables.
InitVars
End Sub
$#
Format Event Procedure for the Page Header
Sub PageHeader0_Format (Cancel As Integer, FormatCount As Integer)
Dim i As Integer
' Put column headings into text boxes in page header.
For i = 1 To IColCnt
Me("Head" + Format$(i)) = RptRS(i - 1).Name
Next i
' Make next available text box Totals heading.
Me("Head" + Format$(IColCnt + 1)) = "Totals"
' Hide unused text boxes in page header.
For i = (IColCnt + 2) To TOTCOLS
Me("Head" + Format$(i)).Visible = False
Next i
End Sub
$#
Format Event Procedure for the Detail Section
Sub Detail1_Format (Cancel As Integer, FormatCount As Integer)
' Place values in text boxes and hide unused text boxes.
'
Dim i As Integer
' Verify that not at end of recordset.
If Not RptRS.EOF Then
' If FormatCount is 1, place values from recordSet into text boxes
' in detail section.
If Me.FormatCount = 1 Then
For i = 1 To IColCnt
' Convert null values to 0.
Me("Col" + Format$(i)) = xtabCnulls(RptRS(i - 1))
Next i
' Hide unused text boxes in detail section.
For i = IColCnt + 2 To TOTCOLS
Me("Col" + Format$(i)).Visible = False
Next i
' Move to next record in recordset.
RptRS.MoveNext
End If
End If
End Sub
$#
Print Event Procedure for the Detail Section
Sub Detail1_Print (Cancel As Integer, PrintCount As Integer)
Dim i As Integer
Dim RowTotal As Long
' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
RowTotal = 0
For i = 2 To IColCnt
' Starting at column 2 (first text box with
crosstab value), compute total for current row in
detail section.
RowTotal = RowTotal + Me("Col" + Format$(i))
' add crosstab value to total for current column.
RgColTot(i) = RgColTot(i) + Me("Col" + Format$(i))
Next i
' Place row total in text box in detail section.
Me("Col" + Format$(IColCnt + 1)) = RowTotal
' Add row total for current row to grand total.
RptTotal = RptTotal + RowTotal
End If
End Sub
$#
Retreat Event Procedure for the Detail Section
Sub Detail1_Retreat ()
' Always back up to previous record when detail section retreats.
RptRS.MovePrevious
End Sub
$#
Print Event Procedure for the Report Footer
Sub ReportFooter4_Print (Cancel As Integer, PrintCount As Integer)
Dim i As Integer
' Place column totals in text boxes in report footer.
' Start at Column 2 (first text box with crosstab value).
For i = 2 To IColCnt
Me("Tot" + Format$(i)) = RgColTot(i)
Next i
' Place grand total in text box in report footer.
Me("Tot" + Format$(IColCnt + 1)) = RptTotal
' Hide unused text boxes in report footer.
For i = IColCnt + 2 To TOTCOLS
Me("Tot" + Format$(i)).Visible = False
Next i
End Sub
$#
Highlights of the Employee Sales Dialog BoxThe Employee Sales Dialog Box form shows how to prompt for criteria for the Employee Sales report. This technique makes the Employee Sales Dialog Box work:
$#Set Report Criteria in a Dialog Box
Instead of setting the criteria for a report in its underlying query, you can display a dialog box that prompts the person using the report to enter the criteria just before printing. A report is more flexible when its criteria is set at run time because users have control over the range of data they print. In the Employee Sales Dialog Box form, a user specifies the beginning and ending dates as the selection criteria.
Overview
Create a dialog box that contains the controls for entering the report's criteria. Add the Preview, Print, and Cancel buttons. In the report's underlying query, set the query parameters and the criteria for the date field using an expression that includes the Forms object, the name of the form, and the names of the controls. In the event procedure for the report's OnOpen property, specify the parameters for the QueryDef .
Objects Used in this Example
| Object | Name |
| Form | Employee Sales Dialog Box |
| Report | Employee Sales |
| Module | Is Loaded |
Step by Step 1 Create the Employee Sales report. 2 Create the Employee Sales Dialog Box form. a) Create an unbound form, and set the following form properties. Property |
Setting |
| Caption | Employee Sales |
| DefaultView | Single Form |
| ViewsAllowed | Form |
| ShortCutMenu | No |
| ScrollBars | Neither |
| RecordSelectors | No |
| NavigationButtons | No |
| AutoCenter | Yes |
| Modal | Yes |
| BorderStyle | Thin |
| MinButton | No |
| MaxButton | No |
b) Create two unbound text boxes. Set their Format properties to Medium Date, and set the Caption properties for the labels and the Name properties for the text boxes as follows. Caption for label |
Name for text box |
| Enter a beginning date: | Beginning Date |
| Enter an ending date: | Ending Date |
c) Create the following command buttons, setting their Name, Caption, and OnClick properties as follows. Also, set the Default property of the Preview button to Yes. Name and Caption |
OnClick |
| Preview | [Event Procedure] |
| [Event Procedure] | |
| Cancel | [Event Procedure] |
d) In the Declarations section of the Form.Employee Sales Dialog Box module, type: Option Explicit Const ERR_REP_CANCELED = 2501 If you use the Control Wizards to create the command buttons, the Wizard automatically adds the Option Explicit statement. 3 Modify the Employee Sales query. a) In the Criteria box for the Shipped Date field, type: Between [Forms]![Employee Sales Dialog Box]![Beginning Date] And [Forms]![Employee Sales Dialog Box]![Ending Date] b) Set query parameters as follows. Parameter |
Data Type |
| Forms!Employee Sales Dialog Box!Beginning Date | Date/Time |
| Forms!Employee Sales Dialog Box!Ending Date | Date/Time |
4 Create the Is Loaded module, if you haven't already done so. a) In the Declarations section of the module, type: Option Explicit b) Create the IsLoaded function. 5 Modify the Open event procedure of the Employee Sales report. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
| Creating forms | User's Guide, Chapter 14, "Form Basics," and Chapter 15, "Designing Forms" |
| Creating queries | User's Guide, Chapter 12, "Advanced Queries" |
|
Click Event Procedure for the Preview Command Button
Sub Preview_Click ()
' Preview report.
On Error GoTo Err_Preview_Click
Dim DocName As String
DocName = "Employee Sales"
DoCmd OpenReport DocName$, 2
Exit_Preview_Click:
Exit Sub
Err_Preview_Click:
If Err = ERR_RPT_CANCELED Then
Resume Exit_Preview_Click
Else
MsgBox Error$
Resume Exit_Preview_Click
End If
End Sub
$#
Click Event Procedure for the Print Command Button
Sub Print_Click ()
' Print report.
On Error GoTo Err_Print_Click
Dim DocName As String
DocName = "Employee Sales"
DoCmd OpenReport DocName$, 0
Exit_Print_Click:
Exit Sub
Err_Print_Click:
If Err = ERR_RPT_CANCELED Then
Resume Exit_Print_Click
Else
MsgBox Error$
Resume Exit_Print_Click
End If
End Sub
$#
Click Event Procedure for the Cancel Command Button
Sub Cancel_Click ()
' Close form. (This code is created by Command Button Wizard.)
On Error GoTo Err_Cancel_Click
DoCmd Close
Exit_Cancel_Click:
Exit Sub
Err_Cancel_Click:
MsgBox Error$
Resume Exit_Cancel_Click
End Sub
$#
IsLoaded () Function
Function IsLoaded (MyFormName)
' Determines if a form is loaded.
Const FORM_DESIGN = 0
Dim i As Integer
IsLoaded = False
For i = 0 To Forms.Count -1
If Forms(i).Formname = MyFormName Then
If Forms(i).CurrentView <> FORM_DESIGN Then
IsLoaded = True
Exit Function ' Quit function once form has been found.
End If
End If
Next
End Function
$#
Open Event Procedure for the Employee Sales Report
The comments and code you need to add to the event procedure to make sure the Employee Sales Dialog Box is loaded, to set parameters for the QueryDef, and to display a message if no records match the search criteria are displayed in red.
Sub Report_Open (Cancel As Integer)
' Create underlying recordset for report using criteria entered in
' Employee Sales Dialog Box form.
'
Dim i As Integer
Dim MyQuery As QueryDef
' Don't open report if Employee Sales Dialog Box form isn't loaded.
If Not (IsLoaded("Employee Sales Dialog Box")) Then
Cancel = True
MsgBox "To preview or print this report, you must open the Employee Sales Dialog Box in Form view.", 48, "Must Open Dialog Box"
Exit Sub
End If
' Set database variable to current database.
Set RptDB = DBEngine.Workspaces(0).Databases(0)
' Open QueryDef.
Set MyQuery = RptDB.QueryDefs("Employee Sales")
' Set parameters for query based on values entered in Employee Sales Dialog Box form.
MyQuery.Parameters("Forms![Employee Sales Dialog Box]![Beginning Date]") = Forms![Employee Sales Dialog Box]![Beginning Date]
MyQuery.Parameters("Forms![Employee Sales Dialog Box]![Ending Date]") = Forms![Employee Sales Dialog Box]![Ending Date]
' Open Recordset.
Set RptRS = MyQuery.OpenRecordset()
' If no records match criteria, display message,
' close recordset, and cancel Open event.
If RptRS.RecordCount = 0 Then
MsgBox "No records match the criteria you entered.", 48, "No Records Found"
RptRS.Close
Cancel = True
Exit Sub
End If
' Set a variable to hold number of columns in crosstab query.
IColCnt = RptRS.Fields.Count
End Sub
$#Print Criteria from a Report's Criteria Dialog Box on the Report
To improve the functionality of a report, you can print the criteria you typed in the report's print dialog box on the report. For example, the values you enter in the Beginning Date and Ending Date text boxes on the Employee Sales Dialog Box appear in the report header of the Employee Sales report.
Overview
Create an unbound text box on the report. Set its ControlSource property to an expression that returns the values entered in the controls in the report's criteria dialog box.
Objects Used in this Example
| Object | Name |
| Form | Employee Sales Dialog Box |
| Report | Employee Sales |
Step by Step 1 Create the Employee Sales report. 2 Create the Employee Sales Dialog Box. 3 Modify the Employee Sales report. Create an unbound text box in the report header. Set its Name property to Range and its ControlSource property to: ="From " & Format(Forms![Employee Sales Dialog Box]![Beginning Date], "dd-mmm-yy") & " through " & Format(Forms![Employee Sales Dialog Box]![Ending Date], "dd-mmm-yy") Cross References For information on |
See |
| Creating expressions | Users's Guide, Chapter 23, "Using Expressions in Reports" |
| Creating reports | User's Guide, Chapter 20, "Report Basics," and Chapter 21, "Designing Reports" |
|
The Enter Or Edit Products form shows how to enter or edit records on the same form. These techniques make the Enter Or Edit Products form work:
These techniques enhance the form:
$#Create a Form in Which You Can Enter or Edit Data
You can have easy access to data by creating a form in which you can switch quickly between entering new records and editing existing ones. For example, the Enter Or Edit Products form opens in data entry mode. To edit records, click the Edit Data button on the toolbar. To switch back to entering records, click the Enter Data button on the toolbar.
Overview
Design the form so that it opens in data entry mode. In the form header, add the combo boxes in which you enter search data, and disable them. Create functions for two custom toolbar buttons, Edit Data and Enter Data. In the functions, set the AllowEditing property of the form so that, in the Activate and Deactivate events of the form, you can use the value of the property as a flag to hide and show the correct toolbar. Create macros to run the functions. Create two toolbars, one that includes the Edit Data button and is displayed when the form is in data entry mode, and another that includes the Enter Data button and is displayed when the form is in edit mode.
Objects Used in this Example
| Object | Names | ||||
| Table | Products | ||||
| Form | Enter Or Edit Products | ||||
| Macro | Enter Or Edit Products | ||||
| Module | Enable Controls, Enter Or Edit Products Buttons | ||||
Step by Step 1 Design the Enter Or Edit Products form. a) Set the following form properties. Property |
Setting | ||||
| RecordSource | Products | ||||
| Caption | Enter Or Edit Products | ||||
| DefaultView | Single Form | ||||
| ViewsAllowed | Form | ||||
| DefaultEditing | Data Entry | ||||
| ShortcutMenu | No | ||||
| Scrollbars | Neither | ||||
| RecordSelectors | No | ||||
b) In the detail section, add the fields from the Products table. Use a check box for the Discontinued field and combo boxes for the Supplier ID and Category ID fields. Use text boxes for all the other fields. c) To prevent the labels from flashing when the controls in the detail section are enabled and disabled, separate the attached labels from the controls by cutting the labels and then pasting them. d) Because it's a Counter field, make the Product ID text box display-only by setting the following properties: Property |
Value | ||||
| Enabled | No | ||||
| BackColor | (same color as detail section's BackColor) | ||||
| BorderStyle | Clear | ||||
| FontWeight | Bold | ||||
2 Add a form header, and create the Select Category and Select Product combo boxes. The procedure for creating the combo boxes was written for the Edit Products form, so you will need to make the following changes. a) Set the RowSource property of the Select Product combo box to: SELECT DISTINCTROW Products.[Product Name], Products.[Product ID], Products.[Category ID] FROM Products WHERE (Products.[Category ID]=[Forms]![Enter Or Edit Products]![Select Category]) ORDER BY Products.[Product Name]; b) Create an event procedure in the AfterUpdate property of the Select Product combo box. c) Set the Enabled property of both combo boxes to No. d) To prevent flashing when the combo boxes are enabled and disabled, separate the attached labels from both combo boxes by cutting and pasting the labels. 3 Create the Enable Controls module, if you haven't already done so. a) In the Declarations section of the module, type: Option Explicit b) Create the EnablesControls function. 4 Create the Enter Or Edit Products Buttons module. a) In the Declarations section of the module, type: Option Explicit b) Create the EditData function. c) Create the EnterData function. 5 Create the following macros in a macro group named Enter Or Edit Products. Macro Name |
Action | Argument | |||
| Enter Data | RunCode | EnterData() | |||
| Edit Data | RunCode | EditData() | |||
6 Create custom toolbars. a) Create a toolbar, and name it Enter Or Edit Products 1. b) Include these built-in buttons on the toolbar: New |
Cut | Copy | Paste | Undo | Undo Current Record |
c) Add a custom button to the toolbar. Macro |
Button Text | Button Description | |||
| Enter Or Edit Products.Edit Data | Edit Data | Edit Data | |||
d) Create a toolbar and name it Enter Or Edit Products 2. e) Include these built-in buttons on the toolbar: Cut, Copy, Paste, Undo, and Undo Current Record. f) Add a custom button to the toolbar. Macro |
Button Text | Button Description | |||
| Enter Or Edit Products.Enter Data | Enter Data | Enter Data | |||
Note The custom toolbars for the forms in Solutions also include the Design View and Form View buttons so that you can easily switch between views while you're looking at the examples. In most cases, you won't include these buttons on the toolbars you create for your applications. 7 Create event procedures for the following form properties. Property |
Setting | ||||
| OnOpen | [Event Procedure] | ||||
| OnActivate | [Event Procedure] | ||||
| OnDeactivate | [Event Procedure] | ||||
8 In the Declarations section of the Form.Enter Or Edit Products module, type: Option Explicit Cross References For information on |
See | ||||
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | ||||
| Creating forms | User's Guide, Chapter 15, "Designing Forms," and Chapter 16, "Customizing Forms" | ||||
| Creating macros | User's Guide, Chapter 24, "Macro Basics" | ||||
| Creating toolbars | User's Guide, Appendix B, "Working with Toolbars" | ||||
|
AfterUpdate Event Procedure for the Select Product Combo Box
Sub Select_Product_AfterUpdate ()
' Find record for product selected in Select Product combo box,
' enable controls in detail section, disable Product ID text box,
' and go to Supplier ID combo box.
Dim Tmp As Variant
DoCmd ApplyFilter , "[Product ID] = Forms![Enter Or Edit Products]![Select Product]"
Tmp = EnableControls("Detail", True)
Me![Product ID].Enabled = False
Me![Supplier ID].SetFocus
End Sub
$#
EditData () Function
Function EditData () As Integer
' Code for Edit Data button on Enter Or Edit Products 1 toolbar.
'
On Error GoTo EditData_Err
Dim MyForm As Form
Dim ComboBox1 As Control
Dim ComboBox2 As Control
Dim EditColor As Long
Dim Tmp As Variant
Set MyForm = Forms![Enter Or Edit Products]
Set ComboBox1 = Forms![Enter Or Edit Products]![Select Category]
Set ComboBox2 = Forms![Enter Or Edit Products]![Select Product]
EditColor = 16776960
' Set AllowEditing property to false to indicate editing mode.
' Set DefaultEditing property to Can't Add Records (4).
MyForm.AllowEditing = False
MyForm.DefaultEditing = 4
' Switch toolbars.
DoCmd ShowToolbar "Enter Or Edit Products 1", A_TOOLBAR_NO
DoCmd ShowToolbar "Enter Or Edit Products 2", A_TOOLBAR_WHERE_APPROP
' Enable and move to first combo box.
ComboBox1.Enabled = True
ComboBox1.SetFocus
' Disable controls in detail section.
Tmp = EnableControls("Detail", False)
Exit Function
EditData_Err:
MsgBox Error$
Exit Function
End Function
$#
EnterData () Function
Function EnterData () As Integer
' Code for Enter Data button on Enter Or Edit Products 2 toolbar.
'
On Error GoTo EnterData_Err
Dim MyForm As Form
Dim ComboBox1 As Control
Dim ComboBox2 As Control
Dim EnterColor As Long
Dim Tmp As Variant
Set MyForm = Forms![Enter Or Edit Products]
Set ComboBox1 = Forms![Enter Or Edit Products]![Select Category]
Set ComboBox2 = Forms![Enter Or Edit Products]![Select Product]
EnterColor = 8421376
' Set AllowEditing property to true to indicate data entry mode.
' Set DefaultEditing property to Data Entry (1).
MyForm.AllowEditing = True
MyForm.DefaultEditing = 1
' Enable controls in detail section and disable Product ID text box.
Tmp = EnableControls("Detail", True)
MyForm![Product ID].Enabled = False
' Go to a new record and move to Supplier ID combo box.
DoCmd GoToRecord , , A_NEWREC
MyForm![Supplier ID].SetFocus
' Switch toolbars.
DoCmd ShowToolbar "Enter Or Edit Products 2", A_TOOLBAR_NO
DoCmd ShowToolbar "Enter Or Edit Products 1", A_TOOLBAR_WHERE_APPROP
' Disable combo boxes in form header.
ComboBox1.Enabled = False
ComboBox2.Enabled = False
Exit Function
EnterData_Err:
MsgBox Error$
Exit Function
End Function
$#
Open Event Procedure for the Enter Or Edit Products Form
Sub Form_Open (Cancel As Integer)
' Set AllowEditing property to True when form opens.
Me.AllowEditing = True
End Sub
$#
Activate Event Procedure for the Enter Or Edit Products Form
Sub Form_Activate ()
' Hide Form View toolbar.
' Use AllowEditing property setting to determine which toolbar to
' show.
DoCmd ShowToolbar "Form View", A_TOOLBAR_NO
If Me.AllowEditing = True Then
DoCmd ShowToolbar "Enter Or Edit Products 1", A_TOOLBAR_YES
Else
DoCmd ShowToolbar "Enter Or Edit Products 2", A_TOOLBAR_YES
End If
End Sub
$#
Deactivate Event Procedure for the Enter Or Edit Products Form
Sub Form_Deactivate ()
' Use AllowEditing property setting to determine which toolbar to
' hide.
' Show Form View toolbar.
If Me.AllowEditing = True Then
DoCmd ShowToolbar "Enter Or Edit Products 1", A_TOOLBAR_NO
Else
DoCmd ShowToolbar "Enter Or Edit Products 2", A_TOOLBAR_NO
End If
DoCmd ShowToolbar "Form View", A_TOOLBAR_WHERE_APPROP
End Sub
$#
Control When You Go to a New RecordRather than automatically advancing to a new record when you press tab or enter in the last field on a form, you can stay in a record until you want to proceed. For example, when you enter data in the Enter Or Edit Products form, you can verify that the information you typed is correct and then advance to a new record by clicking the New button on the toolbar.
Overview
Remove the navigation buttons from the form. In the detail section, create a transparent button, and define an event procedure in its OnEnter property that uses the SetFocus method to move the focus to the Supplier ID combo box. Place the button at the end of the tab order.
Objects Used in this Example
| Type | Name |
| Table | Products |
| Form | Enter Or Edit Products |
Step by Step 1 Set the form's NavigationButtons property to No. 2 In the detail section, create the Go To Supplier ID command button. a) Set its properties and create an event procedure as follows. Property |
Setting |
| Name | Go To Supplier ID |
| Transparent | Yes |
| OnEnter | [Event Procedure] |
b) To prevent users from inadvertantly clicking the command button, hide it behind the Supplier ID combo box. (Make the command button the same height as the combo box, put it on top of the combo box, and then select Send To Back from the Format menu.) 3 Edit the tab order for the form. Make sure Supplier ID is the first field in the list and Go To Supplier ID and Product ID are the last two fields in the list. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
| Creating forms | User's Guide, Chapter 15, "Designing Forms" |
| Creating toolbars | User's Guide, Appendix B, "Working with Toolbars" |
|
The Add Category form is opened from the Enter Or Edit Products form when you add a new category to the Category ID combo box. This technique makes the Add Category form work:
$#Add a New Record to a List
As you enter or edit records, you might need to add a new item to the list in a combo box. For example, on the Enter Or Edit Products form, you can add a new category to the list by typing the name in the combo box. The form displays a message box asking if you want to add a new record. If you choose Yes, it displays a form in which you enter the information for the new category.
Overview
Create an event procedure for the OnNotInList property of the Category ID combo box on the Enter Or Edit Products form. (This event procedure displays a dialog box in which you can confirm that you want to add a new category; if you choose Yes, it opens the Add Category form.) Create an event procedure for the OnClose event of the Add Category form that uses the Requery method to requery the Category ID combo box on the Enter Or Edit Products form.
Objects Used in this Example
| Type | Names |
| Table | Products, Categories |
| Form | Enter Or Edit Products, Add Category |
| Module | Is Loaded |
Step by Step 1 On the Enter or Edit Products form, create an event procedure in the OnNotInList property of the Category ID combo box. 2 Create the Is Loaded module, if you haven't already done so. a) In the Declarations section of the module, type: Option Explicit b) Create the IsLoaded function. 3 Design the Add Category form. a) Set form properties and create event procedures as follows. Property |
Setting |
| RecordSource | Categories |
| Caption | Add Category |
| DefaultView | Single Form |
| ViewsAllowed | Form |
| DefaultEditing | Data Entry |
| ScrollBars | Neither |
| RecordSelectors | No |
| NavigationButtons | No |
| AutoResize | No |
| AutoCenter | Yes |
| OnUnload | [Event Procedure] |
| OnActivate | [Event Procedure] |
| OnDeactivate | [Event Procedure] |
b) In the detail section, add the Category Name, Description, and Picture fields from the Categories table. 4 In the Declarations section of the Form.Add Category module, type: Option Explicit Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
| Creating forms | User's Guide, Chapter 15, "Designing Forms," and Chapter 19, "Using Pictures, Graphs, and Other Objects" |
|
NotInList Event Procedure for the Category ID Combo Box
Sub Category_ID_NotInList (NewData As String, Response As Integer)
' Add a new category by typing a name in
' Category ID combo box.
Dim NewCategory As Integer, TruncateName As Integer, Title As String, MsgDialog As Integer
Const MB_OK = 0
Const MB_YESNO = 4
Const MB_ICONQUESTIONMARK = 32
Const MB_ICONEXCLAMATION = 64
Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7
' Display message box asking if user wants to add a
' new category.
Title = "Category Not In List"
MsgDialog = MB_YESNO + MB_ICONQUESTIONMARK + MB_DEFBUTTON1
NewCategory = MsgBox("Do you want to add a new category?", MsgDialog, Title)
If NewCategory = IDYES Then
' Remove new name from Category ID combo box so
' control can be requeried when user returns to form.
DoCmd DoMenuItem A_FORMBAR, A_EDIT, A_UNDOFIELD, , A_MENU_VER20
' Display message box and adjust length of value entered in
' Category ID combo box.
Title = "Name Too Long"
MsgDialog = MB_OK + MB_ICONEXCLAMATION
If Len(NewData) > 15 Then
TruncateName = MsgBox("Category names can be no longer than 15 characters. The name you entered will be truncated.", MsgDialog, Title)
NewData = Left$(NewData, 15)
End If
' Open Add Category form.
DoCmd OpenForm "Add Category", A_NORMAL, , , A_ADD
Forms![Add Category]![Category Name] = NewData
' Continue without displaying default error message.
Response = DATA_ERRCONTINUE
End If
End Sub
$#
$#
$#
$#
UnLoad Event Procedure for the Add Category Form
Sub Form_Unload (Cancel As Integer)
' If Enter Or Edit Products form is loaded,
' select it, requery Category ID combo box,
' and set value of Category ID combo box.
Dim MyControl As Control
If IsLoaded("Enter Or Edit Products") Then
Set MyControl = Forms![Enter Or Edit Products]![Category ID]
DoCmd SelectObject A_FORM, "Enter Or Edit Products"
MyControl.Requery
MyControl = Me![Category ID]
End If
End Sub
Activate Event Procedure for the Add Category Form
Sub Form_Activate ()
' Show toolbar for Add Category form.
'
' Hide Form View toolbar.
' Show Custom Form View toolbar.
DoCmd ShowToolbar "Form View", A_TOOLBAR_NO
DoCmd ShowToolbar "Custom Form View", A_TOOLBAR_YES
' If Enter Or Edit Products form is loaded, hide its current toolbar.
If IsLoaded("Enter Or Edit Products") Then
If Forms![Enter Or Edit Products].AllowEditing = True Then
DoCmd ShowToolbar "Enter Or Edit Products 1", A_TOOLBAR_NO
Else
DoCmd ShowToolbar "Enter Or Edit Products 2", A_TOOLBAR_NO
End If
End If
End Sub
Deactivate Event Procedure for the Add Category Form
Sub Form_Deactivate ()
' Hide toolbar for Add Category form.
'
' Hide Custom Form View toolbar.
' Show built-in Form View toolbar.
DoCmd ShowToolbar "Custom Form View", A_TOOLBAR_NO
DoCmd ShowToolbar "Form View", A_TOOLBAR_WHERE_APPROP
End Sub
You can make forms more readable and reports look more professional if the data in the fields has a consistent appearance. For example, the product names in the Northwind Traders database should all begin with a capital letter. Because a product name may include several words, some of which you don't want capitalized, you can define a function that capitalizes only the first letter of the first word and leaves the rest of the letters in the case in which you entered them.
Overview
Create a function that capitalizes the first letter of the value in a field. Use that function in an event procedure in the AfterUpdate property of the control.
Objects Used in this Example
| Type | Name |
| Table | Products |
| Form | Enter Or Edit Products |
| Module | Proper |
Step by Step 1 Create the Proper module. a) In the Declarations section, type: Option Explicit b) Create the CapitalizeFirst function. 2 On the Enter Or Edit Products form, create an event procedure in the AfterUpdate property of the Product Name text box. 3 In the Declarations section of the Form.Enter Or Edit Products module, type: Option Explicit Tip You can also create a function that capitalizes the first letter of every word in the field. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
$# CapitalizeFirst Function Function CapitalizeFirst (X) ' Make first letter in field uppercase; leave other ' letters as typed. Dim Temp Temp = Trim(X) CapitalizeFirst = UCase(Left(Temp, 1)) & Mid(Temp, 2) End Function
|
AfterUpdate Event Procedure for the Product Name Text Box
Sub Product_Name_AfterUpdate ()
' Capitalize first letter in product name.
If Not (IsNull(Me![Product Name])) Then
Me![Product Name] = CapitalizeFirst((Me![Product Name]))
End If
End Sub
$#
Proper Function
Function Proper (X)
' Capitalize first letter of every word in a field.
' Use in an event procedure in AfterUpdate of control;
' for example, [Last Name] = Proper([Last Name]).
' Names such as O'Brien and Wilson-Smythe are properly capitalized,
' but MacDonald is changed to Macdonald, and van Buren to Van Buren.
' Note: For this function to work correctly, you must specify
' Option Compare Database in the Declarations section of this module.
Dim Temp$, C$, OldC$, i As Integer
If IsNull(X) Then
Exit Function
Else
Temp$ = CStr(LCase(X))
' Initialize OldC$ to a single space because first
' letter needs to be capitalized but has no preceding letter.
OldC$ = " "
For i = 1 To Len(Temp$)
C$ = Mid$(Temp$, i, 1)
If C$>="a" And C$<= "z" And (OldC$ < "a" Or OldC$ > "z") Then
Mid$(Temp$, i, 1) = UCase$(C$)
End If
OldC$ = C$
Next i
Proper = Temp$
End If
End Function
$#
Change the Color of a Section and Its Controls at Run TimeBy changing the color of a section and its controls in response to a user action, you can indicate whether the section is active or inactive. For example, while you enter new records in the Enter Or Edit Products form, the form header is teal and its controls are not enabled. When you click the Edit Data button, the form header changes to light blue and the controls become enabled. The darker color indicates an inactive state while the lighter color indicates an active state.
Overview
On the form, set the BackColor property of the form header and any controls in it to the inactive color. To the EditData function, add code that sets the BackColor properties of the form header and each control in it to the active color. To the EnterData function, add code that sets the BackColor properties of the form header and its controls to the inactive color.
Objects Used in this Example
| Type | Name |
| Table | Products |
| Form | Enter Or Edit Products |
| Module | Enter Or Edit Products Buttons |
Step by Step 1 On the Enter Or Edit Products form, set the BackColor property of the form header, and the Select Category and the Select Product combo boxes to 8421376. (Teal on the palette.) 2 Create the Enter Data and Edit Data buttons, if you haven't already done so. 3 Modify the Enter Or Edit Products Buttons module. a) Modify the EditData function. b) Modify the EnterData function. 4 Create an event procedure that resets the color of the form header and its controls in the OnClose property of the form. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
| Creating forms | User's Guide, Chapter 15, "Designing Forms" |
| Creating macros | User's Guide, Chapter 24, "Macro Basics" |
| Creating toolbars | User's Guide, Appendix B, "Working with Toolbars" |
|
EditData () Function
The comment and code you need to add to the function to set the color of the form header and its controls are displayed in red.
Function EditData () As Integer
' Code for Edit Data button on Enter Or Edit Products 1 toolbar.
'
On Error GoTo EditData_Err
Dim MyForm As Form
Dim ComboBox1 As Control
Dim ComboBox2 As Control
Dim EditColor As Long
Dim Tmp As Variant
Set MyForm = Forms![Enter Or Edit Products]
Set ComboBox1 = Forms![Enter Or Edit Products]![Select Category]
Set ComboBox2 = Forms![Enter Or Edit Products]![Select Product]
EditColor = 16776960
' Set AllowEditing property to false to indicate editing mode.
' Set DefaultEditing property to Can't Add Records (4).
MyForm.AllowEditing = False
MyForm.DefaultEditing = 4
' Switch toolbars.
DoCmd ShowToolbar "Enter Or Edit Products 1", A_TOOLBAR_NO
DoCmd ShowToolbar "Enter Or Edit Products 2", A_TOOLBAR_WHERE_APPROP
' Change color of form header and combo boxes.
MyForm.Section(1).BackColor = EditColor
ComboBox1.BackColor = EditColor
ComboBox2.BackColor = EditColor
' Enable and move to first combo box.
ComboBox1.Enabled = True
ComboBox1.SetFocus
' Disable controls in detail section.
Tmp = EnableControls("Detail", False)
Exit Function
EditData_Err:
MsgBox Error$
Exit Function
End Function
$#
EnterData () Function
The comment and code you need to add to the function to set the color of the form header and its controls are displayed in red.
Function EnterData () As Integer
' Code for Enter Data button on Enter Or Edit Products 2 toolbar.
'
On Error GoTo EnterData_Err
Dim MyForm As Form
Dim ComboBox1 As Control
Dim ComboBox2 As Control
Dim EnterColor As Long
Dim Tmp As Variant
Set MyForm = Forms![Enter Or Edit Products]
Set ComboBox1 = Forms![Enter Or Edit Products]![Select Category]
Set ComboBox2 = Forms![Enter Or Edit Products]![Select Product]
EnterColor = 8421376
' Set AllowEditing property to true to indicate data entry mode.
' Set DefaultEditing property to Data Entry (1).
MyForm.AllowEditing = True
MyForm.DefaultEditing = 1
' Enable controls in detail section and disable Product ID text box.
Tmp = EnableControls("Detail", True)
MyForm![Product ID].Enabled = False
' Go to a new record and move to Supplier ID combo box.
DoCmd GoToRecord , , A_NEWREC
MyForm![Supplier ID].SetFocus
' Switch toolbars.
DoCmd ShowToolbar "Enter Or Edit Products 2", A_TOOLBAR_NO
DoCmd ShowToolbar "Enter Or Edit Products 1", A_TOOLBAR_WHERE_APPROP
' Change color of form header and combo boxes.
MyForm.Section(1).BackColor = EnterColor
ComboBox1.BackColor = EnterColor
ComboBox2.BackColor = EnterColor
' Disable combo boxes in form header.
ComboBox1.Enabled = False
ComboBox2.Enabled = False
Exit Function
EnterData_Err:
MsgBox Error$
Exit Function
End Function
$#
$#
Close Event Procedure of the Enter Or Edit Products Form
Sub Form_Close ()
' Reset background color of form header and its
' controls to original color.
Dim StartColor As Long
' Set StartColor to teal.
StartColor = 8421376
Me.Section(1).BackColor = StartColor
Me![Select Category].BackColor = StartColor
Me![Select Category].BackColor = StartColor
End Sub
The About Solutions form shows how to display an application's name, copyright, and logo in a dialog box. This technique makes the About Solutions form work:
Creating an About dialog box.
$#KCreate an About Dialog Box
You can display information about your application in a dialog box that's available from a custom menu bar. The About Solutions dialog box is available from the Help menu of the Edit Products form.
Overview
Create an unbound form that displays information about your application. Create a macro that adds a command to display the form in the Help menu macro group.
Objects Used in this Example
| Object | Name | |
| Form | About Solutions | |
| Macro | Edit Products Menu Bar_Help | |
Step by Step 1 Design the About Solutions form. a) Set the following form properties. Property |
Setting | |
| Caption | About Solutions | |
| DefaultView | Single Form | |
| ViewsAllowed | Form | |
| DefaultEditing | Read Only | |
| ShortcutMenu | No | |
| ScrollBars | Neither | |
| RecordSelectors | No | |
| NavigationButtons | No | |
| AutoCenter | Yes | |
| PopUp | Yes | |
| Modal | Yes | |
| MinButton | No | |
| MaxButton | No | |
| BorderStyle | Dialog | |
Note The Modal and PopUp properties in the About Solutions form in Solutions are set to No so that you can have access to menu bar commands and toolbar buttons when you look at the form. When Modal and PopUp are set to Yes, the form will have a dialog box border. b) In the detail section, create labels that display your application's name, version number, and copyright. Also, create an object frame that displays your application's logo. c) Create a command button. Set its properties and create an event procedure as follows. Property |
Setting | |
| Name | OK | |
| Caption | OK | |
| OnClick | [Event Procedure] | |
2 In the Declarations section of the Form.About Solutions module, type: Option Explicit 3 Add a macro to the end of the Edit Products Menu Bar_Help macro group. See Create a Toolbar and Menu That Include Only Editing Tasks for information on creating a custom menu. Macro Name |
Action | Arguments |
| A&bout Solutions | OpenForm | Form Name: About Solutions View: Form Data Mode: Read Only Window Mode: Normal |
Tip The Solutions application uses a custom menu only for the Edit Products form, so About Solutions is available from the Help menu only when the Edit Products form is open in Form view. If you use custom menu bars throughout your application, add the A&bout Solutions macro to every Help menu macro group. Cross References For information on |
See | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating forms | User's Guide, Chapter 15, "Designing Forms," Chapter 16, "Customizing Forms," and Chapter 19, "Using Pictures, Graphs, and Other Objects" | |
| Creating macros | User's Guide, Chapter 24, "Macro Basics" | |
|
OnClick Event Procedure for the OK Command Button
Sub OK_Click()
' Close form.
DoCmd Close
End Sub
$#
Highlights of the Startup FormThe Startup form shows how to automatically display a form for a few seconds when you open an application. This technique makes the Startup form work:
$#Create a Startup Form
When your application starts, you might want to display its name, copyright, and logo for a brief time. In Solutions, the Startup form is displayed for several seconds when you first open the database.
Overview
Create an unbound form that displays information about your application. Set the form's TimerInterval property to an interval of time (expressed in milliseconds). Create an event procedure for the OnTimer property to close the form. Create an AutoExec macro to open the form.
Objects Used in this Example
| Object | Name |
| Form | Startup |
| Macro | AutoExec |
Step by Step 1 Design the Startup form. a) Set the following form properties. Property |
Setting |
| DefaultView | Single Form |
| ViewsAllowed | Form |
| ShortcutMenu | No |
| ScrollBars | Neither |
| RecordSelectors | No |
| NavigationButtons | No |
| AutoCenter | Yes |
| BorderStyle | None |
| OnTimer | [Event Procedure] |
| TimerInterval | 7000 |
Note So that you can open the Startup form and look at it in Solutions, the TimerInterval property is set to 7000 when you run the AutoExec macro and reset to 0 in the event procedure of the OnClose property. b) In the detail section, create labels that display your application's name, copyright, and other pertinent information. Also, create an object frame that displays your application's logo. 2 In the Declarations section of the Form.Startup module, type: Option Explicit 3 Create a macro group named AutoExec to automatically display the Startup form. Action |
Argument |
| OpenForm | Form Name: Startup View: Form Data Mode: Read Only Window Mode: Normal |
Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
| Creating forms | User's Guide, Chapter 15, "Designing Forms," Chapter 16, "Customizing Forms," and Chapter 19, "Using Pictures, Graphs, and Other Objects" |
| Creating macros | User's Guide, Chapter 24, "Macro Basics" |
$# Timer Event Procedure for the Startup Form Sub Form_Timer ' Close Startup form. DoCmd Close A_FORM, "Startup" End Sub
|
The Invoice report shows how to create personalized form letters that summarize the sales totals for each salesperson. This technique makes the Invoice report work:
This technique enhances the report:
$#
Create a Report That Prints on a Preprinted FormSome reports require that you print data in a predefined location. Invoice data on a preprinted form, for example, must appear in the appropriate areas of the form. If an invoice requires more than one page, some information, such as the customer's name and address, needs to be printed on every page. Other information, such as the invoice total, needs to be printed only on the last page.
Overview
Place the controls for the customer's name, address, and other recurring data in the page header. Place the controls for the subtotal, freight, and total in the page footer. Create an unbound text box in the Order ID footer. Create an event procedure in the OnFormat property of the group footer that sets the value of the text box to True. Create an event procedure in the OnFormat property of the page footer that prints the page footer only after all the detail records have been printed (that is, when the value in the unbound text box is True).
Objects Used in this Example
| Type | Names | |
| Table | Customers, Employees, Orders | |
| Query | Invoice, Order Details Extended | |
| Report | Invoice | |
Step by Step 1 Import the Order Details Extended query from NWIND.MDB, if you haven't already done so. 2 Create the Invoice query. a) Use the following tables and fields. Table/Query |
Fields | |
| Customers table | Company Name, Address, City, Region, Postal Code, Country | |
| Employees table | Last Name, First Name | |
| Orders table | Order ID, Customer ID, Ship Name, Ship Address, Ship City, Ship Region, Ship Postal Code, Ship Country, Ship Via, Order Date, Required Date, Shipped Date, Freight | |
| Order Details Extended query | Product Name, Product ID, Unit Price, Quantity, Discount, Extended Price | |
b) Set the Criteria for the Order Date field to: Between #2/1/94# and #3/30/94#. Tip In your application, you'll probably want to prompt users for date criteria when they preview or print the report rather than specifying the dates in the query. For an example of setting criteria at run time, see the Employee Sales report. 3 Design the Invoice report. a) Set the following sorting and grouping properties. Field/Expression |
Sort Order | Group Properties |
| Order ID | Descending | Group Header: Yes Group Footer: Yes Group On: Each Value Group Interval: 1 Keep Together: No |
| Product ID | Ascending | (Default settings) |
Note Order ID is sorted in descending order in the Invoice report so that the invoice requiring multiple pages appears first. b) In the Print Setup dialog box, set the Left, Right, Top, and Bottom margins to 1.000 in. c) Set the Height property for the following sections. Section |
Height | |
| Page header | 3.6875 in | |
| Detail section | 0.25 in | |
| Page footer | 1 in | |
The Order ID header and footer are not visible on the printed report, so you don't need to set their Height properties. d) In the page header, create text boxes for the customer's name, address, and any other information that you want to appear on every page. e) In the detail section, create text boxes with their properties set as follows. Name |
ControlSource | |
| Product ID | Product ID | |
| Product Name | Product Name | |
| Quantity | Quantity | |
| Unit Price | Unit Price | |
| Discount | Discount | |
| Extended Price | Extended Price | |
f) In the Order ID footer, create an unbound text box. Set its Name property to Print Page Footer. g) In the Order ID footer, create a text box that calculates the invoice subtotal. Set its Name property to Subtotal and its ControlSource property to: = Sum([Extended Price]) h) Create text boxes in the page footer with their properties set as follows. Name |
ControlSource | |
| Invoice Subtotal | =[Subtotal] | |
| Freight | Freight | |
| Invoice Total | =[Invoice Subtotal]+ [Freight] | |
i) Set properties and create event procedures for the following sections. Section |
Property | Setting |
| Order ID header | Visible | No |
| Order ID footer | ForceNewPage | After Section |
| Visible | No | |
| OnFormat | [Event Procedure] | |
| Page footer | OnFormat | [Event Procedure] |
j) In the Declarations section of the Report.Invoice module, type: Option Explicit Cross References For information on |
See | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating queries | User's Guide, Chapter 10, "Query Basics," and Chapter 11, "Designing Select Queries" | |
| Creating reports | User's Guide, Chapter 20, "Report Basics," and Chapter 21, "Designing Reports" | |
| Setting sorting and grouping properties | User's Guide, Chapter 22, "Sorting and Grouping Data" | |
$# Format Event Procedure for the Order ID Footer Sub GroupFooter3_Format (Cancel As Integer, FormatCount As Integer) ' Reset Print Page Footer text box to show that all detail ' records have been printed for current order. Report will print ' page footer when value of Print Page Footer is True. Me![Print Page Footer] = True End Sub
|
Format Event Procedure for the Page Footer
Sub PageFooter4_Format (Cancel As Integer, FormatCount As Integer)
' Determine whether to print page footer.
'
' Print page footer if all detail records for
' current order have been printed.
If Me![Print Page Footer] Then
Me.PrintSection = True
Me![Print Page Footer] = False
Else
' Don't print page footer if all detail records
' for current order haven't been printed.
Me.PrintSection = False
End If
End Sub
$#
Print "Continued" at the Bottom of a Preprinted FormWhen there are many detail records for a group, you can print "Continued" at the bottom of the page to indicate that more records follow on the next page. For example, in the Invoice report, there is room to print 17 detail records on a page. If the invoice has more than 17 detail records, the report prints 16 detail records and "continued" at the bottom of the first page where detail record 17 would have been printed, and then prints detail record 17 on the next page.
Overview
Determine the maximum number of detail sections that can be printed on one page. When the report formats the last detail section on a page (the 17th detail section for this report), hide the controls in the detail record, print "Continued," and don't advance to the next record.
Objects Used in this Example
| Type | Names | |
| Table | Customers, Employees, Orders | |
| Query | Invoice, Order Details Extended | |
| Report | Invoice | |
Step by Step 1 Create a report for a preprinted form. 2 In the page header, create an unbound text box. Set its Name property to Rows Per Page, its ControlSource property to: = 17, and its Visible property to No. 3 In the Order ID header, create two unbound text boxes. a) Set the Name property of one text box to Row Count, and set its ControlSource property to: =Count([Product ID]) b) Set the Name property of the other text box to Order Page, and leave its ControlSource property blank. Tip On the Invoice report, the Order ID header isn't visible. If you create a report where it is visible, be sure to set the Visible property for these controls to No. 4 Make the following changes in the detail section. a) Create an unbound text box behind the other controls in the center of the detail section. Set its Name property to Continued, its FontItalic property to Yes, and its ControlSource property to: ="Continued on next page..." b) Create an unbound text box at the right edge of the detail section. Set its Name property to Row and its Visible property to No. c) Create a page break control at the bottom of the section. Set its Name property to Detail Page Break. 5 Create event procedures for the following sections. Section |
Property | Setting |
| Page header | OnFormat | [Event Procedure] |
| Order ID header | OnFormat | [Event Procedure] |
| Detail section | OnFormat | [Event Procedure] |
Cross References For information on |
See | |
| Creating controls | User's Guide, Chapter 21, "Designing Reports" | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating expressions | User's Guide, Chapter 23, "Using Expressions in Reports" | |
|
Format Event Procedure for the Page Header
Sub PageHeader0_Format (Cancel As Integer, FormatCount As Integer)
' At top of page, initialize controls in detail section.
'
' Set value of Row text box to 0.
' Set Visible property to show controls in detail record
' and hide Continued and Detail Page Break controls.
Me![Row] = 0
Me![Continued].Visible = False
Me![Detail Page Break].Visible = False
Me![Product ID].Visible = True
Me![Product Name].Visible = True
Me![Quantity].Visible = True
Me![Unit Price].Visible = True
Me![Discount].Visible = True
Me![Extended Price].Visible = True
End Sub
$#
Format Event Procedure for the Detail Section
Sub Detail2_Format (Cancel As Integer, FormatCount As Integer)
' Determine whether to print detail record or "Continued."
'
' Show Continued text box if at maximum number of
' detail records for page.
If (Me![Row] = Me![Order Page] * (Me![Rows Per Page] - 1) + 1) And Me![Row] <> Me![Row Count] Then
Me![Continued].Visible = True
End If
' Show page break and hide controls in detail record.
If Me![Continued].Visible Then
Me![Detail Page Break].Visible = True
Me![Product ID].Visible = False
Me![Product Name].Visible = False
Me![Quantity].Visible = False
Me![Unit Price].Visible = False
Me![Discount].Visible = False
Me![Extended Price].Visible = False
' Increase value in Order Page.
Me.NextRecord = False
Me![Order Page] = Me![Order Page] + 1
' Increase row count if detail record is printed.
Else
Me![Row] = Me![Row] + 1
End If
End Sub
$#
Format Event Procedure for the Order ID Header
Sub GroupHeader1_Format (Cancel As Integer, FormatCount As Integer)
' Set value of Row text box to 1 to mark beginning
' of group for current order.
' Set value of Order Page text box to 1 to show that
' this is first page of order.
Me![Row] = 1
Me![Order Page] = 1
' Hide Continued and Detail Page Break controls.
Me![Continued].Visible = False
Me![Detail Page Break].Visible = False
End Sub
$#
Highlights of the Shipping Log ReportThe Shipping Log report shows how to present data in a snaking-column, or phone-book style, format. This technique makes the Shipping Log report work:
These techniques enhance the report:
$#
Create a Multiple-Column ReportYou can create a phone-book style report by printing data in two or more snaking columns on the page. For example, the Shipping Log report prints its data in two columns.
Overview
Set the report's width to the width of the printing area on the page (the area inside the margins). Set the number and width of columns on the page in the report's Print Setup properties.
Objects Used in this Example
| Object | Names | |
| Table | Orders | |
| Query | Order Subtotals, Shipping Log | |
| Report | Shipping Log | |
Step by Step 1 Import the Order Subtotals query from NWIND.MDB, if you haven't already done so. 2 Create the Shipping Log query. Table |
Field | Criteria |
| Orders table | Shipped Date | Between #2/1/92# And #3/31/92# |
| Order ID | -- | |
| Order Subtotals query | Subtotal | -- |
Tip In your application, you'll probably want to prompt users for date criteria when they preview or print the report rather than specifying the dates in the query. For an example of setting criteria at run time, see the Employee Sales report. 3 Design the Shipping Log report. a) Set the following sorting and grouping properties. Field/Expression |
Sort Order | Group Properties |
| Shipped Date | Descending | Group Header: Yes Group Footer: Yes Group On: Each Value Group Interval: 1 Keep Together: With first detail |
| Order ID | Ascending | (Default settings) |
b) Set the Width property of the report to 9 inches. c) Set the following Print Setup options. Option |
Setting | |
| Orientation | Landscape | |
| Margins | Left: 1.000 in Right: 1.000 in Top: 1.000 in Bottom: 1.000 in |
|
| Items Across | 2 | |
| Column Spacing | 0.75 in | |
| Item Size | Width: 3.25 in Height: 0.2299 in |
|
| Item Layout | Vertical | |
d) Place labels with the following captions in the page header: Shipped Date, Order ID, and Order Amount. e) Place the following text boxes in the following sections of the report, setting their Name and Control Source properties as follows. Section |
Name | ControlSource |
| Shipped Date header | Shipped Date | Shipped Date |
| Detail | Order ID Subtotal |
Order ID Subtotal |
f) Copy the Shipped Date, Order ID, and Order Amount labels in the page header, and paste them to the right of the first set. (The Left property of the second Shipped Date label should be set to 4 in.) Cross References For information on |
See | |
| Creating queries | User's Guide, Chapter 10, "Query Basics," and Chapter 11, "Designing Select Queries" | |
| Creating reports | User's Guide, Chapter 20, "Report Basics," and Chapter 21, "Designing Reports" | |
| Setting sorting and grouping properties | User's Guide, Chapter 22, "Sorting and Grouping Data" | |
|
Microsoft Access prints a group header once at the beginning of a group so that you can easily identify where a group begins. However, if the detail records in a group span several columns or pages, it's helpful to print the group name at the top of each new column or page. For example, in the Shipping Log report, orders are grouped by shipped date. When a group continues onto another column or page, the shipped date is printed again.
Overview
Use the Top property to determine if a detail section is being formatted at the top of a column or page. If it is, show the text box that repeats the group name, hide the other controls in the detail section, and don't advance to the next record.
Objects Used in this Example
| Object | Names | |
| Table | Orders | |
| Query | Order Subtotals, Shipping Log | |
| Report | Shipping Log | |
Step by Step 1 Create the Shipping Log report. 2 Add the following controls to the report. a) In the Shipped Date header, create an unbound text box. Set its Name property to Top1 and its Visible property to No. b) In the detail section, create an unbound text box. Set its Name property to Continued, its FontItalic property to Yes, and its ControlSource property to: =Format([Shipped Date], "dd-mmm-yy") & " continued" 3 Create event procedures for the following sections. Section |
Property | Setting |
| Page header | OnFormat | [Event Procedure] |
| Shipped Date header | OnFormat | [Event Procedure] |
| Detail | OnFormat | [Event Procedure] |
4 In the Declarations section of the Report.Shipping Log module, type: Option Explicit Cross References For information on |
See | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating reports | User's Guide, Chapter 20, "Report Basics," and Chapter 21, "Designing Reports" | |
|
Format Event Procedure for the Page Header
Sub PageHeader1_Format (Cancel As Integer, FormatCount As Integer)
' At top of page, set value of Top1 text box to 0.
Me![Top1] = 0
End Sub
$#
Format Event Procedure for the Shipped Date Header
Sub GroupHeader2_Format (Cancel As Integer, FormatCount As Integer)
' Set value of Top1 text box to value of Top
' property to track position of group header on page.
If Me![Top1] = 0 Then
Me![Top1] = Me.Top
End If
End Sub
$#
Format Event Procedure for the Detail Section
Sub Detail3_Format (Cancel As Integer, FormatCount As Integer)
' Determine if detail section is first section
' formatted after page header. If it is, hide controls
' in detail record and show Continued text box.
'
' Set value of Top1 text box to value of Top property
' to track position of detail section on page.
If Me![Top1] = 0 Then
Me![Top1] = Me.Top
End If
' Show Continued text box and hide text boxes in
' detail record. Don't advance to next record.
If Me.Top = Me![Top1] Then
Me![Continued].Visible = True
Me![Order ID].Visible = False
Me![Subtotal].Visible = False
Me.NextRecord = False
' Hide Continued text box and show text boxes in
' detail record.
Else
Me![Continued].Visible = False
Me![Order ID].Visible = True
Me![Subtotal].Visible = True
End If
End Sub
$#
Hide a Section When It Prints at the Top of a Page or ColumnUsing a blank group footer to separate groups is useful when you want the division between them to be very obvious. However, you don't want a blank group footer to print when it's the first section on a page or in a column. In the Shipping Log report, the Shipped Data footer is a blank section that isn't printed if it falls at the top of a page or column.
Overview
Use the Top property to determine if the group footer is the first section being formatted at the top of a page or column. If it is, don't print the section and don't move to the next printing location on the page.
Objects Used in this Example
| Object | Names |
| Table | Orders |
| Query | Order Subtotals, Shipping Log |
| Report | Shipping Log |
Step by Step 1 Create a multiple-column report. 2 Add the Top1 text box to the report, and create the event procedures for the page header and Shipped Date header as described in Repeat a Group Name at the Top of a Column or Page. 3 Create an event procedure for the OnFormat property of the Shipped Date footer. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
$# Format Event Procedure for the Shipped Date Footer Sub GroupFooter4_Format (Cancel As Integer, FormatCount As Integer) ' Determine if group footer is first section formatted ' at top of page or column. ' ' Don't print group footer if either statement is true. If Me![Top1] = 0 Or Me.Top = Me![Top1] Then Cancel = True End Sub
|
The Sales Letter report is a form letter that summarizes an employee's sales totals. This technique makes the Sales Letter report work:
This technique enhances the report:
$#
Create a Form LetterWhen you need to send the same letter to many people, you can use a report to create a form letter. Using the data in your database, you can personalize the name, address, and other information in each letter. For example, the Sales Letter report prints the same congratulatory message on every letter, but the name, goal, and quarterly totals are specific to each salesperson.
Overview
Create the underlying queries for the report. Place the name, address, and other controls for the main report in its detail section. Create a subreport for the quarterly totals, and add it to the detail section of the main report.
Objects Used in this Example
| Object | Names | |
| Table | Employees, Orders, Sales Goals | |
| Query | Order Subtotals, Sales Letter 1, Sales Letter 2 | |
| Report | Sales Letter, Sales Letter Subreport | |
Step by Step 1 Import the Order Subtotals query from NWIND.MDB, if you haven't already done so. 2 Create Sales Letter 1, a crosstab query. a) Use the following tables and fields. Table/Query |
Field/Expression | Properties |
| Employees table | Employee ID |
Total: Group By Crosstab: Row Heading |
| Name: [Last Name] & ", " & [First Name] | Total: Group By Crosstab: Row Heading |
|
| Orders table | Year: DatePart("yyyy", [Shipped Date]) | Total: Group By Crosstab: Row Heading |
| Expr1: "Qtr" & DatePart("q", [Shipped Date]) | Total: Group By Crosstab: Column Heading |
|
| DatePart("yyyy", [Shipped Date]) | Total: Where Criteria: 1993 |
|
| Order Subtotals query | Subtotal | Total: Sum Crosstab: Value |
Tip In your application, you'll probably want to prompt users for date criteria when they preview or print the report rather than specifying the dates in the query. For an example of setting criteria at run time, see the Employee Sales report. b) Set the query's ColumnHeadings property to the following value: "Qtr1", "Qtr2","Qtr3","Qtr4" 3 Create the Sales Letter 2 query. Table/Query |
Field/Expression | Properties |
| Employees table | Last Name | Sort: Ascending |
| Salesperson: [First Name] & " " & [Last Name] | -- | |
| Employee ID, Address, City, Region, Postal Code, Country | -- | |
| Sales Goals query | Goal | -- |
| Goal Year: DatePart("yyyy", [Sales Goals].[Year]) | Criteria: [Sales Letter 1].[Year] | |
| Sales Letter 1 query | Qtr1, Qtr2, Qtr3, Qtr4 | -- |
4 Create the Sales Letter report. a) Set the report's RecordSource property to Sales Letter 2. b) Remove the page header and footer. (You need only the detail section for this report.) c) In the Sorting and Grouping box, select Last Name in the Field/Expression column and set Sort Order to Ascending. d) Set the ForceNewPage property of the detail section to After Section. e) Create text boxes for the name, address, salutation, and the text of the letter. For the salutation, use an expression such as the following: ="Dear " & [Salesperson] & ":" To concatenate text and data in a paragraph, use an expression such as: ="Congratulations! You met or exceeded your quarterly sales goal of " & Format([Goal], "$#,##0") & " in " & {Goal Year] & ". In the following summary of your sales, each quarter that is within 90% of your goal is circled." If a text box contains one or more paragraphs, make the text box the height of one line of text and set its CanGrow property to Yes. 5 Create the Sales Letter Subreport. a) Set the report's RecordSource property to Sales Letter 2. b) Remove the page header and footer. (You need only the detail section.) c) Create the text boxes for the quarter totals and the goal. Make the text boxes the same width and height, and space them evenly across the detail section. For example, for each text box, set the Width property to 1 inch and the Height property to 0.1979 inch. Leave 0.125 inch between each text box. d) Save the report. 6 Add the subreport to the main report. a) Select the Sales Letter Subreport in the Database window, and drag it to the detail section of the Sales Letter report. b) Delete the label for the subreport control and adjust the size of the subreport control. For example, make the subreport the height of one line of text. c) Verify that the LinkChildFields and LinkMasterFields properties of the Sales Letter Subreport control are set to Employee ID. 7 Adjust the placement of the controls on the main report. For example, make sure that the parts of the letter Cross References For information on |
See | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating queries | User's Guide, Chapter 11, "Designing Select Queries" and Chapter 12, "Advanced Queries" | |
| Creating reports | User's Guide, Chapter 20, "Report Basics," and Chapter 21, "Designing Reports" | |
| Creating subreports | User's Guide, Chapter 22, "Sorting and Grouping Data" | |
| Creating expressions | User's Guide, Chapter 23, "Using Expressions in Reports" | |
| Setting sorting and grouping properties | User's Guide, Chapter 22, "Sorting and Grouping Data" | |
|
You can call attention to specific data by highlighting it in some way. For example, the Sales Letter report highlights a quarter total that is 90 percent or greater than the goal by encircling it.
Overview
Use the Circle method to draw a circle or ellipse around a control that meets criteria you specify.
Objects Used in this Example
| Object | Names |
| Table | Employees, Orders, Sales Goals |
| Query | Order Subtotals, Sales Letter 1, Sales Letter 2 |
| Report | Sales Letter, Sales Letter Subreport |
Step by Step 1 Create the Sales Letter report. 2 In the Sales Letter Subreport, create an event procedure for the OnPrint property of the detail section. 3 In the Declarations section of the Report.Sales Letter Subreport module type: Option Explicit Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
|
Print Event Procedure for the Detail Section
Sub Detail0_Print (Cancel As Integer, PrintCount As Integer)
' Draw ellipse around controls that meet specified criteria.
Dim MyControl As Control, MyReport As Report, MySubreport As Control
Dim PrintCircle As Integer
Dim Aspect As Single, YOffset As Single
Dim EllipseHeight As Integer, EllipseWidth As Integer
Dim XCoord As Single, YCoord As Single
' To yield desired ellipse, set Aspect and YOffset
' according to size and location of text boxes on
' subreport.
Aspect = .23
YOffset = 200
' Determine height and width of ellipse.
EllipseHeight = Me![Goal].Height * 1.1
EllipseWidth = Me![Goal].Width * 1.04
YCoord = (Me![Goal].Top + Me![Goal].Height) \ 2 + YOffset
' Identify controls that can be circled.
Set MyControl = Me![Qtr1]
GoSub PrintCircle
Set MyControl = Me![Qtr2]
GoSub PrintCircle
Set MyControl = Me![Qtr3]
GoSub PrintCircle
Set MyControl = Me![Qtr4]
GoSub PrintCircle
Exit Sub
PrintCircle:
' If MyControl is not null, then determine if value is 90% or
' greater than value in Goal text box.
If Not IsNull(MyControl) Then
PrintCircle = (MyControl >= .9 * Me![Goal])
' Determine coordinates of MyControl and draw ellipse.
If PrintCircle Then
XCoord = MyControl.Left + (MyControl.Width \ 2)
Me.Circle (XCoord, YCoord), EllipseWidth \ 2, , , , Aspect
End If
End If
Return
End Sub
$#Highlights of the Find Customers Form
The Find Customers form shows how to enter search criteria in one or more fields. This technique makes the Find Customers form work:
Finding records based on criteria you enter in the Company, Contact Name, City, and Country fields.
This technique enhances the Find Customers form:
Clearing the search fields and the subform with the Clear button.
$#KKFind Records Based on Criteria You Enter in One or More Fields
When you search for records, the search criteria might vary each time you search. On the Find Customers form, for example, you can find a customer record by entering a value in any one or more of the fields in the form header: Company, Contact Name, City, or Country. Also, you may have only part of the search criteria you needfor example, you may remember only that the name of the company starts with "B." On the Find Customers form, you can enter "B" in the Company field and then look through the records for all the companies that start with "B."
Overview
Create a subform that contains the fields from the Customers table. Create an unbound main form that contains the search fields and a command button. In the OnClick property of the command button, create an event procedure that creates a SELECT statement based on the criteria entered in the search fields and then sets the RecordSource property of the subform to the SELECT statement.
Objects Used in this Example
| Object | Names |
| Table | Customers |
| Form | Find Customers, Find Customers Subform |
| Module | Enable Controls |
Step by Step 1 Create the Enable Controls module, if you haven't already done so. a) In the Declarations section of the module, type: Option Explicit b) Create the EnableControls function. 2 Create the Find Customers form. a) Create an unbound form, and set its properties as follows. Property |
Setting |
| Caption | Find Customers |
| DefaultView | Single Form |
| ViewsAllowed | Form |
| ScrollBars | Neither |
| RecordSelectors | No |
| NavigationButtons | No |
| AutoCenter | Yes |
| OnOpen | [Event Procedure] |
b) Add a form header and footer. Set the Height property of the form footer to 0. c) In the Declarations section of the Form.Find Customers module, type: Option Explicit d) In the General section of the Form.Find Customers module, create the AddToWhere sub and the DisableControl sub. e) In the form header, create four unbound text boxes. Set their properties as follows. Name |
AfterUpdate |
| Look For Company | [Event Procedure] |
| Look For Contact | [Event Procedure] |
| Look For City | [Event Procedure] |
| Look For Country | [Event Procedure] |
f) In the form header, create a command button, and set its properties as follows. Property |
Value |
| Name | Show Customers |
| Caption | Show Customers |
| Default | Yes |
| OnClick | [Event Procedure] |
3 Create the Find Customers Subform. a) Set the following form properties. Property |
Setting |
| RecordSource | SELECT * FROM Customers WHERE False |
| DefaultView | Datasheet |
b) In the detail section, create text boxes for all the fields in the field list. Tip To adjust the width of the columns in Datasheet view, cut the WHERE False statement from the RecordSource property, switch to Datasheet view, adjust column widths, and then save the form. Return to Design view, paste the WHERE False statement, and save the form again. 4 Add the Find Customers Subform to the Find Customers form. a) Select the Find Customers Subform in the Database window, and drag it to the detail section of the Find Customers form. b) Delete the label attached to the subform control. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
| Creating forms | User's Guide, Chapter 15, "Designing Forms," Chapter 16, "Customizing Forms," and Chapter 17, "Creating Forms Based on More Than One Table" |
|
Open Event Procedure for the Find Customers Form
Sub Form_Open (Cancel As Integer)
' Move insertion point to Look For Company text box when form is opened.
Me![Look For Company].SetFocus
End Sub
$#
AddToWhere Sub
Sub AddToWhere (FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)
' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "
End If
' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))
' Increase argument count.
ArgCount = ArgCount + 1
End If
End Sub
$#
DisableControl Sub
Sub DisableControl ()
' If enabled, disable control in detail section after changing search criterion.
Dim Tmp As Variant
If Me![Find Customers Subform].Enabled Then
Tmp = EnableControls("Detail", False)
End If
End Sub
$#
AfterUpdate Event Procedure for the Look For Company Text Box
Sub Look_For_Company_AfterUpdate ()
' Disable control in detail section after changing search criterion.
DisableControl
End Sub
$#
AfterUpdate Event Procedure for the Look For Contact Text Box
Sub Look_For_Contact_AfterUpdate ()
' Disable control in detail section after changing search criterion.
DisableControl
End Sub
$#
AfterUpdate Event Procedure for the Look For City Text Box
Sub Look_For_City_AfterUpdate ()
' Disable control in detail section after changing search criterion.
DisableControl
End Sub
$#
AfterUpdate Event Procedure for the Look For Country Text Box
Sub Look_For_Country_AfterUpdate ()
' Disable control in detail section after changing search criterion.
DisableControl
End Sub
$#
Click Event Procedure for the Show Customers Command Button
Sub Show_Customers_Click ()
' Create a WHERE clause using search criteria entered by user and
' set RecordSource property of Find Customers Subform.
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant
' Initialize argument count.
ArgCount = 0
' Initialize SELECT statement.
MySQL = "SELECT * FROM Customers WHERE "
MyCriteria = ""
' Use values entered in text boxes in form header to create criteria for WHERE clause.
AddToWhere [Look For Company], "[Company Name]", MyCriteria, ArgCount
AddToWhere [Look For Contact], "[Contact Name]", MyCriteria, ArgCount
AddToWhere [Look For City], "[City]", MyCriteria, ArgCount
AddToWhere [Look For Country], "[Country]", MyCriteria, ArgCount
' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If
' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria
' Set RecordSource property of Find Customers Subform.
Me![Find Customers Subform].Form.RecordSource = MyRecordSource
' If no records match criteria, display message.
If Me![Find Customers Subform].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "No records match the criteria you entered.", 48, "No Records Found"
Else
' Enable control in detail section.
Tmp = EnableControls("Detail", True)
' Move insertion point to Find Customers Subform.
Me![Find Customers Subform].SetFocus
End If
End Sub
$#
KClear the Values in Unbound Text Boxes and a SubformOn a form where you can use the values in several fields to search for records, you might want to clear the search fields before you enter your next set of criteria. To avoid confusing the records returned by the previous set of critera with those that might be returned by the current set, you might want to clear the records from the subform. On the Find Customers form, clicking the Clear button clears all the search fields in the form header and clears the records from the subform in the detail section.
Overview
Create the Clear command button in the form header. In the command button's OnClick property, create an event procedure that sets each text box in the form header to null, resets the RecordSource property of the subform, and moves the insertion point to the first text box in the form header.
Objects Used in this Example
| Object | Name |
| Table | Customers |
| Form | Find Customers, Find Customers Subform |
Step by Step 1 Create the Find Customers form. 2 In the form header of the Find Customers form, create a command button, and set its properties as follows. Property |
Value |
| Name | Clear |
| Caption | Clear |
| OnClick | [Event Procedure] |
3 Modify the event procedure in the OnClick property of the Show Customers command button. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
| Creating forms | User's Guide, Chapter 15, "Designing Forms," Chapter 16, "Customizing Forms" |
$# Click Event Procedure for the Clear Command Button Sub Clear_Click () ' Clear controls in form header and remove records from subform. ' Dim MySQL As Sting Dim Tmp As Variant MySQL = "SELECT & FROM Customers WHERE False" ' Clear search text boxes. Me![Look For Company] = Null Me![Look For Contact] = Null Me![Look For City] = Null Me![Look For Country] = Null ' Reset subform's RecordSource property to remove records. Me![Find Customers Subform].Form.Recordsource = MySQL ' Move insertion point to Look For Company text box. Me![Look For Company].SetFocus End Sub
|
Click Event Procedure for the Show Customers Command Button
The text and code you need to add to the event procedure to set the focus to the Clear button are displayed in red.
Sub Show_Customers_Click ()
' Create a WHERE clause using search criteria entered by user and
' set RecordSource property of Find Customers Subform.
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant
' Initialize argument count.
ArgCount = 0
' Initialize SELECT statement.
MySQL = "SELECT * FROM Customers WHERE "
MyCriteria = ""
' Use values entered in text boxes in form header to create criteria for WHERE clause.
AddToWhere [Look For Company], "[Company Name]", MyCriteria, ArgCount
AddToWhere [Look For Contact], "[Contact Name]", MyCriteria, ArgCount
AddToWhere [Look For City], "[City]", MyCriteria, ArgCount
AddToWhere [Look For Country], "[Country]", MyCriteria, ArgCount
' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If
' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria
' Set RecordSource property of Find Customers Subform.
Me![Find Customers Subform].Form.RecordSource = MyRecordSource
' If no records match criteria, display message.
' Move focus to Clear button.
If Me![Find Customers Subform].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "No records match the criteria you entered.", 48, "No Records Found"
Me!Clear.SetFocus
Else
' Enable control in detail section.
Tmp = EnableControls("Detail", True)
' Move insertion point to Find Customers Subform.
Me![Find Customers Subform].SetFocus
End If
End Sub
$#
Highlights of the Solutions FormThe Solutions form shows how to access all the examples in the Solutions sample application. This technique makes the Solutions form work:
This technique enhances the form:
$#Open a Form or Report from a Main Form
If there are many forms and reports in an application, you might want to provide a main form that organizes them by task or job function. The Solutions form is the main form of the Solutions sample application. It groups the examples by category of application development.
Overview
Create an unbound form. Create one list box that lists the categories. Create a second list box that lists the examples that pertain to the selected category. Create a command button. In its OnClick property, create an event procedure that uses the Column property to open the sample form or report selected in the examples list box.
Objects Used in this Example
| Object | Names | ||
| Table | Example Objects, Example Topics, Examples | ||
| Query | Example List, Example Topic List | ||
| Form | Solutions | ||
Step by Step 1 Create the Example Topic List query. Table |
Field (Ascending) | Show | |
| Example Topics | Topic ID Description Sort Order (Ascending) |
Yes Yes No |
|
2 Create the Example List query. Table |
Field (Ascending) | Show | Criteria |
| Examples | Description (Ascending) | Yes | -- |
| Example Objects | Object Name | Yes | -- |
| Object Type | Yes | -- | |
| Topic ID | No | Forms![Solutions]![Select Topic] | |
3 Design the Solutions form. a) Create an unbound form, and set its properties as follows. Property |
Setting | ||
| Caption | Solutions | ||
| DefaultView | Single Form | ||
| ViewsAllowed | Form | ||
| ShortcutMenu | No | ||
| ScrollBars | Neither | ||
| RecordSelectors | No | ||
| NavigationButtons | No | ||
| AutoCenter | Yes | ||
b) Create an unbound list box, and set its properties as follows. Property |
Setting | ||
| Name | Select Topic | ||
| RowSourceType | Table/Query | ||
| RowSource | Example Topic List | ||
| ColumnCount | 2 | ||
| ColumnWidths | 0 in;5 in | ||
| BoundColumn | 1 | ||
| StatusBarText | Select a topic. | ||
| AfterUpdate | [Event Procedure] | ||
c) Create another unbound list box, and set its properties as follows. Property |
Setting | ||
| Name | Select Example | ||
| RowSourceType | Table/Query | ||
| RowSource | Example List | ||
| ColumnCount | 3 | ||
| ColumnWidths | 5 in;0 in;0 in | ||
| BoundColumn | 2 | ||
| StatusBarText | Select an example. | ||
| OnDblClick | [Event Procedure] | ||
d) Create a command button, and set its properties as follows. Property |
Setting | ||
| Name | OK | ||
| Caption | OK | ||
| Default | Yes | ||
| OnClick | [Event Procedure] | ||
Cross References For information on |
See | ||
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | ||
| Creating forms | User's Guide, Chapter 15, "Designing Forms," and Chapter 16, "Customizing Forms" | ||
| Creating queries | User's Guide, Chapter 11, "Designing Select Queries" | ||
|
AfterUpdate Event Procedure of the Select Topic List Box
Sub Select_Topic_AfterUpdate ()
' Requery Select Example list box.
Me![Select Example].Requery
End Sub
$#
DblClick Event Procedure of the Select Example List Box
Sub Select_Example_DblClick (Cancel As Integer)
' Run event procedure in OK_Click sub.
OK_Click
End Sub
$#
Click Event Procedure of the OK Command Button
Sub OK_Click ()
' Open form or report for selected example.
'
' Open form for selected example.
If Me![Select Example].Column(2) = "Form" Then
DoCmd OpenForm Forms![Solutions]![Select Example].Column(1), , , , 0
' Open report for selected example.
ElseIf Me![Select Example].Column(2) = "Report" Then
DoCmd OpenReport Forms![Solutions]![Select Example].Column(1), A_PREVIEW
End If
End Sub
$#
KClose All Database Objects When You Exit an ApplicationWhen you exit your application, you can have all open database objects close automatically. If you have made any changes to any objects, you can have your application prompt you to either save or discard the changes. For example, when you click the Exit button on the Solutions form, Solutions attempts to close all open objects. If you have changed the design of an object, Microsoft Access prompts you either to save or discard the changes or to cancel. If you click Cancel, the Solutions form displays a message telling you that you must save or discard changes.
Overview
Create a function that closes any open objects. Add a command button to the form. Create an event procedure in the OnClick property that closes each type of database object and then closes the database.
Objects Used in this Example
| Object | Name |
| Form | Solutions |
Step by Step 1 In the General section of the Form.Solutions module, create the CloseObject function. 2 In the Declarations section of the Form.Solutions module, type: Option Explicit 3 Add a command button to the Solutions form and set its properties as follows. Property |
Setting |
| Name | Exit |
| Caption | Exit |
| OnClick | [Event Procedure] |
Note The Click event procedure for the Exit command button does not work when you discard changes to a module. When you choose not to save changes to a module, Microsoft Access closes the module window but halts the Exit command button's Click event procedure, and you must close the database yourself. Cross References For information on |
See |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" |
| Creating forms | Chapter 16, "Customizing Forms" |
$# CloseObject() Function Function CloseObject (ContainerName As String, ContainerType As Integer) ' Close open database objects. Dim MyDatabase As Database, MyContainer As Container Dim I As Integer Set MyDatabase = DBEngine.Workspaces(0).Databases(0) Set MyContainer = MyDatabase.Containers(ContainerName) For I = 0 To MyContainer.Documents.Count - 1 DoCmd Close ContainerType, MyContainer.Documents(I).Name Next I End Function
|
Click Event Procedure of the Exit Command Button
Sub Exit_Click ()
' Close all open database objects, and then exit application.
'
' Turn on error trapping.
On Error GoTo ErrHandler
Dim ReturnValue As Integer
Dim Tmp As Variant
Tmp = CloseObject("Tables", A_TABLE)
Tmp = CloseObject("Tables", A_QUERY)
Tmp = CloseObject("Forms", A_FORM)
Tmp = CloseObject("Reports", A_REPORT)
Tmp = CloseObject("Scripts", A_MACRO)
Tmp = CloseObject("Modules", A_MODULE)
' Exit application.
DoCmd DoMenuItem 1, 0, 2, , A_MENU_VER2
Exit Sub
' Branch here if error occurs.
ErrHandler:
MsgBox "You must either save or discard changes to the open object.", 48, "Must Close Object"
Resume Next
End Sub
$#
Highlights of the Sales Totals FormThe Sales Totals form shows how to display progressively more detail about an employee's sales. This technique makes the Sales Totals form work:
$#
KKKCreate a One-to-Many-to-Many FormWhen you look at data, you often want to see a top-level view as well as a detailed view. On the Sales Totals form, you can review the yearly sales totals for the selected salesperson and then review his or her quarterly sales totals for the selected year.
Overview
Create one subform that displays yearly totals and a second subform that displays quarterly totals. Create a main form bound to the Employees table. In the form header of the main form, create an unbound combo box from which you find records for the selected employee. In the detail section of the main form, create an unbound text box that links the second subform to the first subform. Set the ControlSource property of the text box to an expression that displays the value from T-Year, the linking field on the first subform. Set both the LinkChildFields and LinkMasterFields properties of the second subform to the fields that identify the employee and the year
Objects Used in this Example
| Object | Names | ||
| Table | Employees, Orders, Order Details | ||
| Query | Sales Totals (Qtr), Sales Totals (Year), Salespeople | ||
| Form | Sales Totals, Sales Totals Subform 1, Sales Totals Subform 2 | ||
| Module | Enable Controls | ||
Step by Step 1 Create the Salespeople query. Table |
Field/Expression | Sort | |
| Employees | Salesperson: [Last Name] &", " & [First Name] | Ascending | |
| Employee ID | -- | ||
| Orders | -- | -- | |
Note You don't use any fields from the Orders table in this query; you use the Orders table only to limit the employee names returned to those with records in the Orders table. 2 Create the Sales Totals (Year) query. Table |
Field/Expression | Total | Criteria |
| Orders | Employee ID | Group By | -- |
| T-Year: Year([Shipped Date]) | Group By | Is Not Null | |
| Order Details | Total: CCur(Sum(CLng([Unit Price]*[Quantity]*(1-[Discount])*100)/100)) | Expression | -- |
3 Create the Sales Totals (Qtr) query. Table |
Field/Expression | Total | Criteria |
| Orders | Employee ID | Group By | -- |
| T-Year: Year([Shipped Date]) | Group By | Is Not Null | |
| Quarter: DatePart("q",[Shipped Date]) | Group By | Is Not Null | |
| Order Details | Subtotal: CCur(Sum(CLng([Unit Price]*[Quantity]*(1-[Discount])*100)/100)) | Expression | -- |
4 Design Sales Totals Subform 1. a) Set the following form properties. Property |
Setting | ||
| RecordSource | Sales Totals (Year) | ||
| DefaultView | Datasheet | ||
b) Create two text boxes in the detail section, and set their Name and ControlSource properties as follows. Name |
ControlSource | ||
| T-Year | T-Year | ||
| Total | Total | ||
c) Switch to Datasheet view. Adjust the width of the columns, and save the form. 5 Design Sales Totals Subform 2. a) Set the following form properties. Property |
Setting | ||
| RecordSource | Sales Totals (Qtr) | ||
| DefaultView | Datasheet | ||
b) Create two text boxes in the detail section, and set their Name and ControlSource properties as follows. Name |
ControlSource | ||
| Quarter | Quarter | ||
| Subtotal | Subtotal | ||
c) Switch to Datasheet view. Adjust the width of the columns, and save the form. 6 Create the Enable Controls module, if you haven't already done so. a) In the Declarations section, type: Option Explicit b) Create the EnableControls function. 7 Design the Sales Totals form. a) Set the following form properties. Property |
Setting | ||
| RecordSource | Employees | ||
| DefaultView | Single Form | ||
| ViewsAllowed | Form | ||
| ShortcutMenu | No | ||
| ScrollBars | Neither | ||
| RecordSelectors | No | ||
| NavigationButtons | No | ||
| AutoCenter | Yes | ||
b) Add a form header and footer. Set the Height property of the form footer to 0. c) In the form header, create an unbound combo box, and set its properties as follows. Property |
Setting | ||
| Name | Select Salesperson | ||
| RowSourceType | Table/Query | ||
| RowSource | Salespeople | ||
| ColumnCount | 2 | ||
| ColumnWidths | 2 in;0 in | ||
| BoundColumn | 2 | ||
| LimitToList | Yes | ||
| AfterUpdate | [Event Procedure] | ||
d) In the Declarations section of the Form.Sales Totals module, type: Option Explicit e) In the detail section, create a text box, and detach its label. Then set the properties of the text box as follows. Property |
Setting | ||
| Name | Salesperson | ||
| ControlSource | =[Last Name] &", "&[First Name] | ||
| Enabled | No | ||
| TabStop | No | ||
| BackColor | 8388608 (dark blue) | ||
| BorderStyle | Clear | ||
| ForeColor | 16777215 (white) | ||
f) In the detail section, create a hidden text box with its properties set as follows. Property |
Setting | ||
| Name | T-Year | ||
| ControlSource | =[Sales Totals Subform 1].Form![T-Year] | ||
| Visible | No | ||
| TabStop | No | ||
|
Note Microsoft Access uses the value in this text box to link the values in Sales Totals Subform 2 to the selected row in Sales Totals Subform 1. 8 Add the Sales Totals Subform 1 to the Sales Totals form. a) Select the Sales Totals Subform 1 in the Database window, and drag it to the detail section of the Sales Totals form. b) Change the label for the subform control to Select Year: and adjust the size of the subform control. c) Verify that the LinkChildFields and LinkMasterFields properties of the Sales Totals Subform 1 control are both set to Employee ID. d) Set the Enabled property of the Sales Totals Subform 1 control to No. 9 Add the Sales Totals Subform 2 to the Sales Totals form. a) Select the Sales Totals Subform 2 in the Database window, and drag it to the detail section of the Sales Totals form. b) Change the label for the subform control to Quarter Totals for Selected Year: and adjust the size of the subform control. c) Set both the LinkChildFields and LinkMasterFields properties of the Sales Totals Subform 2 control to: T-Year;Employee ID d) Set the Enabled property of the Sales Totals Subform 2 control to No. Cross References For information on |
See | ||
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | ||
| Creating forms | User's Guide, Chapter 15, "Designing Forms," Chapter 16, "Customizing Forms," and Chapter 17, "Creating Forms Based on More Than One Table" | ||
| Creating queries | User's Guide, Chapter 11, "Designing Select Queries" | ||
|
AfterUpdate Event Procedure for the Select Salesperson Combo Box
Sub Select_Salesperson_AfterUpdate ()
' Find records for salesperson selected in Salesperson combo box.
' Enable controls in detail section.
' Go to Sales Totals Subform 1.
Dim Tmp As Variant
DoCmd ApplyFilter , "[Employee ID] = Forms![Sales Totals]![Select Salesperson]"
Tmp = EnableControls("Detail", True)
Me![Sales Totals Subform 1].SetFocus
End Sub
$#
Highlights of the Show Sales FormShow Sales is a query by form that shows how to use text boxes and an option group to specify criteria. This technique makes the Show Sales form work:
$#KKKKKKKKKKKKKKUse a Custom Function in a Form's SELECT Statement (SQL)
On a query by form, you might want users to choose one option from a specific set of criteria. On the Show Sales form, you can specify a date range and whether you want to see all records in the date range or records in the date range where the sales are less than or greater than 1000.
Overview
Create a query by form where you enter criteria in text boxes and an option group in the form header of the main form, and display records in a subform in the detail section. Create a function that returns the value selected in the option group. Create a command button in the form header of the main form. In the OnClick property of the command button, create an event procedure that creates an SQL SELECT statement that uses the custom function to return the user's criteria and sets the RecordSource property of the subform to the SELECT statement.
Objects Used in this Example
| Object | Names | |
| Table | Customers, Orders | |
| Query | Order Subtotals, Show Sales | |
| Form | Show Sales, Show Sales Subform | |
| Module | Enable Controls | |
Step by Step 1 Import the Order Subtotals query from NWIND.MDB, if you haven't already done so. 2 Create the Show Sales query. Table/Query |
Fields | Sort |
| Customers | Company Name | -- |
| Orders | Shipped Date | -- |
| Order Subtotals | Order ID | -- |
| Subtotal | Descending | |
3 Create the Show Sales Subform. a) Set the following form properties. Property |
Setting | |
| RecordSource | Show Sales | |
| DefaultView | Datasheet | |
| ViewsAllowed | Datasheet | |
| ShortcutMenu | No | |
b) Create text boxes for the following fields in the detail section: Company Name, Order ID, and Subtotal. c) Switch to Datasheet view. Adjust the width of the columns, and save the form. d) Reset the form's RecordSource property to the following SQL statement: SELECT [Company Name] FROM Customers WHERE False Note Setting the RecordSource property to this SQL statement will display the subform as an empty datasheet when the Show Sales form is opened. 4 Create the Show Sales form. a) Set the following form properties. Property |
Setting | |
| RecordSource | -- | |
| Caption | Show Sales | |
| DefaultView | Single Form | |
| ViewsAllowed | Form | |
| ShortcutMenu | No | |
| ScrollBars | Neither | |
| RecordSelectors | No | |
| NavigationButtons | No | |
| AutoCenter | Yes | |
| OnOpen | [Event Procedure] | |
b) Select the Show Sales Subform in the Database window, and drag it to the detail section of the Show Sales form. Adjust the size and location of the subform control, and remove its attached label. (Leave the LinkChildFields and LinkMasterFields properties blank.) c) Add a form header and footer. Set the Height property of the form footer to 0. d) In the form header, create two unbound text boxes, and set their Name and Format properties as follows. Name |
Format | |
| Beginning Date | Medium Date | |
| Ending Date | Medium Date | |
e) Type the name of each text box in the Caption property of its attached label. f) In the form header, create an unbound option group, and set its Name property to Sales and its DefaultValue property to 1. g) Type Sales in the Caption property of the option group's attached label. h) Create three option buttons in the Sales option group, and set their Name and OptionValue properties as follows. Name |
OptionValue | |
| Less Than 1000 | 1 | |
| Greater Than 1000 | 2 | |
| All | 3 | |
i) Type the name of each option button in the Caption property of its attached label. 5 Modify the Show Sales query. a) Specify the following criteria for the Shipped Date field: Between Forms![Show Sales]![Beginning Date] And Forms![Show Sales]![Ending Date] b) For the Subtotal field, specify Descending sort order. Tip You can copy the SQL statement from the query's SQL window to use in the event procedure for the command button you create in step 8. 6 In the General section of the Form.Show Sales module, create the ShowSales function. 7 In the Declarations section of the Form.Show Sales module, type: Option Explicit 8 Create the Enable Controls module, if you haven't already done so. a) In the Declarations section of the module, type: Option Explicit b) Create the EnablesControls function. 9 In the form header of the Show Sales form, create a command button, and set its properties as follows. Property |
Setting | |
| Name | Show Sales | |
| Caption | Show Sales | |
| Default | Yes | |
| OnClick | [Event Procedure] | |
Cross References For information on |
See | |
| Creating event procedures | Building Applications, Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals" | |
| Creating forms | User's Guide, Chapter 15, "Designing Forms," and Chapter 16, "Customizing Forms" | |
| Creating queries | User's Guide, Chapter 10, "Query Basics," and Chapter 11, Designing Select Queries" | |
|
Open Event Procedure for the Show Sales Form
Sub Form_Open (Cancel As Integer)
' Move insertion point to Beginning Date field.
Me![Beginning Date].SetFocus
End Sub
$#
ShowSales() Function
Function ShowSales (OptionGroupValue As Long) As String
' Return value selected in Sales option group.
'
' Define constants for option group values.
Const SALES_UNDER_1000 = 1
Const SALES_OVER_1000 = 2
Const ALL_SALES = 3
' Create restriction based on value of option group.
Select Case OptionGroupValue
Case SALES_UNDER_1000:
ShowSales = "[Order Subtotals].Subtotal < 1000"
Case SALES_OVER_1000:
ShowSales = "[Order Subtotals].Subtotal >= 1000"
Case Else
ShowSales = "[Order Subtotals].Subtotal = True"
End Select
End Function
$#
Click Event Procedure for the Show Sales Command Button
Sub Show_Sales_Click ()
' Create an SQL statement that uses search criteria entered by user and
' set RecordSource property of Show Sales Subform.
Dim MySQL As String, Restrict As String
Dim x As Long
Dim Tmp As Variant
x = Forms![Show Sales]![Sales].Value
Restrict = ShowSales(x)
' Create SELECT statement.
MySQL = "SELECT DISTINCTROW Customers.[Company Name], [Order Subtotals].[Order ID], "
MySQL = MySQL & "[Order Subtotals].Subtotal , Orders.[Shipped Date]"
MySQL = MySQL & "FROM Customers INNER JOIN ([Order Subtotals] INNER JOIN Orders ON "
MySQL = MySQL & "[Order Subtotals].[Order ID] = Orders.[Order ID]) ON "
MySQL = MySQL & "Customers.[Customer ID] = Orders.[Customer ID] "
MySQL = MySQL & "WHERE (Orders.[Shipped Date] Between [Forms]![Show Sales]![Beginning Date] "
MySQL = MySQL & "And [Forms]![Show Sales]![Ending Date]) "
MySQL = MySQL & "And " & Restrict
MySQL = MySQL & " ORDER BY [Order Subtotals].Subtotal DESC;"
' Set RecordSource property of Show Sales Subform.
Me![Show Sales Subform].Form.RecordSource = MySQL
' If no records match criteria, reset subform's RecordSource property,
' display message, and move focus to Beginning Date text box.
If Me![Show Sales Subform].Form.RecordsetClone.RecordCount = 0 Then
Me![Show Sales Subform].Form.RecordSource = "SELECT [Company Name] From Customers WHERE False"
MsgBox "No records match the criteria you entered.", 48, "No Records Found"
Me![Beginning Date].SetFocus
Else
' Enable control in detail section.
Tmp = EnableControls("Detail", True)
' Move insertion point to Show Sales Subform.
Me![Show Sales Subform].SetFocus
End If
End Sub