• Choose a category
• All
• Classic-Fiction

Table 3-13. Combo box field settings on the sample form

Name

RowSource

cboField0

zstbl_rptCompaniesFields

cboField1

SELECT ReportFieldName FROM zstbl_rptCompaniesFields WHERE ReportFieldName <> Forms!frm_rptCompaniesSetup!cboField0;

cboField2

SELECT ReportFieldName FROM zstbl_rptCompaniesFields WHERE ReportFieldName <> Forms!frm_rptCompaniesSetup!cboField0 And ReportFieldName <> Forms!frm_rptCompaniesSetup!cboField1

cboField3

SELECT ReportFieldName FROM zstbl_rptCompaniesFields; WHERE ReportFieldName <> Forms!frm_rptCompaniesSetup!cboField0 And ReportFieldName <> Forms!frm_rptCompaniesSetup!cboField1 And ReportFieldName <> Forms!frm_rptCompaniesSetup!cboField2;

For all but the last combo box created in Step 3, create an event procedure attached to the AfterUpdate event of the control containing code similar to the following:

Private Sub cboField1_AfterUpdate( )

Me.cboField2.Requery

Call FixUpCombos(Me.cboField1)

End Sub

Replace "cboField1" with the name of the first combo box and "cboField2" with the name of the next combo box. Add the following code to the end of the first combo box's event procedure:

' Enable the buttons once you've chosen the group field.

If Not IsNull(Me.cboField0) Then

Me.cmdPrint.Enabled = True

Me.cmdPreview.Enabled = True

End If

Don't create an AfterUpdate event procedure for the last combo box.

Add one option group control alongside each combo box, as listed in Table 3-14. If you have more than four fields, add additional option groups, following the same naming pattern and assigning default values of 1 to each additional option group.

For each option group, add three option buttons, as listed in Table 3-15. The names of the option buttons don't matter.

Table 3-14. Option groups for the sample form

Name

Default value

grpSort0

0

grpSort1

1

grpSort2

1

grpsort3

1

Table 3-15. Option buttons

Label

Option value

No sort

1

Ascending

0

Descending

-1

Add a command button named cmdPreview with the caption "Preview" to the form. Attach the following code to its AfterUpdate event:

Private Sub cmdPreview_Click( )

Call HandlePrinting(acbcReport, acPreview)

End Sub

Add a command button named cmdPrint with the caption "Print" to the form. Attach the following code to its AfterUpdate event:

Private Sub cmdPrint_Click( )

Call HandlePrinting(acbcReport, acNormal)

End Sub

Edit the form's module (click on the Code button on the Report Design toolbar or choose the View → Code menu option) and enter the following lines of code in the module's declarations section:

Const acbcReport As String = "rptCompanies"

Const acbcTemp As String = "rptTemp"

Const acbcNoSort = 1

Const acbcMaxGroupFields = 1

Const acbcMaxSortFields = 3

With the form's module still open, add the following two procedures to the module (or copy them into your form's module from the sample database):

Private Sub FixUpCombos(ctlCalling As Control)

Dim intIndex As Integer

Dim intI As Integer

' Grab the last character of the calling

' control's name and convert to an integer

intIndex = CInt(Right(ctlCalling.Name, 1))

' Enable the next control if and only if the

' value of the calling control is non-null

If intIndex < acbcMaxSortFields Then

With Me("cboField" & intIndex + 1)

.Value = Null

.Enabled = (Not IsNull(ctlCalling))

End With

Me("grpSort" & intIndex + 1).Enabled = (Not IsNull(ctlCalling))

End If

' Disable all controls after the next one

If intIndex < acbcMaxSortFields - 1 Then

For intI = intIndex + 2 To acbcMaxSortFields

With Me("cboField" & intI)

.Value = Null

.Enabled = False

End With

With Me("grpSort" & intI)

.Value = acbcNoSort

.Enabled = False

End With

Next intI

End If

End Sub

Public Sub HandlePrinting(strReport As String, ByVal intPrintOption As Integer)

Dim intI As Integer

Dim intFieldCnt As Integer

Dim avarFields(0 To acbcMaxSortFields) As Variant

Dim aintSorts(0 To acbcMaxSortFields) As Integer

Dim