Re: How to return a result of a SQL query within an Event Procedure (&what am I doing wrong here)?
- From: Marshall Barton <marshbarton@xxxxxxxxxx>
- Date: Tue, 01 Jan 2008 16:45:04 -0600
raylopez99 wrote:
I am trying to return the results of a SQL query in an Event
Procedure, that returns the number of rows (COUNT) that satisfies
something. I ran the query manually and I get "five" hits for "Table
1" below.
But when I run the query programmically (see the below--it's the first
time I've tried to run a parametized SQL query within Visual Basic), I
always seem to get a RecordCount of 1. Is RecordCount the number of
hits returned by a SQL query, or is it something else? If something
else, what property from the Recordset object will give the number of
hits returned by a SQL query?
Please advise.
If it matters, here is the simple SQL query that I call "Query1" and
yields five hits for the letter 'a' in field Text for the table Table1
I'm using: SELECT Count(*) AS MyCount1 FROM Table1 WHERE
(((Table1.Text1)=[Text]));
RL
Private Sub OUTPUT_Click() '<--this is a textbox OUTPUT and the Event
is "Click"
Dim rs As DAO.Recordset 'holds query resultset
Dim db As DAO.Database
Dim qdfParmQry As QueryDef 'actual query object
Set db = CurrentDb
Set qdfParmQry = db.QueryDefs("Query1") '<--the name of the query is
Query1
qdfParmQry![Text] = "a" <--if "a" is input into the query, you get
five (5) hits in Query1
'qdfParmQry("TEXT") = a 'this format didn't work, despite a source on
the Net saying it would
Set rs = qdfParmQry.OpenRecordset()
Dim TempVar As Variant
TempVar = rs.RecordCount ' I assign rs.RecordCount to a temporary
variable but same thing: always get 1
If TempVar = 1 Then
Dim msg1 As String
Dim title As String
'Dim style As MsgBoxStyle
Dim response
msg1 = "rs.RecordCount is always 1???!!!" ' Define message.
title = "MsgBox Output" ' Define title.
' Display message.
response = MsgBox(msg1, 3, title)
End If
End Sub
RecordCount returns the number of records that have been
accessed so far. This is often just one record immediately
after the recordset is opened. You can do a rs.MoveLast to
force all the records to be accessed, but this is the long
way around to just get a count.
If you are enamored with recordsets then you should use a
Totals type query that just counts the records, but even
this is more code than needed without any significant
performance benefit.
I suggest that you remove the parameter from the query and
just use the DCount function:
Private Sub OUTPUT_Click()
Dim TempVar As Long
TempVar = DCount("*", "Query1", "[some field] = 'a' ")
. . .
--
Marsh
MVP [MS Access]
.
- Follow-Ups:
- References:
- Prev by Date: Re: Run a query based on user input to a text box
- Next by Date: Re: New Wine in Old Bottles problem (never use an old form on a new table schema / architecture)
- Previous by thread: How to return a result of a SQL query within an Event Procedure (&what am I doing wrong here)?
- Next by thread: Re: How to return a result of a SQL query within an Event Procedure (&what am I doing wrong here)?
- Index(es):
Relevant Pages
|
