Highlights of the EditProducts Form

The EditProducts form shows how to create a form in which you can edit existing records but not enter new ones. These techniques make the EditProducts form work:

Limiting the values in the SelectProduct combo box based on the value you pick in the SelectCategory combo box.

Finding the record for the product you pick in the SelectProduct combo box.

Updating the list in the SelectProduct combo box after you move to another record.

Creating a toolbar and menu with buttons and commands limited to editing tasks.

This technique enhances the EditProducts form:

Changing the color of the UnitsInStock text box and displaying a message when the number of units is lower than the reorder level.


Limit the Contents of One List Based on the Value Selected in Another

If 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 SelectProduct list on the EditProducts form displays only those products in the category you pick in the SelectCategory list.

Overview

Create an event procedure for the AfterUpdate property of the SelectCategory combo box that uses the Requery method to requery the SelectProduct combo box. Include the CategoryID field in the underlying query for the SelectProduct combo box. Set the criteria for the field to an expression that limits the list to those products whose CategoryID matches the CategoryID of the value in the SelectCategory combo box.

Objects Used in This Example

Type	Name
Table	Categories, Products
Query	CategoryList, LimitProductList
Form	EditProducts
Module	EnableControlsModule
Step by Step

1	Create the CategoryList query.

Table	Field
Categories	CategoryName (Ascending)
	CategoryID
2	Create the LimitProductList query.

a)	Use the following fields from the Products table.

Field	Sort
ProductName	Ascending
ProductID	--
CategoryID	--
b)	In the Criteria cell of the CategoryID field, type:

Forms![EditProducts]![SelectCategory]

3	Create the EnableControlsModule module, if you haven't already done so.

Create the EnableControls function.

4	Create the EditProducts form.

a)	Set the following form properties.

Property	Value
RecordSource	Products
Caption	Edit Products
DefaultView	Single Form
ViewsAllowed	Form
AllowEdits	Yes
AllowDeletions	Yes
AllowAdditions	No
DataEntry	No
ScrollBars	Neither
RecordSelectors	No
NavigationButtons	No
AutoCenter	Yes
ShortcutMenu	No
b)	In the detail section, add the fields from the Products table. Use combo boxes for the SupplierID and CategoryID fields, a check box for the Discontinued field, and 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)	Add a form header and footer. Set the Height property of the form footer to 0".

e)	Create the SelectCategory combo box in the form header and set its properties as follows.

Property	Value
Name	SelectCategory
RowSourceType	Table/Query
RowSource	CategoryList
ColumnCount	2
ColumnWidths	;0"
BoundColumn	2
LimitToList	Yes
AfterUpdate	[Event Procedure]
f)	Create the SelectProduct combo box in the form header. Separate the attached label by cutting and pasting it (so that it doesn't flash when SelectProduct is enabled and disabled). Set the properties of the SelectProduct combo box as follows.

Property	Value
Name	SelectProduct
RowSourceType	Table/Query
RowSource	LimitProductList
ColumnCount	2
ColumnWidths	;0"
BoundColumn	2
LimitToList	Yes
Enabled	No
Tip   On an edit-only form, you can prevent users from inadvertently making changes to the wrong record. Disable the controls in the detail section while they enter search criteria and enable the controls only after they find the record.

Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with forms	Chapter 3, "Using Forms to Collect, Display, and Filter Information"


EnableControls Function

Function EnableControls(strWhichSection As String, intState As Integer) As Integer

'  Enable or disable controls in specified section of form.

Dim frm As Form

Dim ctl As Control

Dim intX As Integer, intSelectedSection As Integer

'  Use active form. If no form is active, exit

'  function without displaying error message.

On Error Resume Next

Set frm = Screen.ActiveForm

If Err Then

EnableControls = False

On Error GoTo 0

Exit Function

End If

'  Define valid values for strWhichSection.

Select Case UCase$(strWhichSection)

Case "FORM HEADER"

intSelectedSection = 1

Case "PAGE HEADER"

intSelectedSection = 3

Case "DETAIL"

intSelectedSection = 0

Case "PAGE FOOTER"

intSelectedSection = 4

Case "FORM FOOTER"

intSelectedSection = 2

Case Else

MsgBox "Invalid argument", , "EnableControls"

EnableControls = False

Exit Function

End Select

'  Set intState for all controls in specified section.

For intX = 0 To frm.Count - 1

Set ctl = frm(intX)

If ctl.Section = intSelectedSection Then

On Error Resume Next

ctl.Enabled = intState

On Error GoTo 0

End If

Next intX

EnableControls = True

End Function


AfterUpdate Event Procedure for the SelectCategory Combo Box

Private Sub SelectCategory_AfterUpdate()

'  Enable and requery SelectProduct combo box.

'  Disable controls in detail section.

Dim varTmp As Variant

Me!SelectProduct.Enabled = True

Me!SelectProduct.Requery

varTmp = EnableControls("Detail", False)

End Sub


Find a Record Based on a Value You Select from a List

A fast way to find a record is to select a value from a list. For example, the EditProducts form finds the record for the product you select in the SelectProduct combo box.

Tip   On an edit-only form, you can prevent users from inadvertently making changes to the wrong record. Disable the controls in the detail section while they enter search criteria and enable the controls only after they find the record they want to edit.

Overview

For the AfterUpdate property of the SelectProduct combo box, create an event procedure 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	EditProducts
Module	EnableControlsModule
Step by Step

1	Create the SelectCategory and SelectProduct combo boxes. Creating these combo boxes is explained in the topic Limit the Contents of One List Based on the Value Selected in Another.

2	Create the EnableControlsModule module, if you haven't already done so.

Create the EnableControls function.

3	Create an event procedure for the AfterUpdate property of the SelectProduct combo box.

Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with forms	Chapter 3, "Using Forms to Collect, Display, and Filter Information"


AfterUpdate Event Procedure for the SelectProduct Combo Box

Private Sub SelectProduct_AfterUpdate()

'  Find record for product selected in SelectProduct combo box.

'  Enable controls in detail section and disable ProductID text box.

'  Go to SupplierID combo box.

Dim varTmp As Variant

DoCmd.ApplyFilter , "ProductID = Forms!EditProducts!SelectProduct"

varTmp = EnableControls("Detail", True)

Me!ProductID.Enabled = False

Me!SupplierID.SetFocus

End Sub


Requery a List After You Move to Another Record

When 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 change a record on the EditProducts form, the SelectProduct combo box is requeried.

Overview

For the AfterUpdate property of the form, create an event procedure that uses the Requery method to requery the SelectProduct combo box.

Object Used in This Example

Type	Name
Form	EditProducts
Step by Step

Create an event procedure in the AfterUpdate property of the EditProducts form.

Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


AfterUpdate Event Procedure for the EditProducts Form

Private Sub Form_AfterUpdate()

'  Requery SelectProduct combo box.

Me!SelectProduct.Requery

End Sub


Create a Toolbar and Menu That Include Only Editing Tasks

You can control the kinds of tasks that users perform on a form by creating a custom toolbar and menu. For example, on the EditProducts form, the custom toolbar and menu only include buttons and commands for editing tasks.

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	EditProducts
Macro	EditProductsMenuBar, EditProductsMenuBar_File, EditProductsMenuBar_Edit
Step by Step

1	Create the EditProducts form. Creating this form is explained in the topic Limit the Contents of One List Based on the Value Selected in Another.

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
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 EditProductsMenuBar.  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 Cut Copy Paste Delete Delete Record Select Record
Note   The custom menu bar in the EditProducts form also includes the View, Tools, Window, and Help menus so that you can switch easily between views and use the regular features of the Form View menu bar.

b)	Set the form's MenuBar property to the macro group, EditProductsMenuBar.

Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating menus and toolbars	Chapter 1, "Application Basics"
Creating event procedures	Chapter 2, "Introducing Visual Basic"


Activate Event Procedure for the EditProducts Form

Private Sub Form_Activate()

'  Used by Solutions to display toolbar that includes

'  Show Me button.

'  Hide built-in Form View toolbar.

'  Show Edit Products Toolbar.

DoCmd.ShowToolbar "Form View", acToolbarNo

DoCmd.ShowToolbar "Edit Products Toolbar", acToolbarYes

End Sub


Deactivate Event Procedure for the EditProducts Form

Private Sub Form_Deactivate()

'  Used by Solutions to hide toolbar that includes

'  Show Me button.

'  Hide Edit Products Toolbar.

'  Show built-in Form View toolbar.

DoCmd.ShowToolbar "Edit Products Toolbar", acToolbarNo

DoCmd.ShowToolbar "Form View", acToolbarWhereApprop

End Sub


Change the Color of a Control and Display a Message Based on the Control's Value

When the value in a control meets certain criteria, you can flag it by changing its color and displaying a message beside it. On the EditProducts form, for example, when the units in stock fall below the reorder level and there are no units on order, the text of the UnitsInStock 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 event procedure in the AfterUpdate properties of the UnitsInStock, UnitsOnOrder, and ReorderLevel controls.

Object Used in This Example

Type	Name
Form	EditProducts
Step by Step

1	Create a label beside the UnitsInStock text box, and set its properties as follows.

Property	Setting
Name	LLowStock
Caption	Units in stock are below the reorder level.
Visible	No
2	Create event procedures for the form and controls as follows.

Form/Control	Property	Setting
EditProducts form	OnCurrent	[Event Procedure]
UnitsInStock text box	AfterUpdate	[Event Procedure]
UnitsOnOrder text box	AfterUpdate	[Event Procedure]
ReorderLevel text box	AfterUpdate	[Event Procedure]
Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with forms	Chapter 3, "Using Forms to Collect, Display, and Filter Information"


Current Event Procedure for the EditProducts Form

Private Sub Form_Current()

'  If value in UnitsInStock text box is less than value in

'  ReorderLevel

'  text box and value in UnitsOnOrder text box is 0, display

'  value in UnitsInStock in red and display LLowStock label.

If Me!UnitsInStock <= Me!ReorderLevel And Me!UnitsOnOrder = 0 Then

Me!UnitsInStock.ForeColor = 128

Me!LLowStock.Visible = True

'  Otherwise, display value in UnitsInStock text box in black

'  and hide LLowStock label.

Else

Me!UnitsInStock.ForeColor = 0

Me!LLowStock.Visible = False

End If

End Sub


AfterUpdate Event Procedure for the UnitsInStock Text Box

Private Sub UnitsInStock_AfterUpdate()

'  Run event procedure in Form_Current sub.

Form_Current

End Sub


AfterUpdate Event Procedure for the UnitsOnOrder Text Box

Private Sub UnitsOnOrder_AfterUpdate()

'  Run event procedure in Form_Current sub.

Form_Current

End Sub


AfterUpdate Event Procedure for the ReorderLevel Text Box

Private Sub ReorderLevel_AfterUpdate()

'  Run event procedure in Form_Current sub.

Form_Current

End Sub


Highlights of the EmployeeSales Report

The EmployeeSales report shows how to create a crosstab report with dynamic column headings. These techniques make the EmployeeSales report work:

Creating a crosstab report with dynamic column headings.

Setting report criteria in a dialog box.

This technique enhances the report:

Printing the criteria you specified in the dialog box on the report.


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, since not every employee in the EmployeeSales report has sales each week, the report prints columns only for 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	Name
Table	Employees, Orders, Products
Query	EmployeeSales, OrderDetailsExtended
Report	EmployeeSales
Step by Step

1	Import the OrderDetailsExtended query from the Northwind sample database, if you haven't already done so.

2	Create the EmployeeSales crosstab query.

Table/Query	Field	Setting
Employees table	LastName	Total: Group By Crosstab: Column Heading
Products table	ProductName	Total: Group By Crosstab: Row Heading
OrderDetailsExtended query	Order Amount: ExtendedPrice	Total: Sum Crosstab: Value
Orders table	ShippedDate	Total: Where Criteria: Between #1/2/95# And #1/16/95#
Note   This query uses a sample time period between January 2, 1995 and January 16, 1995, but you'll probably want to prompt users for date criteria. If you look at the criteria for the ShippedDate field in the EmployeeSales query, you'll see that it references controls on the EmployeeSalesDialogBox form. The steps to modify the query are covered in Set Report Criteria in a Dialog Box.

3	Create the EmployeeSales report.

a)	Set the report's RecordSource property to EmployeeSales.

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. In its ControlSource property box, type ="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_EmployeeSales module, declare the variables, and create the InitVars Sub procedure and the xtabCNulls function.

g)	Create event procedures and set properties as described in the following table.

Report/Section	Property	Setting
Report	OnOpen	[Event Procedure]
	OnClose	[Event Procedure]
	OnNoData	[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-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


Declarations Section of the Report_EmployeeSales Module

Option Compare Database   'Use database order for string comparisons.

Option Explicit

'  Constant for maximum number of columns EmployeeSales query would

'  create plus 1 for a Totals column.

Const conTotalColumns = 11

'  Variables for database object and recordset.

Dim dbsReport As Database

Dim rstReport As Recordset

'  Variables for number of columns and row and report totals.

Dim intColumnCount As Integer

Dim lngRgColumnTotal(1 To conTotalColumns) As Long

Dim lngReportTotal As Long


InitVars Sub Procedure

Private Sub InitVars()

Dim intX As Integer

' Initialize lngReportTotal variable.

lngReportTotal = 0

' Initialize array that stores column totals.

For intX = 1 To conTotalColumns

lngRgColumnTotal(intX) = 0

Next intX

End Sub


xtabCnulls Function

Private Function xtabCnulls(varX As Variant)

' Test if a value is null.

If IsNull(varX) Then

' If varX is null, set varX to 0.

xtabCnulls = 0

Else

' Otherwise, return varX.

xtabCnulls = varX

End If

End Function


Open Event Procedure for the EmployeeSales Report

Private Sub Report_Open(Cancel As Integer)

'  Create underlying recordset for report.

Dim intX As Integer

Dim qdf As QueryDef

'  Set database variable to current database.

Set dbsReport = CurrentDb()

'  Open QueryDef.

Set qdf = dbsReport.QueryDefs("EmployeeSales")

'  Open Recordset.

Set rstReport = qdf.OpenRecordset()

'  Set a variable to hold number of columns in crosstab query.

intColumnCount = rstReport.Fields.Count

End Sub


Close Event Procedure for the EmployeeSales Report

Private Sub Report_Close()

On Error Resume Next

'  Close recordset.

rstReport.Close

End Sub


NoData Event Procedure for the EmployeeSales Report

Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the criteria you entered.", 48, _

"No Records Found"

rstReport.Close

Cancel = True

End Sub


Format Event Procedure for the Report Header

Private 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 a report from Print Preview window, or when you return

'  to a previous page while previewing.)

rstReport.MoveFirst

'Initialize variables.

InitVars

End Sub


Format Event Procedure for the Page Header

Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)

