ADODB Recordset won't work

[kagard]

Greetings:

I keep getting "No value given for one or more required parameters,"
when trying to open a recordset. Here's the pertinent code:

Dim rsProgram As ADODB.Recordset
SQLStmt = "SELECT * FROM tblPrograms WHERE Mid([strProgramNumber],
6, 2)='" & ProgramType & "';"
Set rsProgram = New ADODB.Recordset
rsProgram.Open SQLStmt, CurrentProject.Connection, adOpenStatic,
adLockOptimistic

The error appears on execution of the last line. When I drop the WHERE
clause, the code works but, obviously, grabs more records than I need.
In the Immediate window, I get the following results in break mode:

? Mid([strProgramNumber], 6, 2)
S1
? ProgramType
S1
? Mid([strProgramNumber], 6, 2) = ProgramType
True

I don't see what I'm doing wrong. Any ideas. TIA

Keith

 

[Graham R Seach]

Keith,

Your code looks OK, but I don't see where ProgramType is defined or set. I'm
guessing that since you didn't get an error at line 2, that you're simply
not supplying a value for it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Sylvain Lafontaine]

Often, you'll see this error message when the name of one or more
columns/fields is misspelled; so are you sure it's strProgramNumber and not
something else.

BTW, I don't see how you could write « ? Mid([strProgramNumber], 6, 2) » in
the immediate window if strProgramNumber is the name of one of the columns
for the table "tblPrograms".

 

['69 Camaro]

Hi, Keith.

I keep getting "No value given for one or more required parameters,"
when trying to open a recordset.

The following expression:

Mid([strProgramNumber], 6, 2)

.. . . _must_ evaluate to the name of a column in tblPrograms, and
ProgramType must evaluate to a string value that can be assigned to the
column. Most likely, your expression, S1, isn't the name of a column in the
table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Greetings:

I keep getting "No value given for one or more required parameters,"
when trying to open a recordset. Here's the pertinent code:

Dim rsProgram As ADODB.Recordset
SQLStmt = "SELECT * FROM tblPrograms WHERE Mid([strProgramNumber],
6, 2)='" & ProgramType & "';"
Set rsProgram = New ADODB.Recordset
rsProgram.Open SQLStmt, CurrentProject.Connection, adOpenStatic,
adLockOptimistic

The error appears on execution of the last line. When I drop the WHERE
clause, the code works but, obviously, grabs more records than I need.
In the Immediate window, I get the following results in break mode:

? Mid([strProgramNumber], 6, 2)
S1
? ProgramType
S1
? Mid([strProgramNumber], 6, 2) = ProgramType
True

I don't see what I'm doing wrong. Any ideas. TIA

Keith

 

[kagard]

Hi Sylvain:

All the names are right - I check them 3 or 4 times. But your comment
about the immediate window is interesting. strProgramNumber appears in
the recordset of the current record of my continuous form, and in
tblPrograms. The mid value the immediate window returned was probably
for the value of strProgramNumber in the current record of my form,
which would be the same as ProgramType.

Thanks for your help.

Keith

 

[kagard]

Hi Gunny:

The Mid function does not evaluate to a field name in tblPrograms, but
to a substring of the contents of the field. What is the most
efficient way to say "Get the program requirements from the record in
tblPrograms whose program number has "S1" in positions 6 and 7"?
(Could be "S1", "S2", etc.)

Thanks to you, and to the others who have taken time to respond.

Keith

Hi,Keith.

I keep getting "No value given for one or more required parameters,"
when trying to open a recordset.

The following expression:

Mid([strProgramNumber], 6, 2)

. . . _must_ evaluate to the name of a column in tblPrograms, and
ProgramType must evaluate to a string value that can be assigned to the
column. Most likely, your expression, S1, isn't the name of a column in the
table.

 

[kagard]

Thanks Graham:

ProgramType is Mid([strProgramNumber], 6, 2) for the current record on
a continuous form. The value is assigned before I get to this point in
the code, and it work.

What I'm trying to do is find the program type ("S1" or "S2") imbedded
in the program number of that record, and then lookup various program
completion requirements in tblPrograms for the program of that type.

 

[Sylvain Lafontaine]

Hum, so you are trying to open this recordset from inside a bound form and
probably/possibly against the same record as the current one? Maybe a
locking problem here (either a record lock or a page lock): it's possible
that the returned error message is not necessarily the right one (not
remembering for sure but I think that I've heard of this possibility in the
past).

