Read all open objects cseifferly 14.09.2006 16:59:01 I'd like to create an autokey that when pressed, will close whatever query is opened. How do I read which objects are open? Crystal

Re: Read all open objects Marshall Barton <marshbarton[ at ]wowway.com> 14.09.2006 17:50:48 cseifferly wrote: [Quoted Text] >I'd like to create an autokey that when pressed, will close whatever query is >opened. > >How do I read which objects are open? Note that an "open query" is a query being deisplayed in datasheet view. This is something that should not be done in a runnung application. What that caveat, you can enumerate them by using a little VBA code: Dim db As Database Dim qry As AccessObject Set db = CurrentDb() For Each qry In db.AllQueries If qry.IsLoaded _ Then DoCmd.Close acQuery, qry.Name Next qry Set db = Nothing -- Marsh MVP [MS Access]

Re: Read all open objects cseifferly 14.09.2006 19:06:01 Still confused. The IsLoaded function is designed for forms. How would I adapt it to read queries? Additionally, "AllQueries" is not an option. Will QueryDefs work?

Re: Read all open objects Marshall Barton <marshbarton[ at ]wowway.com> 14.09.2006 21:00:36 cseifferly wrote: [Quoted Text] >Still confused. > >The IsLoaded function is designed for forms. How would I adapt it to read >queries? > >Additionally, "AllQueries" is not an option. Will QueryDefs work? Well, I was confused too and used the wrong objects. I would think I would know better than to trust my own air code, sorry about that. Let's give this a try: Dim db As Object Dim qry As AccessObject Set db = Application.CurrentData For Each qry In db.AllQueries If qry.IsLoaded _ Then DoCmd.Close acQuery, qry.Name Next qry Set db = Nothing Note that IsLoaded applies to all AccessObjects, not just forms. The QueryDef object does not have an equivalent to the IsLoaded property and there is no Queries collection in the Application object. -- Marsh MVP [MS Access]

Re: Read all open objects cseifferly 15.09.2006 19:08:03 There's got to be something I'm missing here. Is there a particular reference I need to add in order to use "AccessObject" as a datatype and "db.AllQueries"? Also, should "Set db = Application.CurrentData read "Set db = Application.CurrentDb"? This code won't actually run for me. [Quoted Text] > > Dim db As Object > Dim qry As AccessObject > Set db = Application.CurrentData > For Each qry In db.AllQueries > If qry.IsLoaded _ > Then DoCmd.Close acQuery, qry.Name > Next qry > Set db = Nothing > > Note that IsLoaded applies to all AccessObjects, not just > forms. The QueryDef object does not have an equivalent to > the IsLoaded property and there is no Queries collection in > the Application object. >

Re: Read all open objects "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 15.09.2006 19:27:24 What version of Access are you using? That code should be fine in all versions since Access 2000: it's essentially the same code as in http://msdn.microsoft.com/library/en-us/vbaac11/html/accolAllQueries_HV05251615.asp If you're still using Access 97 or earlier, try: Dim db As DAO.Database Dim qry As DAO.QueryDef Dim intLoop As Integer Set db = CurrentDb() For intLoop = (db.QueryDefs.Count - 1) To 0 Step -1 Set qry = db.QueryDefs(intLoop) If Syscmd(acSysCmdGetObjectState , acQuery, qdf.Name) > 0 Then DoCmd.Close acQuery,. qdf.Name End If Next intLoop Set db = Nothing -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "cseifferly" <cseifferly[ at ]discussions.microsoft.com> wrote in message news:4F2178C1-39AE-43C0-B336-F587A904FFE0[ at ]microsoft.com... [Quoted Text] > There's got to be something I'm missing here. Is there a particular > reference I need to add in order to use "AccessObject" as a datatype and > "db.AllQueries"? Also, should "Set db = Application.CurrentData read "Set > db > = Application.CurrentDb"? > > This code won't actually run for me. > >> >> Dim db As Object >> Dim qry As AccessObject >> Set db = Application.CurrentData >> For Each qry In db.AllQueries >> If qry.IsLoaded _ >> Then DoCmd.Close acQuery, qry.Name >> Next qry >> Set db = Nothing >> >> Note that IsLoaded applies to all AccessObjects, not just >> forms. The QueryDef object does not have an equivalent to >> the IsLoaded property and there is no Queries collection in >> the Application object. >> >

Re: Read all open objects cseifferly 15.09.2006 19:51:02 EXCELLENT! Thank you. I totally forgot to mention that at my company, we rock the Access 97. We're only slightly behind the times. "Douglas J. Steele" wrote: [Quoted Text] > What version of Access are you using? That code should be fine in all > versions since Access 2000: it's essentially the same code as in > http://msdn.microsoft.com/library/en-us/vbaac11/html/accolAllQueries_HV05251615.asp > > If you're still using Access 97 or earlier, try: > > Dim db As DAO.Database > Dim qry As DAO.QueryDef > Dim intLoop As Integer > > Set db = CurrentDb() > For intLoop = (db.QueryDefs.Count - 1) To 0 Step -1 > Set qry = db.QueryDefs(intLoop) > If Syscmd(acSysCmdGetObjectState , acQuery, qdf.Name) > 0 Then > DoCmd.Close acQuery,. qdf.Name > End If > Next intLoop > Set db = Nothing > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "cseifferly" <cseifferly[ at ]discussions.microsoft.com> wrote in message > news:4F2178C1-39AE-43C0-B336-F587A904FFE0[ at ]microsoft.com... > > There's got to be something I'm missing here. Is there a particular > > reference I need to add in order to use "AccessObject" as a datatype and > > "db.AllQueries"? Also, should "Set db = Application.CurrentData read "Set > > db > > = Application.CurrentDb"? > > > > This code won't actually run for me. > > > >> > >> Dim db As Object > >> Dim qry As AccessObject > >> Set db = Application.CurrentData > >> For Each qry In db.AllQueries > >> If qry.IsLoaded _ > >> Then DoCmd.Close acQuery, qry.Name > >> Next qry > >> Set db = Nothing > >> > >> Note that IsLoaded applies to all AccessObjects, not just > >> forms. The QueryDef object does not have an equivalent to > >> the IsLoaded property and there is no Queries collection in > >> the Application object. > >> > > > > >