Too Few Parameters. Expected 6 - Query in VBA Issue

[JGrizz]

Greetings, I first off want to state that I am new to the forum, so if this question ends up in the wrong area, I apologize. This pertains to Access 2003/VBA/SQL issues...

I have been doing some investigating regarding the error which I will explain shortly, and thus far it has all pertained to WHERE clauses relating to forms causing errors when transferred over to VBA.

My issue is a wee bit different and I will explain the situation below. I have a query of a query at the moment which is made up of the following SQL code:

SELECT [Shop_Order_Number] & " " & [Document_Description] AS Description, Count([Chart Filter Query].Revision_Number) AS [Number of Passes]
FROM [Chart Filter Query]
GROUP BY [Shop_Order_Number] & " " & [Document_Description];

When I attempt to set this query as a recordset in VBA I get the too few parameters error. Since none of this data comes from a form (it is all from another query) I can not figure out for the life of me what the issue is. I will keep this post short and answer more questions as needed, but the error is occuring when I attempt to set the recordset. That code is shown below:

Set rs1 = db.OpenRecordse t("Number of Passes Chart Query", dbOpenDynaset)

Much abliged,

JGrizz

[ADezii]

1. Does it Run within the Query Grid?
2. Has the Object variable db been Declared and Instantiated, as in:
   

   Dim db As DAO.Database
   
   Set db = CurrentDb

[JGrizz]

In response to question 1: Yes, the query runs fine when I don't need to load it as an active recordset. In fact, I have a command after which exports the query to Excel with no problem. All of the data is present that needs to be represented and it exports the data nicely over to Excel (via a command in VBA in the same section of code further down).

In response to question 2: Yes, the proper initialization did occur. I did not want to include the declarations, as I did not know if I would be supplying too much information.

When I was programming I had to perform two subsections for testing. I completed the first task (which included sending data to Excel directly from the query), and now I need to further manipulate the data in the query to send additional information to Excel.

The second round of manipulation (which I am currently performing) requires that I be able to open the query as a recordset. I have tested my code and it works properly when dealing with a test table, it however choked on the query of a query. It errors on the set rs1 = etc.....

Let me know if you need any more information. I would supply my code directly, but I do not want to provide too much code that I already know is functioning without it being necessary.

[ADezii]

Do the [Shop_Order_Numb er], [Document_Descri ption], and [Revision_Number] Fields exist in the Chart Filter Query?

[JGrizz]

Yes, the fields exist in the Chart Filter Query. In an attempt to get past the lighter details, here are a few images to help hopefully cover any other questions. Sorry for not providing all of this information in the beginning, as I said, I am new to posting questions like this and don't know exactly what needs supplied.

Image- Number of Passes Chart Query Design View:
Attachment 1

Image- Chart Filter Query Design View:
Attachment 2

Image- Section of Code Under Consideration- Attachment 3

The cases come from a pull down on the form which this chart will be generated from. The form itself has no table/query/etc. behind it, it is meant purely to be used as a filtering source based on the user's selected criteria. Again, as I said, the error comes from calling the query as a recordset. I need to be able to manipulate the data within the query (shown in the code) before moving on to the next step. I could always create a temp table to place the information from the query on, but I feel that is sloppy programming and supplying a bandaid as opposed to a solution. Also, I will need to do similar manipulations a few more times to generate other results important to this database, so having a temp table for 4 manipulations quickly gets out of hand.

Thanks,
JGrizz

[ADezii]

The Error appears to indicate that a Parameter is expected and is not being received:

1. Try fully qualifying the SQL Statement, as in:
   

   SELECT [Chart Filter Query].[Shop_Order_Number] & " " & [Chart Filter Query].[Document_Description] AS Description,
   Count([Chart Filter Query].Revision_Number) As [Number of Passes]
   FROM [Chart Filter Query]
   GROUP BY [Chart Filter Query].[Shop_Order_Number] & " " & [Chart Filter Query].[Document_Description];

2. Double check the Syntax of you Criteria in the 2nd Query, eliminating all of them, then adding one at a time since a Form or Control Reference may be incorrect.
3. Is the Form 'Open' when the 2nd Query is executed?
4. If all else fails, can you E-Mail me the DB to my Private Address?

[JGrizz]

ADezii,

I attempted item 1, and still received the same error.

In regards to item 2, do you believe that the controls from the Chart Filter Query are affecting the output of the Number of Passes Chart Query? I can see where you might be coming from in that case, as that would go back to similar problems in other posts dealing with the WHERE clause from form data. The Query behind the Chart Filter Query, called the Pre-Chart Filter Query, contains similar filters (there were so many filters occuring that I had to do a query of a query just to get all of the filters implemented).

For item 3, yes the form is open when all of this occurs. The filters used in the Pre-Chart Filter Query and Chart Filter Query are generated from the form via a series of pulldowns. These help to drive the final query and as I said in item 2, could possibly be afftecting the "parameters " complaint, as I do believe there are 6 filtering points. Attached is an image of the form with the series of pulldowns.See Attachment 1.

