Using a query as a recordset - PARAMETERS

[JoanieB]

In the code behind a form, I have a query that pulls records from a table based on the values of two fields in that form. When I use a duplicate copy of that query (called something else) as a recordset object, Access tells me I have 'too few parameters, expecting 2', but there are two qualifiers (the values in 2 of the form's txt boxes)in the query. Do I have to put something in the openrecordset statement in this instance? here is what I have code-wise:

Set ResRS = CurrentDb.OpenRecordset("QryDomRes&quot
If ResRS.RecordCount > 0 Then
Do Until ResRs.EOF
'*Do math, etc
Loop
End If

It stops on the Set ResRS statement.

 

[Junior1544]

i had a simmalare problem... you need to use sql to find what you need... make the recordset based on sql, instead of a query... just an idea to look into... i can't help with details because i don't know much about it...

--Junior

JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[RickBerem]

Hi, if you want to use your query that way try this

dim qdf as QueryDef
dim rs as recordset

set qdf = currentdb.querydefs("QryDomRes&quot
set rs = qdf.openrecordset

use the rest of your code the same way

Hope it helps! Salvatore Grassagliata

 

[Scoty]

Joanie,
What Junior would be referring to may actually be your best bet (if you plan on releasing future versions of your database). Here is how I do it and it works great--most of the time.

First I make a query of the info I wish to extract. then I click on veiw then click SQL. What this will do is take your your query and transfer it into an SQL statement. And you can copy it (right click copy). Then in the above code you would dim a string Ie.

Dim lsSQL As String

then

 lsSQL =

'then paste your the sql statemnt there Now this will paste in red. you just have to adjust it so it a. all fits on one line or b. you can concantate the string using

& CHR(13) & _

from line to line. Then when you

Set ResRS = CurrentDb.OpenRecordset("QryDomRes")

then just substitue

"lsSQL"

for

 "QryDomRes"

After a while of doing this you will notice that you can actually write your own SQL statements.
So now you can

lsSQL = "SELECT tbl" & format$(Date,"mmddyy") & ".* FROM tbl" & format$(Date,"mmddyy") & "(WHERE tbl" & format$(Date,"mmddyy") & ".Name = " & Environ("Full") & ");"

I better stop there I'm makeing a faq

Sorry for the length of my responce.

hth
Scoty ::-) "Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[Guest_imported]

thanks Scoty, i knew i had done it... but don't know the details or the finer points... i'm glad you steped up...

--Junior