Best way to prompt for report parameters in microsoft access

Question

Lets say you are designing a sales report in microsoft access. You have 2 parameters: Startdate and EndDate.

I can think of 3 ways to prompt the end user for these parameters when the report is run.

• Create a form with 2 Text Boxes and a button. The Button runs the report, and the report refers back to the form by name to get the start date and end date.

• Create a form with 2 text boxes and a button. The button runs the report but sets the appropriate filter via the docmd.openreport command.

• Base the report on a Query and define query parameters. Access will automatically prompt for those parameters one by one.

Which is the best way to go?

Answer 1

Which is best depends on a number of factors. First off, if you want to run the report without parameters, you don't want to define them in the recordsource of the report. This is also the problem with your first suggestion, which would tie the report to the form (from Access/Jet's point of view, there is little difference between a PARAMETER declared in the SQL of the recordsource and a form control reference; indeed, if you're doing it right, any time you use a form control reference, you will define it as a parameter!).

Of the three, the most flexible is your second suggestion, which means the report can be run without needing to open the form and without needing to supply the parameters at runtime.

You've left out one possibility that's somewhat in between the two, and that's to use the Form's OnOpen event to set the recordsource at runtime. To do that, you'd open the form where you're collecting your dates using the acDialog argument, hide the form after filling it out, and then write the recordsource on the fly. Something like this:

Private Sub Report_Open(Cancel As Integer)
  Dim dteStart as Date
  Dim dteEnd As Date

  DoCmd.OpenForm "dlgGetDates", , , , , acDialog 
  If IsLoaded("dlgGetDates") Then
     With Forms!dlgGetDates
       dteStart = !StartDate
       dteEnd = !EndDate
     End With
     Me.Recordsource = "SELECT * FROM MyTable WHERE DateField Between #" _
        & dteStart & "# AND #" & dteEnd & "#;" 
     DoCmd.Close acForm, "dlgGetDates"
  End If
End Sub

[Edit: IsLoaded() is a function provided by MS. It's so basic to all my Access coding I forget it's not a built-in function. The code:]

Function IsLoaded(ByVal strFormName As String) As Boolean
 ' Returns True if the specified form is open in Form view or Datasheet view.
  Const conObjStateClosed = 0
  Const conDesignView = 0

  If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
     If Forms(strFormName).CurrentView <> conDesignView Then
        IsLoaded = True
     End If
  End If
End Function

Now, there are certain advantages to this approach:

1. you can pre-fill the dates such that the user would have to click only OK.

2. you can re-use the form in multiple locations to collect date values for multiple forms, since the form doesn't care what report it's being used in.

However, there is no conditional way to choose whether to open the form or not.

Because of that, I often use a class module to store filtering criteria for reports, and then check if the relevant class module instance has filter values set at the time the OnOpen event fires. If the criteria are set, then I write the SQL for the recordsource on the fly, if they aren't set, I just use the default recordsource (and any filter passed in the OpenReport argument). In this setup, you would run the report only after you've collected the criteria and set up the class module instance. The advantage of this approach is that the report can be run in either of the two contexts, and none of the parts need to know anything about each other -- the report simply needs to understand the class module's interface.

Answer 2

I used to use the Docmd.Openreport Where clause, your option 2. But I've switched to using report filters in the reports Open event, your option 1, as it supports creating PDF files which the Where clause does not. See Microsoft Access Report Printing Criteria Selection Form at my website.

As far as the dates go in SQL strings I use the Format statement as at Return Dates in US #mm/dd/yyyy# format

Also your option three can be handled by putting in the following example in the criteria in the reports record source SQL query. Forms!Form_Name!Control_Name. This is generally simpler for folks who don't want to get into VBA.