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,586 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
5 Comments
 
LVL 84

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 57

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 57
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

840 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