Solved

Parameters for OpenRecordset in VB

Posted on 2011-02-20
35
823 Views
Last Modified: 2012-05-11
I am getting the following syntax error on my OpenRecordset command in VB within MS Access:

Run-time error '3061': Too few parmaeters.  Expected 3.

I am passing a single parameter which is a SQL statement.  In researching correct syntax for OpenRecordset on the internet, it seems to me that only 1 parameter is required.  Here is my code:

Function Check4EmptyD()

'CALL THIS FUNCTION BEFORE PAYOUT - 1D IN THE PAYOUT MACRO. This code is only called for "D" format races.

Dim db As DAO.Database
Dim class_rs As DAO.recordset

Dim EmptyDPayout As Currency    'Total amount of payout originally allocated to "D"s that are empty
Dim NumEmptyDs As Integer       'Number of "D"s with no entries

Set db = CurrentDb
Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE ((Class.EventID = [Forms]![Calculate Results]![Event ID]) AND (Class.[Class Type] = [Forms]![Calculate Results]![Calculate Results - Class Subform].[Form]![Class Type]))")
EmptyDPayout = 0
NumEmptyDs = 0
0
Comment
Question by:ProgrammingGrandma
  • 12
  • 12
  • 11
35 Comments
 
LVL 6

Expert Comment

by:AkAlan
Comment Utility
I believe you have to split out the reference to the form control with ampersands an quotes like this:


Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE ((Class.EventID = " & [Forms]![Calculate Results]![Event ID]) & " AND  
 (Class.[Class Type] = " & [Forms]![Calculate Results]![Calculate Results - Class Subform].[Form]![Class Type] & " ))")

I believe I got them all in the right place but you have to verify that.
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
Change to :

Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE (Class.EventID = " & [Forms]![Calculate Results]![Event ID] & ") AND (Class.[Class Type] = " & [Forms]![Calculate Results]![Calculate Results - Class Subform].[Form]![Class Type] & "))")
0
 
LVL 6

Expert Comment

by:AkAlan
Comment Utility
I did missplace the ampersand, looks like jacko got it right. You get the idea, you were trying to pass a parameter but the system can't just get the reference from within the parenthasis. One thing that works for mew is to first declare the Select string as a variable and step through the code in the Locals window and you can see when you get the string correct.
0
 

Author Comment

by:ProgrammingGrandma
Comment Utility
I'm getting closer.  Now I get the error:

Run-time error '3075':
Syntax error (missing operator) in query expression
'((Class.EventID=56) AND (Class.[Class Type] = Open Barrels))'.

I am guessing the string value Open Barrels might need quotation marks.  If so, how do I add those to the query statement?
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
Youre right add some single quotes as below :

Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE (Class.EventID = " & [Forms]![Calculate Results]![Event ID] & ") AND (Class.[Class Type] = '" & [Forms]![Calculate Results]![Calculate Results - Class Subform].[Form]![Class Type] & "'))")
0
 

Author Comment

by:ProgrammingGrandma
Comment Utility
I tried putting the single quote both outside the double quotes (as you did) and inside.  Both ways I get a syntax error.
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
Is it the same syntax error?
0
 

Author Comment

by:ProgrammingGrandma
Comment Utility
No. The error is:

Run-time error '3061':
Too few parameters. Expected 1.

which makes me think the compiler can't parse the statement anymore.
0
 
LVL 6

Expert Comment

by:AkAlan
Comment Utility
Try hard coding the parameters with known values first.
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
got the last single quote in the wrong position should be :

Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE (Class.EventID = " & [Forms]![Calculate Results]![Event ID] & ") AND (Class.[Class Type] = '" & [Forms]![Calculate Results]![Calculate Results - Class Subform].[Form]![Class Type] & ")')")
0
 

Author Comment

by:ProgrammingGrandma
Comment Utility
Jacko72:

That change yielded the following:

Run-time error '3075':

Missing ),], or Item in query expression '((Class.EventID = 56) AND
(Class.[Class Type] = 'Open Barrels)')'.

