Need help with syntax error using ADO RecordSet.Open

G

Guest

I'm using ACCESS 2003, ADO Data Objects library 2.8.

I am receiving a syntax error at runtime on the RecordSet.Open command. The
error is: "No value given for one or more required parameters". Obviously I
have something coded wrong, but I sure can't see the problem.

My code is (note: the SQL statement is not word wrapped in my module):

Private Sub PType_AfterUpdate()

Dim myConnection As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim mySQL As String

Set myConnection = CurrentProject.Connection
myRecordSet.ActiveConnection = myConnection

mySQL = "SELECT [Product Types].UnitPrice FROM [Product Types] WHERE
((([Product Types].VendorName)=[Vendor]) AND (([Product
Types].Name)=[Product]) AND (([Product Types].Type)=[PType]))"

myRecordSet.Open mySQL, , adOpenStatic

myRecordSet.MoveFirst

Me!UPrice = myRecordSet.Fields("UnitPrice")

myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing

End Sub
 
6

'69 Camaro

Hi, Richard.
I am receiving a syntax error at runtime on the RecordSet.Open command.
The
error is: "No value given for one or more required parameters".

There aren't any values assigned to [Vendor], [Product], and [PType] within
that string. If these are text box control names, then you need to
concatenate the values of these text box controls with the rest of the
string. And if any one of these text boxes contains a value that isn't a
numerical data type, it needs to be delimited with quotes for strings and #
for dates. (It doesn't appear that yours contains a date data type, but I'm
just mentioning it for future reference).

Assuming Vendor and Product are strings and PType is a number (I don't
really know, but that's my best guess based upon your names, so change the
delimiters appropriately if you are using different data types), change the
following:

mySQL = "SELECT [Product Types].UnitPrice FROM [Product Types] WHERE
((([Product Types].VendorName)=[Vendor]) AND (([Product
Types].Name)=[Product]) AND (([Product Types].Type)=[PType]))"

.. . . to this (watch out for word wrap):

mySQL = "SELECT [Product Types].UnitPrice " & _
"FROM [Product Types] " & _
"WHERE ([Product Types].VendorName = '" & Me!Vendor.Value & _
"' AND [Product Types].[Name] = '" & Me!Product.Value & _
"' AND [Product Types].[Type] = " & Me!PType.Value & ")"

Both Name and Type are Reserved Words, and therefore shouldn't be used when
naming objects to avoid Jet and Access using them for their real uses
instead of what you intended them to be, which introduces bugs into your
code. Brackets surrounding the names can often be used to fix these syntax
and logic errors, but there's no guarantee that it works in all cases. As a
rule of thumb, if brackets are required to fix these errors, then better
names should be used. Only use alphanumeric characters and the underscore
character and avoid Reserved Words for names, and you'll avoid those bugs --
and having to ask someone else why your code doesn't work.

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.


Richard said:
I'm using ACCESS 2003, ADO Data Objects library 2.8.

I am receiving a syntax error at runtime on the RecordSet.Open command.
The
error is: "No value given for one or more required parameters". Obviously
I
have something coded wrong, but I sure can't see the problem.

My code is (note: the SQL statement is not word wrapped in my module):

Private Sub PType_AfterUpdate()

Dim myConnection As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim mySQL As String

Set myConnection = CurrentProject.Connection
myRecordSet.ActiveConnection = myConnection

mySQL = "SELECT [Product Types].UnitPrice FROM [Product Types] WHERE
((([Product Types].VendorName)=[Vendor]) AND (([Product
Types].Name)=[Product]) AND (([Product Types].Type)=[PType]))"

myRecordSet.Open mySQL, , adOpenStatic

myRecordSet.MoveFirst

Me!UPrice = myRecordSet.Fields("UnitPrice")

myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing

End Sub
 
6

'69 Camaro

Hi, Richard.

For lists of keywords and Reserved Words to avoid, please see the following
Web pages:

http://support.microsoft.com/default.aspx?scid=286335

http://support.microsoft.com/default.aspx?id=321266

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.


'69 Camaro said:
Hi, Richard.
I am receiving a syntax error at runtime on the RecordSet.Open command.
The
error is: "No value given for one or more required parameters".

There aren't any values assigned to [Vendor], [Product], and [PType]
within that string. If these are text box control names, then you need to
concatenate the values of these text box controls with the rest of the
string. And if any one of these text boxes contains a value that isn't a
numerical data type, it needs to be delimited with quotes for strings and
# for dates. (It doesn't appear that yours contains a date data type, but
I'm just mentioning it for future reference).

Assuming Vendor and Product are strings and PType is a number (I don't
really know, but that's my best guess based upon your names, so change the
delimiters appropriately if you are using different data types), change
the following:

mySQL = "SELECT [Product Types].UnitPrice FROM [Product Types] WHERE
((([Product Types].VendorName)=[Vendor]) AND (([Product
Types].Name)=[Product]) AND (([Product Types].Type)=[PType]))"

. . . to this (watch out for word wrap):

mySQL = "SELECT [Product Types].UnitPrice " & _
"FROM [Product Types] " & _
"WHERE ([Product Types].VendorName = '" & Me!Vendor.Value & _
"' AND [Product Types].[Name] = '" & Me!Product.Value & _
"' AND [Product Types].[Type] = " & Me!PType.Value & ")"

Both Name and Type are Reserved Words, and therefore shouldn't be used
when naming objects to avoid Jet and Access using them for their real uses
instead of what you intended them to be, which introduces bugs into your
code. Brackets surrounding the names can often be used to fix these
syntax and logic errors, but there's no guarantee that it works in all
cases. As a rule of thumb, if brackets are required to fix these errors,
then better names should be used. Only use alphanumeric characters and
the underscore character and avoid Reserved Words for names, and you'll
avoid those bugs -- and having to ask someone else why your code doesn't
work.

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.


Richard said:
I'm using ACCESS 2003, ADO Data Objects library 2.8.

I am receiving a syntax error at runtime on the RecordSet.Open command.
The
error is: "No value given for one or more required parameters".
Obviously I
have something coded wrong, but I sure can't see the problem.

My code is (note: the SQL statement is not word wrapped in my module):

Private Sub PType_AfterUpdate()

Dim myConnection As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim mySQL As String

Set myConnection = CurrentProject.Connection
myRecordSet.ActiveConnection = myConnection

mySQL = "SELECT [Product Types].UnitPrice FROM [Product Types] WHERE
((([Product Types].VendorName)=[Vendor]) AND (([Product
Types].Name)=[Product]) AND (([Product Types].Type)=[PType]))"

myRecordSet.Open mySQL, , adOpenStatic

myRecordSet.MoveFirst

Me!UPrice = myRecordSet.Fields("UnitPrice")

myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top