query problem - recordset not opening / runtime error 3709

[JB]

Thanks to both of you for responding.

To Pieter: The second asterisk actually isn't there. The original code
reads
just as you suggested it should.
To Phil: I tried changing the 'And' to 'and' --no luck. I also did the
following.

verified that the types of var in the sql statement were the same.
verified that the variables held values at the time the statement is
generated.
verified that the connection was open.
debugger reports :
CurConn.State = 1
Rst.State = 0

With those conditions, the error message is vb runtime error 3709-- "The
connecton cannot be used to perform this operation. It is either closed or
invalid in this context." Maybe I made a mistake in defining CurConn? This
is the code I used before the If statement, if that makes a difference...

Set CurConn = New ADODB.Connectio n

With CurConn
.Provider = "Microsoft.Jet. OLEDB.4.0"
.ConnectionStri ng = CurrentProject. Connection
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimisti c
-Yaw

"Pieter Linden" <pi********@hot mail.com> wrote in message
news:bf******** *************** ***@posting.goo gle.com...

"JB" <im******@nospa m.com> wrote in message news:<10******* ******@corp.sup ernews.com>...

Hello,
I am trying to write a query that selects a row from another table

with a field that matches the Me!RecordingID that's on the form. For some reason my query is not working and the recordset I am trying to build will not open. Can someone help me with this? Thanks for your help!

rst.Open _
"SELECT FROM * ItemsTable *" _
& "WHERE ItemsTable.[ItemID] = Me![RecordingID]" _
& " And Me![ItemID] = ItemsTable.[EntryID], CurConn, , , adCmdText" -Yaw

This query looks a bit dodgy. It should be something like

SELECT * FROM ItemsTable WHERE...

What the extra asterisk is doing there, I don't know, but that will
definitely cause problems.

[Pieter Linden]

"JB" <im******@nospa m.com> wrote in message news:<10******* ******@corp.sup ernews.com>...

Thanks to both of you for responding.

To Pieter: The second asterisk actually isn't there. The original code
reads
just as you suggested it should.
To Phil: I tried changing the 'And' to 'and' --no luck. I also did the
following.

verified that the types of var in the sql statement were the same.
verified that the variables held values at the time the statement is
generated.
verified that the connection was open.
debugger reports :
CurConn.State = 1
Rst.State = 0

With those conditions, the error message is vb runtime error 3709-- "The
connecton cannot be used to perform this operation. It is either closed or
invalid in this context." Maybe I made a mistake in defining CurConn? This
is the code I used before the If statement, if that makes a difference...

Set CurConn = New ADODB.Connectio n

With CurConn
.Provider = "Microsoft.Jet. OLEDB.4.0"
.ConnectionStri ng = CurrentProject. Connection
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimisti c
-Yaw

Shouldn't it be...

set currConn=Curren tproject.Connec tion

I don't think you even need most of that other stuff...

[Pieter Linden]

"JB" <im******@nospa m.com> wrote in message news:<10******* ******@corp.sup ernews.com>...

Thanks to both of you for responding.

To Pieter: The second asterisk actually isn't there. The original code
reads
just as you suggested it should.
To Phil: I tried changing the 'And' to 'and' --no luck. I also did the
following.

verified that the types of var in the sql statement were the same.
verified that the variables held values at the time the statement is
generated.
verified that the connection was open.
debugger reports :
CurConn.State = 1
Rst.State = 0

With those conditions, the error message is vb runtime error 3709-- "The
connecton cannot be used to perform this operation. It is either closed or
invalid in this context." Maybe I made a mistake in defining CurConn? This
is the code I used before the If statement, if that makes a difference...

Set CurConn = New ADODB.Connectio n

With CurConn
.Provider = "Microsoft.Jet. OLEDB.4.0"
.ConnectionStri ng = CurrentProject. Connection
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimisti c
-Yaw

Shouldn't it be...

set currConn=Curren tproject.Connec tion

I don't think you even need most of that other stuff...