Results 1 to 7 of 7
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    How to Tell if Access Objects are Open


    I've created a custom ribbon to use in my databases instead of the old-fashioned Switchboard system. So far it's working great. Now I would like to disable buttons on the ribbon if certain conditions exist. For example, if I open a particular form, then that form's button on the ribbon should be disabled until the form is closed. Also would be nice to disable the "Quit" ribbon button if ANY objects are open. Here is what I have so far (I have 1 form with a graphic that's always open, so that is fine. Here is code so far:

    Code:
    Public Sub GetEnabledCallBack(control As IRibbonControl, ByRef enabled)
    
    
        Select Case control.ID
            
            ' for quit button, only enable if main form is only one open
            Case "quit"
                enabled = False
                If Reports.Count = 0 And Forms.Count = 1 Then
                    If Forms(0).Name = "frmMain" Then
                        enabled = True
                    End If
                End If
            
            ' for other controls, enable if particular form / report is NOT open
            Case Else
                On Error Resume Next
                enabled = Not CurrentProject.AllForms(control.Tag).isLoaded
                If Err.Number <> 0 Then
                    Err.Clear
                    enabled = Not CurrentProject.AllReports(control.Tag).isLoaded
                End If
                If Err.Number <> 0 Then enabled = True
        End Select
    
    
    End Sub
    Here is code which I put in each form's or report's Load and Close events. The values in parentheses are the ID property of the controls in the ribbon xml of the USysRibbons table.
    Code:
        ' to enable/disable ribbon buttons
        If Not gRibbon Is Nothing Then
            gRibbon.InvalidateControl ("enterStudents")
            gRibbon.InvalidateControl ("quit")
        End If
    The code above works fine for indicating whether a particular form or report is open (Case Else). The Case "quit" is somewhat working, because it will properly return enabled if NO reports are open and only frmMain is open.

    There are other objects which should be checked for also, such as open queries or other objects. There is a Forms collection and a Reports collection, but no Queries collection to poll. I can iterate the CurrentData.AllQueries collection and see if any have .IsLoaded as True. That seems cumbersome, and there is no collection for macros or other objects.

    Is there an easy way to find out if ANY objects at all are open? Am I being too demanding?

    Thanks...

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    'go thru all the forms

    Code:
    dim f as form
    
    enabled =false
    for each f in forms
       if f.name = "frmMain" then
    
    enabled = True exit for
    endif next set f = nothing

  3. #3
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks ranman256, but I already have forms and reports covered. I need a way to ascertain whether there are ANY objects open, including saved queries and new queries which haven't been saved.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  5. #5
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks Steve. I am aware of that technique, but it requires that you know the name and type of the object you want to test for.
    Here is my latest effort, which seems to be closer to what I need:
    Code:
    Public Function ObjectsOpen() As String
    
        Dim ob  As Object
        
        ObjectsOpen = ""
        For Each ob In Forms
            If FormIsLoaded(ob.Name) Then ObjectsOpen = ObjectsOpen & "," & ob.Name
        Next
        
        For Each ob In Reports
            If ReportIsLoaded(ob.Name) Then ObjectsOpen = ObjectsOpen & "," & ob.Name
        Next
        
        For Each ob In CurrentData.AllQueries
            If ob.isLoaded Then ObjectsOpen = ObjectsOpen & "," & ob.Name
        Next
        
        For Each ob In CurrentData.AllTables
            If ob.isLoaded Then ObjectsOpen = ObjectsOpen & "," & ob.Name
        Next
        
        If ObjectsOpen <> "" Then ObjectsOpen = Mid$(ObjectsOpen, 2)
    
    End Function
    The FormIsLoaded and ReportIsLoaded methods verify that the form or report is loaded and NOT in design mode.
    This returns a comma-delimited list of all objects open. Then for each control on the ribbon, I can test whether its object name is in that list. If so, that ribbon button is disabled. For the quit button, I just need to test whether the returned string is empty or is "frmMain", which would enable that button. Once again, frmMain is usually always open, and is basically the background for the access database, and contains an image identifying the database.

    Thanks...

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Hummmmm,

    So you know how to loop through the collections to get the object name and the object type is in the referenced web site (above), therefore you have the two parameters for the IsOpen function.
    But as long as you are happy with what you have, great!

    Goodluck with your project.

  7. #7
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks Steve,
    Here's what I ended up with so far. It includes most Access types and also has option of including or ignoring objects that may be in design mode.
    Code:
    Public Function ObjectsOpen() As String
    
    
        Const includeDesignMode As Boolean = True
        ' if False, does not include objects in design mode
        
        Dim ob  As AccessObject
        Dim frm As Form
        Dim i   As Integer
        
        ObjectsOpen = ""
        
        ' get all open forms
        For Each frm In Forms
            If SysCmd(acSysCmdGetObjectState, acForm, frm.Name) <> 0 Then
                If includeDesignMode Or frm.CurrentView <> acCurViewDesign Then
                    ObjectsOpen = ObjectsOpen & "," & frm.Name
                End If
            End If
        Next
        
        ' get all open reports
        For Each ob In Reports
            If SysCmd(acSysCmdGetObjectState, acReport, ob.Name) <> 0 Then
                If includeDesignMode Or ob.CurrentView <> acCurViewDesign Then
                    ObjectsOpen = ObjectsOpen & "," & ob.Name
                End If
            End If
        Next
        
        ' get all open tables
        For Each ob In CurrentData.AllTables
            If ob.isLoaded Then
                If includeDesignMode Or ob.CurrentView <> acCurViewDesign Then
                    ObjectsOpen = ObjectsOpen & "," & ob.Name
                End If
            End If
        Next
        
        ' get all open saved queries
        For Each ob In CurrentData.AllQueries
            If ob.isLoaded Then
                If includeDesignMode Or ob.CurrentView <> acCurViewDesign Then
                    ObjectsOpen = ObjectsOpen & "," & ob.Name
                End If
            End If
        Next
        
        ' get any new unsaved queries (Query1...Query10)
        For i = 1 To 10
            If SysCmd(acSysCmdGetObjectState, acQuery, "Query" & CStr(i)) <> 0 Then
                ObjectsOpen = ObjectsOpen & "," & "Query" & i
            End If
        Next
        
        If ObjectsOpen <> "" Then ObjectsOpen = Mid$(ObjectsOpen, 2)
    
    
    End Function
    Thanks for your help...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-20-2020, 11:51 AM
  2. Replies: 6
    Last Post: 03-28-2019, 09:41 AM
  3. Access keeps on resizing objects by itself
    By brunces in forum Access
    Replies: 2
    Last Post: 11-18-2016, 12:19 PM
  4. Replies: 2
    Last Post: 04-27-2016, 06:56 AM
  5. Check for open objects
    By Perceptus in forum Programming
    Replies: 4
    Last Post: 01-13-2015, 02:56 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums