title: How to use form variables with ECount/OpenRecordset in MS Access
body: <p>I have a module in a large Access database which attempts to count the number of unique records in a table using the ECount (Extended D-Count) function written by Allen Browne (here: <a href="http://allenbrowne.com/ser-66.html" rel="nofollow">http://allenbrowne.com/ser-66.html</a>). </p>
  
  <p>As he mentions in the guide to using it: </p>
  
  <blockquote>
    <p>You cannot embed a reference to a form in the arguments. 
    For example, this will not work:</p>
    
    <p>ECount("*", "Customers", "City = Forms!Customers!City")</p>
    
    <p>Instead, concatenate the value into the string:</p>
    
    <p>ECount("*", "Customers", "City = """ &amp; Forms!Customers!City &amp; """")</p>
  </blockquote>
  
  <p>Unfortunately, the query being sent in to ECount (the "domain" parameter) itself calls another query which relies on a form for input. So what I end up with is, indirectly, ECount attempting to use a form variable, even though the query string itself doesn't have the form variable written in it, so I get: </p>
  
  <p>Run-time Error 3061: Too few parameters. Expected 1.
  At the line: "Set rs = db.OpenRecordset(strSql)"</p>
  
  <p>The query being sent to OpenRecordset is: "SELECT Key FROM Report7Query WHERE (Key Is Not Null) AND (MainCategory= "Source" AND  1=1 AND ([State]='AR') ) GROUP BY Key;". This query is generated based on inputs to a form, and Report7Query calls another query called CustomQuery which (I think) relies on a form (also called CustomQuery) for its inputs.</p>
  
  <p>If I just try to run Report7Query with no forms open, a dialog box comes up asking for "Forms!CustomQuery!ConsolidatedSelection.Value", which is a variable on the CustomQuery form. The same goes if I just try to open the CustomQuery query. Interestingly, if I open the CustomQuery form in Design view and then run either Report7Query or the CustomQuery query, or even the above select statement in its own query, then it all works.</p>
  
  <p>I've tried numerous approaches to fixing this, such as using QueryDefs, but I can't seem to get ECount to work. Interestingly, DCount works just fine with the exact same query.</p>