[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1751
  • Last Modified:

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

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
pgerman
Asked:
pgerman
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
pgermanAuthor Commented:
JDettman,
What do you mean by opening a form hidden and setting its record source?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
chilliconcarneCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now