If Query Open Close and Re-Open

[LadyDi]

Is there a way to run code to see if a query is open? Then if the query is open, program the database to close it and re-run it?

I have a query that is based on a field on my form. It runs fine the first time. However, if the user leaves the query open and goes back to the form, makes a new selection, and clicks the button to open the query, the query doesn't refresh. The user either has to close the query and re-run it or press the Refresh All button. I need a way for the query to refresh automatically if it is open already or not. The only other alternative I can think of is to program the database to close the query and re-open it. I tried using QueryName.Requery and QueryName.Refresh but in both cases, it tells me that an object is required and I don't know what it wants. I also tried setting the query definitions using VB, but the query is apparently too long because no matter what I do, I can't get it on one line and Access doesn't seem to like it if the query takes up two lines.

Any assistance you can provide would be greatly appreciated.

 

[Michael3Smith]

Hello! There is a built in function called IsLoaded, but it won't check to see if a query is loaded (a.k.a. open). I suggest you make a form and base it on the query. Make the form open in datasheet mode so it looks like a query. Then, on the after update of any control that you want to effect the query put:
DoCmd.RunCommand acCmdSaveRecord
If CurrentProject.AllForms("frm_formBasedOnQuery").IsLoaded Then
Forms!frm_formBasedOnQuery.Requery
Else
MsgBox "Not loaded" 'This is just for testing purposed
End If

 

[JHB]

You can use the below code to find out if the query is open/loaded.

    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    ' Search for open AccessObject objects in AllQueries collection.
    For Each obj In dbs.AllQueries
      If obj.Name = "YourQueryName" And obj.IsLoaded = True Then
        'Do something
      End If
    Next obj

 

[nanscombe]

That could be condensed a bit ...

Private Sub mySubroutine()
  Dim myQueryName as String

... ' Your other code
...

  myQueryName = "yourQueryName"

  If CurrentData.AllQueries(myQueryName).IsLoaded Then DoCmd.Close acQuery, myQueryName, acSaveNo
  DoCmd.OpenQuery myQueryName

... ' More of your code
End Sub

 

[gemma-the-husky]

instead of opening the query directly, have a form based on the query. then you can certainly requery the form to do what you want.

It is easier to present and manage data in a forrm anyway.

 

[nanscombe]

You can, of course, open a form in "datasheet" mode so your user should notice little, if any, difference.

Docmd.OpenForm "yourForm", acFormDS

 

[LadyDi]

Thank you very much for all the suggestions. I was able to get it working.