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.

 

[SkipVought]

Hi,

sSQL = "SELECT [Net] FROM [tblRecap] WHERE [Yr]=" & txtYR 
 & " And [Event]='" & txtEvent & "' "

Set Rs = CurrentDb.OpenRecordset(sSQL)

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[lynchg]

Create variables to hold the user entries for year and event. Then fill those variables with the values the user enters, and then plug those varaibles into your sql for the recordset.

It doesn't seem like you are using a form for data entry, just a message box, I don't know how you ensure that they enter valid values. That is where I am not sure how to fill those variables (???? below),if it were on a form with controls for user entry you could fill the 2 variables with the values from the 2 user entry controls. If you can figure that out, my Set RS statement will work for you.

Dim Rs As DAO.Recordset

Dim intYear as integer
Dim strEvent as string

Set intYear = ????
Set strEvent = ????

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

Hopefully I have gotten you more than halfway there.

 

[theSizz]

Thanks guys, I'm moving in the right direction now. I added the user input controls to a form then referenced the form in the sub procedure. So my code in part now looks like this:


Dim ConsqWin As Long
Dim ConsqLoss As Long
Dim tmpWin As Long
Dim tmpLoss As Long
Dim txtyr As Integer
Dim txtevent As String
Dim sSql As String
Dim Rs As DAO.Recordset

txtyr = [Forms]![frmgetreports]![Yr]
txtevent = [Forms]![frmgetreports]![Event]

sSql = "SELECT [Net] FROM [tblRecap] WHERE [Yr]=" & txtyr & " And [Event]='" & txtevent & "' "

Set Rs = CurrentDb.OpenRecordset(sSql)

It all appears to work fine.

Thanks