If you could tell me how to properly/fully reference the form data in the queries which supply the information for the Number of Passes Chart Query, then I will attempt to 'fix' this issue in that manner.

Let me know what you think.

Again thanks for the continued effort,

JGrizz

P.S. I also decided to attach the design view of the very first Query, the Pre-Chart Filter Query, so you can see the mess that is the filtering going on in there (I wish access had a better option, but for now it works, and if it ain't broke don't fix it). See Attachment 2.

[ADezii]

If I understand you correctly, you are trying to create a Recordset based on a Query, which in turn in based on another Query (Chart Filter Query), which in turn is based on even another Query (Pre-Chart Filter Query). Within these Queries are nested approximately 15 assorted Criteria (Form-Control references) with various Logical AND and OR Operations. Is all this correct?

[JGrizz]

Yes, quite the fustercluck isn't it? I will admit its quite a mess, but by my knowledge (which is limited and only as expansive as I've come to learn from trial and error and reading online help posts such as this) that was the only way to go about it. The worst part is that the Query is only able to have so many Criteria, otherwise, there would be only 2 Queries. The Chart Filter Query is going to serve as the basis to generate a series of charts in Excel, one of which is number of passes.

Thanks again.

I will say that there shouldn't be 15 different Criteria, it is all based on 6 criteria, but all options included there-in, if that makes sense. So A & B, Not C; A & C, Not B; etc. etc.

[ADezii]

A brainstorm!

1. Modify Pre-Chart Filter Query to make it a Make Table Query.
2. Base the Chart Filter Query on the newly created Table.
3. Base the Final Query on the Chart Filter Query.
4. I am now out of Options unless you want to send the DB to my Personal E-Maill Address (LOL).

[JGrizz]

Alright, I'll give it a try tomorrow morning. Does the Make Table Query allow me to utilize all of the filters that I need to implement? If so, do you believe that making it a Make Table Query will solve the current issue at hand...

Thanks,

JGrizz

[ADezii]

Alright, I'll give it a try tomorrow morning. Does the Make Table Query allow me to utilize all of the filters that I need to implement?

It should maintain the existing Filters.

If so, do you believe that making it a Make Table Query will solve the current issue at hand...

I'm all outta other Options (LOL)!

[JGrizz]

I took your input and did a bit of brainstorming myself and was able to come up with a workable solution. It was indeed the WHERE criteria on the subqueries.

Since the final query was based off a query with criteria (also itself based off another query with criteria) I swapped out the last query that had criteria from a SELECT query, to a MAKE TABLE Query. This created a table, of course, and the data was no longer dependent on the form data.

This in turn allowed me to utilize my final query (Number of Passes Chart Query) but with a data source (resource table) or the resulting table from the make table query. From there the code ran without issue.

I'm very happy with the final product, looks pretty pro (if I do say so myself). Thanks for the help! Although I still feel this was a bandaid, in that I believe the query of a query of a query can still be used, but with different syntax for the form controls. However, as of now, it works and I can make it pretty and do more R&D in the future.

One more quick question as I have your attention.... Since I can use rs.Field(#) to access a specific column of data, how do I access the name of the of said column, is there a rs.Name(#) command? I have no looked into it as of yet, but will be doing so now. I'd appreciate it if you could let me know when you read this final post :).

Sincerely,
JGrizz

[ADezii]

I wrote a simple Routine for you that should precisely answer your Question, just replace the Value of the CONSTANT conDATA_SOURCE with your own Table/Query. I executed the code against the Employees Table of the Sample Northwind Database:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim intNumOfFields As Integer
Dim intFldCtr As Integer

'********************* Replace with own Table/Query *********************
Const strDATA_SOURCE As String = "Employees"
'************************************************************************

Set MyDB = CurrentDb()
Set rst = MyDB.OpenRecordset(strDATA_SOURCE, dbOpenSnapshot)

intNumOfFields = rst.Fields.Count

For intFldCtr = 0 To intNumOfFields - 1       'Indexed at 0
  Debug.Print "Field #" & Format(intFldCtr + 1, "000") & " ==> " & _
               rst.Fields(intFldCtr).Name
Next

OUTPUT:

Field #001 ==> EmployeeID
Field #002 ==> LastName
Field #003 ==> FirstName
Field #004 ==> Title
Field #005 ==> TitleOfCourtesy
Field #006 ==> BirthDate
Field #007 ==> HireDate
Field #008 ==> Address
Field #009 ==> City
Field #010 ==> Region
Field #011 ==> PostalCode
Field #012 ==> Country
Field #013 ==> HomePhone
Field #014 ==> Extension
Field #015 ==> Photo
Field #016 ==> Notes
Field #017 ==> ReportsTo
Field #018 ==> OLE Field

[JGrizz]

Thanks much!

I'll be able to use the rs.Fields(#).Na me to get what I need. From there it simply be throwing them into a new tables as actual data and I'm good to go for my final chart.

Much appreciative on the help and time spent on assisting me with a solution.

JGrizz