Quick Question. Problem with rst.open

[momon]

When I try to open the form, it gives me the error:
"The connection cannot be used to perform this operation. It is either closed or invalid in this context."

Here is the code for the On_Load event for the form:

Private Sub Form_Load()
On Error GoTo Form_Load_Err
Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As Database

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.Name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

'something is wrong here
rst.Open "SELECT WSPECS.*, WSPECS.

, WSPECS.[ITEM NUMBER] FROM WSPECS INNER JOIN INSSPECS ON (WSPECS.

= INSSPECS.

) AND (WSPECS.[ITEM NUMBER] = INSSPECS.[ITEM NUMBER]) WHERE (((WSPECS.

)=[forms]![start]!

) AND ((WSPECS.[ITEM NUMBER])=[forms]![start]![item number]))", CurConn, , , adCmdText

Me![Text0] = rst!

Me![Text2] = rst![item number]
Me![Text4] = rst![Nick]
rst.Update
rst.Close

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err.Description
Resume Form_Load_Exit

End Sub

When I change the SQL statement for rst.Open to something like
SELECT * FROM WSPECS WHERE "

= '" & "something" & "'"
the code runs perfectly.
I was wondering maybe there's something wrong with the syntax of the rst.open statement.
The SQL statement I have specified in the code should be correct since it was generated using MS ACCESS.

Thanks in advance.

 

[FancyPrairie]

Try this:

dim cnn as ADODB.Connection
Dim rst as ADODB.Recordset

Set cnn = CurrentProject.Connection
set rst = New ADODB.Recordset

rst.open = "Select...",cnn

 

[momon]

hmmm.. tried it but now there is a compile error.
The SQL is pasted right from Access, so I am not sure what is wrong.

Code looks like this now.

Private Sub Form_Load()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
'syntax error here

rst.Open = "SELECT WSPECS.*, WSPECS.

, WSPECS.[ITEM NUMBER] FROM WSPECS INNER JOIN INSSPECS ON (WSPECS.[ITEM NUMBER] = INSSPECS.[ITEM NUMBER]) AND (WSPECS.

= INSSPECS.

)WHERE (((WSPECS.

)=[forms]![start]!

) AND ((WSPECS.[ITEM NUMBER])=[forms]![start]![item number]))", cnn

End Sub

 

[RiverGuy]

you need to add " & _ " after each line. It doesn't know that you're continuing with your same statement when there are line breaks.

 

[FancyPrairie]

It should look like this:


rst.Open = "SELECT WSPECS.*, WSPECS.

, WSPECS.[ITEM NUMBER] FROM WSPECS INNER JOIN INSSPECS ON (WSPECS.[ITEM NUMBER] = INSSPECS.[ITEM NUMBER]) AND (WSPECS.

= INSSPECS.

) WHERE (((WSPECS.

)=" & [forms]![start]!

& "

) AND ((WSPECS.[ITEM NUMBER])=" & [forms]![start]![item number] & "))", cnn

I'm assumming that [forms]![start]!

and [forms]![start]![item number] are numbers.

Also, I put a space before the "Where" clause.

 

[LittleSmudge]

The problem with your rst.Open SQL string is that you are saying you want ALL fields from WSPECS ( WSPECS.* ) AND you want some additional fields from WSPECS was well ( WSPECS.QuoteNumber etc.. ) So the recordset tries to open with duplicate field names within it.

Sort out in your mind - do you want ALL fields from WSPECS or just the selected list. You don't need both.


And a mod to FancyPrairie's first post

Dim rst as ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection

rst.open = "Select..."

Is even simpler way of doing the same thing.


The reason momon hasn't got " & _" at the end of the lines RiverGuy is because the lines only appear on the tec-tips board because of the width restriction.
If there are no line breaks in the original code then the " & _" are not needed - Though I do agree they make reading the code a lot easier.
Something like :-

rst.Open "SELECT WSPECS.* " _
       & "FROM WSPECS INNER JOIN INSSPECS " _
       & "ON (WSPECS.[quote=NUMBER] = INSSPECS.[quote=NUMBER]) " _
       & "AND (WSPECS.[ITEM NUMBER] = INSSPECS.[ITEM NUMBER]) " _
       & "WHERE (((WSPECS.[quote=NUMBER])=Forms!start![quote=number]) " _
       & "AND ((WSPECS.[ITEM NUMBER])=Forms!start![item number]))"

'ope-that-'elps.

G LS

 

[ShawnR]

I had a very similar issue just yesterday with pulling in fields from form fields and the code above works for me if you break it up as in the code above.