ms-access: how to do a proper requery?

i am distributing ms-access-2007 front ends. the back end is sql server. a user will click on a button and it will return a query from the sql server 2008 database.

is it possible to requery this query if the user clicks the button again?

usually the behavior of access is that if you quer开发者_开发百科y it again, it displays old results. how do i make it refresh results?

If you know the query is still the ActiveDatasheet, requery that:

  Screen.ActiveDatasheet.Requery

If you don't know that (you could check Screen.ActiveDatasheet.Name), you can open it again (which may or may not requery if the query is already open -- I would expect it not to, but I could be wrong), and then it will be the ActiveDatasheet and you can requery that.

Or, you could do:

  DoCmd.SelectObject acQuery, "NameOfYourQuery"
  Screen.ActiveDatasheet.Requery

Surely one or more of these methods will work.

But let me point out that it's not a good idea to use tables and queries as user interface objects. You should instead make forms for allow users to interact with the data displayed in them because that gives you far more control over them.

(one thing that many people do not know is that a datasheet is a form object, so the properties and methods of Screen.ActiveDatasheet are the same as those of any form, which means you can actually assign values to events on the Screen.ActiveDatasheet object and have them fire; but I wouldn't recommend this as the easiest route to building a UI...)

If you data is in a form that's bound to an Access table or query linked to Sql Server, a form.requery will do the trick.

if you query it again, it displays old results

I do not agree with your statement above. If you reopen a form or a query, you always get the latest values. A Refresh, will update THOSE RECORDS to the latest value, but will ignore new records. A Requery will repopulate the form/recordset completely.