query problem - recordset not opening

[JB]

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!

Else
'Update ItemsTable record with new info
Debug.Print ("attempting to open recordset")
If Me.ItemID <> Null Then
'Opens the recordset, getting the matching row from the itemstable with
the current itemid
rst.Open _
"SELECT FROM * ItemsTable *" _
& "WHERE ItemsTable.[ItemID] = Me![RecordingID]" _
& " And Me![ItemID] = ItemsTable.[EntryID], CurConn, , , adCmdText"
End If
'Recorset Status
If rst.State = 1 Then
Debug.Print ("Open Recordset! " & rst.RecordCount & " Records Selected")
'Determines whether records were found in the query
If rst.RecordCount <> 0 Then
rst![ItemAccessed] = Me![lblAccessed].Caption
rst![ItemModified] = Me![lblModified].Caption
rst.Update
MsgBox ("Record Updated Successfully!!")
End If
End If
Set rst = Nothing
CurConn.Close
End If

-Yaw

[Phil]

JB <im******@nospam.com> posted in
news:10*************@corp.supernews.com

& " And Me![ItemID] = ItemsTable.[EntryID], CurConn, , ,

try
& " and " & me![itemid] & " itemstable etc.
--
Phil

[Pieter Linden]

"JB" <im******@nospam.com> wrote in message news:<10*************@corp.supernews.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.

[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.Connection

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

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

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

"JB" <im******@nospam.com> wrote in message

news:<10*************@corp.supernews.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.