Dim intX As Integer

'  Put column headings into text boxes in page header.

For intX = 1 To intColumnCount

Me("Head" + Format$(intX)) = rstReport(intX - 1).Name

Next intX

'  Make next available text box Totals heading.

Me("Head" + Format$(intColumnCount + 1)) = "Totals"

'  Hide unused text boxes in page header.

For intX = (intColumnCount + 2) To conTotalColumns

Me("Head" + Format$(intX)).Visible = False

Next intX

End Sub


Format Event Procedure for the Detail Section

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)

'  Place values in text boxes and hide unused text boxes.

Dim intX As Integer

'  Verify that not at end of recordset.

If Not rstReport.EOF Then

'  If FormatCount is 1, place values from recordset into

'  text boxes in detail section.

If Me.FormatCount = 1 Then

For intX = 1 To intColumnCount

'  Convert null values to 0.

Me("Col" + Format$(intX)) = xtabCnulls(rstReport(intX - 1))

Next intX

'  Hide unused text boxes in detail section.

For intX = intColumnCount + 2 To conTotalColumns

Me("Col" + Format$(intX)).Visible = False

Next intX

'  Move to next record in recordset.

rstReport.MoveNext

End If

End If

End Sub


Print Event Procedure for the Detail Section

Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer

Dim lngRowTotal As Long

'  If PrintCount is 1, initialize rowTotal variable.

'  Add to column totals.

If Me.PrintCount = 1 Then

lngRowTotal = 0

For intX = 2 To intColumnCount

'  Starting at column 2 (first text box with crosstab value),

'  compute total for current row in detail section.

lngRowTotal = lngRowTotal + Me("Col" + Format$(intX))

'  Add crosstab value to total for current column.

lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + _

Me("Col" + Format$(intX))

Next intX

'  Place row total in text box in detail section.

Me("Col" + Format$(intColumnCount + 1)) = lngRowTotal

'  Add row total for current row to grand total.

lngReportTotal = lngReportTotal + lngRowTotal

End If

End Sub


Retreat Event Procedure for the Detail Section

Private Sub Detail1_Retreat()

' Always back up to previous record when detail section retreats.

rstReport.MovePrevious

End Sub


Print Event Procedure for the Report Footer

Private Sub ReportFooter4_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer

'  Place column totals in text boxes in report footer.

'  Start at Column 2 (first text box with crosstab value).

For intX = 2 To intColumnCount

Me("Tot" + Format$(intX)) = lngRgColumnTotal(intX)

Next intX

'  Place grand total in text box in report footer.

Me("Tot" + Format$(intColumnCount + 1)) = lngReportTotal

'  Hide unused text boxes in report footer.

For intX = intColumnCount + 2 To conTotalColumns

Me("Tot" + Format$(intX)).Visible = False

Next intX

End Sub


Highlights of the EmployeeSalesDialogBox Form

The EmployeeSalesDialogBox form shows how to prompt for criteria for the EmployeeSales report, and how to specify date criteria by using a custom calendar control. This technique makes the EmployeeSalesDialogBox form work:

Setting report criteria in a dialog box.


Set Report Criteria in a Dialog Box

Instead of having users set 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 EmployeeSalesDialogBox form, a user specifies the beginning and ending dates as the selection criteria by clicking dates on a calendar.

Overview

Create a dialog box that contains a custom calendar control and text box controls for entering the report's criteria. Add the Preview, Print, Cancel, and SetDate 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 collection, 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 query.

Objects Used in This Example

Object	Name
Form	EmployeeSalesDialogBox
Report	EmployeeSales
Module	IsLoadedModule
Step by Step

1	Create the EmployeeSales report.

2	Create the EmployeeSalesDialogBox form.

a)	Create an unbound form, and set the following form properties.

Property	Setting
Caption	Employee Sales
DefaultView	Single Form
ViewsAllowed	Form
ScrollBars	Neither
RecordSelectors	No
NavigationButtons	No
AutoCenter	Yes
Modal	Yes
BorderStyle	Thin
MinMaxButtons	None
ShortcutMenu	No
OnLoad	[Event Procedure]
b)	Create a custom calendar control, and set the following control properties.

Property	Setting
Name	SelectDate
Verb	0
c)	Create two unbound text boxes. Set their Format properties to Short 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
Beginning Date:	BeginningDate
Ending Date:	EndingDate
d)	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	Caption	OnClick
Preview	Preview	[Event Procedure]
Print	Print	[Event Procedure]
Cancel	Cancel	[Event Procedure]
SetDate	Set Beginning Date	[Event Procedure]
e)	In the Declarations section of the Form_EmployeeSalesDialogBox module, type:

Const ConErrRptCanceled = 2501

3	Modify the EmployeeSales query.

a)	In the Criteria box for the ShippedDate field, type:

Between Forms!EmployeeSalesDialogBox!BeginningDate And Forms!EmployeeSalesDialogBox!EndingDate

b)	Set the following query parameters: Forms!EmployeeSalesDialogBox!BeginningDate and Forms!EmployeeSalesDialogBox!EndingDate.

c)	Set the data type for the query parameters to Date/Time.

4	Create the IsLoadedModule module, if you haven't already done so.

Create the IsLoaded function.

5	Modify the Open event procedure of the EmployeeSales report.

Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with forms	Chapter 3, "Using Forms to Collect, Display, and Filter Information"


Load Event Procedure for the EmployeeSalesDialogBox Form

Private Sub Form_Load()

'  Used by Solutions to return records for example.

'  Enter parameters in BeginningDate and EndingDate text boxes when

'  form is loaded.

Me!SelectDate = Now()

Me!BeginningDate = #4/1/95#

Me!EndingDate = #4/30/95#

End Sub


Click Event Procedure for the Preview Command Button

Private Sub Preview_Click()

'  Preview report.

On Error GoTo Err_Preview_Click

Dim DocName As String

'Check to see that Ending Date is later than Beginning Date.

If EndingDate < BeginningDate Then

MsgBox "The Ending Date must be later than the Beginning Date."

DoCmd.CancelEvent

SetDate.Caption = "Set Ending Date"

SelectDate.SetFocus

Exit Sub

End If

DocName = "EmployeeSales"

DoCmd.OpenReport DocName, 2

'  Used by Solutions to open Show Me topic for report.

If IsLoaded("Solutions") Then

If Forms!Solutions!ShowMe Then

SendKeys "{F1}", True

End If

End If

Exit_Preview_Click:

Exit Sub

Err_Preview_Click:

If Err = ConErrRptCanceled Then

Resume Exit_Preview_Click

Else

MsgBox Err.Description

Resume Exit_Preview_Click

End If

End Sub


Click Event Procedure for the Print Command Button

Private Sub Print_Click()

'  Print report.

On Error GoTo Err_Print_Click

Dim DocName As String

'Check to see that Ending Date is later than Beginning Date.

If EndingDate < BeginningDate Then

MsgBox "The Ending Date must be later than the Beginning Date."

DoCmd.CancelEvent

SetDate.Caption = "Set Ending Date"

SelectDate.SetFocus

Exit Sub

End If

DocName = "EmployeeSales"

DoCmd.OpenReport DocName, 0

Exit_Print_Click:

Exit Sub

Err_Print_Click:

If Err = ConErrRptCanceled Then

Resume Exit_Print_Click

Else

MsgBox Err.Description

Resume Exit_Print_Click

End If

End Sub


Click Event Procedure for the Cancel Command Button

Private Sub Cancel_Click()

'  Close form.  (This code created by Command Button Wizard.)

On Error GoTo Err_Cancel_Click

DoCmd.Close

Exit_Cancel_Click:

Exit Sub

Err_Cancel_Click:

MsgBox Err.Description

Resume Exit_Cancel_Click

End Sub


Click Event Procedure for the SetDate Command Button

Private Sub SetDate_Click()

On Error GoTo SetDate_Error

If SetDate.Caption = "Set Beginning Date" Then

BeginningDate = SelectDate.Value

SetDate.Caption = "Set Ending Date"

Else

EndingDate = SelectDate.Value

SetDate.Caption = "Set Beginning Date"

End If

Exit Sub

SetDate_Error:

MsgBox Err.Description

Exit Sub

End Sub


IsLoaded Function

Function IsLoaded(frmName)

'  Determines if a form is loaded.

Const conFormDesign = 0

Dim intX As Integer

IsLoaded = False

For intX = 0 To Forms.Count - 1

If Forms(intX).FormName = frmName Then

If Forms(intX).CurrentView <> conFormDesign Then

IsLoaded = True

Exit Function  ' Quit function once form has been found.

End If

End If

Next

End Function


Open Event Procedure for the EmployeeSales Report

Text in red represents the comments and code you need to add to the event procedure to make sure the EmployeeSalesDialogBox form is loaded, and to set parameters for the EmployeeSales query.

Private Sub Report_Open(Cancel As Integer)

'  Create underlying recordset for report using criteria entered in

'  EmployeeSalesDialogBox form.

Dim intX As Integer

Dim qdf As QueryDef

'   Don't open report if EmployeeSalesDialogBox form isn't loaded.

If Not (IsLoaded("EmployeeSalesDialogBox")) 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 dbsReport = CurrentDb()

'  Open QueryDef.

Set qdf = dbsReport.QueryDefs("EmployeeSales")

'  Set parameters for query based on values entered

'  in EmployeeSalesDialogBox form.

qdf.Parameters("Forms!EmployeeSalesDialogBox!BeginningDate") _

= Forms!EmployeeSalesDialogBox!BeginningDate

qdf.Parameters("Forms!EmployeeSalesDialogBox!EndingDate") _

= Forms!EmployeeSalesDialogBox!EndingDate

'  Open Recordset.

Set rstReport = qdf.OpenRecordset()

'  Set a variable to hold number of columns in crosstab query.

intColumnCount = rstReport.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 criteria dialog box on the report. For example, the values you enter in the BeginningDate and EndingDate text boxes on the EmployeeSalesDialogBox form appear in the report header of the EmployeeSales 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	EmployeeSalesDialogBox
Report	EmployeeSales
Step by Step

1	Create the EmployeeSales report.

2	Create the EmployeeSalesDialogBox form.

3	Modify the EmployeeSales report. Create an unbound text box in the report header. Set its Name property to Range and in its ControlSource property, type:

="From " & Format([Forms]![EmployeeSalesDialogBox]![BeginningDate], "dd-mmm-yy") & " through " & Format([Forms]![EmployeeSalesDialogBox]![EndingDate], "dd-mmm-yy")


Highlights of the EnterOrEditProducts Form

The EnterOrEditProducts form shows how to enter or edit records on the same form. These techniques make the EnterOrEditProducts form work:

Creating a form that displays different toolbars when you switch between entering new records and editing existing ones.

Updating the list in the SelectProduct combo box after you switch to another record. (The technique is the same as that used on the EditProducts form.)

These techniques enhance the form:

Adding a new category to the category list.

Capitalizing the first letter of the product name automatically.


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 EnterOrEditProducts form opens in Data Entry mode. To edit records, click Edit Data on the toolbar. To switch back to entering records, click Enter Data on the toolbar.

Overview

Design the form so that it opens in Data Entry mode for entering new records. 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 AllowEdits 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	Name
Table	Products
Form	EnterOrEditProducts
Macro	EnterOrEditProducts
Module	EnableControlsModule, EnterOrEditProductsButtons
Step by Step

1	Create the EnterOrEditProducts form.

a)	Set the following form properties.

Property	Setting
RecordSource	Products
Filter	ProductID = Forms!EnterOrEditProducts!SelectProduct
Caption	Enter Or Edit Products
DefaultView	Single Form
ViewsAllowed	Form
DataEntry	Yes
ScrollBars	Neither
RecordSelectors	No
ShortcutMenu	No
b)	In the detail section, add the fields from the Products table. Use a check box for the Discontinued field, combo boxes for the SupplierID and CategoryID fields, and 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 an AutoNumber field, make the ProductID text box display-only by setting its Enabled property to No.

2	Add a form header, and create the SelectCategory and SelectProduct combo boxes. Creating these combo boxes is explained in the topic Limit the Contents of One List Based on the Value Selected in Another. The procedure for creating the combo boxes was written for the EditProducts form, so you will need to make the following changes.

a)	Set the RowSource property of the SelectProduct combo box to:

SELECT DISTINCTROW Products.ProductName, Products.ProductID, Products.CategoryID
FROM Products
WHERE (((Products.CategoryID)=[Forms]![EnterOrEditProducts]![SelectCategory]))
ORDER BY Products.ProductName;

b)	Create an event procedure in the AfterUpdate property of the SelectProduct 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 EnableControlsModule module, if you haven't already done so.

Create the EnableControls function.

4	Create the EnterOrEditProductsButtons module.

a)	Create the EditData function.

b)	Create the EnterData function.

5	Create the following macros in a macro group named EnterOrEditProducts.

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:

Cut	Copy	Paste	New	Undo	Undo Current Record
c)	Add to the toolbar a custom button that runs the macro EnterOrEditProducts.EditData. To set the button text and button description, in the Choose Button Image dialog box, type Edit Data in the Text and Description boxes.

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 to the toolbar a custom button that runs the macro EnterOrEditProducts.EnterData. To set the button text and button description, in the Choose Button Image dialog box, type Enter Data in the Text and Description boxes.