You should try opening other records (and possibly other records not on the
same page if you are using page locking instead of record locking) to make
sure that this not simply a locking problem.

Any reason why you are using ADO instead of DAO in a MDB database against
itself?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Hi Sylvain:

All the names are right - I check them 3 or 4 times. But your comment
about the immediate window is interesting. strProgramNumber appears in
the recordset of the current record of my continuous form, and in
tblPrograms. The mid value the immediate window returned was probably
for the value of strProgramNumber in the current record of my form,
which would be the same as ProgramType.

Thanks for your help.

Keith

 

[David W. Fenton]

ProgramType is Mid([strProgramNumber], 6, 2) for the current
record on a continuous form. The value is assigned before I get to
this point in the code, and it work.

Is strProgramNumber a public variable? It would appear to be since
you say in another reply that the expression works in the Immediate
Window.

You can't refer directly to a variable in a query -- you need a
function that returns the value of the variable in question.

 

[Graham R Seach]

Keith,

Your error says "No value given for one or more required parameters". That
means a required parameter is NOT being given a value. There are only two
parameters I can see which would fire that particular error.

Is this an MDB or an ADP?? If you're in an MDB, then you can't use
CurrentProject.Connection, because the CurrentProject doesn't have a
connection. In that case, you're probably better to use DAO.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Thanks Graham:

ProgramType is Mid([strProgramNumber], 6, 2) for the current record on
a continuous form. The value is assigned before I get to this point in
the code, and it work.

What I'm trying to do is find the program type ("S1" or "S2") imbedded
in the program number of that record, and then lookup various program
completion requirements in tblPrograms for the program of that type.

Keith,

Your code looks OK, but I don't see where ProgramType is defined or set.
I'm
guessing that since you didn't get an error at line 2, that you're simply
not supplying a value for it.

 

