[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 858
  • Last Modified:

Parameters for OpenRecordset in VB

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
ProgrammingGrandma
Asked:
ProgrammingGrandma
  • 12
  • 12
  • 11
1 Solution
 
AkAlanCommented:
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
 
Paul JacksonCommented:
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
 
AkAlanCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ProgrammingGrandmaAuthor Commented:
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
 
Paul JacksonCommented:
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
 
ProgrammingGrandmaAuthor Commented:
I tried putting the single quote both outside the double quotes (as you did) and inside.  Both ways I get a syntax error.
0
 
Paul JacksonCommented:
Is it the same syntax error?
0
 
ProgrammingGrandmaAuthor Commented:
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
 
AkAlanCommented:
Try hard coding the parameters with known values first.
0
 
Paul JacksonCommented:
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
 
ProgrammingGrandmaAuthor Commented:
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
 
Paul JacksonCommented:
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
 
AkAlanCommented:
Looks like there should be a single quote after Open Barrels like this (Class.[Class Type] = 'Open Barrels'))'
0
 
ProgrammingGrandmaAuthor Commented:
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
 
Paul JacksonCommented:
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
 
ProgrammingGrandmaAuthor Commented:
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
 
AkAlanCommented:
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
 
ProgrammingGrandmaAuthor Commented:
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
 
Paul JacksonCommented:
missing a closing bracket :

RSQuery = "SELECT * FROM Class WHERE ((Class.EventID = 56) AND(Class.[Class Type] = 'Open Barrels')))"
0
 
Paul JacksonCommented:
or I could just be talking rubbish
0
 
AkAlanCommented:
Yea, too many closing on your last post Jacko
0
 
Paul JacksonCommented:
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
 
AkAlanCommented:

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
 
ProgrammingGrandmaAuthor Commented:
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
 
AkAlanCommented:
Provide a mdb with your table and some sample data and the form and I'll help.
0
 
Paul JacksonCommented:
try this :
Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE (Class.EventID=" & EventIDvar & ") AND (Class.[Class Type]='" & ClassTypevar & "')")

0
 
ProgrammingGrandmaAuthor Commented:
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
 
AkAlanCommented:
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
 
Paul JacksonCommented:
I think you'll find that the spaces are important
0
 
ProgrammingGrandmaAuthor Commented:
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
 
AkAlanCommented:
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
 
ProgrammingGrandmaAuthor Commented:
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
 
AkAlanCommented:
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
 
ProgrammingGrandmaAuthor Commented:
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
 
AkAlanCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 12
  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now