Saved query works as OpenQuery, but not on form

  • Thread starter Thread starter DP
  • Start date Start date
D

DP

Could someone perhaps explain to me what a query like this (saved as
qryTreeviewKeywords):

SELECT tblRecordKeywordsOR.Keyword, tblOrders.*
FROM tblRecordKeywordsOR INNER JOIN tblOrders ON
tblRecordKeywordsOR.RecordNumber = tblOrders.ID
WHERE (((tblRecordKeywordsOR.Keyword) Like
[Forms]![frmTreeviewKeywords]![SearchText]));

Will work perfectly to open a table:

DoCmd.OpenQuery "qryTreeviewKeywords"

But it will NOT open - "filtered" the main form with code like this:



DoCmd.OpenForm "frmOrders" , , qryTreeviewKeywords

What am I missing? If the query works as a query, why doesn't it work to
open the from using the query as a "filter?"

(The main criterion comes from an open form:
Forms!frmTreeviewKeywords!SearchText.)

Thanks! David Pike
 
But it will NOT open - "filtered" the main form with code like this:



DoCmd.OpenForm "frmOrders" , , qryTreeviewKeywords

What am I missing? If the query works as a query, why doesn't it work to
open the from using the query as a "filter?"

(The main criterion comes from an open form:
Forms!frmTreeviewKeywords!SearchText.)
Click to expand...
Because you are filtering you don't need the where clause. You change the
query to

SELECT tblRecordKeywordsOR.Keyword, tblOrders.*
FROM tblRecordKeywordsOR INNER JOIN tblOrders ON
tblRecordKeywordsOR.RecordNumber = tblOrders.ID

You make the frmOrders record source qryTreeviewKeywords on the property
sheet for the form.

Then you change the code to:

Dim strQry as string
strQry = "tblRecordKeywordsOR.Keyword Like
[Forms]![frmTreeviewKeywords]![SearchText]" (one line)

DoCmd.OpenForm "frmOrders" , , strQry

HTH
Marc
 
Back
Top