Meanwhile, I am trying AkAlan's suggestion but haven't gotten anything that compiles yet.
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
OK move the closing bracket as below:

Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE (Class.EventID = " & [Forms]![Calculate Results]![Event ID] & ") AND (Class.[Class Type]) = '" & [Forms]![Calculate Results]![Calculate Results - Class Subform].[Form]![Class Type] & "')")
0
 
LVL 6

Expert Comment

by:AkAlan
Comment Utility
Looks like there should be a single quote after Open Barrels like this (Class.[Class Type] = 'Open Barrels'))'
0
 

Author Comment

by:ProgrammingGrandma
Comment Utility
Jack072:

Your last suggestion still gets the error 3061 - too few parameters.  It is the same as your initial suggestion to add single quotes outside the double quotes except that you removed the parentheses surrounding the two conditions in the WHERE clause separated by AND.

AkAlan:

I am trying your suggestion to hard code in my query.  I use the following statement:

Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE ((Class.EventID - 65) AND (Class.[Class Type] = 'Open Barrels'))")

This statement also gets the error 3061 - Too few parameters. Expected 1.

Any suggestions?
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
try this :
Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE (Class.EventID = " & [Forms]![Calculate Results]![Event ID] & ") AND (Class.[Class Type] = '" & [Forms]![Calculate Results]![Calculate Results - Class Subform].[Form]![Class Type] & "')")
0
 

Author Comment

by:ProgrammingGrandma
Comment Utility
Jacko72,

I may be missing something.  Your suggestion at 1:38pm looks the same to me as the one at 1:05pm.  I still get error 3061 - Too few parameters. Expected 1.
0
 
LVL 6

Expert Comment

by:AkAlan
Comment Utility
Try declaring variables first

Dim eventID as Integer
Dim classType as String

eventID =  [Forms]![Calculate Results]![Event ID]
classType = [Forms]![Calculate Results]![Calculate Results - Class Subform].[Form]![Class Type]

Then make your select statemnet like this
Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE (Class.EventID = " & eventID & ") AND
 (Class.[Class Type] = '" & classtype & "')")

Are you using the Locals window to see what you are actually concatinating?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:ProgrammingGrandma
Comment Utility
I tried something new, which of course doesn't work either. Here is my code:

Function Check4EmptyD()

'CALL THIS FUNCTION BEFORE PAYOUT - 1D IN THE PAYOUT MACRO. This code is only called for "D" format races.

Dim db As DAO.Database
Dim class_rs As DAO.recordset
Dim RSQuery As String

Dim EmptyDPayout As Currency    'Total amount of payout originally allocated to "D"s that are empty
Dim NumEmptyDs As Integer       'Number of "D"s with no entries

'Build the query statement that will define the recordset we need.
'That recordset will contain the record from the class table that corresponds to the current Event and Class.
RSQuery = "SELECT * FROM Class WHERE ((Class.EventID = 56) AND(Class.[Class Type] = 'Open Barrels'))"
Set db = CurrentDb
Set class_rs = db.OpenRecordset(RSQuery)

I set a watch with the debugger on RSQuery and verified that the string was initialized as expected.  When I execute the OpenRecordset, I still get the error:

Run-time error 3061: Too few parameters. 1 expected.
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
missing a closing bracket :

RSQuery = "SELECT * FROM Class WHERE ((Class.EventID = 56) AND(Class.[Class Type] = 'Open Barrels')))"
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
or I could just be talking rubbish
0
 
LVL 6

Expert Comment

