Solved

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

Posted on 2009-05-06
5
1,622 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 250 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 250 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

728 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