Note   The custom toolbars for the forms in the Solutions application 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]
Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with forms	Chapter 3, "Using Forms to Collect, Display, and Filter Information"


AfterUpdate Event Procedure for the SelectProduct Combo Box

Private Sub SelectProduct_AfterUpdate()

'  Find record for product selected in SelectProduct combo box,

'  enable controls in detail section, disable ProductID text box,

'  and go to SupplierID combo box.

Dim varTmp As Variant

DoCmd.ApplyFilter , "ProductID = Forms!EnterOrEditProducts!SelectProduct"

varTmp = EnableControls("Detail", True)

Me!ProductID.Enabled = False

Me!SupplierID.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 frm As Form

Dim ctlCombo1 As Control

Dim ctlCombo2 As Control

Dim varTmp As Variant

Set frm = Forms![EnterOrEditProducts]

Set ctlCombo1 = Forms![EnterOrEditProducts]![SelectCategory]

Set ctlCombo2 = Forms![EnterOrEditProducts]![SelectProduct]

'  Set AllowEdits property to false to indicate editing mode.

'  Set DefaultEditing property to Can't Add Records (4).

frm.AllowEdits = False

frm.DefaultEditing = 4

'  Switch toolbars.

DoCmd.ShowToolbar "Enter Or Edit Products 1", acToolbarNo

DoCmd.ShowToolbar "Enter Or Edit Products 2", acToolbarWhereApprop

'  Enable and move to first combo box.

ctlCombo1.Enabled = True

ctlCombo1.SetFocus

'  Disable controls in detail section.

varTmp = EnableControls("Detail", False)

Exit Function

EditData_Err:

MsgBox Err.Description

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 frm As Form

Dim ctlCombo1 As Control

Dim ctlCombo2 As Control

Dim varTmp As Variant

Set frm = Forms![EnterOrEditProducts]

Set ctlCombo1 = Forms![EnterOrEditProducts]![SelectCategory]

Set ctlCombo2 = Forms![EnterOrEditProducts]![SelectProduct]

'  Set AllowEdits property to true to indicate data entry mode.

'  Set DefaultEditing property to Data Entry (1).

frm.AllowEdits = True

frm.DefaultEditing = 1

'  Enable controls in detail section and disable ProductID text box.

varTmp = EnableControls("Detail", True)

frm![ProductID].Enabled = False

'  Go to a new record and move to SupplierID combo box.

DoCmd.GoToRecord , , acNewRec

frm![SupplierID].SetFocus

'  Switch toolbars.

DoCmd.ShowToolbar "Enter Or Edit Products 2", acToolbarNo

DoCmd.ShowToolbar "Enter Or Edit Products 1", acToolbarWhereApprop

'  Disable combo boxes in form header.

ctlCombo1.Enabled = False

ctlCombo2.Enabled = False

Exit Function

EnterData_Err:

MsgBox Err.Description

Exit Function

End Function


Open Event Procedure for the EnterOrEditProducts Form

Private Sub Form_Open(Cancel As Integer)

'  Set AllowEdits property to True when form opens.

Me.AllowEdits = True

End Sub


Activate Event Procedure for the EnterOrEditProducts Form

Private Sub Form_Activate()

'  Hide Form View toolbar.

'  Use AllowEdits property setting to determine which toolbar to

'  show.

DoCmd.ShowToolbar "Form View", acToolbarNo

If Me.AllowEdits = True Then

DoCmd.ShowToolbar "Enter Or Edit Products 1", acToolbarYes

Else

DoCmd.ShowToolbar "Enter Or Edit Products 2", acToolbarYes

End If

End Sub


Deactivate Event Procedure for the EnterOrEditProducts Form

Private Sub Form_Deactivate()

'  Use AllowEdits property setting to determine which toolbar to

'  hide.

'  Show Form View toolbar.

If Me.AllowEdits = True Then

DoCmd.ShowToolbar "Enter Or Edit Products 1", acToolbarNo

Else

DoCmd.ShowToolbar "Enter Or Edit Products 2", acToolbarNo

End If

DoCmd.ShowToolbar "Form View", acToolbarWhereApprop

End Sub


Highlights of the AddCategory Form

The AddCategory form is opened from the EnterOrEditProducts form when you add a new category to the CategoryID combo box. This technique makes the AddCategory form work:

Adding a new category to the category list.


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 EnterOrEditProducts 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 CategoryID combo box on the EnterOrEditProducts form. (This event procedure displays a dialog box in which you can confirm that you want to add a new category; if you click Yes, it opens the AddCategory form.) Create an event procedure for the OnClose event of the AddCategory form that uses the Requery method to requery the CategoryID combo box on the EnterOrEditProducts form.

Objects Used in This Example

Type	Name
Table	Products, Categories
Form	EnterOrEditProducts, AddCategory
Module	IsLoadedModule
Step by Step

1	On the EnterOrEditProducts form, create an event procedure in the OnNotInList property of the CategoryID combo box.

2	Create the IsLoadedModule module, if you haven't already done so.

Create the IsLoaded function.

3	Create the AddCategory form.

a)	Set form properties and create event procedures as follows.

Property	Setting
RecordSource	Categories
Caption	Add Category
DefaultView	Single Form
ViewsAllowed	Form
DataEntry	Yes
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 CategoryName, Description, and Picture fields from the Categories table.

Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with forms	Chapter 3, "Using Forms to Collect, Display, and Filter Information"


NotInList Event Procedure for the CategoryID Combo Box

Private Sub CategoryID_NotInList(strNewData As String, intResponse As Integer)

' Add a new category by typing a name in

' CategoryID combo box.

Dim intNewCategory As Integer, intTruncateName As Integer, strTitle As String, intMsgDialog As Integer

Const conMBOK = 0

Const conMBYesNo = 4

Const conMBIconQuestionMark = 32

Const conMBIconExclamation = 64

Const conMBDefButton1 = 0, conIDYes = 6, conIDNo = 7

' Display message box asking if user wants to add a

' new category.

strTitle = "Category Not In List"

intMsgDialog = conMBYesNo + conMBIconQuestionMark + conMBDefButton1

intNewCategory = MsgBox("Do you want to add a new category?", intMsgDialog, strTitle)

If intNewCategory = conIDYes Then

' Remove new name from CategoryID combo box so

' control can be requeried when user returns to form.

DoCmd.DoMenuItem acFormBar, acEdit, acUndo, , acMenuVer20

'  Display message box and adjust length of value entered in

'  Category ID combo box.

strTitle = "Name Too Long"

intMsgDialog = conMBOK + conMBIconExclamation

If Len(strNewData) > 15 Then

intTruncateName = MsgBox("Category names can be no " _

& "longer than 15 characters. The name you entered will " _

& "be truncated.", intMsgDialog, strTitle)

strNewData = Left$(strNewData, 15)

End If

'  Open AddCategory form.

DoCmd.OpenForm "AddCategory", acNormal, , , acAdd

Forms!AddCategory!CategoryName = strNewData

'  Continue without displaying default error message.

intResponse = acDataErrContinue

End If

End Sub


Unload Event Procedure for the AddCategory Form

Private Sub Form_Unload(Cancel As Integer)

'  If EnterorEditProducts form is loaded,

'  select it, requery CategoryID combo box,

'  and set value of CategoryID combo box.

Dim ctl As Control

If IsLoaded("EnterOrEditProducts") Then

Set ctl = Forms!EnterOrEditProducts!CategoryID

DoCmd.SelectObject acForm, "EnterOrEditProducts"

ctl.Requery

ctl = Me!CategoryID

End If

End Sub


Activate Event Procedure for the AddCategory Form

Private Sub Form_Activate()

'  Show toolbar for AddCategory form.

'  Hide Form View toolbar.

'  Show custom Form View toolbar.

DoCmd.ShowToolbar "Form View", acToolbarNo

DoCmd.ShowToolbar "Custom Form View", acToolbarYes

'  If EnterOrEditProducts form is loaded, hide its current toolbar.

If IsLoaded("EnterOrEditProducts") Then

If Forms!EnterOrEditProducts.AllowEdits = True Then

DoCmd.ShowToolbar "Enter Or Edit Products 1", acToolbarNo

Else

DoCmd.ShowToolbar "Enter Or Edit Products 2", acToolbarNo

End If

End If

End Sub


Deactivate Event Procedure for the AddCategory Form

Private Sub Form_Deactivate()

'  Hide toolbar for AddCategory form.

'  Hide custom Form View toolbar.

'  Show built-in Form View toolbar.

DoCmd.ShowToolbar "Custom Form View", acToolbarNo

DoCmd.ShowToolbar "Form View", acToolbarWhereApprop

End Sub


Capitalize the First Letter in a Field

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 sample 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	EnterOrEditProducts
Step by Step

1	In the EnterOrEditProducts form module, Form_EnterOrEditProducts, create the CapitalizeFirst function.

2	On the EnterOrEditProducts form, create an event procedure in the AfterUpdate property of the ProductName text box.

Tip   You can capitalize the first letter of every word in the field by using the Visual Basic for applications StrConv function.

Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


CapitalizeFirst Function of the Form_EnterOrEditProducts Module

Function CapitalizeFirst(Str)

'  Make first letter in field uppercase; leave other

'  letters as typed.

Dim strTemp As String

strTemp = Trim(Str)

CapitalizeFirst = UCase(Left(strTemp, 1)) & Mid(strTemp, 2)

End Function


AfterUpdate Event Procedure for the ProductName Text Box

Private Sub ProductName_AfterUpdate()

'  Capitalize first letter in product name.

If Not (IsNull(Me!ProductName)) Then

Me!ProductName = CapitalizeFirst((Me!ProductName))

End If

End Sub


Highlights of the Invoice Report

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:

Creating a report that prints on a preprinted form.

This technique enhances the report:

Printing "Continued" at the bottom of a preprinted form.


Create a Report That Prints on a Preprinted Form

Some 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 OrderID footer. In the OnFormat property of the group footer, create an event procedure that sets the value of the text box to True. In the OnFormat property of the page footer, create an event procedure 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	Name
Table	Customers, Employees, Orders
Query	Invoice, OrderDetailsExtended
Report	Invoice
Step by Step

1	Import the OrderDetailsExtended query from the Northwind sample database, if you haven't already done so.

2	Create the Invoice query.

a)	Use the fields in the following tables and queries.

Table/Query	Field
Customers table	CompanyName, Address, City, Region, PostalCode, Country
Employees table	LastName, FirstName
Orders table	OrderID, CustomerID, OrderDate, RequiredDate, ShippedDate, ShipVia,  Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry
OrderDetailsExtended query	ProductName, ProductID, UnitPrice, Quantity, Discount, ExtendedPrice
b)	In the Criteria row for the OrderDate field, type Between #5/1/95# and #5/31/95#.

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 EmployeeSales report.

3	Create the Invoice report.

a)	 In the Sorting And Grouping dialog box, set the following properties.

Field/Expression	Sort Order	Group Property
OrderID	Descending	GroupHeader: Yes GroupFooter: Yes GroupOn: Each Value GroupInterval: 1 KeepTogether: No
ProductID	Ascending	(Default settings)
Note  The order that requires multiple pages is 11077, the most current order in the database. In order to display that order first, OrderID is sorted in descending order.

b)	In the Page Setup dialog box, click the Margins tab, and then set the Left, Right, Top, and Bottom margins to 1".

c)	Set the Height property for the following sections as follows.

Section	Height
Page header	3.6875"
Detail section	0.25"
Page footer	1"
The OrderID 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 and set their properties as follows.

Name	ControlSource
ProductID	ProductID
ProductName	ProductName
Quantity	Quantity
UnitPrice	UnitPrice
Discount	Discount
ExtendedPrice	ExtendedPrice
f)	In the OrderID footer, create an unbound text box. Set its Name property to PrintPageFooter.

g)	In the OrderID footer, create a text box that calculates the invoice subtotal, and set its properties as follows.

Property	Setting
Name	Subtotal
ControlSource	= Sum([ExtendedPrice])
h)	Create text boxes in the page footer and set their properties as follows.

Name	ControlSource
InvoiceSubtotal	=[Subtotal]
Freight	Freight
InvoiceTotal	=[InvoiceSubtotal]+ [Freight]
i)	Set properties and create event procedures for the following sections.

Section	Property	Setting
OrderID header	Visible	No
	OnFormat	[Event Procedure]
OrderID footer	ForceNewPage	After Section
	Visible	No
	OnFormat	[Event Procedure]
Page footer	OnFormat	[Event Procedure]
Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


Format Event Procedure for the OrderID Header

Private 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 OrderPage text box to 1 to show that

'  this is first page of order.

Me!Row = 1

Me!OrderPage = 1

'  Hide Continued and DetailPageBreak controls.

Me!Continued.Visible = False

Me!DetailPageBreak.Visible = False

End Sub


Format Event Procedure for the OrderID Footer

Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)

'  Reset PrintPageFooter text box to show that all detail

'  records have been printed for current order.  Report will print

'  page footer when value of PrintPageFooter is True.

Me!PrintPageFooter = True

End Sub


Format Event Procedure for the Page Footer

Private 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!PrintPageFooter = True Then

Me.PrintSection = True

Me!PrintPageFooter = 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


Page Event Procedure for the Invoice Report
Page Event Procedure for the SalesLetter Report

Private Sub Report_Page()

' Draw a page border around this report.

Me.Line (0, 0)-(Me.LogicalPageWidth, Me.LogicalPageHeight), , B

End Sub


Print "Continued" at the Bottom of a Preprinted Form

When 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 on the next page, starting with detail record 17.

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 the Invoice report), hide the controls in the detail record, print "Continued", and don't advance to the next record.

Objects Used in This Example