by:AkAlan
Comment Utility
Yea, too many closing on your last post Jacko
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
grasping at straws now but what about a space between the AND and (

RSQuery = "SELECT * FROM Class WHERE ((Class.EventID = 56) AND (Class.[Class Type] = 'Open Barrels'))"
0
 
LVL 6

Expert Comment

by:AkAlan
Comment Utility

Here is a quick database with the code you are trying to run. MAke the changes you need to and go from there.
Database2.accdb
0
 

Author Comment

by:ProgrammingGrandma
Comment Utility
Jacko72:

I tried putting a space after AND, but no change.

AkAlan:

I tried implementing the code as you suggested.  Here is the cut and paste of my code:

Function Check4EmptyD()

'CALL THIS FUNCTION BEFORE PAYOUT - 1D IN THE PAYOUT MACRO. This code is only called for "D" format races.

Dim db As DAO.Database
Dim class_rs As DAO.recordset
Dim RSQuery As String

Dim EventIdvar As Integer
Dim ClassTypevar As String
Dim EmptyDPayout As Currency    'Total amount of payout originally allocated to "D"s that are empty
Dim NumEmptyDs As Integer       'Number of "D"s with no entries

'Build the query statement that will define the recordset we need.
'That recordset will contain the record from the class table that corresponds to the current Event and Class.
'RSQuery = "SELECT * FROM Class WHERE ((Class.EventID = 56) AND (Class.[Class Type] = 'Open Barrels'))"

Set db = CurrentDb
EventIdvar = [Forms]![Calculate Results]![Event ID]
ClassTypevar = [Forms]![Calculate Results]![Calculate Results - Class Subform].[Form]![Class Type]
Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE (Class.EventID="&EventIDvar&") AND (Class.[Class Type]='"&ClassTypevar&"')")

When I run this, I get the enlightening error message, "Compile error: Syntax error."  Access drops me into the code with the entire OpenRecordset line of code highlighted blue.
0
 
LVL 6

Expert Comment

by:AkAlan
Comment Utility
Provide a mdb with your table and some sample data and the form and I'll help.
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
try this :
Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE (Class.EventID=" & EventIDvar & ") AND (Class.[Class Type]='" & ClassTypevar & "')")

0
 

Author Comment

by:ProgrammingGrandma
Comment Utility
jacko72:

I can't see how what you are suggesting is different than what I already have.

AkAlan:

I'm obviously new to Access and not sure what you mean by an mdb.  Can I just send you the whole database and tell you how to get to the problem code?
0
 
LVL 6

Expert Comment

by:AkAlan
Comment Utility
Yes an mdb is a way of saying the whole database. Just make a copy and delete everything except the form, and the table with just a few records that match the query.
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
I think you'll find that the spaces are important
0
 

Author Comment

by:ProgrammingGrandma
Comment Utility
AkAlan,

One more question.  How do I attach a file to this message?  and how can reading one record from a file be so difficult?...
0
 
LVL 6

Expert Comment

by:AkAlan
Comment Utility
Just below where you type to reply to this is a link called File. Click on it and it will let you browse to the file and upload it.
0
 

Author Comment

by:ProgrammingGrandma
Comment Utility
Here is the pared down database program.  It tries to start a form called "Main Menu" when it starts but since I deleted that form, you will get an error.  Just open the form "Calculate Results".  I eliminated all data except for the event "Incentive Test" which is event #56.  Select the class "Open Barrels" on the form and press the Calculate Results button.  That will execute the code with the syntax error on OpenRecordset.  

The code with the problem is in the module called "EmptyD".  Of course, I haven't been able to test any of the code following the OpenRecordset so no telling what will happen if you get that to compile.

Thanks
THCBRA-Barrels---EE.accdb
0
 
LVL 6

Accepted Solution

by:
AkAlan earned 500 total points
Comment Utility
I didn't realize you were working with Macros. I have never used them before and I don't recommend them to anyone. Everything you do should be with VBA.

OK, so I added the code that will get you what you want. If you step through the code you will see that the fields in the recordset are populated with the correct data. I only selected the first 4 fields just so I could make sure it works. you can obviously delete that part. Good Luck with the rest of it. Looks pretty involved for a begginer.
THCBRA-Barrels---EE.accdb
0
 

Author Closing Comment

by:ProgrammingGrandma
Comment Utility
Swapping the order of the 2 conditions in the WHERE clause fixed the problem.  I guess the compiler was confused by the order of quotation marks.

As for macros vs code, my partner on the project uses macros and I prefer code.  I'm gradually converting her.  It is a fairly complex problem. I am a beginner in Access but was a professional programmer in the dark ages of software.  My first husband and I wrote Btrieve, if you are old enough to remember that database.
0
 
LVL 6

Expert Comment

by:AkAlan
Comment Utility
I appreciate the points. I really don't think the order of the
Parameters had anything to do with the fix. I had made
A query to grab the SQL code and it just worked that
Way.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now