By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

Too Few Parameters. Expected 6 - Query in VBA Issue

P: 10
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Shop_Order_Number] & " " & [Document_Description] AS Description, Count([Chart Filter Query].Revision_Number) AS [Number of Passes]
  2. FROM [Chart Filter Query]
  3. 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.OpenRecordset("Number of Passes Chart Query", dbOpenDynaset)

Much abliged,

JGrizz
Dec 7 '09 #1
Share this Question
Share on Google+
18 Replies


ADezii
Expert 5K+
P: 8,668
  1. Does it Run within the Query Grid?
  2. Has the Object variable db been Declared and Instantiated, as in:
    Expand|Select|Wrap|Line Numbers
    1. Dim db As DAO.Database
    2.  
    3. Set db = CurrentDb
Dec 7 '09 #2

P: 10
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.
Dec 7 '09 #3

ADezii
Expert 5K+
P: 8,668
Do the [Shop_Order_Number], [Document_Description], and [Revision_Number] Fields exist in the Chart Filter Query?
Dec 7 '09 #4

P: 10
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
Attached Images
File Type: jpg Num_Passes_Query.jpg (18.6 KB, 324 views)
File Type: jpg Chart_Filter_Query.jpg (13.6 KB, 319 views)
File Type: jpg VBA_Code.jpg (10.0 KB, 565 views)
Dec 8 '09 #5

ADezii
Expert 5K+
P: 8,668
The Error appears to indicate that a Parameter is expected and is not being received:
  1. Try fully qualifying the SQL Statement, as in:
    Expand|Select|Wrap|Line Numbers
    1. SELECT [Chart Filter Query].[Shop_Order_Number] & " " & [Chart Filter Query].[Document_Description] AS Description,
    2. Count([Chart Filter Query].Revision_Number) As [Number of Passes]
    3. FROM [Chart Filter Query]
    4. 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?
Dec 8 '09 #6

P: 10
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.
Attached Images
File Type: jpg Select_Chart_Form.jpg (10.6 KB, 349 views)
File Type: jpg Pre-Chart_Query.jpg (19.8 KB, 353 views)
Dec 8 '09 #7

ADezii
Expert 5K+
P: 8,668
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?
Dec 8 '09 #8

P: 10
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.
Dec 8 '09 #9

ADezii
Expert 5K+
P: 8,668
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).
Dec 8 '09 #10

P: 10
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
Dec 8 '09 #11

ADezii
Expert 5K+
P: 8,668
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)!
Dec 8 '09 #12

P: 10
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
Dec 9 '09 #13

ADezii
Expert 5K+
P: 8,668
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim intNumOfFields As Integer
  4. Dim intFldCtr As Integer
  5.  
  6. '********************* Replace with own Table/Query *********************
  7. Const strDATA_SOURCE As String = "Employees"
  8. '************************************************************************
  9.  
  10. Set MyDB = CurrentDb()
  11. Set rst = MyDB.OpenRecordset(strDATA_SOURCE, dbOpenSnapshot)
  12.  
  13. intNumOfFields = rst.Fields.Count
  14.  
  15. For intFldCtr = 0 To intNumOfFields - 1       'Indexed at 0
  16.   Debug.Print "Field #" & Format(intFldCtr + 1, "000") & " ==> " & _
  17.                rst.Fields(intFldCtr).Name
  18. Next
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Field #001 ==> EmployeeID
  2. Field #002 ==> LastName
  3. Field #003 ==> FirstName
  4. Field #004 ==> Title
  5. Field #005 ==> TitleOfCourtesy
  6. Field #006 ==> BirthDate
  7. Field #007 ==> HireDate
  8. Field #008 ==> Address
  9. Field #009 ==> City
  10. Field #010 ==> Region
  11. Field #011 ==> PostalCode
  12. Field #012 ==> Country
  13. Field #013 ==> HomePhone
  14. Field #014 ==> Extension
  15. Field #015 ==> Photo
  16. Field #016 ==> Notes
  17. Field #017 ==> ReportsTo
  18. Field #018 ==> OLE Field
Dec 9 '09 #14

P: 10
Thanks much!

I'll be able to use the rs.Fields(#).Name 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
Dec 9 '09 #15

ADezii
Expert 5K+
P: 8,668
Glad it all worked out for you.
Dec 9 '09 #16

P: 10
I got a bit over-excited about the first victory and overlooked the minor issue that followed... In regards to the Fields.name, after testing it out, I realized what I want is not the field name, but the field caption (ie, basically the name without the underscores).

So I have two thoughts here and would appreciate some input:

1. Is there some way I can call the Fields.Caption? I checked the list and saw default value, validation rule, validation text, and even type, but no caption option. Does this exist?

2. If option 1 is not possible, then perhaps load the actual field name as an array, or string, and search through the string removing all '_' with spaces. The second option doesn't seem too difficult, but I didn't see the ability to define an array. If I attempt to manipulate the string, how can I go about that? It's been about 3 years since I've touched C++, where I would have known how to easily do this, but since we are in a different language and its been a while, a little assistance is appreciated.

Thanks again,
JGrizz
Dec 9 '09 #17

P: 10
Never mind... I found the Replace() function, I did not think it would be that easy. I'll let you know if I need to pick your brain any further (lol). Thanks again.
Dec 9 '09 #18

ADezii
Expert 5K+
P: 8,668
Caption is a User-Defined Property and thus is a little tricky to access, but here it goes using the previous scenario:
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim MyDB As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim intNumOfFields As Integer
  5. Dim intFldCtr As Integer
  6.  
  7. '********************* Replace with own Table/Query *********************
  8. Const strDATA_SOURCE As String = "Employees"
  9. '************************************************************************
  10.  
  11. Set MyDB = CurrentDb()
  12. Set rst = MyDB.OpenRecordset(strDATA_SOURCE, dbOpenSnapshot)
  13.  
  14. intNumOfFields = rst.Fields.Count
  15.  
  16. Debug.Print "Field Name", , "Caption"
  17. Debug.Print
  18.  
  19. For intFldCtr = 0 To intNumOfFields - 1       'Indexed at 0
  20.   Debug.Print rst.Fields(intFldCtr).Name, , rst.Fields(intFldCtr).Properties("Caption")
  21. Next
  22.  
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Field Name                  Caption
  2.  
  3. EmployeeID                  Employee ID
  4. LastName                    Last Name
  5. FirstName                   First Name
  6. TitleOfCourtesy             Title Of Courtesy
  7. BirthDate                   Birth Date
  8. HireDate                    Hire Date
  9. PostalCode                  Postal Code
  10. HomePhone                   Home Phone
  11. Extension                   Newly added Test Caption
  12. ReportsTo                   Reports To
  13.  
Dec 9 '09 #19

Post your reply

Sign in to post your reply or Sign up for a free account.