Dialog Form for report selection criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Northwind there is a form called... Sales Report Dialog.

I want a form that is pretty much the same, but I want to have 2 buttons
that allows for a selection. Is this feasible? I want one button that
allows for a choice of one parameter to print a report, and I want one button
that opens a choice of a different parameter.

To understand what I am saying you need to be looking at the Sales Report
Dialog from Northwind template.

Also, Is there someway to modify this form so that if ctrl or shift is held
they can select multiple parameters? from the list?

Thanks.
 
Hi TJ

This kind of form is very common in Access applications. If there are lots
of reports, each with lots of possible options, it can involve quite a bit
of code.

The basic idea is to place unbound controls on the form, where the user can
enter limiting criteria for their report.

Northwind uses an option group where the user selects which report they
want. The AfterUpdate event the enables/disables (or shows/hides) the
controls that are available to filter that report.

Then when the user clicks the Print or Preview button, you need to write the
code that loops through all these controls, and if they are Visible and
Enabled and not Null, then build them into the WhereCondition string you
will use when you OpenReport.

The aircode example below illustrates how to do that. It uses the names of
the objects on the Northwind dialog. It illustrates the different delimiters
you need for text fields (quotes) and dates (hashes), and how to format the
dates as required by JET. It tacks " AND " onto the end of each one so you
can expand the idea to as many as you need, and then removes the final
trailing " AND ". Some of my report forms use this approach for 30 or 40
possible controls used for filtering a few dozen different reports, and I
find the code easy to maintain when they want extra reports and filtering
options added later. HTH

--------------code starts------------
Private Sub cmdPreview_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

'Pick the report to print
Select Case me.ReportToPrint.Value
Case Me.EmployeeSalesByCountry.OptionValue
strReport = "Employee Sales by Country"
Case Me.SalesSummaries.OptionValue
...
End Select

'Build up the WhereCondition
With Me.cboFilterClient 'Number field example
If .Visible And .Enabled And Not IsNull(.Value) Then
strWhere = strWhere & "([ClientID] = " & .Value & ") AND "
End If
End With

With Me.txtFilterCity 'Text field example
If .Visible And .Enabled And Not IsNull(.Value) Then
strWhere = strWhere & "([City] = """ & .Value & """) AND "
End If
End With

With Me.txtFilterDate 'Date field example
If .Visible And .Enabled And Not IsNull(.Value) Then
strWhere = strWhere & "([Invoice] = " & Format(.Value,
strcJetDate) & ") AND "
End If
End With

'Remove the trailing " AND "
lngLen = len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

'Open the report
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub
--------------code ends------------
 
Back
Top