Have User input WHERE criteria in an OpenRecordset method

[theSizz]

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.

 

[llkhoutx]

Reference the criteria fields on another object, a form is good for this; don't forget to include the quotation encloses for a string criteria (I concantenate a chr(34) instead of using a tic or quote).

Optionally use the InputMessage instruction to request each crteria. See how to use it with the Help menu.

The latter is fraught with potential problems and potential user errors. Using form controls that the User customarily uses is a good practice..

 

[DB7]

Like llkhoutx said.
More specifically, your SQL would look like this,(if you decide to use a popup Form(recommended)

("SELECT [Net] FROM [tblRecap] WHERE [Yr] = " & Me.txtYear & " AND [Event] = " & chr(34) & Me.txtEvent & chr(34) & ")"

 

[theSizz]

Thanks to both of you.
I got it ........decided to use a form and a SQL string.

Thanks again.