Have User Input WHERE Criteria In An OpenRecordset Method

How can I have a user enter the WHERE criteria in the Set Rs statement below.


Set Rs = CurrentDb.OpenRecordset("SELECT [Net] FROM [tblRecap] WHERE [Yr] = 2000 and [Event] = 'lancaster'")

I would like a message box to ask the user to define the [Yr] = and [Event] = fields since they would change from time to time and then pass the values to the OpenRecordset method. Is this possible? Can someone help supply the code?

A parameter query won’t work because when you manipulate these queries in DAO through VBA you need to supply the parameter value before you open the recordset object. If you don’t DAO generates an error.

Here is my code:


Option Compare Database
Option Explicit


Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)

Dim ConsqWin As Long
Dim ConsqLoss As Long
Dim tmpWin As Long
Dim tmpLoss As Long
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("SELECT [Net] FROM [tblRecap] WHERE [Yr = 2000 and [Event] = 'lancaster'")
Do While Not Rs.EOF
Do While Rs!Net > 1
tmpWin = tmpWin + 1
Rs.MoveNext
If Rs.EOF Then Exit Do
Loop
If Rs.EOF Then Exit Do
If tmpWin > ConsqWin Then ConsqWin = tmpWin
tmpWin = 0
Rs.MoveNext
Loop

Set Rs = Nothing

End Sub

By the way the YR field is a number and not a date so the WHERE clause doesn’t need #2000#

Any help would be appreciated.