Solved

Parameters for OpenRecordset in VB

Posted on 2011-02-20
35
842 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 12
  • 11
35 Comments
 
LVL 6

Expert Comment

by:AkAlan
ID: 34938610
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
ID: 34938615
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
ID: 34938629
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
Technology Partners: 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!

 

Author Comment

by:ProgrammingGrandma
ID: 34938724
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
ID: 34938761
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
ID: 34938827
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
ID: 34938838
Is it the same syntax error?
0
 

Author Comment

by:ProgrammingGrandma
ID: 34938856
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
ID: 34938865
Try hard coding the parameters with known values first.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 34938890
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
ID: 34938946
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
ID: 34938959
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
ID: 34938961
Looks like there should be a single quote after Open Barrels like this (Class.[Class Type] = 'Open Barrels'))'
0
 

Author Comment

by:ProgrammingGrandma
ID: 34939018
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
ID: 34939079
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
ID: 34939123
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
ID: 34939159
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
 

Author Comment

by:ProgrammingGrandma
ID: 34939166
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
ID: 34939169
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
ID: 34939180
or I could just be talking rubbish
0
 
LVL 6

Expert Comment

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

Expert Comment

by:Paul Jackson
ID: 34939191
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
ID: 34939267

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
ID: 34939280
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
ID: 34939304
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
ID: 34939316
try this :
Set class_rs = db.OpenRecordset("SELECT * FROM Class WHERE (Class.EventID=" & EventIDvar & ") AND (Class.[Class Type]='" & ClassTypevar & "')")

0
 

Author Comment

by:ProgrammingGrandma
ID: 34939351
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
ID: 34939354
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
ID: 34939355
I think you'll find that the spaces are important
0
 

Author Comment

by:ProgrammingGrandma
ID: 34939357
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
ID: 34939358
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
ID: 34939499
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
ID: 34939588
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
ID: 34939729
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
ID: 34939746
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

717 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