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:
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:
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:
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!
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:
Code:
[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:
Code:
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:
Code:
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!