Error on ADO RecordSet.Open


G

Guest

The following code generates a syntax error on the RecordSet.Open command.
In looking at a number of examples it appears to me as if I'm apllying the
syntax properly (please note thea the sql string is word wrapped here, but
not in my app). What am I doing wrong here?

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,myConnection,adOpenStatic)

myRecordSet.MoveFirst

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

myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing

End Sub
 
Ad

Advertisements

R

RoyVidar

Richard said:
The following code generates a syntax error on the RecordSet.Open
command. In looking at a number of examples it appears to me as if
I'm apllying the syntax properly (please note thea the sql string is
word wrapped here, but not in my app). What am I doing wrong here?

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,myConnection,adOpenStatic)

myRecordSet.MoveFirst

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

myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing

End Sub

Drop the parenthesis

myRecordSet.Open mySQL,myConnection,adOpenStatic

Other things - I don't like implicit instantiation

Dim myRecordSet As ADODB.Recordset
Set myRecordSet = New ADODB.Recordset

It should be enough to assign the connection once

With myRecordset
Set .ActiveConnection = CurrentProject.Connection
.Open mySQL, , adOpenStatic
if ((not .bof) and (not .eof)) then
' contains records, do stuff
end if
end with

You probably need to update, too, I think.

I think also that I might consider adding brackets to the [Name] and
[Type] field, at least if there's any more errors when opening.

Though - in these groups, you'd probably be adviced to use DAO in
stead of ADO for Jet tables.
 
Ad

Advertisements

N

NthDegree via AccessMonster.com

In your sql string what is [Vendor], [Product] and [Ptype]? Fields on a form
or ?
The following code generates a syntax error on the RecordSet.Open command.
In looking at a number of examples it appears to me as if I'm apllying the
syntax properly (please note thea the sql string is word wrapped here, but
not in my app). What am I doing wrong here?

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,myConnection,adOpenStatic)

myRecordSet.MoveFirst

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

myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing

End Sub

--
Never let it be said that I was totally comitted to sanity. It is the dark
places of my mind that fascinate me.

NthDegree

Message posted via AccessMonster.com
 

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