I have written code to close all open reports, or close all forms (except a couple), using code similar to the following:
While Application.Rep orts.Count > 0 DoCmd.Close acReport, Application.Rep orts(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
9 19158 ADezii 8,834
Recognized Expert Expert @BhanteU - Dim aob As AccessObject
-
-
With CurrentData
-
For Each aob In .AllQueries
-
If aob.IsLoaded Then
-
DoCmd.Close acQuery, aob.Name, acSaveYes
-
End If
-
Next
-
End With
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?
FishVal 2,653
Recognized Expert Specialist @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
- ? TypeName(CurrentData.AllQueries(0))
Regards,
Fish
ADezii 8,834
Recognized Expert Expert @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: - On Error Resume Next
-
Dim intNumOfQueries As Integer
-
Dim intQCounter As Integer
-
-
intNumOfQueries = CurrentDb.QueryDefs.Count
-
-
For intQCounter = 0 To intNumOfQueries - 1
-
DoCmd.Close acQuery, CurrentDb.QueryDefs(intQCounter).Name, acSaveYes
-
Next
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.Query Defs(intQCounte r).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
ADezii 8,834
Recognized Expert Expert
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.
NeoPa 32,566
Recognized Expert Moderator MVP
An alternative that doesn't need to maintain the counter would be : - Dim qdf As DAO.QueryDef
-
-
For Each qdf In CurrentDb.QueryDefs
-
Call DoCmd.Close(acQuery, qdf.Name)
-
Next qdf
Unfortunately, neither cycles through only those queries that are currently open. I wasn't able to find anything that indicated that.
ADezii 8,834
Recognized Expert Expert @NeoPa
Hello NeoPa. I don't think that the For...Each Construct will work in Access 97, but I could be wrong.
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.)
Sign in to post your reply or Sign up for a free account.
Similar topics |
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 (which I will
paste below)
I have put it on the BeforeUpdate event of a form I am using, but
whenever it is called, I get "Run-time error '13':, Type...
|
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?
|
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 this can be done?
I've tried setting the default value of the text fields on the form to
be equal to ! using Access' expression
|
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 solution, I would be interested.
Thank you.
Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load...
|
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 result to trigger another query
| |
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. Below you
will find the code I've written and the error that results. I'm hoping
that someone can give me some direction as to what syntax or...
|
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 30 seconds to return.
The solution that I've devised is to create an admin function to cache all these query results and then dump that data into...
|
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 format-example: (10/22/08)
Load Number -Number-Single (should not be higher than 35-40 per day)
WorkOrder -Text- 50 (string of letters and numbers of...
|
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.
I would like to have the query run in the background and not pop up as a datasheet, and then.
I would like to get the results back onto the form...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |