Pass a variable to a Pass Through Query

[violeta_barajas]

Hello,

I have below pass through query object created in MS Access that makes an SELECT from an Oracle table. The issue with this is that when passing the variable from VBA to this query object, error 3265 "item not found in this collection" is generated.

Here the code in VBA:

Set myQuery = myDB.QueryDefs("SelectFromUsersQuery")
myQuery.Parameters("user_id") = Me.[USER_ID] ' The error is generated at this point
myQuery.Execute
myQuery.Close

Here the local pass through query:
SELECT USERID, FIRST_NAME FROM USERS WHERE USERID = &user_id;

I intent for Oracle to execute this query and not MS Access. But I need that value from that form.


Thank you.

[ranman256]

how bout?

Set myQuery = currentDB.QueryDefs("SelectFromUsersQuery")

[pbaldy]

Try

myQuery.SQL = Replace(myQuery.SQL, "&user_id", Me.[USER_ID] )