Type	Name
Table	Customers, Employees, Orders
Query	Invoice, OrderDetailsExtended
Report	Invoice
Step by Step

1	Create a report for a preprinted form.

2	In the page header, create an unbound text box, and set its properties as follows.

Property	Setting
Name	RowsPerPage
ControlSource	=17
Visible	No
3	In the OrderID header, create two unbound text boxes.

a)	Set the Name property of one text box to RowCount, and in the ControlSource property box, type =Count([ProductID]).

b)	Set the Name property of the other text box to OrderPage, and leave its ControlSource property blank.

Tip   On the Invoice report, the OrderID 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, and set its properties as follows.

Property	Setting
Name	Continued
ControlSource	="Continued on next page..."
FontItalic	Yes

b)	Create an unbound text box at the right edge of the detail section, and set its properties as follows.

Property	Setting
Name	Row
Visible	No
c)	Create a page break control at the bottom of the section. Set its Name property to DetailPageBreak.

5	Create event procedures for the following sections.

Section	Property	Setting
Page header	OnFormat	[Event Procedure]
OrderID header	OnFormat	[Event Procedure]
Detail section	OnFormat	[Event Procedure]
Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


Format Event Procedure for the Page Header

Private 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!DetailPageBreak.Visible = False

Me!ProductID.Visible = True

Me!ProductName.Visible = True

Me!Quantity.Visible = True

Me!UnitPrice.Visible = True

Me!Discount.Visible = True

Me!ExtendedPrice.Visible = True

End Sub


Format Event Procedure for the Detail Section

Private 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!OrderPage * (Me!RowsPerPage - 1) + 1) _

And Me!Row <> Me!RowCount Then

Me!Continued.Visible = True

End If

' Show page break and hide controls in detail record.

If Me!Continued.Visible Then

Me!DetailPageBreak.Visible = True

Me!ProductID.Visible = False

Me!ProductName.Visible = False

Me!Quantity.Visible = False

Me!UnitPrice.Visible = False

Me!Discount.Visible = False

Me!ExtendedPrice.Visible = False

' Increase value in Order Page.

Me.NextRecord = False

Me!OrderPage = Me!OrderPage + 1

' Increase row count if detail record is printed.

Else

Me!Row = Me!Row + 1

End If

End Sub


Format Event Procedure for the OrderID Header

Private 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 OrderPage text box to 1 to show that

'  this is first page of order.

Me!Row = 1

Me!OrderPage = 1

'  Hide Continued and DetailPageBreak controls.

Me!Continued.Visible = False

Me!DetailPageBreak.Visible = False

End Sub


Highlights of the ShippingLog Report

The ShippingLog report shows how to present data in a snaking-column, or telephone-directory-style, format. This technique makes the ShippingLog report work:

Creating a multiple-column report.

This technique enhances the report:

Hiding a group footer when it appears at the top of a page or column.


Create a Multiple-Column Report

You can create a telephone-directory-style report by printing data in two or more snaking columns on the page. For example, the ShippingLog 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 Page Setup options.

Objects Used in This Example

Object	Name
Table	Orders
Query	OrderSubtotals, ShippingLog
Report	ShippingLog
Step by Step

1	Import the OrderSubtotals query from the Northwind sample database, if you haven't already done so.

2	Create the ShippingLog query.

Table/Query	Field	Criteria
Orders table	ShippedDate	Between #2/1/95# And #3/31/95#
	OrderID	--
OrderSubtotals 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 EmployeeSales report.

3	Create the ShippingLog report.

a)	In the Sorting And Grouping dialog box, set the following properties.

Field/Expression	Sort Order	Group Property
ShippedDate	Descending	GroupHeader: Yes GroupFooter: Yes GroupOn: Each Value GroupInterval: 1 KeepTogether: With first detail
OrderID	Ascending	(Default settings)
b)	Set the Width property of the report to 9".

c)	Set the following Page Setup options.

Tab	Option	Setting
Page	Orientation	Landscape
Margins	Margins	Left: 1" Right: 1" Top: 1" Bottom: 1"
Layout	Grid Settings	Items Across: 2
		Column Spacing:  0.75"
	Item Size	Width: 3.25" Height: 0.2299"
	Layout Items	Down, Then Across
d)	Place labels with the following captions in the page header: ShippedDate, OrderID, and OrderAmount.

e)	Place the following text boxes in the following sections of the report, and set their Name and ControlSource properties as follows.

Section	Name	ControlSource
ShippedDate header	ShippedDate	ShippedDate
Detail section	OrderID Subtotal	OrderID Subtotal
f)	Copy the ShippedDate, OrderID, and OrderAmount labels in the page header, and paste them to the right of the first set. (The Left property of the second ShippedDate label should be set to 4".)


Prevent a Section from Printing at the Top of a Page or Column

Using a blank group footer to separate groups is useful when you want the division between them to be 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 ShippingLog report, the ShippedDate 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	Name
Table	Orders
Query	OrderSubtotals, ShippingLog
Report	ShippingLog
Step by Step

1	Create a multiple-column report.

2	In the ShippedDate header, create an unbound text box, and set its properties as follows.

Property	Setting
Name	Top1
Visible	No
3	Create event procedures for the following sections.

Section	Property	Setting
Page header	OnFormat	[Event Procedure]
ShippedDate footer	OnFormat	[Event Procedure]
Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


Format Event Procedure for the Page Header

Private 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 ShippedDate Footer

Private Sub GroupFooter4_Format(Cancel As Integer, _

FormatCount As Integer)

'  Determine if group footer is first section formatted

'  at top of page or column.

'  Dont print group footer if either statement is true.

If Me!Top1 = 0 Or Me.Top = Me!Top1 Then Cancel = True

End Sub


Highlights of the SalesLetter Report

The SalesLetter report is a form letter that summarizes an employee's sales totals. This technique makes the SalesLetter report work:

Creating a form letter with a page border.

This technique enhances the report:

Highlighting data by drawing a circle around it.


Create a Form Letter with a Page Border

When 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 SalesLetter report prints the same congratulatory message on every letter, but the name, goal, and quarterly totals are specific to each salesperson. You may also want to include a page border on the printed form.

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. In the OnPage property of the report, create an event procedure that draws a page border.

Objects Used in This Example

Object	Name
Table	Employees, Orders, SalesGoals
Query	OrderSubtotals, SalesLetter1, SalesLetter2
Report	SalesLetter, SalesLetterSubreport
Step by Step

1	Import the OrderSubtotals query from the Northwind sample database, if you haven't already done so.

2	Create the SalesLetter1 crosstab query.

a)	In the tables and queries listed, use the following fields and expressions.

Table/Query	Field/Expression	Setting
Employees table	EmployeeID	Total: Group By Crosstab: Row Heading
	Name: [LastName] & ", " & [FirstName]	Total: Group By Crosstab: Row Heading
Fields from the Orders table	Year: DatePart("yyyy", [ShippedDate])	Total: Group By Crosstab: Row Heading
	Expr1: "Qtr" & DatePart("q", [ShippedDate])	Total: Group By Crosstab: Column Heading
	DatePart("yyyy", [ShippedDate])	Total: Where Criteria: 1993
OrderSubtotals 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 EmployeeSales report.

b)	Set the query's ColumnHeadings property to the following value: "Qtr1", "Qtr2","Qtr3","Qtr4".

3	Create the SalesLetter2 query.

Table/Query	Field/Expression	Setting
Employees table	LastName	Sort: Ascending
	Salesperson: [FirstName] & " " & [LastName]	--
	EmployeeID, Address, City, Region, PostalCode, Country	--
SalesGoals table	Goal	--
	Goal Year: DatePart("yyyy", [SalesGoals].[Year])	Criteria: [SalesLetter1].[Year]
SalesLetter1 query	Qtr1, Qtr2, Qtr3, Qtr4	--
4	Create the SalesLetter report.

a)	Set the report's RecordSource property to SalesLetter2.

b)	Remove the page header and footer. (You need only the detail section for this report.)

c)	In the Sorting And Grouping dialog box, select LastName 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 combine 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 " & [GoalYear] & ". 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.

f)	Set the SalesLetter report's OnPage property to [Event Procedure] to create a page border.

5	Create the SalesLetterSubreport report.

a)	Set the report's RecordSource property to SalesLetter2.

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" and the Height property to 0.1979". Leave 0.125 inch between each text box.

d)	Save the report.

6	Add the subreport to the main report.

a)	Select the SalesLetterSubreport report in the Database window, and drag it to the detail section of the SalesLetter report.

b)	Delete the label for the subreport control and adjust the size of the subreport control. For example, make the subreport control the height of one line of text.

c)	Verify that the LinkChildFields and LinkMasterFields properties of the SalesLetterSubreport control are set to EmployeeID.

7	Adjust the placement of the controls on the main report. For example, make sure that the parts of the letter (address, salutation, body text, and closing) are placed according to the letter style that you follow.


Highlight Data by Drawing a Circle Around It

You can call attention to specific data by highlighting it in some way. For example, the SalesLetter report highlights a quarter total that is 90 percent of the goal or greater by circling 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	Name
Table	Employees, Orders, SalesGoals
Query	OrderSubtotals, SalesLetter1, SalesLetter2
Report	SalesLetter, SalesLetterSubreport
Step by Step

1	Create the SalesLetter report.

2	In the SalesLetterSubreport report, create an event procedure for the OnPrint property of the detail section.

Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


Print Event Procedure for the Detail Section

Private Sub Detail0_Print(Cancel As Integer, PrintCount As Integer)

'  Draw ellipse around controls that meet specified criteria.

Dim ctl As Control

Dim intPrintCircle As Integer

Dim sngAspect As Single, sngYOffset As Single

Dim intEllipseHeight As Integer, intElipseWidth As Integer

Dim sngXCoord As Single, sngYCoord As Single

Dim intI As Integer

'  To yield desired ellipse, set sngAspect and sngYOffset

'  according to the size and location of text boxes on

'  subreport.

sngAspect = 0.23

sngYOffset = 200

'  Determine height and width of ellipse.

intEllipseHeight = Me!Goal.Height * 1.1

intElipseWidth = Me!Goal.Width * 1.04

sngYCoord = (Me!Goal.Top + Me!Goal.Height) \ 2 + sngYOffset

'  Loop through the controls that could be circled.

'  If ctl is not null, then determine if value is 90% or

'  greater than value in Goal text box.

For intI = 1 To 4

Set ctl = Me("Qtr" & intI)

If Not IsNull(ctl) Then

intPrintCircle = (ctl >= 0.9 * Me!Goal)

'  Determine coordinates of ctl and draw ellipse.

If intPrintCircle Then

sngXCoord = ctl.Left + (ctl.Width \ 2)

Me.Circle (sngXCoord, sngYCoord), _

intElipseWidth \ 2, , , , sngAspect

End If

End If

Next intI

End Sub


Highlights of the SalesTotals Form

The SalesTotals form shows how to display progressively more detail about an employee's sales. This technique makes the SalesTotals form work:

Creating a one-to-many-to-many form.


Create a One-to-Many-to-Many Form

When you look at data, you often want to see a top-level view as well as a detailed view. On the SalesTotals 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 - EmployeeID and T-Year.

Objects Used in This Example

Object	Name
Table	Employees, OrderDetails, Orders
Query	Salespeople, SalesTotals(Qtr), SalesTotals(Year)
Form	SalesTotals, SalesTotalsSubform1, SalesTotalsSubform2
Module	EnableControlsModule
Step by Step

1	Create the Salespeople query.

a)	Use the fields and expressions from the following tables.

Table	Field/Expression
Employees	EmployeeID
Fields from the Employees table	Salesperson: [LastName] &", " & [FirstName]
Orders	--
b)	In the Sort cell of the Salesperson expression, click Ascending.

Note   You don't use any fields from the Orders table in this query; you use the Orders table only to limit employee names to those with records in the Orders table.

2	Create the SalesTotals(Year) query.

Table	Field/Expression	Total	Criteria
Orders	EmployeeID	Group By	--
	T-Year: Year([ShippedDate])	Group By	Is Not Null
Fields  from the OrderDetails table	Total: CCur(Sum(CLng([UnitPrice] * [Quantity] * (1-[Discount]) * 100)/100))	Expression	--
3	Create the SalesTotals(Qtr) query.

Table	Field/Expression	Total	Criteria
Orders	EmployeeID	Group By	--
	T-Year: Year([ShippedDate])	Group By	Is Not Null
	Quarter: DatePart("q",[ShippedDate])	Group By	Is Not Null
Fields  from the OrderDetails table	Subtotal: CCur(Sum(CLng([UnitPrice] * [Quantity] * (1-[Discount]) * 100)/100))	Expression	--
4	Create the SalesTotalsSubform1 form.

a)	Set the following form properties.

Property	Setting
RecordSource	SalesTotals(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	Create the SalesTotalsSubform2 form.

a)	Set the following form properties.

Property	Setting
RecordSource	SalesTotals(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 EnableControlsModule module, if you haven't already done so.

Create the EnableControls function.

7	Create the SalesTotals form.

a)	Set the following form properties.

Property	Setting
RecordSource	Employees
Caption	Sales Totals
DefaultView	Single Form
ViewsAllowed	Form
ScrollBars	Neither
RecordSelectors	No
NavigationButtons	No
AutoCenter	Yes
ShortcutMenu	No
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	SelectSalesperson
RowSourceType	Table/Query
RowSource	Salespeople
ColumnCount	2
ColumnWidths	2";0"
BoundColumn	2
LimitToList	Yes
AfterUpdate	[Event Procedure]
d)	In the detail section, create a text box, detach its label, and then set the properties of the text box as follows.

Property	Setting
Name	Salesperson
ControlSource	=[LastName] & ", " & [FirstName]
Enabled	No
TabStop	No
e)	In the detail section, create a hidden text box, and set its properties as follows.

Property	Setting
Name	T-Year
ControlSource	=[SalesTotalsSubform1].Form![T-Year]
Visible	No
TabStop	No
Note   Microsoft Access uses the value in this text box to link the values in SalesTotalsSubform2 to the selected row in SalesTotalsSubform1.

