views:

30

answers:

2

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 query it again, it displays old results. how do i make it refresh results?

A: 

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.

iDevlop
@idevlop if you leave the query open and you run the query again, it will not be updated. you have to first to docmd CLOSE
i am a girl
@i am a girl: put you query as the source of a form, and Requery the form. And since you have the chance to have SQL Server as your back-end, use a SQL Server view that is linked as an Access "table" for improved performance.
iDevlop
@idevlop nope im just running a query, its not the recordsource of anything, its just a query, its not attached to anything, anyway i have the answer, like i said i did dncmd CLOSE and that works
i am a girl
+2  A: 

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...)

David-W-Fenton