sub and temp query

littlelegs

Registered User.
Local time
Today, 09:14
Joined
May 8, 2002
Messages
10
Hope someone here can help me...

I keep getting run-time error '3061 - Too few parameters, expected one' on the 'Set class_rst = ' line of the following sub. I went and had a look the article mentioned previously about '3061' but it wasn't any help as it seemed to deal with permanent queries.

Sub Get_horse_recs(class_rst, class)

sql_string = ""
RIDER_F = query_rst(0)
RIDER_L = query_rst(1)

l1 = "SELECT * "
l2 = "FROM [RIDSCORE]"
l3 = " WHERE ((([RIDSCORE].RIDER_L) = """ & RIDER_L & """) And (([RIDSCORE].RIDER_F) = """ & RIDER_F & """) And (([RIDSCORE].LEVEL) = """ & class & """))"
l4 = " ORDER BY [RIDSCORE].YEAR DESC;"
sql_string = l1 & l2 & l3 & l4

Set tempa_qdef = xdb.CreateQueryDef("", sql_string)

Set class_rst = tempa_qdef.OpenRecordset(dbOpenDynaset) 'get the records for each rider via a query


End Sub

One of the calls to the sub:

Get_horse_recs classb_rst, b


And the declarations:

Dim class_rst As DAO.Recordset
Dim tempa_qdef As DAO.QueryDef
Dim sql_string As String
Dim l1 As String
Dim l2 As String
Dim l3 As String
Dim l4 As String

I am using 22 different called queries that list a number of people, then use this sub to fetch all records for one rider at one level (ie all recs for Joe Smith at B1). So the queries are temps for getting records for each rider from the main table.

The stupid thing is.... the whole thing did work... now it doesn't. I've also posted over in the queries forum as well, hopefully someone is able to see what I've missed.


[This message has been edited by littlelegs (edited 05-08-2002).]
 
The quotes are not properly placed. To make this easier, define a constant.

Public Const QUOTE As String = """"

l3 = " WHERE [RIDSCORE].[RIDER_L] = " & QUOTE & Me.RIDER_L & QUOTE & " And [RIDSCORE].[RIDER_F] = " & QUOTE & Me.RIDER_F & QUOTE & " And [RIDSCORE].[LEVEL] = " & QUOTE & Me.class & QUOTE
l4 = " ORDER BY [RIDSCORE].[YEAR] DESC;"

I see two best practices issues with your code also. You should NOT use the names of functions or other VBA reserved words such as "year" as column names. The second issue is that you don't seem to have Option Explicit specified. I know it is extra lines of code but unless you never make typos, all variables should be Dim'd prior to use.
 
Thanks very much for your very good advice. I hadn't considered the implications of the usage of specific words for field names. Most of my problems have arisen because of importing the tables from a (yech!!) db3 format.... as well a ton of data (non)integrity issues... So I renamed the year field and will try the other changes. As for the Option Explicit, I do have it specified 'cause I *know* I'm not perfect in the typing dept! Thanks for the tips!!!
smile.gif


[This message has been edited by littlelegs (edited 05-09-2002).]
 

Users who are viewing this thread

Back
Top Bottom