Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Is there a function in Access VBA to check if a query is open, if so close?

Posted on 2009-05-06
5
Medium Priority
?
1,696 Views
Last Modified: 2013-11-28
I have a dynamic query builder designed in MS Access set up with a main Form for the user.  I have noticed that if the user clicks away from the query window after it has run, it becomes 'hidden' behind the form and the user can not close it without going into the back end of the db.  I have a refresh button which closes the form then immediately reopens it and a view query button which runs and opens the query as defined by the user.  For both buttons, I want them to check to see if the query is open and if it is close it before doing anything else.

How can this be done?
0
Comment
Question by:pgerman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 24316096
Queries have no Event model associated with them, so you really cannot determine if they're in use. generally if your users need to view data, you should build a Form that shows them this data, and you can determine if a Form is open or not.
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 24316120
Within Access itself, there is no way I know of to determine if a query window is open.  You'd have to use the window API to go through all of Access's child windows to figure out if it was open or not.
  Instead, you can determine if a form is open.  So what I'd so is use a form to display your query by letting them build the SQL string however you have it, then opening a form hidden, set it's recordsource, and then display it.
  By doing this, you'll have a lot more control over the process, including if you wish for example, opening the form dialog, so it remains on top of all other forms and they mosut close or hide it before they can more onto anything else.
JimD.
0
 

Author Comment

by:pgerman
ID: 24316371
JDettman,
What do you mean by opening a form hidden and setting its record source?
0
 
LVL 58
ID: 24316456
<<What do you mean by opening a form hidden and setting its record source?>>
  Just that.  Design a form with a series of controls.  Open the form hidden so it is not displayed to the user, then set the forms recordsource property, loop through the controls and set the controlsource property for each control, hide un-needed controls, and then make the form visible.
  You could also go the unbound route and push the data into the controls.
  hard to say what is the best approach without knowing a lot more about how it is you apprached the ad-hoc query capability and how much flexability you give the user in doing that.
JimD.
0
 

Expert Comment

by:chilliconcarne
ID: 33480877
Copied from: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23546210.html

Function isOpen(strName As String, intObjectType As Integer) As Integer
'checks to see if the database object passed is open or not
'returns true if object is open
'acTable = 0
'acQuery = 1
'acForm = 2
'acReport = 3
'acMacro = 4
'acModule = 5

isOpen = (SysCmd(SYSCMD_GETOBJECTSTATE, intObjectType, strName) <> 0)

End Function

OM Gang
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question