473,289 Members | 1,940 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

How do I close all open query results (tables)?

I have written code to close all open reports, or close all forms (except a couple), using code similar to the following:

While Application.Reports.Count > 0
DoCmd.Close acReport, Application.Reports(0).Name, acSavePrompt
Wend

I can’t figure out how to do the same thing with the half-dozen or so small query windows (tables) that may remain open when I finish a particular task. I have to click the [x] close button on the Title bar of each one.

TIA

-BhanteU
Jun 28 '10 #1

✓ answered by ADezii

@BhanteU
Access 97 is a horse of a different color, and none of the previously mentioned approaches will work. The following, 'Generic' Code template should work for you:
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim intNumOfQueries As Integer
  3. Dim intQCounter As Integer
  4.  
  5. intNumOfQueries = CurrentDb.QueryDefs.Count
  6.  
  7. For intQCounter = 0 To intNumOfQueries - 1
  8.   DoCmd.Close acQuery, CurrentDb.QueryDefs(intQCounter).Name, acSaveYes
  9. Next

9 19111
ADezii
8,834 Expert 8TB
@BhanteU
Expand|Select|Wrap|Line Numbers
  1. Dim aob As AccessObject
  2.  
  3. With CurrentData
  4.   For Each aob In .AllQueries
  5.     If aob.IsLoaded Then
  6.       DoCmd.Close acQuery, aob.Name, acSaveYes
  7.     End If
  8.   Next
  9. End With
Jun 28 '10 #2
Thank you.

Access97 doesn’t like the AccessObject data type; it returns a “User-defined type not defined” error at compile time. I’m running Access 97.

When I enter the keyword accesstype (in small letters), Access 97 automatically changes the case, indicating that it recognizes the string.

Substituting AcObjectType doesn’t work, either.

Looks like some kind of undocumented “feature”.

BTW, how did you get that code snippet into the reply window?
Jun 29 '10 #3
FishVal
2,653 Expert 2GB
@BhanteU
You can determine the type of AllQueries collection member using either of the following two methods:
  • Open Object Browser window, find "AllQueries" class, check the type returned by "Item" property of this class.
  • In Immediate pane run the following command
    Expand|Select|Wrap|Line Numbers
    1. ? TypeName(CurrentData.AllQueries(0))

Regards,
Fish
Jun 29 '10 #4
ADezii
8,834 Expert 8TB
@BhanteU
Access 97 is a horse of a different color, and none of the previously mentioned approaches will work. The following, 'Generic' Code template should work for you:
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim intNumOfQueries As Integer
  3. Dim intQCounter As Integer
  4.  
  5. intNumOfQueries = CurrentDb.QueryDefs.Count
  6.  
  7. For intQCounter = 0 To intNumOfQueries - 1
  8.   DoCmd.Close acQuery, CurrentDb.QueryDefs(intQCounter).Name, acSaveYes
  9. Next
Jun 29 '10 #5
Thank you, ADezii!

Access Help was not helpful in wading through the quagmire of objects, methods, and properties. I tried many commands like the one you supplied:

CurrentDb.QueryDefs(intQCounter).Name

but I never could get it right. Yous works swell!

Thank you so much!

(And still I would like to know how one gets that nicely-formatted code window into a reply.)

-Ven. Upatissa
Jun 29 '10 #6
ADezii
8,834 Expert 8TB
Select the Code Segment with the Mouse, then click the Hash (#) on the Message Window Toolbar. You will now see the Code surrounded by Code Tags, and it will be formatted accordingly.
Jun 29 '10 #7
NeoPa
32,554 Expert Mod 16PB
An alternative that doesn't need to maintain the counter would be :
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As DAO.QueryDef
  2.  
  3. For Each qdf In CurrentDb.QueryDefs
  4.     Call DoCmd.Close(acQuery, qdf.Name)
  5. Next qdf
Unfortunately, neither cycles through only those queries that are currently open. I wasn't able to find anything that indicated that.
Jun 29 '10 #8
ADezii
8,834 Expert 8TB
@NeoPa
Hello NeoPa. I don't think that the For...Each Construct will work in Access 97, but I could be wrong.
Jun 29 '10 #9
My utmost thanks to Adezi and NeoPa. I can report that both routines work in Access 97. My apologies for not revealing earlier that I was using antiquated Access 97.

Several years ago I wrote a medium-sized management system in MS Access. I’ve been doing periodic maintenance and tweaking ever since. The DBMS handles the membership records for a not-for-profit institution. At present it contains around 7,000 member records and 100,000 transactions. The organization is not keen to spend any money at all, and I have advised them to stick with Win 98 SE and Access 97 for the membership clerk. Why fix it if it ain’t broke?

So again, my thanks to everyone.

Sadhu!

(please mark this issue as closed.)
Jun 29 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Max Harvey | last post by:
Hi, I have looked at the example called "Open Parameter queries from code" from the site http://www.mvps.org/access/queries/qry0003.htm I made up a test which I though looked pretty close...
5
by: Ryan Hubbard | last post by:
Is it possible to get the recordset from an open query window? So you run the query. The window is open. Can vba retrieve this data?
6
by: Brian | last post by:
Hello, Basically, I'm running a query on a form's activation, and I'd like to have the results of the query be placed into other fields on the same form automatically. Does anybody know how...
0
by: Rob | last post by:
I doubt this is the best way to do it, but what I came up with was to hide the XML in an HTML Comment then edit the file deleting the HTML stuff and keep the XML results. If anyone has a better...
2
by: kevcar40 | last post by:
hi i have a query which returns the top 5 Areas of concern i.e Area(field name) Area 1 Area 2 Area 3 Area 4 Area 5 the results are displayed on a continuous form i am now trying to use this...
9
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped....
2
by: Himmel | last post by:
Hello! The reference database I currently use runs queries that pull data from hundreds of tables in order to create user-friendly form view. The problem is that these queries can take upwards of...
1
by: Arli | last post by:
I have the following linked tables: tblMainPL is my main table that I need to pull the information in from. It has the following fields: Autonumber1 -PK set as autonumber Date - short date...
1
by: ebasshead | last post by:
Hi Everybody, Behind a combo box on an open form, I have the below code to run a query, which it does, and pops up the query results.. MaxofID, NPPricePerWeek and IDProperty2, in data sheet view. ...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.