Jeff:
Open the second form in dialogue mode using the acDialog option for the
WindowMode argument of the OpenForm method, e.g.
DoCmd.OpenForm "frmReportParameters", WindowMode:=acDialog
Any code after this line will not be executed until the frmReportParameters
form has been closed (or hidden).
You could pass the parameter values back to the calling form, which you
could do by assigning them to unbound hidden text box controls on the calling
form, which can then be referenced when opening the report with code in the
calling form's module. You could do this in the parameter form's Close event
procedure. A simpler method is to open the report with code in the
parameters form's Close event procedure. You can pas the report name from
the calling form to the parameters form via the OpenArgs mechanism. I've
assumed a constant report name below but you could of course use a variable
if the calling form allows a choice of reports. So the calling form's code
would be like this:
' open dialogue form passing report name to it
DoCmd.OpenForm "frmReportParameters", _
WindowMode:=acDialog, _
OpenArgs:= "MyReport"
' close this form
DoCmd.Close acForm, Me.Name
The code for the parameters form's Close event procedure might be like this:
Dim strCriteria As String
strCriteria = "[MyDate] >= #" & _
Format(Me.txtStartDate, "mm/dd/yyyy") & _
"# And [MyDate] < #" & _
Format(DateAdd("d", 1, CDate(Me.txtEndDate)), _
"mm/dd/yyyy") & "#"
DoCmd.OpenReport Me.OpenArgs, _
View:=acViewPreview, _
WhereCondition:=strCriteria
where txtStartDate and txtEndDate are the two text box controls on the form
in which the start and end of the date range are entered. Note that the
range is defined as being on or later than the start date and before the day
following the end date. This caters for any values in the MyDate column
which might have non-zero times of day. Such values can all too easily creep
in without you being aware of it unless you have specifically taken steps to
prevent this in the table definition. Any rows with such values on the final
day of the range would otherwise not be returned.
Ken Sheridan
Stafford, England
