?
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,663 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 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