Error on ADO RecordSet.Open

[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

 

[RoyVidar]

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.

 

[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