access: how to detect with VBA whether a query is opened?

Question

is it possible to detect whether there is an open query using VBA in access-2007?

i am opening a query like this:

    stDocName = "Meeting_Reasons_Frequency"
  DoCmd.OpenQuery stDocName

is it possible to detect whether it is open?

Arlyn

Answer 1

How about:

 If SysCmd(acSysCmdGetObjectState, acQuery, "QueryName") = acObjStateOpen Then

More info: http://msdn.microsoft.com/en-us/library/aa205281(office.10).aspx

Foredate

Answer 2

Not sure if this applies to queries, but I noticed that for forms, you should use And instead of =.

If SysCmd(acSysCmdGetObjectState, acForm, "FormName") And acObjStateOpen Then

Apparently the form can have multiple "states" simultaneously. Using And picks out the one you want; it acts as a bitwise operator in this context.

Diphyodont

The same acSysCmdGetObjectState issues apply to both queries and forms. Thanks for bringing this to our attention. – Urana

Answer 3

SysCmd(acSysCmdGetObjectState, acQuery, "QueryName") returns zero if QueryName is closed or does not exist.

Otherwise it returns the sum of the constants for whichever of the following apply:

• acObjStateOpen (1) open in any state (new, dirty, etc.) or view (Design, Datasheet, etc.)
• acObjStateDirty (2) open but with unsaved design changes
• acObjStateNew (4) a new query which has not yet been saved to its parent collection (QueryDefs)

Perhaps most of the time checking whether the SysCmd expression = acObjStateOpen will be sufficient. However if you want to avoid a surprise when the query is open with unsaved design changes you could And acObjStateOpen as pianoJames suggested.

But if your intention is to do something when QueryName is open, you could simply check whether it is not closed (zero):

If SysCmd(acSysCmdGetObjectState, acQuery, "QueryName") <> 0 Then

Urana

I'm a bit confused about the end of this part acObjStateOpen (1) open in any state (new, dirty, etc.) because the other two constants represent dirty and new – Vichyssoise

If you open a saved query, its state will be just acObjStateOpen --- neither acObjStateDirty or acObjStateNew apply. – Urana