['69 Camaro]

Hi, Keith.

The Mid function does not evaluate to a field name in tblPrograms

With the syntax in your WHERE clause, it _has_ to use the actual column name
or evaluate to an actual column name -- not a variable, or a string, or a
substring of some variable string.

Let me try to explain this in a different way. If your WHERE clause doesn't
tell the database engine which column name to look for the value "S1" then
why would the database engine look for the value "S1" in the CustomerID
column as opposed to ShippingDate, or OrderID, or ProductID, or Quantity, or
any other column?

Answer: It won't look in any of the columns of the table for a certain
value.

It will evaluate the strings you gave it instead, and totally ignore all of
the column names and values in each of the columns for every record in the
table. Therefore, you're passing the WHERE clause (roughly) equivalent to
the following:

WHERE S1 = 'S1'

.. . . and the database engine can't find the S1 column name, because it
doesn't exist. Change your syntax to the following:

sqlStmt = "SELECT * FROM tblPrograms WHERE ColumnName = '" & _
ProgramType & "';"

Replace ColumnName with the full, exact spelling of the actual column name.
(The column name that contains "S1," not a substring of the column name.)

If you want to use a variable to determine the column name, use syntax such
as the following:

sqlStmt = "SELECT * FROM tblPrograms WHERE " & _
Mid([strProgramNumber], 6, 2) & " = '" & ProgramType & "';"

In your case, the column name S1 _must_ exist in the tblPrograms table if
the above syntax is used. If it doesn't exist, you'll receive the error
message you're complaining about.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Hi Gunny:

The Mid function does not evaluate to a field name in tblPrograms, but
to a substring of the contents of the field. What is the most
efficient way to say "Get the program requirements from the record in
tblPrograms whose program number has "S1" in positions 6 and 7"?
(Could be "S1", "S2", etc.)

Thanks to you, and to the others who have taken time to respond.

Keith

Hi,Keith.

I keep getting "No value given for one or more required parameters,"
when trying to open a recordset.

The following expression:

Mid([strProgramNumber], 6, 2)

. . . _must_ evaluate to the name of a column in tblPrograms, and
ProgramType must evaluate to a string value that can be assigned to the
column. Most likely, your expression, S1, isn't the name of a column in
the
table.

 

['69 Camaro]

Hi, Keith.

Correction: Now that I think more on it, the strProgramNumber variable
doesn't have a value assigned to it within the SQL string. So the WHERE
clause isn't equivalent to WHERE S1 = 'S1' -- not even roughly -- even
though you can read the variable's value within the VBA procedure. It's
just an undeclared parameter with an unassigned value when it's inside the
SQL string.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Hi, Keith.

The Mid function does not evaluate to a field name in tblPrograms

With the syntax in your WHERE clause, it _has_ to use the actual column
name or evaluate to an actual column name -- not a variable, or a string,
or a substring of some variable string.

Let me try to explain this in a different way. If your WHERE clause
doesn't tell the database engine which column name to look for the value
"S1" then why would the database engine look for the value "S1" in the
CustomerID column as opposed to ShippingDate, or OrderID, or ProductID, or
Quantity, or any other column?

Answer: It won't look in any of the columns of the table for a certain
value.

It will evaluate the strings you gave it instead, and totally ignore all
of the column names and values in each of the columns for every record in
the table. Therefore, you're passing the WHERE clause (roughly)
equivalent to the following:

WHERE S1 = 'S1'

. . . and the database engine can't find the S1 column name, because it
doesn't exist. Change your syntax to the following:

sqlStmt = "SELECT * FROM tblPrograms WHERE ColumnName = '" & _
ProgramType & "';"

Replace ColumnName with the full, exact spelling of the actual column
name. (The column name that contains "S1," not a substring of the column
name.)

If you want to use a variable to determine the column name, use syntax
such as the following:

sqlStmt = "SELECT * FROM tblPrograms WHERE " & _
Mid([strProgramNumber], 6, 2) & " = '" & ProgramType & "';"

In your case, the column name S1 _must_ exist in the tblPrograms table if
the above syntax is used. If it doesn't exist, you'll receive the error
message you're complaining about.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Hi Gunny:

The Mid function does not evaluate to a field name in tblPrograms, but
to a substring of the contents of the field. What is the most
efficient way to say "Get the program requirements from the record in
tblPrograms whose program number has "S1" in positions 6 and 7"?
(Could be "S1", "S2", etc.)

Thanks to you, and to the others who have taken time to respond.

Keith

Hi,Keith.

I keep getting "No value given for one or more required parameters,"
when trying to open a recordset.

The following expression:

Mid([strProgramNumber], 6, 2)

. . . _must_ evaluate to the name of a column in tblPrograms, and
ProgramType must evaluate to a string value that can be assigned to the
column. Most likely, your expression, S1, isn't the name of a column in
the
table.

 

[kagard]

Hi Gunny:

Thanks for your insights. Got it working.

Thanks to all who participated in this thread.

Keith

 

[Aaron Kempf]

I'm not so sure that I agree with that; kid

if you want to spread mis-information you'll have a hard time in this
newsgroup

Keith,

Your error says "No value given for one or more required parameters". That
means a required parameter is NOT being given a value. There are only two
parameters I can see which would fire that particular error.

Is this an MDB or an ADP?? If you're in an MDB, then you can't use
CurrentProject.Connection, because the CurrentProject doesn't have a
connection. In that case, you're probably better to use DAO.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Thanks Graham:

ProgramType is Mid([strProgramNumber], 6, 2) for the current record on
a continuous form. The value is assigned before I get to this point in
the code, and it work.

What I'm trying to do is find the program type ("S1" or "S2") imbedded
in the program number of that record, and then lookup various program
completion requirements in tblPrograms for the program of that type.

Keith,

Your code looks OK, but I don't see where ProgramType is defined or set.
I'm
guessing that since you didn't get an error at line 2, that you're simply
not supplying a value for it.

 

[Aaron Kempf]

'better to use DAO'

where do you get off, kid

Keith,

Your error says "No value given for one or more required parameters". That
means a required parameter is NOT being given a value. There are only two
parameters I can see which would fire that particular error.

Is this an MDB or an ADP?? If you're in an MDB, then you can't use
CurrentProject.Connection, because the CurrentProject doesn't have a
connection. In that case, you're probably better to use DAO.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Thanks Graham:

ProgramType is Mid([strProgramNumber], 6, 2) for the current record on
a continuous form. The value is assigned before I get to this point in
the code, and it work.

What I'm trying to do is find the program type ("S1" or "S2") imbedded
in the program number of that record, and then lookup various program
completion requirements in tblPrograms for the program of that type.

Keith,

Your code looks OK, but I don't see where ProgramType is defined or set.
I'm
guessing that since you didn't get an error at line 2, that you're simply
not supplying a value for it.

 

[Aaron Kempf]

CurrentProject.connection DOES WORK KID
Seriously if you want to spread mis-information you are going to have a hard
time in this newsgroup

SOME OF US HAVE A BACKBONE AND WILL NOT STAND FOR YOUR _LIES_

Option Explicit

Public Sub HelloWorld()

Dim rst As New ADODB.Recordset
rst.Open "Select Name from MSysObjects", CurrentProject.Connection

Do Until rst.EOF
Debug.Print rst!Name
rst.MoveNext
Loop

End Sub

Keith,

Your error says "No value given for one or more required parameters". That
means a required parameter is NOT being given a value. There are only two
parameters I can see which would fire that particular error.

Is this an MDB or an ADP?? If you're in an MDB, then you can't use
CurrentProject.Connection, because the CurrentProject doesn't have a
connection. In that case, you're probably better to use DAO.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Thanks Graham:

ProgramType is Mid([strProgramNumber], 6, 2) for the current record on
a continuous form. The value is assigned before I get to this point in
the code, and it work.

What I'm trying to do is find the program type ("S1" or "S2") imbedded
in the program number of that record, and then lookup various program
completion requirements in tblPrograms for the program of that type.

Keith,

Your code looks OK, but I don't see where ProgramType is defined or set.
I'm
guessing that since you didn't get an error at line 2, that you're simply
not supplying a value for it.

 

[Aaron Kempf]

because the CURRENTPROJECT does not have a connection?

PLEASE tell me where you get off?
Make a new MDB; and paste this in a module, kid


Option Explicit

Public Sub HelloWorld()

Dim rst As New ADODB.Recordset
rst.Open "Select Name from MSysObjects", CurrentProject.Connection

Do Until rst.EOF
Debug.Print rst!Name
rst.MoveNext
Loop

End Sub

Keith,

Your error says "No value given for one or more required parameters". That
means a required parameter is NOT being given a value. There are only two
parameters I can see which would fire that particular error.

Is this an MDB or an ADP?? If you're in an MDB, then you can't use
CurrentProject.Connection, because the CurrentProject doesn't have a
connection. In that case, you're probably better to use DAO.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Thanks Graham:

ProgramType is Mid([strProgramNumber], 6, 2) for the current record on
a continuous form. The value is assigned before I get to this point in
the code, and it work.

What I'm trying to do is find the program type ("S1" or "S2") imbedded
in the program number of that record, and then lookup various program
completion requirements in tblPrograms for the program of that type.

Keith,

Your code looks OK, but I don't see where ProgramType is defined or set.
I'm
guessing that since you didn't get an error at line 2, that you're simply
not supplying a value for it.

 

[Aaron Kempf]

BECAUSE ADO WON?

WHAT ARE YOU TALKING ABOUT SYLVAIN-- DAO?

WHAT PLANET ARE YOU FROM

Hum, so you are trying to open this recordset from inside a bound form and
probably/possibly against the same record as the current one? Maybe a
locking problem here (either a record lock or a page lock): it's possible
that the returned error message is not necessarily the right one (not
remembering for sure but I think that I've heard of this possibility in the
past).

You should try opening other records (and possibly other records not on the
same page if you are using page locking instead of record locking) to make
sure that this not simply a locking problem.

Any reason why you are using ADO instead of DAO in a MDB database against
itself?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Hi Sylvain:

All the names are right - I check them 3 or 4 times. But your comment
about the immediate window is interesting. strProgramNumber appears in
the recordset of the current record of my continuous form, and in
tblPrograms. The mid value the immediate window returned was probably
for the value of strProgramNumber in the current record of my form,
which would be the same as ProgramType.

Thanks for your help.

Keith

Often, you'll see this error message when the name of one or more
columns/fields is misspelled; so are you sure it's strProgramNumber and
not
something else.

BTW, I don't see how you could write « ? Mid([strProgramNumber], 6, 2) »
in
the immediate window if strProgramNumber is the name of one of the columns
for the table "tblPrograms".

 

[Tom Ellison]

Dear Keith:

Here's a practice I find invaluable. Put the SQL in a string variable (I
believe you have) and put a breakpoint on the code after you have generated
the string, but before you use it. Look at the value of the string. Paste
that in as the SQL to a new query and run it. Debug that, then copy the
string back into your code, or otherwise edit the code to produce the
changes you had to make to the SQL so it works correctly.

Please let me know if this helps, and if I can be of any other assistance.

Tom Ellison
Access MVP

 

['69 Camaro]

You're welcome. Glad to hear you sorted it out.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.