Form-based Parameters

[Jeff H]

I’m following instructions I found online to open a form using parameters from another form. But it’s not working. I have tried to check all my spelling and syntax, but can anyone spot something wrong with the code below?

The target form is frmServceEventsFind whose control source Record Source is qryServiceEvents.

In qryServiceEvents I put the following in the Criteria row for the SvcDate field:

Code: Select all

Between [Forms]![frmSvcEventParameters]![Date1] And [Forms]![frmSvcEventParameters]![Date2]

In the Parameters box of that query I added
Forms!frmSvcEventParameters!Date1 as “Date with Time” (didn’t see a plain “Date” option)
Forms!frmSvcEventParameters!Date2 as “Date with Time”

That works if I open the query directly and manually enter the dates, but it isn't getting the dates from frmSvcEventParameters.

On frmSvcEventParameters I have the following vba on the cmdOk button click event:

Code: Select all

Private Sub cmdOk_Click()
Dim sStart As String
Dim dStart As Date
Dim sEnd As String
Dim dEnd As Date

If Not IsNull(Me.[cboMonth1]) And Not IsNull(Me.[cboYear1]) Then
    sStart = Me.[cboMonth1] & " 1, " & Me.[cboYear1]
    dStart = DateValue(sStart)
    dEnd = DateAdd("m", 1, dStart)
    dEnd = (DateSerial(Year(dEnd), Month(dEnd), 0))
End If

If dStart > Now() Then
    MsgBox "Can't call a future month."
    Exit Sub
End If

Me.[Date1] = dStart
Me.[Date2] = dEnd

DoCmd.OpenForm "frmServiceEventsFind", acViewNormal, acEdit

End Sub

The dates are being constructed as expected: the user selects a month and year then dStart is the first of that month and dEnd is the last day of the month.

[HansV]

Could you attach a stripped-down and zipped copy of the database?

If not, I have a suggestion for a slightly different method.

[Jeff H]

That might be difficult because I'm using live, confidential information. But I should be able to redirect to a neutral table. Meanwhile, I wouldn't mind hearing the alternate method.

Incidentally, since I posted, I noticed that when I open the query or form directly and manually enter the dates, I'm prompted for three parameters: Date1, Date2, then Date1 again. And the second Date1 is the one that is used.

[HansV]

As an alternative: remove the parameters from the query, both from the Criteria line and from the Parameters dialog. Then save the query.

If you open frmServiceEventsFind by itself now, it should not ask for the dates and display all records.

Change the line

Code: Select all

DoCmd.OpenForm "frmServiceEventsFind", acViewNormal, acEdit

to

Code: Select all

DoCmd.OpenForm FormName:="frmServiceEventsFind", WhereCondition:="SvcDate Between #" & _
    Format(dStart, "mm/dd/yyyy") & "# And #" & Format(dEnd, "mm/dd/yyyy") & "#"

[Jeff H]

I like that! I'll try it and report back pretty soon (got something to do first).

- Jeff

[Jeff H]

Yeah man!! It works great and I think it's a much better solution.

As always, thanks so much Hans.

[HansV]

This is a very powerful technique, you can use it to open forms and reports with a filter specified on the fly.