Using string variable in Query

  • Thread starter Thread starter BudMan
  • Start date Start date
B

BudMan

I'm trying to load a select statement from code into a query. I'm
getting a run time error 3265, Item not found in this collection. My
code looks like this:

Private Sub cmdCallQry_Click()

CurrentDb.QueryDefs(qryPrjTr).SQL = "SELECT * FROM Transactions WHERE
PrjID = '" & strPrjID & "' "

DoCmd.OpenQuery "qryPrjTr"

End Sub

My query name, table and field are all correct. The line beginning
CurrentDB thru 'strPrjID......" is on one line in my code, so that is
why there is no '&_". The actual code is only two lines long.

Any suggestions on what's wrong.
 
I'm trying to load a select statement from code into a query. I'm
getting a run time error 3265, Item not found in this collection. My
code looks like this:

Private Sub cmdCallQry_Click()

CurrentDb.QueryDefs(qryPrjTr).SQL = "SELECT * FROM Transactions WHERE
PrjID = '" & strPrjID & "' "

DoCmd.OpenQuery "qryPrjTr"

End Sub

My query name, table and field are all correct. The line beginning
CurrentDB thru 'strPrjID......" is on one line in my code, so that is
why there is no '&_". The actual code is only two lines long.

Any suggestions on what's wrong.
Click to expand...

You remembered the quotes in the OpenQuery but not in the QueryDef.

CurrentDb.QueryDefs(qryPrjTr).SQL etc.
should be
CurrentDb.QueryDefs("qryPrjTr").SQL etc.
 
Thanks.....I looked at it so long it just never hit me. Integrating
SQL, VBA and Access are still new to me. Most of my experience is VB,
so I'm still learning.

again....thanks.
 
Back
Top