Parameters for OpenRecordset in VB

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.

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] & "))")

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.

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?

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] & "'))")

I tried putting the single quote both outside the double quotes (as you did) and inside.  Both ways I get a syntax error.

Is it the same syntax error?

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.

Try hard coding the parameters with known values first.

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] & ")')")

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.

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] & "')")

Looks like there should be a single quote after Open Barrels like this (Class.[Class Type] = 'Open Barrels'))'

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?

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] & "')")

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.

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?

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.

missing a closing bracket :

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

or I could just be talking rubbish

Yea, too many closing on your last post Jacko

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'))"

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

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.

Provide a mdb with your table and some sample data and the form and I'll help.

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

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?

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.

I think you'll find that the spaces are important

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?...

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.

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

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.

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.