Dynamic SQL select criteria in a Form

[cmmrfrds]

I would like to pass varied select criteria to a Form. In effect, I would like to vary the WHERE clause in the Forms RecordSource. I was planning on building everything in VBA code, but it seems to me the Filter Property should work for this. If so, what would the syntax look like and in what events would it be applied. Thank you.

 

[JimHorton]

Primarily, the Where clause in the filter property is the same as sql, but without the 'Where' keyword.&nbsp;&nbsp;Be sure to set the FilterOn to True.<br>--Jim

 

[CharlesDFW]

If, for example, you wanted to change the recordsource for the form when a selection is made from combobox cboPickOne on that form, you might have in the combobox's lostfocus event:<br><br>me.filter=&quot;[Myfield]=&quot; & cboPickOne.value<br>me.filteron=true<br><br>As soon as the user chooses and tabs out of the combobox, your form is now based on that record.<br><br>For doing it outside the form, of course, you need:<br><br>Forms![MyForm].Filter=&quot;[Myfield]=&quot; & myvariable<br>forms![MyForm].filteron=true<br><br>though sometimes Access doesn't like this.<br>

 

[cmmrfrds]

Thank you for your suggestions. I was going to build the selection criteria (WHERE CLAUSE) in the first form when the user enters all the selection data, then, pass the selection string to the second form. At that point I could equate the filter to the selection string, which would follow your suggestions. I was doing some more reading on the OpenForm method and it looks like &quot;wherecondition&quot; should do the same thing when I open the form. That way I can localize the criteria in the first form and the second form will just run the query and give the results.<br><br>Thank you again, this puts me on the right path and gives me some options to try. <br><br>