8	Add the SalesTotalsSubform1 form to the SalesTotals form.

a)	Select the SalesTotalsSubform1 form in the Database window, and drag it to the detail section of the SalesTotals 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 SalesTotalsSubform1 control are both set to EmployeeID.

d)	Set the Enabled property of the SalesTotalsSubform1 control to No.

9	Add the SalesTotalsSubform2 form to the SalesTotals form.

a)	Select the SalesTotalsSubform2 form in the Database window, and drag it to the detail section of the SalesTotals form.

b)	Change the label for the subform control to Quarter Totals for Selected Year: and adjust the size of the subform control.

c)	In both the LinkChildFields and LinkMasterFields properties of the SalesTotalsSubform2 control, type T-Year;EmployeeID.

d)	Set the Enabled property of the SalesTotalsSubform2 control to No.

Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with forms	Chapter 3, "Using Forms to Collect, Display, and Filter Information"


AfterUpdate Event Procedure for the SelectSalesperson Combo Box

Private Sub SelectSalesperson_AfterUpdate()

'  Find records for salesperson selected in Salesperson combo box.

'  Enable controls in detail section.

'  Go to SalesTotalsSubform1.

Dim Tmp As Variant

DoCmd.ApplyFilter , "EmployeeID = Forms!SalesTotals!SelectSalesperson"

Tmp = EnableControls("Detail", True)

Me!SalesTotalsSubform1.SetFocus

End Sub


Highlights of the ShowSales Form

ShowSales is a form that shows how to use text boxes and an option group to specify criteria for a query. This technique makes the ShowSales form work:

Using a custom function in an SQL SELECT statement.


Use a Custom Function in a Form's SELECT Statement (SQL)

On a form in which you specify criteria for a query, you might want users to choose one option from a specific set of criteria. For example, on the ShowSales form, you can specify a date range and whether you want to see all records in the date range or only those records in the date range where the sales are less than or greater than 1000.

Overview

Create a form in which 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	Name
Table	Customers, Orders
Query	OrderSubtotals, ShowSales
Form	ShowSales, ShowSalesSubform
Module	EnableControlsModule
Step by Step

1	Create the ShowSalesSubform form.

a)	Set the following form properties.

Property	Setting
RecordSource	SELECT [CompanyName] FROM Customers WHERE False
DefaultView	Datasheet
ViewsAllowed	Datasheet
ShortcutMenu	No
Note   Setting the RecordSource property to this SQL statement will display the subform as an empty datasheet when the ShowSales form is opened.

b)	In the detail section, create text boxes for the CompanyName, OrderID, and Subtotal fields.

c)	Switch to Datasheet view. Adjust the width of the columns, and save the form.

2	Create the ShowSales form.

a)	Set the following form properties.

Property	Setting
RecordSource	--
Caption	Show Sales
DefaultView	Single Form
ViewsAllowed	Form
ScrollBars	Neither
RecordSelectors	No
NavigationButtons	No
AutoCenter	Yes
ShortcutMenu	No
OnOpen	[Event Procedure]
b)	In the Database window, select the ShowSalesSubform form and drag it to the detail section of the ShowSales 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
BeginningDate	Medium Date
EndingDate	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 properties as follows.

Property	Setting
Name	Sales
DefaultValue	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.

3	Import the OrderSubtotals query from the Northwind sample database, if you haven't already done so.

4	Create the ShowSales query.

a)	Use the fields from the following tables and query.

Table/Query	Field
Customers table	CompanyName
Orders table	ShippedDate
OrderSubtotals query	OrderID
	Subtotal (Descending)
b)	In the Criteria cell of the TopicID field, type:

Between Forms![ShowSales]![BeginningDate] And Forms![ShowSales]![EndingDate]

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

5	In the General section of the Form_ShowSales module, create the ShowSalesValue function.

6	Create the EnableControlsModule module, if you haven't already done so.

Create the EnableControls function.

7	In the form header of the ShowSales form, create a command button, and set its properties as follows.

Property	Setting
Name	ShowSales
Caption	Show Sales
Default	Yes
ControlTipText	Show sales that match specified criteria.
OnClick	[Event Procedure]
Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with forms	Chapter 3, "Using Forms to Collect, Display, and Filter Information"


Open Event Procedure for the ShowSales Form

Private Sub Form_Open(Cancel As Integer)

'  Move insertion point to BeginningDate field.

Me!BeginningDate.SetFocus

'  Used by Solutions to return records for example.

Me!BeginningDate = #4/1/95#

Me!EndingDate = #4/30/95#

End Sub


ShowSalesValue Function

Private Function ShowSalesValue(lngOptionGroupValue As Long) As String

'  Return value selected in Sales option group.

'  Define constants for option group values.

Const conSalesUnder1000 = 1

Const conSalesOver1000 = 2

Const conAllSales = 3

'  Create restriction based on value of option group.

Select Case lngOptionGroupValue

Case conSalesUnder1000:

ShowSalesValue = "OrderSubtotals.Subtotal < 1000"

Case conSalesOver1000:

ShowSalesValue = "OrderSubtotals.Subtotal >= 1000"

Case Else

ShowSalesValue = "OrderSubtotals.Subtotal = True"

End Select

End Function


Click Event Procedure for the ShowSales Command Button

Private Sub ShowSales_Click()

'  Create an SQL statement using search criteria entered by user and

'  set RecordSource property of ShowSalesSubform.

Dim strSQL As String, strRestrict As String

Dim lngX As Long

Dim varTmp As Variant

lngX = Forms!ShowSales!Sales.Value

strRestrict = ShowSalesValue(lngX)

'  Create SELECT statement.

strSQL = "SELECT DISTINCTROW Customers.CompanyName, _

OrderSubtotals.OrderID, "

strSQL = strSQL & "OrderSubtotals.Subtotal , Orders.ShippedDate "

strSQL = strSQL & "FROM Customers INNER JOIN _

(OrderSubtotals INNER JOIN Orders ON "

strSQL = strSQL & "OrderSubtotals.OrderID = Orders.OrderID) ON "

strSQL = strSQL & "Customers.CustomerID = Orders.CustomerID "

strSQL = strSQL & "WHERE (Orders.ShippedDate Between _

Forms!ShowSales!BeginningDate "

strSQL = strSQL & "And Forms!ShowSales!EndingDate) "

strSQL = strSQL & "And " & strRestrict

strSQL = strSQL & " ORDER BY OrderSubtotals.Subtotal DESC;"

'  Set RecordSource property of ShowSalesSubform.

Me!ShowSalesSubform.Form.RecordSource = strSQL

'  If no records match criteria, reset subform's RecordSource property,

'  display message, and move focus to BeginningDate text box.

If Me!ShowSalesSubform.Form.RecordsetClone.RecordCount = 0 Then

Me!ShowSalesSubform.Form.RecordSource = "SELECT CompanyName _

FROM Customers WHERE False;"

MsgBox "No records match the criteria you entered.", 48, _

"No Records Found"

Me!BeginningDate.SetFocus

Else

'  Enable control in detail section.

varTmp = EnableControls("Detail", True)

'  Move insertion point to ShowSalesSubform.

Me!ShowSalesSubform.SetFocus

End If

End Sub


Highlights of the CustomerPhoneList Form

The CustomerPhoneList form shows how to display a record on a form, then simultaneously display a second record on another copy of the form. This technique makes the CustomerPhoneList form work:

Displaying multiple instances of a form.

This technique enhances the CustomerPhoneList form:

Using a function to create a multi-select list box.


Display Multiple Instances of a Form

In a form that displays records in a list, such as a phone list of customers, you may want to view detail information about more than one record at the same time. On the CustomerPhoneList form, you can click a command button to view detail information about a customer in a second form, then view detail information about additional customers in separate copies of the second form.

Overview

Create a CustomerPhoneList form that displays a list of customers. Create a Customers form that displays detail information about customers. Define a custom property and procedures to handle multiple instances of the form. Add a command button to the form that displays a separate instance of the Customers form for each customer record.

Objects Used in This Example

Object	Name
Table	Customers
Form	CustomerPhoneList, Customers
Step by Step

1	Create the CustomerPhoneList form.

a)	Set the following form properties.

Property	Setting
RecordSource	Customers
Caption	Customer Phone List
DefaultView	Continuous Forms
ViewsAllowed	Both
AllowEdits	No
AllowDeletions	No
AllowAdditions	No
AutoResize	Yes
AutoCenter	Yes
b)	In the form header, create Company Name, Contact Name, Country, and Phone labels.

c)	In the detail section, add the CompanyName, ContactName, Country, and Phone fields from the Customer table as text boxes, and delete the text box labels that are automatically created.

2	Create the Customers form.

a)	Set the following form properties.

Property	Setting
RecordSource	Customers
Caption	Customers
DefaultView	Single Form
ViewsAllowed	Both
AllowEdits	No
AllowDeletions	No
AllowAdditions	No
RecordSelectors	No
AutoResize	Yes
AutoCenter	Yes
b)	In the form header, create a Customers label.

c)	In the detail section, add the fields from the Customers table as text boxes.

3	In the Declarations section of the Form_CustomerPhoneList module, declare variables.

4	In the Declarations section of the Form_Customers module, declare variables.

5	At the module level of the Form_Customers module, add Property Get and Property Let statements to define a custom property for managing form instances.

6	At the module level of the Form_CustomerPhoneList module, create a custom Sub procedure for managing form instances.

7	Create event procedures for the CustomerPhoneList form as follows.

Property	Setting
OnClose	[Event Procedure]
OnActivate	[Event Procedure]
OnDeactivate	[Event Procedure]
8	Create event procedures for the Customers form as follows.

Property	Setting
OnClose	[Event Procedure]
OnOpen	[Event Procedure]
9	In the detail section of the CustomerPhoneList form, create a View command button, and set its properties as follows.

Property	Setting
Name	View
Caption	More...
ControlTipText	Click here for more information about the current customer.
OnClick	[Event Procedure]
Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with forms	Chapter 3, "Using Forms to Collect, Display, and Filter Information"
Working with objects and collections	Chapter 5, "Working with Objects and Collections"


Declarations Section of the Form_CustomerPhoneList Module

Option Compare Database

Option Explicit

' Collection to contain instances of the Customers form.

Dim colCustomerForms As New Collection


Declarations Section of the Form_Customers Module

Option Compare Database

' Public variables that will be used

' to keep track of form instances.

Dim strMultiInstKey As String

Dim frmCustomers As Form_Customers


Property Get Statement of the Form_Customers Module

Public Property Get MultiInstKey() As String

'Define the custom MultiInstKey property.

MultiInstKey = strMultiInstKey

End Property


Property Let Statement of the Form_Customers Module

Public Property Let MultiInstKey(strVal As String)

'Define the custom MultiInstKey property.

strMultiInstKey = strVal

End Property


RemoveFormInstances Sub Procedure of the Form_CustomerPhoneList Module

Public Sub RemoveFormInstance(strWhich As String)

'Removes the instance of the Customers form specified by

'strWhich from the collection.

colCustomerForms.Remove strWhich

End Sub


Activate Event Procedure for the CustomerPhoneList Form

Private Sub Form_Activate()

'  Used by Solutions to show toolbar that includes Show Me button.

'  Hide built-in Form View toolbar.

'  Show Custom Form View toolbar.

DoCmd.ShowToolbar "Form View", acToolbarNo

DoCmd.ShowToolbar "Custom Form View", acToolbarYes

End Sub


Close Event Procedure for the CustomerPhoneList Form

Private Sub Form_Close()

Dim intIndex As Integer

Dim frm As Form_Customers

' Bring up CustomersDialog asking the user which forms to keep open.

If colCustomerForms.Count > 0 Then

DoCmd.OpenForm "CustomersDialog", , , , , 3

End If

' Close all the forms in the collection so they don't call back into

' this form when they close.  Note that this form closes before the

' others and removes

' the collection from scope.  This will free up all the form

' variables and cause each of the

' Customers forms to close.

For Each frm In colCustomerForms

frm.MultiInstKey = ""

Next

End Sub


Deactivate Event Procedure for the CustomerPhoneList Form

Private Sub Form_Deactivate()

'  Used by Solutions to hide toolbar that includes Show Me button.

'  Hide Custom Form View toolbar.

'  Show built-in Form View toolbar.

DoCmd.ShowToolbar "Custom Form View", acToolbarNo

DoCmd.ShowToolbar "Form View", acToolbarWhereApprop

End Sub


Close Event Procedure for the Customers Form

Private Sub Form_Close()

' Remove this instance of the form from the collection run by

' CustomerPhoneList.

' The CustomerPhoneList form sets a user-defined property on this

' form to let it know that it's a multiple-instance form.

' This procedure checks that property to see if this item should be

' deleted from the collection.

If strMultiInstKey <> "" Then

Forms![CustomerPhoneList].RemoveFormInstance strMultiInstKey

End If

End Sub


Open Event Procedure for the Customers Form

Private Sub Form_Open(Cancel As Integer)

' Initialize strMultiInstKey.

strMultiInstKey = ""

End Sub


Click Event Procedure for the View Command Button

Private Sub View_Click()

Dim frmNewInst As Form_Customers

Const conFormDoesNotExist = 5

On Error Resume Next

' Check to see if instance of the form exists

' in the collection already.

Set frmNewInst = colCustomerForms(Me!CustomerID)

If Err = conFormDoesNotExist Then

' Form does not exist.

Set frmNewInst = New Form_Customers

'Set form properties, including custom MultiInstKey property.

frmNewInst.MultiInstKey = Me!CustomerID

frmNewInst.AllowAdditions = False

frmNewInst.AllowDeletes = False

frmNewInst.NavigationButtons = False

frmNewInst.RecordSelectors = False

frmNewInst.RecordSource = _

"Select * from Customers Where Customers.CustomerID = """ _

