requery of form has no effect

[selahlynch]

I have a form that I view in datasheet view. The record source for this form is a query. When I change the definition of this query, the change is not reflected in my form until I close and reopen the form. I have tried the requery function on my form but it did nothing. Any idea why this is and how I can get my form to show the updated query?

? forms!testform.recordsource
testquery

CurrentDb().QueryDefs("testquery").SQL = modifiedquery

Forms!testform.Requery

' the form still does not reflect my modified query :(

 

[vbaInet]

When you say you changed the definition, what do you mean? Why not just set the recordsource of the form to the new query and then requery it?

 

[selahlynch]

I have a user enter some parameters into text boxes on a form.
Based on those parameters I redefine a query.

userparam = textbox1.Value
newquery = "SELECT * FROM shottable WHERE ffid = " & userparam
CurrentDb().QueryDefs("formquery").SQL = newquery

Now I want the user to see the results of the query on their form.

A user might make 50 different queries at one sitting by entering different parameters on the form.

 

[vbaInet]

You can use the Filter and FilterOn properties of the form. Lookup those.

 

[selahlynch]

Ahah!

If I reset the recordsource of the form (even though I'm setting it to the same query name that it was before), this results in the form reflecting the changes I made to my query definition.

? forms!testform.recordsource
testquery

CurrentDb().QueryDefs("testquery").SQL = modifiedquery

''' Forms!testform.Requery 
'''form does not show modified query :(

Forms!testform.recordsource = "testquery"
'''SUCCESS!! :)  changes to query are show in form :) :)

 

[vbaInet]

I know I suggested changing the recordsource but that was just to get you thinking. You might be performing unnecessary operations and there could be an easier and much efficient way.

I'm guessing modifiedquery is doing some sort of filtering based on one value retrieved from a text box?

 

[selahlynch]

Yeah, the reason I modifying the query is to add a WHERE based on user input. If I don't modify it I will be dealing with recordsets of around 40,000 records. After it is modified I only have two or three records.

 

[vbaInet]

You can add a WHERE clause by changing the SQL string of your record source. What you are doing is two operations, whereas there is a way to do just one. How many fields are you filtering by?

 

[selahlynch]

Ahh, so instead of...

CurrentDb().QueryDefs("testquery").SQL = modifiedsql
Forms!testform.recordsource = "testquery"

I could put...

Forms!testform.recordsource = modifiedsql

That is simpler. Thanks.

 

[selahlynch]

I am filtering using three separate fields.

 

[gemma-the-husky]

instead of this - why not get the parameter from a form reference or variable, instead of as you probably are, writing an absolute value into the changed query.


so the query looks like either

with a form reference
select * frm mytable where ffid = " & forms!myform!somefield

or with a variable
select * frm mytable where ffid = " & functiontoreadvariable() (I generally prefer this construct, tbh)

either way, you can then just do somethnig like - use an unbound drop down on your form header to pick what you want, and then just do

me.requery to reflect the changes.

 

[vbaInet]

instead of this - why not get the parameter from a form reference or variable, instead of as you probably are, writing an absolute value into the changed query.


so the query looks like either

with a form reference
select * frm mytable where ffid = " & forms!myform!somefield

or with a variable
select * frm mytable where ffid = " & functiontoreadvariable() (I generally prefer this construct, tbh)

either way, you can then just do somethnig like - use an unbound drop down on your form header to pick what you want, and then just do

me.requery to reflect the changes.

Just what I was trying to get across. Thanks Dave.

 

[selahlynch]

Okay, thanks. I'll try it out.