Modifying a Query 1

[Jengo]

How to I modify the criteria of a query through a form after it has been already made?

 

[JimHorton]

jengo,<br>Set a querydef object for the query, and alter the SQL through the querydef.sql property.&nbsp;&nbsp;This will permanantly change the sql, until you change it again either manually or through the querydefs.sql property.<br>--Jim

 

[Jengo]

I'm new at queries.&nbsp;&nbsp;I don't usually don't deal alot with them.&nbsp;&nbsp;But I do work good with examples could you give me an example of what you mean?&nbsp;&nbsp;I'll probably have a better chance of understanding it.&nbsp;&nbsp;Thanx.

 

[gol4]

On the forms control source property click on the 3 small dots next to the control source. This will open the QBE grid. Make what ever changes you want to make. Exit, and when prompted say you want to update.

 

[JimHorton]

Jengo,<br>First you need to decide where or when you'll change the criteria.&nbsp;&nbsp;I'm going to assume the query you want to change is the recordsource of the form, and not just some random query in the database.&nbsp;&nbsp;For an example, lets say you have a texbox where you'll enter a letter or a few letters of a the name of someone in a customers table, and then a button which will change the criteria to show only customers who'se names start with those letters.&nbsp;&nbsp;In the Click event of the button, do this<br><br>dim sq as string<br>sq = &quot;Select * from Customers where Lastname &quot;<br>sq = sq & &quot; LIKE &quot;&quot;&quot; & me!textbox &&nbsp;&nbsp;&quot;*&quot;&quot;<br><br>'Now, there are 2 choices--modify the query permanently, so the next time the form opens it retains the last criteria, or just do it temporary.&nbsp;&nbsp;We'll do it the first way here...<br>Dim qd as querydef<br>set qd = currentdb.querydefs(&quot;Query that is source of this form&quot<br>qd.sql = sq<br>me.requery