Error 3061 1

[ump38]

Using Access Version 7
I write a lot of VBA code behind buttons that result in fairly complex recordset processing loops. I generally build the recordset based on a query that contains anywhere from 1 to several criteria expressions that are something like this:

[Forms]![Switchboard]![txt_City]

I know the query itself works and returns exactly the records it should because I can run the query independently after filling in the form and see the results.

But... when I click the button and run the code I get an Error 3061: Too few parameters. Expected x where x is always the number of criteria that I have in the query that are based on form values (as above). My workaround has been to substitute a constructed piece of SQL code that I almost literally cut & paste from the qeury itself. For example:

Dim SQLCode as String
Dim UpdateFingerprints as Recordset

SQLCode="SELECT DISTINCTROW {FingerprintInformation].* "
SQLCode=SQLCode & "FROM [FingerprintInformation] "
SQLCode=SQLCode & "WHERE ((([Fingerprint Information].SSN_Sequence)=" & Chr(34) & Me!txt_UseThisSSN  & Chr(34) & "));"
Set UpdateFingerprints = CurrentDb.OpenRecordset(SQLCode, dbOpenDynaset)

While I've never had a problem accomplishing this in this manner above with the SQL code, it is a pain to have to construct/enhance the SQL even though I do copy most of it from the query design SQL view. I'd much prefer to be able to code it as this:

Dim QueryName as String
QueryName="qry_GetThisRecordBasedOnSSN"
Set UpdateFingerprints = CurrentDb.OpenRecordset(QueryName, dbOpenDynaset)

Am I missing something or is this just an Access "feature" that I have to live with? I can construct the query on the fly using QueryDef but I'm trying to minimize the database size growing at that rate. If the query has no criteria based on a Forms! reference, this method works great by simply referencing the query name.

Any help would be most appreciated!

 

[Rolliee]

If the query exists as named, you are dooing something else wrong. What is the curly bracket doing in the code. It will not fly!! How does either choice cause the 'code to grow?'

rollie@bwsys.net

 

[ump38]

Rolliee,
The curly bracket (or left french brace was a typo...it's actually a left bracket) and the code works fine. (sorry)

The query does exist because, as I stated, I can run the query directly with returned results as expected.

As for the database "growing".... if I construct the query with code using QueryDef in such a way that the query criteria cells have literal values ("ATL&quot instead of references to form fields, then the process of deleting the query then re-adding a new one (because of new values) causes the front-end .MDB file to grow in size faster than normal. I've done that many times too and I just have to compact it more often. When accomplishing this as outlined above with SQLCode, there is no change to the file size just as there isn't if I reference an actual query.

Does that help in my asking of this question?

 

[Rolliee]

If you look at your query in the SQL view, does the code look like the code in your SQLCode example? Why not try and use this code, with modifies to get the runtime value Me!txt_UseThisSSN in the code as test. Thanks for the comment on the growing code. I have seen this happen and just not thought about what caused it.

Rollie E

 

[ump38]

Rolliee,
My constructed SQL Code looks pretty much like the SQL in the query in SQL view with the exception being I substitute Me!txt_UseThisSSN for where the query SQL view has something like Forms!Switchboard!txt_UseThisSSN.

Occasionally, for debug purposes, I'll actually take the exact same SQLCode built and create a dummy query using the QueryDef just to compare results and SQL view code.... and the returned query results are identical.

This situation may be very similar to Access' problem with crosstab queries that use preliminary queries that also contain Form! references as criteria.... where you get some error when you run the crosstab that says something like "Can't bind..".

 

[ump38]

Just did a keyword search on this site for Too few parameters and found a post from 3/12/01 on this same subject in the Other topics forum as:

Programmers Area - Microsoft: Access Other topics Forum
GeneJohnson (Visitor) 3/12/01 (posted 3/12) 2 responses
Too Few Parameters - Error Msg.

There's a post there from Katerine that suggests prefacing the query criteria with the Eval function. Her post was exactly the error I was getting under the exact same circumstances. I changed my criteria statement in the actual query (qry_GetThisRecordBasedOnSSN) as follows:

Like Eval("[Forms]![Switchboard]![txt_UseThisSSN]")

and it works beautifully. This site has really proved worthwhile!

 

[Rolliee]

Very Good!!!