& Me!CustomerID & """;"

frmNewInst.Requery

frmNewInst.Visible = True

colCustomerForms.Add Item:=frmNewInst, Key:=Me!CustomerID

Else

' Form exists.  Switch focus to it.

frmNewInst.SetFocus

DoCmd.Restore

End If

End Sub


Highlights of the CustomersDialog Form

The CustomersDialog form shows how to use Visual Basic for applications to fill the contents of a list box and let users select multiple items from the list. This technique makes the CustomersDialog form work:

Using a function to create a multi-select list box.


Use a Function to Create a Multi-Select List Box

When a user closes a main form that displays multiple instances of a second form, you may want the user to be able to choose instances of the second form to keep open. For example, when the CustomerPhoneList form closes, the CustomersDialog form displays a list box showing the customer records currently displayed in instances of the Customers form. The user can select one or more customer records to keep open.

Overview

Create a CustomersDialog form that lists currently displayed customer records in a list box. Create a function to fill the list box. Add a command button to the form that closes the CustomersDialog form but keeps selected records open in instances of the Customers form.

Objects Used in This Example

Object	Name
Table	Customers
Form	CustomerPhoneList, Customers, CustomersDialog
Step by Step

1	Create the CustomersDialog form.

a)	Set the following form properties.

Property	Setting
DefaultView	Single Forms
ViewsAllowed	Both
RecordSelectors	No
NavigationButtons	No
AutoResize	Yes
AutoCenter	Yes
PopUp	Yes
Modal	Yes
BorderStyle	Dialog
b)	In the detail section, create a label giving instructions on using the dialog box.

c)	In the detail section, create the lstForms list box and the cmdOk command button.

2	In the Form_CustomerPhoneList module, create the ReturnCollection function.

3	In the Form_Customers module, create the KeepMeAlive Sub procedure.

4	In the Declarations section of the Form_CustomersDialog module, type the following:

Dim colCustomerForms As Collection

5	In the Form_CustomersDialog module, create the lstForm_Fill function.

6	Set the RowSourceType property of the lstForms list box to lstForm_Fill.

7	Set the OnClick property of the cmdOk command button to [Event Procedure].

Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with forms	Chapter 3, "Using Forms to Collect, Display, and Filter Information"


ReturnCollection Function for the Form_CustomerPhoneList Module

Public Function ReturnCollection() As Collection

'Returns the collection of open Customers forms.

Set ReturnCollection = colCustomerForms

End Function


KeepMeAlive Sub Procedure for the Form_Customers Module

Public Sub KeepMeAlive(frmWhoAmI As Form_Customers)

' Indicates that this form should be kept open

' when CustomersDialog closes.

Set frmCustomers = frmWhoAmI

End Sub


lstForm_Fill function

Function lstForms_Fill(ctl As Control, lngID As Long, lngRow As Long, lngCol As Long, intCode As Integer) As Variant

Dim frm As Form_Customers

'Error if CustomerPhoneList form isn't open.

Const conFormNotOpen = 2450

On Error GoTo Fill_Error

Select Case intCode

Case acLBInitialize         'Initialize the list box.

Set colCustomerForms = Forms![CustomerPhoneList].ReturnCollection

lstForms_Fill = True

Case acLBOpen               'Open.

lstForms_Fill = Timer

Case acLBGetRowCount        'Get the number of rows.

lstForms_Fill = colCustomerForms.Count

Case acLBGetColumnCount     'Get the number of columns.

lstForms_Fill = 1

Case acLBGetColumnWidth     'Get the column width.

lstForms_Fill = -1       'Use the default width.

Case acLBGetValue           'Get the data.

Set frm = colCustomerForms(lngRow + 1)

lstForms_Fill = frm![CompanyName]

End Select

Exit Function

Fill_Error:

If Err = conFormNotOpen Then

Exit Function

Else

MsgBox Err.Description

Exit Function

End If

End Function


Click Event Procedure for the cmdOk Command Button

Private Sub cmdOk_Click()

Dim intIndex As Integer

Dim frm As Form_Customers

Dim varFormName As Variant

Const conObjectRequired = 424

On Error GoTo Close_Error

' Set the value of the user-defined KeepMeAlive property

' of the forms that should stay open.

intIndex = 1

' Determine which list box items are selected.

For Each varFormName In lstForms.ItemsSelected

Set frm = colCustomerForms(intIndex)

frm.KeepMeAlive frm

intIndex = intIndex + 1

Next varFormName

DoCmd.Close acForm, "CustomersDialog"

Exit Sub

Close_Error:

If Err = conObjectRequired Then

DoCmd.Close acForm, "CustomersDialog"

Exit Sub

Else

MsgBox Err.Description

Exit Sub

End If

End Sub


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 in Building Applications with Microsoft Access for Windows 95.

Tips for Using Solutions

All of the examples in this application are available from the Solutions main form, which is displayed when you open the Solutions database. To open a sample form or report, first select a category of examples in the upper list box of the Solutions form, and then double-click the example in the lower list box, or select the example and click OK. 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 the Show Me topic for a form in Form view or a report in Print Preview, press F1 or click Show Me 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 Help Topics in the Show Me window and then click the Index or Find tab.

To print a topic, click Options and then click Print Topic.

To copy text or Visual Basic for applications code from a topic, use the right mouse button to select the text you want to copy, and then click Copy on the shortcut menu.

To keep the Show Me window on top, click Options and then click Keep Help On Top.


Highlights of the LimitListContents Form

The LimitListContents form shows how to display only the products in a selected category. This technique makes the LimitListContents form work:

Using an option group to limit the values displayed in a list box.


Limit the Contents of a List Based on the Value Selected in an Option Group

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 LimitListContents form, only products in that category are displayed.

Overview

Include the CategoryID field in the underlying query for the SelectProduct list box. Set the criteria for CategoryID to an expression that limits the list to those products whose CategoryID value matches the value selected in the SelectCategory option group. Requery the product list after you select a category.

Objects Used in This Example

Type	Name
Table	Products
Query	LimitProductList2
Form	LimitListContents
Step by Step

1	Create the LimitProductList2 query.

a)	Use the following fields from the Products table.

Field	Sort
ProductName	Ascending
ProductID	--
CategoryID	--
b)	In the Criteria cell of the categoryID field, type:

Forms!LimitListContents!SelectCategory

2	Create the LimitListContents form.

a)	Set the following form properties.

Property	Setting
RecordSource	Products
Caption	Limit List Contents
DefaultView	Single Form
ViewsAllowed	Form
ScrollBars	Neither
RecordSelectors	No
NavigationButtons	No
AutoCenter	Yes
ShortcutMenu	No
b)	Create an unbound option group, and set its Name property to SelectCategory.

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 the corresponding CategoryID value. For example, the CategoryID value for Beverages is 1, so you should set the OptionValue property of the Beverages option button to 1.

e)	Set the DefaultValue property of the option group to 1.

f)	Create an event procedure for the AfterUpdate property of the option group.

g)	Create an unbound list box, and set its properties as follows.

Property	Value
Name	SelectProduct
RowSourceType	Table/Query
RowSource	LimitProductList2
ColumnCount	2
ColumnWidths	;0"
BoundColumn	2
Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


AfterUpdate Event Procedure for the SelectCategory Option Group

Private Sub SelectCategory_AfterUpdate()

'  Requery SelectProduct list box.

Me!SelectProduct.Requery

End Sub


Highlights of the AddAllToList Form

The AddAllToList form shows how to add "(All)" to the list in a list box or combo box. This technique makes the AddAllToList form work:

Adding "(All)" to a list.


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 AddAllToList form, the list in the SelectCustomer combo box includes "(All)."

Overview

Create a function that adds "(All)" to the list in a list box or combo 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	AddAllToList
Module	AddAllToListModule
Step by Step

1	Create the AddAllToListModule module.

Create the AddAllToList function.

2	Create the AddAllToList form.

a)	Set the following form properties.

Property	Setting
RecordSource	Customers
Caption	Add All To List
DefaultView	Single Form
ViewsAllowed	Form
ScrollBars	Neither
RecordSelectors	No
AutoCenter	Yes
ShortcutMenu	No
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	SelectCustomer
RowSourceType	AddAllToList
RowSource	CustomerList
ColumnCount	2
ColumnWidths	3";0"
BoundColumn	2
LimitToList	Yes
Tag	1
AfterUpdate	[Event Procedure]
d)	In the detail section of the form, add text boxes for the CustomerID, CompanyName, ContactName, Phone, and Fax fields.

Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


AddAllToList Function

Function AddAllToList(ctl As Control, lngID As Long, lngRow As Long, _

lngCol As Long, intCode As Integer) As Variant

' Adds "(All)" to the top of a combo box or list box.

' You can add "(All)" in a different column of the combo box

' or list box by setting the control's Tag property to a different

' column number, or display text other than "(All)" by appending

' a semicolon(;) and the text you want to display. For example,

' setting the Tag property to "2;<None>" displays "<None>"

' in the second column of the list.

Static dbs As Database, rst As Recordset

Static lngDisplayID As Long

Static intDisplayCol As Integer

Static strDisplayText As String

Dim intSemiColon As Integer

On Error GoTo Err_AddAllToList

Select Case intCode

Case acLBInitialize

' See if function is already in use.

If lngDisplayID <> 0 Then

MsgBox "AddAllToList is already in use by another _

control!"

AddAllToList = False

Exit Function

End If

' Parse the display column and display text from Tag

' property.

intDisplayCol = 1

strDisplayText = "(All)"

If Not IsNull(ctl.Tag) Then

intSemiColon = InStr(ctl.Tag, ";")

If intSemiColon = 0 Then

intDisplayCol = Val(ctl.Tag)

Else

intDisplayCol = Val(Left(ctl.Tag, intSemiColon - 1))

strDisplayText = Mid(ctl.Tag, intSemiColon + 1)

End If

End If

' Open the recordset defined in the RowSource property.

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset(ctl.RowSource, dbOpenSnapshot)

' Record and return the lngID for this function.

lngDisplayID = Timer

AddAllToList = lngDisplayID

Case acLBOpen

AddAllToList = lngDisplayID

Case acLBGetRowCount

' Return number of rows in recordset.

rst.MoveLast

AddAllToList = rst.RecordCount

Case acLBGetColumnCount

' Return number of fields (columns) in recordset.

AddAllToList = rst.Fields.Count

Case acLBGetColumnWidth

AddAllToList = -1

Case acLBGetValue

If lngRow = 0 Then

If lngCol = intDisplayCol - 1 Then

AddAllToList = strDisplayText

Else

AddAllToList = Null

End If

Else

rst.MoveFirst

rst.Move lngRow

AddAllToList = rst(lngCol)

End If

Case acLBEnd

lngDisplayID = 0

rst.Close

End Select

Bye_AddAllToList:

Exit Function

Err_AddAllToList:

MsgBox Err.Description, 16, "AddAllToList"

AddAllToList = False

Resume Bye_AddAllToList

End Function


AfterUpdate Event Procedure for the SelectCustomer Combo Box

Private Sub SelectCustomer_AfterUpdate()

'  Return record(s) that match value selected in SelectCustomer combo box.

If Me!SelectCustomer.Column(1) = "" Then

DoCmd.ShowAllRecords

Else

DoCmd.ApplyFilter , "CustomerID = Forms!AddAllToList!SelectCustomer"

End If

End Sub


Highlights of the SolutionsIntro Form

The SolutionsIntro 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 SolutionsIntro 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 that users can choose not to display again. For example, the SolutionsIntro form is displayed after the Startup form and before the Solutions main form every time you start the Solutions sample application. If you don't want it displayed anymore, you can select the check box at the bottom of the form.

Overview

Create a custom database property to indicate whether or not the introductory form should be displayed. Create the introductory form with the following controls: labels that display information about your application, an unbound check box, and a command button that closes the form. Create a main form. In the OnClick property of the command button, create an event procedure that sets the custom property to False if the check box is selected. In the OnLoad property of the introductory form, create an event procedure that closes the form and opens the main form if the custom property is set to False.

Objects Used in This Example

Object	Name
Form	Solutions, SolutionsIntro
Step by Step

1	Create the DisplaySolutionsIntro custom database property.

a)	With the database open, click Database Properties on the File menu.

b)	Click the Custom tab.

c)	In the Name box, type DisplaySolutionsIntro.

2	Create the SolutionsIntro form.

a)	Set the following form properties.

Property	Setting
Caption	Solutions Intro
DefaultView	Single Form
ViewsAllowed	Form
AllowEdits	Yes
AllowDeletions	Yes
AllowAdditions	No
DataEntry	No
ScrollBars	Neither
RecordSelectors	No
NavigationButtons	No
AutoCenter	Yes
BorderStyle	Thin
MinMaxButtons	None
CloseButton	Yes
OnLoad	[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	HideSolutionsIntro
StatusBarText	Don't display this form again.
d)	Create a command button, and set the following properties.

Property	Setting
Name	OK
Caption	OK
Default	Yes
ControlTipText	Close form.
OnClick	[Event Procedure]
3	Create the Solutions form.

Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


Load Event Procedure for the SolutionsIntro Form

Private Sub Form_Load()

' Skip to the Solutions form if the user previously

' chose not to see the SolutionsIntro form again.

On Error GoTo Form_Load_Error

Dim dbs As Database

Dim doc As Document

Dim prp As Property

Const conPropertyNotFound = 3270

' If user has previously turned off the SolutionsIntro form,

' close the form and go directly to the Solutions form.

Set dbs = CurrentDb()

Set doc = dbs.Containers("Databases")!UserDefined

If doc.Properties!DisplayStartupForm = False Then

DoCmd.Close acForm, "SolutionsIntro"

DoCmd.OpenForm "Solutions", , , , acNormal

End If

' If database is read-only, disable the HideSolutionsIntro

' check box.

If CurrentDb().Updatable = False Then

HideSolutionsIntro.Enabled = False

End If

Form_Load_Exit:

Exit Sub

Form_Load_Error:

' Create the DisplayStartupForm property if it isn't there.

If Err = conPropertyNotFound And CurrentDb().Updatable Then

Set prp = doc.CreateProperty("DisplayStartupForm", dbBoolean, True)

doc.Properties.Append prp

Resume

Else

MsgBox Err.Description, vbCritical

CloseCurrentDatabase

Resume Form_Load_Exit

End If

End Sub


Click Event Procedure of the OK Command Button

Private Sub OK_Click()

' Open Solutions form and close this form.

Dim dbs As Database

Dim doc As Document

On Error GoTo Err_OK_Click

' If user checked the box, set the DisplaySolutionsIntro custom

' database property to False.

Set dbs = CurrentDb()

Set doc = dbs.Containers("Databases")!UserDefined

If HideSolutionsIntro = True Then

doc.Properties!DisplayStartupForm = False

End If

' Close this form and open the Solutions form.

DoCmd.Close acForm, "SolutionsIntro"

DoCmd.OpenForm "Solutions"

Exit_OK_Click:

Exit Sub

Err_OK_Click:

MsgBox Err.Description

CloseCurrentDatabase

Resume Exit_OK_Click

End Sub


Highlights of the SalesAverages Report

The SalesAverages report shows how to present multiple facts for each row heading in a crosstab report. This technique makes the SalesAverages report work:

Creating a multiple-fact crosstab report.


Create a Multiple-Fact Crosstab Report

You can create a crosstab report that prints multiple facts for each row heading. For example, the SalesAverages 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 LastName header to use as a flag to determine which row heading to print. In the OnFormat property of the LastName header, create an event procedure that uses the NextRecord property to print the row headings in two columns. In the OnFormat property of the ShippedDate header, create an event procedure 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	Name
Table	Employees, Orders
Query	OrderSubtotals, SalesAverages
Report	SalesAverages
Step by Step

1	Import the OrderSubtotals query from the Northwind sample database, if you haven't already done so.

2	Create the SalesAverages query.

a)	Use the fields from the following tables and query.

Table/Query	Field/Expression	Total
Employees table	LastName FirstName	Group By Group By
Orders table	OrderID ShippedDate YearToAverage:  Year([ShippedDate])	Group By Group By Group By
OrderSubtotals query	Subtotal	Group By
b)	In the Criteria cell of the YearToAverage expression, type "1994".

3	Create the SalesAverages report.

a)	In the Sorting And Grouping dialog box, set the following properties.

Field/Expression	Sort Order	Group Property
LastName	Ascending	GroupHeader:  Yes GroupFooter:  Yes GroupOn:  Each Value GroupInterval:  1 KeepTogether:  No
ShippedDate	Ascending	GroupHeader:  Yes GroupFooter:  No GroupOn:  Month GroupInterval:  1 KeepTogether:  No
b)	Set the Width property of the report to 9.7813".

c)	Set the following Page Setup options.

Tab	Option	Setting
Page	Orientation	Landscape
Margins	Margins	Left:  0.4000" Right:  0.4000" Top:  1" Bottom:  1"
Layout	Grid Settings	Items Across:  14
		Column Spacing:  0.1"
	Item Size	Width:  0.6" Height:  0"
	Layout Items	Across, Then Down
d)	In the Page header, paste the labels for the type of currency and the names of the months.

e)	In the LastName header, create the following text boxes, one below the other.

Name	ControlSource
SalespersonLastName	=[LastName]&","
FirstName	FirstName
f)	In the LastName header, create the following labels on top of the text boxes you created in step 3e.

Name	Caption
LMinimum	Minimum:
LAverage	Average:
LMaximum	Maximum:
g)	In the LastName header, create the following text boxes, and set their properties as follows.

Name	Property
PrintWhat	Visible: No
ColumnWidth	ControlSource:  =(0.6+0.1)*1440 Format: General Number Visible: No
LeftMargin	ControlSource:  =0.4*1440 Format:  General Number Visible:  No
h)	In the ShippedDate header, create the following text boxes, one below the other. For all three, set the Format property to Standard, the DecimalPlaces property to 0, and the ControlSource property as follows.

Name	ControlSource
MonthMinimum	=Min([Subtotal])
MonthAverage	=Avg([Subtotal])
MonthMaximum	=Max([Subtotal])
i)	Set the following section properties.

Section	Property	Setting
Page header	OnFormat	[Event Procedure]
LastName header	KeepTogether OnFormat	Yes [Event Procedure]
ShippedDate header	KeepTogether OnFormat	Yes [Event Procedure]
Detail section	Height	0"
LastName footer	NewRowOrCol Height	After Section 0"
Report footer	Height	0"
Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


Format Event Procedure for the Page Header

Private Sub PageHeader1_Format(Cancel As Integer, FormatCount As Integer)

' Set PrintWhat text box to 0 at top of page.

Me!PrintWhat = 0

End Sub


Format Event Procedure for the LastName Header

Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As Integer)

'  Print information in row headings in correct order.

'  Print SalespersonLastName and FirstName text boxes,

'  hide Minimum, Average, and Maximum labels,

'  set PrintWhat text box to -1, and don't advance to next record.

If Me!PrintWhat = 0 Then

Me!SalespersonLastName.Visible = True

Me!FirstName.Visible = True

Me!LMinimum.Visible = False

Me!LAverage.Visible = False

Me!LMaximum.Visible = False

Me!PrintWhat = -1

Me.NextRecord = False

'  Hide SalespersonLastName and FirstName text boxes,

'  print Minimum, Average, and Maximum labels,

'  and set PrintWhat text box to 0.

Else

Me!SalespersonLastName.Visible = False

Me!FirstName.Visible = False

Me!LMinimum.Visible = True

Me!LAverage.Visible = True

Me!LMaximum.Visible = True

Me!PrintWhat = 0

End If

End Sub


Format Event Procedure for the ShippedDate Header

Private 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!LeftMargin + (Month(Me!ShippedDate) + 1) _

* Me!ColumnWidth Then

Me.NextRecord = False

Me.PrintSection = False

End If

End Sub


Highlights of the CustomerPhoneList Report

The CustomerPhoneList report shows how to identify the first and last companies printed on each page. This technique makes the CustomerPhoneList report work:

Printing the first and last entries on a page in the page header.


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 CustomerPhoneList report, 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 FirstEntry and LastEntry text boxes.

Objects Used in This Example

Object	Name
Table	Customers
Report	CustomerPhoneList
Step by Step

Create the CustomerPhoneList report.

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 dialog box, select CompanyName in the Field/Expression column, and set Sort Order to Ascending.

d)	In the detail section, create the CompanyName, ContactName, 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	FirstEntry	--
	LastEntry	--
Page footer	PageNumber	=Page & " of " & Pages & " Pages"
g)	In the Declarations section of the Report_CustomerPhoneList module, declare variables.

h)	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-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


Declarations Section of the Report_CustomerPhoneList Module

Option Compare Database   'Use database order for string comparisons

Option Explicit

'  Array for last record on each page and last record in recordset.

Dim gLast() As String

'  Flag for number of passes through report.

Dim gLastPage As Integer


Format Event Procedure for the Page Header

Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)

'  During second pass, fill in FirstEntry

'  and LastEntry text boxes.

If gLastPage = True Then

Reports!CustomerPhoneList!FirstEntry = _

Reports!CustomerPhoneList!CompanyName

Reports!CustomerPhoneList!LastEntry = _

gLast(Reports!CustomerPhoneList.Page)

End If

End Sub


Format Event Procedure for the Page Footer

Private Sub PageFooter2_Format(Cancel As Integer, FormatCount As Integer)

'  During first pass, increase size of array

'  and enter last record on page into array.

If Not gLastPage Then

ReDim Preserve gLast(Reports!CustomerPhoneList.Page + 1)

gLast(Reports!CustomerPhoneList.Page) = _

Reports!CustomerPhoneList!CompanyName

End If

End Sub


Format Event Procedure for the Report Footer

Private Sub ReportFooter4_Format(Cancel As Integer, FormatCount As Integer)

Dim dbs As Database

Dim rst As Recordset

'  Set flag after first pass has been completed.

gLastPage = True

'  Open recordset for report.

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("Customers")

'  Move to last record in recordset.

rst.MoveLast

'   Enter last record into array.

ReDim Preserve gLast(Reports!CustomerPhoneList.Page + 1)

gLast(Reports!CustomerPhoneList.Page) = rst.CompanyName

End Sub


Highlights of the Solutions Form

The Solutions form shows how to access all the examples in the Solutions sample application. These techniques make the Solutions form work:

Linking tables at Startup.

Opening a form or report from a main form.

This technique enhances the form:

Closing all database objects when you exit the application.


Link Tables at Startup

When you use multiple databases for an application, you can ensure that connection information for linked tables is updated when the application is started. When the Solutions form opens, for example, it runs code that checks the links to tables in the Northwind sample database and refreshes them if necessary.

Overview

Create functions that check the links to external tables and restore them if necessary. Create a RelinkTables function to look for the Northwind database in the Samples subfolder of the folder where Microsoft Access is installed. Create a FindNorthwind function to display the Open dialog box if the Northwind database isn't found so that the user can specify the location of the database. Call the functions in the Solutions form's Form_Load event procedure.

Objects Used in This Example

Object	Name
Form	Solutions
Module	RefreshTableLinks
Step by Step

1	Create the RefreshTableLinks module.

a)	Create the CheckLinks function.

b)	Create the RelinkTables function.

c)	Create the RefreshLinks function.

d)	Add code to the module's Declarations section that enables use of the Microsoft Windows Open dialog box to locate the Northwind sample database.

e)	Create the FindNorthwind function.

f)	Create the MSA_CreateFilterString function.

g)	Create the MSA_GetOpenFilename function.

h)	Create the MSAOF_to_OF Sub procedure.

i)	Create the OF_to_MSAOF Sub procedure.

2	Set the Solutions form's OnLoad property to [Event Procedure].

Cross-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


CheckLinks Function

Public Function CheckLinks() As Boolean

' Check links to the Northwind database; returns true if links are OK.

Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb()

' Open linked table to see if connection information is correct.

On Error Resume Next

Set rst = dbs.OpenRecordset("Products")

' If there's no error, return True.

If Err = 0 Then

CheckLinks = True

Else

CheckLinks = False

End If

End Function


RelinkTables Function

Public Function RelinkTables() As Boolean

' Tries to refresh the links to the Northwind database.

' Returns True if successful.

Const conMaxTables = 8

Const conNonExistentTable = 3011

Const conNotNorthwind = 3078

Const conNwindNotFound = 3024

Const conAccessDenied = 3051

Const conReadOnlyDatabase = 3027

Const conAppTitle = "Solutions"

Dim strAccDir As String

Dim strSearchPath As String

Dim strFileName As String

Dim intError As Integer

Dim strError As String

' Get name of directory where Msaccess.exe is located.

strAccDir = SysCmd(acSysCmdAccessDir)

' Get the default sample database path.

If Dir(strAccDir & "Samples\.") = "" Then

strSearchPath = strAccDir

Else

strSearchPath = strAccDir & "Samples\"

End If

' Look for the Northwind database.

If (Dir(strSearchPath & "Northwind.mdb") <> "") Then

strFileName = strSearchPath & "Northwind.mdb"

Else

' Can't find Northwind, so display the File Open dialog.

MsgBox "Can't find linked tables in the Northwind database. " _

& "You must locate Northwind in order to use " _

& conAppTitle & ".", vbExclamation

strFileName = FindNorthwind(strSearchPath)

If strFileName = "" Then

strError = "Sorry, you must locate Northwind to open " _

& conAppTitle & "."

GoTo Exit_Failed

End If

End If

' Fix the links.

If RefreshLinks(strFileName) Then   ' It worked!

RelinkTables = True

Exit Function

End If

' If it failed, display an error.

Select Case Err

Case conNonExistentTable, conNotNorthwind

strError = "File '" & strFileName & _

"' does not contain the required Northwind tables."

Case Err = conNwindNotFound

strError = "You can't run " & conAppTitle & _

" until you locate the Northwind database."

Case Err = conAccessDenied

strError = "Couldn't open " & strFileName & _

" because it is read-only or located on a read-only share."

Case Err = conReadOnlyDatabase

strError = "Can't reattach tables because " & conAppTitle & _

" is read-only or is located on a read-only share."

Case Else

strError = Err.Description

End Select

Exit_Failed:

MsgBox strError, vbCritical

RelinkTables = False

End Function


RefreshLinks Function

Private Function RefreshLinks(strFileName As String) As Boolean

' Refresh links to the supplied database. Return True if successful.

Dim dbs As Database

Dim intCount As Integer

Dim tdf As TableDef

' Loop through all tables in the database.

Set dbs = CurrentDb()

For intCount = 0 To dbs.TableDefs.Count - 1

Set tdf = dbs.TableDefs(intCount)

' If the table has a connect string, it's a linked table.

If Len(tdf.Connect) > 0 Then

tdf.Connect = ";DATABASE=" & strFileName

Err = 0

On Error Resume Next

tdf.RefreshLink         ' Relink the table.

If Err <> 0 Then

RefreshLinks = False

Exit Function

End If

End If

Next intCount

RefreshLinks = True        ' Relinking complete.

End Function


Declarations Section for the RefreshTableLinks Module

Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _

"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Boolean

Declare Function GetSaveFileName Lib "comdlg32.dll" Alias _

"GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Boolean

Type MSA_OPENFILENAME

' Filter string used for the Open dialog box filters.

' Use MSA_CreateFilterString() to create this.

' Default = All Files, *.*

strFilter As String

' Initial Filter to display.

' Default = 1

lngFilterIndex As Long

' Initial directory for the dialog to open in.

' Default = Current working directory.

strInitialDir As String

' Initial file name to populate the dialog with.

' Default = "".

strInitialFile As String

strDialogTitle As String

' Default extension to append to file if user didn't specify one.

' Default = System Values (Open File, Save File).

strDefaultExtension As String

' Flags (see constant list) to be used.

' Default = no flags.

lngFlags As Long

' Full path of file picked. On OpenFile, if the user picks a

' nonexistent file, only the text in the "File Name" box is

' returned.

strFullPathReturned As String

' Filename of file picked.

strFileNameReturned As String

' Offset in full path (strFullPathReturned) where the file name

' (strFileNameReturned) begins.

intFileOffset As Integer

' Offset in full path (strFullPathReturned) where the file extension

' begins.

intFileExtension As Integer

End Type

Const ALLFILES = "All Files"

Type OPENFILENAME

lStructSize As Long

hwndOwner As Long

hInstance As Long

lpstrFilter As String

lpstrCustomFilter As String

nMaxCustrFilter As Long

nFilterIndex As Long

lpstrFile As String

nMaxFile As Long

lpstrFileTitle As String

nMaxFileTitle As Long

lpstrInitialDir As String

lpstrTitle As String

flags As Long

nFileOffset As Integer

nFileExtension As Integer

lpstrDefExt As String

lCustrData As Long

lpfnHook As Long

lpTemplateName As String

End Type

Const OFN_ALLOWMULTISELECT = &H200

Const OFN_CREATEPROMPT = &H2000

Const OFN_EXPLORER = &H80000

Const OFN_FILEMUSTEXIST = &H1000

Const OFN_HIDEREADONLY = &H4

Const OFN_NOCHANGEDIR = &H8

Const OFN_NODEREFERENCELINKS = &H100000

Const OFN_NONETWORKBUTTON = &H20000

Const OFN_NOREADONLYRETURN = &H8000

Const OFN_NOVALIDATE = &H100

Const OFN_OVERWRITEPROMPT = &H2

Const OFN_PATHMUSTEXIST = &H800

Const OFN_READONLY = &H1

Const OFN_SHOWHELP = &H10


FindNorthwind Function

Private Function FindNorthwind(strSearchPath) As String

' Displays the open file dialog box for the user to locate

' the Northwind database. Returns the full path to Northwind.

Dim msaof As MSA_OPENFILENAME

' Set options for the dialog box.

msaof.strDialogTitle = "Where Is Northwind?"

msaof.strInitialDir = strSearchPath

msaof.strFilter = MSA_CreateFilterString("Databases", "*.mdb")

' Call the Open File dialog routine.

MSA_GetOpenFileName msaof

' Return the path and file name.

FindNorthwind = Trim(msaof.strFullPathReturned)

End Function


MSA_CreateFilterString Function

Private Function MSA_CreateFilterString(ParamArray varFilt() As Variant) As String

' Creates a filter string from the passed-in arguments.

' Returns "" if no args are passed in.

' Expects an even number of args (filter name, extension), but

' if an odd number is passed in, it appends *.*

Dim strFilter As String

Dim intRet As Integer

Dim intNum As Integer

intNum = UBound(varFilt)

If (intNum <> -1) Then

For intRet = 0 To intNum

strFilter = strFilter & varFilt(intRet) & Chr$(0)

Next

If intNum Mod 2 = 0 Then

strFilter = strFilter & "*.*" & Chr$(0)

End If

End If

MSA_CreateFilterString = strFilter

End Function


MSA_GetOpenFilename Function

Private Function MSA_GetOpenFileName(msaof As MSA_OPENFILENAME) As Integer

' Opens the Open dialog box.

Dim of As OPENFILENAME

Dim intRet As Integer

MSAOF_to_OF msaof, of

intRet = GetOpenFileName(of)

If intRet Then

OF_to_MSAOF of, msaof

End If

MSA_GetOpenFileName = intRet

End Function


MSAOF_to_OF Sub Procedure

Private Sub MSAOF_to_OF(msaof As MSA_OPENFILENAME, of As OPENFILENAME)

' This sub converts from the friendly MSAccess structure

' to the win32 structure.

Dim strFile As String * 256

' Initialize some parts of the structure.

of.hwndOwner = 0

of.hInstance = 0

of.lpstrCustomFilter = ""

of.nMaxCustrFilter = 0

If msaof.strFilter = "" Then

of.lpstrFilter = ALLFILES & Chr$(0) & "*.*" & Chr$(0)

Else

of.lpstrFilter = msaof.strFilter

End If

of.nFilterIndex = msaof.lngFilterIndex

strFile = msaof.strInitialFile

of.lpstrFile = strFile

of.nMaxFile = 255

of.lpstrFileTitle = String$(256, 0)

of.nMaxFileTitle = 255

of.lpstrTitle = msaof.strDialogTitle

of.lpstrInitialDir = msaof.strInitialDir

of.lpstrDefExt = msaof.strDefaultExtension

of.flags = msaof.lngFlags

of.lStructSize = Len(of)

End Sub


OF_to_MSAOF Sub Procedure

Private Sub OF_to_MSAOF(of As OPENFILENAME, msaof As MSA_OPENFILENAME)

' This sub converts from the win32 structure

' to the friendly MSAccess structure.

msaof.strFullPathReturned = _

Left$(of.lpstrFile, InStr(of.lpstrFile, Chr$(0)))

msaof.strFileNameReturned = of.lpstrFileTitle

msaof.intFileOffset = of.nFileOffset

msaof.intFileExtension = of.nFileExtension

End Sub


Load Event Procedure for the Solutions Form

Private Sub Form_Load()

' Check links to tables in Northwind database.

On Error GoTo Form_Load_Error

' Check to see if Northwind is available.

If CheckLinks() = False Then

' Try to relink the tables; if it fails, shut down.

If RelinkTables() = False Then

DoCmd.Close acForm, "Startup"

CloseCurrentDatabase

End If

End If

Form_Load_Exit:

Exit Sub

Form_Load_Error:

MsgBox Err.Description

CloseCurrentDatabase

Resume Form_Load_Exit

End Sub


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 an OK command button that opens the sample form or report selected in the examples list box. Create an Exit command button that closes the Solutions application.

Objects Used in This Example

Object	Name
Table	ExampleObjects, Examples, ExampleTopics
Query	ExampleList, ExampleTopicList
Form	Solutions
Step by Step

1	Create the ExampleTopicList query.

Table	Field	Show
ExampleTopics	TopicID Description SortOrder (Ascending)	Yes Yes No
2	Create the ExampleList query.

a)	Use the fields from the following tables.

Table	Field	Show
Examples	Description (Ascending)	Yes
ExampleObjects	ObjectName	Yes
ExampleObjects	ObjectType	Yes
Examples	TopicID	Yes
b)	In the Criteria cell of the TopicID field, type Forms!Solutions!SelectTopic.

3	Create the Solutions form.

a)	Create an unbound form, and set its properties as follows.

Property	Setting
Caption	Solutions
DefaultView	Single Form
ViewsAllowed	Form
ScrollBars	Neither
RecordSelectors	No
NavigationButtons	No
AutoCenter	Yes
ShortcutMenu	No
b)	Create an unbound list box, and set its properties as follows.

Property	Setting
Name	SelectTopic
RowSourceType	Table/Query
RowSource	ExampleTopicList
ColumnCount	2
ColumnWidths	0";5"
BoundColumn	1
StatusBarText	Select a category of examples.
AfterUpdate	[Event Procedure]
c)	Create another unbound list box, and set its properties as follows.

Property	Setting
Name	SelectExample
RowSourceType	Table/Query
RowSource	ExampleList
ColumnCount	3
ColumnWidths	5";0";0"
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
ControlTipText	Display form or report for selected example.
OnClick	[Event Procedure]
Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with forms	Chapter 3, "Using Forms to Collect, Display, and Filter Information"


AfterUpdate Event Procedure of the SelectTopic List Box

Private Sub SelectTopic_AfterUpdate()

' Requery SelectExample list box.

Me!SelectExample.Requery

End Sub


DblClick Event Procedure of the SelectExample List Box

Private Sub SelectExample_DblClick(Cancel As Integer)

' Run event procedure in OK_Click sub.

OK_Click

End Sub


Click Event Procedure of the OK Command Button

Private Sub OK_Click()

' Open form or report for selected example.

' Open form for selected example.

If Me!SelectExample.Column(2) = "Form" Then

DoCmd.OpenForm Forms!Solutions!SelectExample.Column(1)

' Open report for selected example.

ElseIf Me!SelectExample.Column(2) = "Report" Then

DoCmd.OpenReport Forms!Solutions!SelectExample.Column(1), acPreview

End If

End Sub


Close All Database Objects When You Exit an Application

You can have all open database objects close automatically when you exit your application. 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 Exit 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	Add a command button to the Solutions form, and set its properties as follows.

Property	Setting
Name	Exit
Caption	Exit
ControlTipText	Exit the Solutions application.
OnClick	[Event Procedure]
Note   The Click event procedure for the Exit command button doesn't 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 more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"
Working with objects	Chapter 5, "Working with Objects and Collections"


CloseObject Function

Private Function CloseObject(strContainerName As String, intContainerType As Integer)

'  Close open database objects.

Dim dbs As Database, ctr As Container

Dim intX As Integer

Set dbs = CurrentDb()

Set ctr = dbs.Containers(strContainerName)

For intX = 0 To ctr.Documents.Count - 1

DoCmd.Close intContainerType, ctr.Documents(intX).Name

Next intX

End Function


Click Event Procedure of the Exit Command Button

Private Sub Exit_Click()

' Close all open database objects, and then exit application.

' Turn on error trapping.

On Error GoTo ErrHandler

Dim varTmp As Variant

varTmp = CloseObject("Tables", acTable)

varTmp = CloseObject("Tables", acQuery)

varTmp = CloseObject("Forms", acForm)

varTmp = CloseObject("Reports", acReport)

varTmp = CloseObject("Scripts", acMacro)

varTmp = CloseObject("Modules", acModule)

' Exit application.

CloseCurrentDatabase

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 AboutSolutions Dialog Box

The AboutSolutions form shows how to display an application's name, copyright information, and logo in a dialog box. This technique makes the AboutSolutions form work:

Creating an About dialog box.


Create an About Dialog Box

You can display information about your application in a dialog box that's available from a custom menu bar. The AboutSolutions dialog box is available from the Help menu of the EditProducts 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	AboutSolutions
Macro	EditProductsMenuBar_Help
Step by Step

1	Create the AboutSolutions form.

a)	Set the following form properties.

Property	Setting
Caption	About Solutions
DefaultView	Single Form
ViewsAllowed	Form
AllowEdits	No
AllowDeletions	No
AllowAdditions	No
ScrollBars	Neither
RecordSelectors	No
NavigationButtons	No
AutoCenter	Yes
BorderStyle	Dialog
MinMaxButtons	None
ShortcutMenu	No
Note   The Modal and PopUp properties in the AboutSolutions form in the Solutions sample application are set to No so that you can have access to menu commands and toolbar buttons when you open the form directly from the Solutions form. The form will have a dialog box border when the Modal and PopUp properties are set to Yes or when the Window Mode argument is set to Dialog.

b)	In the detail section, create labels that display your application's name, version number, and copyright information. Also, create an unbound 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
ControlTipText	Close About Solutions dialog box.
OnClick	[Event Procedure]
2	Create the ShowAboutSolutions macro, which opens the AboutSolutions form, and set the arguments of the OpenForm action as follows.

Argument	Setting
Form Name View Data Mode Window Mode	AboutSolutions Form Read Only Dialog
3	Add the A&bout Solutions macro to the end of the EditProductsMenuBar_Help macro group. For the RunMacro action, set the Form Name argument to ShowAboutSolutions. For information on creating a custom menu, see Create a Toolbar and Menu That Include Only Editing Tasks.

Tip  The Solutions application uses a custom menu bar only for the EditProducts form, so the AboutSolutions form is available from the Help menu only when the EditProducts 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-reference

For more information on the following subject, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Creating event procedures	Chapter 2, "Introducing Visual Basic"


OnClick Event Procedure for the OK Command Button

Private Sub OK_Click()

'  Close form.

DoCmd.Close

End Sub


Highlights of the Startup Form

The Startup form shows how to display a form automatically for a few seconds when you open an application. This technique makes the Startup form work:

Creating a startup form.


Create a Startup Form

When your application starts, you might want to display its name, copyright information, and logo for a brief time. In the Solutions sample application, 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. Create an event procedure for the OnOpen property to 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. Set the Startup option that designates the form as the Startup form.

Object Used in This Example

Object	Name
Form	Startup
Step by Step

1	Create the Startup form.

a)	Set the following form properties.

Property	Setting
DefaultView	Single Form
ViewsAllowed	Form
ScrollBars	Neither
RecordSelectors	No
NavigationButtons	No
AutoCenter	Yes
BorderStyle	None
ShortcutMenu	No
OnOpen	[Event Procedure]
OnTimer	[Event Procedure]
Note   So that you can open the Startup form and look at it in Solutions, the TimerInterval property is set to 7000 (milliseconds, or 7 seconds) in the event procedure of the OnOpen property and reset to 0 in the event procedure of the OnTimer property.

b)	In the detail section, create labels that display your application's name and copyright information. Also, create an unbound object frame that displays your application's logo.

2	Set the Display Form option to the name of the form.

a)	In the Database window, click Startup on the Tools menu.

b)	In the Display Form box, type Startup.

Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access for Windows 95.

Subject	Chapter
Setting Startup options	Chapter 1, "Application Basics"
Creating event procedures	Chapter 2, "Introducing Visual Basic"


Open Event Procedure for the Startup Form

Private Sub Form_Open(Cancel As Integer)

' If the Solutions main form is open,

' prevent this form from closing automatically.

If IsLoaded("Solutions") Then

Else

Me.TimerInterval = 7000

End If

End Sub


Timer Event Procedure for the Startup Form

Private Sub Form_Timer()

'  First If statement used by Solutions to reset TimerInterval property.

If Me.TimerInterval <> 0 Then

TimerInterval = 0

End If

'  Close startup form.

DoCmd.OpenForm "SolutionsIntro"

DoCmd.Close acForm, "Startup"

End Sub