How to solve run-time error 459 on referencing a form still being loaded but not quite opened yet?

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
At line item #8 - bValue = Nz(Forms!frmMainMenu!sfrmRpts.Form.cDpt, False) - I get the error 459 - object or class does not support the set of events?  Please note code below:

It was code provided to me eariler but I had to tweak it at the following URL:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_25938812.html

This subform called sfrmShwPROJ_DPT is within another subform called sfrmRpts and that subform is a part of the main menu called frmMainMenu.  Thus as the Main Menu form called frmMainMenu is opened and loaded the following is ran inside the subform but the main menu is not completely drawn yet.  

As you can see I'm trying to see if the main menu form is opened before testing a control's value - but testing that its opened already does not help me overcome the error.

To help further explain what I'm trying to do is avoid using global variables and go with controls on the main menu.  The main menu contains an area where report options are selected in the sfrmRpts subform.  Then right beside the sfrmRpts is the other subform (that has no connection to either main menu or the report opitons) that merely displays options of filtering depts.  

People can click on a button and change the subform source and if they go from displaying depts (sfrmShwPROJ_DPT )  to rooms (sfrmShwPROJ_RM ) and then back to depts (sfrmShwPROJ_DPT ) - I don't want it to create the shwPROJ_DPT table again - I want it to use the shwPROJ_DPT  that has been adjusted.  Thus I use the Tst_Dpt method to tell if I need to create the recordsource from scratch or not.

Thanks for any help in advance.  I sure hope capricorn1 comments about this since he helped develop this.
Private Function Tst_Dpt() As Boolean
  
  Dim bMM As Boolean
  Dim bValue As Boolean
  
  bMM = IsOpen("frmMainMenu")
  If bMM = True Then
    bValue = Nz(Forms!frmMainMenu!sfrmRpts.Form.cDpt, False)
  End If
  bMM = IsOpen("sfrmRpts")
  If bMM = True Then
    bValue = Nz(Forms!sfrmRpts.cDpt, False)
  End If
  
  If g_bLoadDept = True Or bValue = True Then Tst_Dpt = True

End Function


'Form Open goes before Form Load in MS Access

Private Sub Form_Open(Cancel As Integer)
  Set_Data
End Sub

Private Sub Set_Data()

  Dim sSQL As String
  
  Dim sMeName As String
  Dim sStdName As String
  Dim sShwName As String
  Dim sTblName As String
  
  sMeName = Me.Form.Name
  sTblName = Replace(sMeName, "sfrmShw", "")
  sStdName = Replace(sMeName, "sfrmShw", "std")
  sShwName = Replace(sMeName, "sfrmShw", "shw")
  
  If This_Table_Exists(sStdName) = False Then
  
    sSQL = "SELECT " & KeyCode & ", " & KEYNAME & ", [Show], 0 AS Sort INTO " & sStdName
    sSQL = sSQL & " FROM " & sTblName
    
    CurrentDb.Execute sSQL
    DoEvents
  End If
  
  If This_Table_Exists(sShwName) = False Then
  
    sSQL = "SELECT " & KeyCode & ", " & KEYNAME & ", [Show], 0 As Sort INTO " & sShwName
    sSQL = sSQL & " FROM " & sStdName
    
    CurrentDb.Execute sSQL
    DoEvents
    
  End If
      
  If Tst_Dpt = False Then
    
    sSQL = "DELETE * FROM [" & sShwName & "]"
    CurrentDb.Execute sSQL
    
    sSQL = "INSERT INTO [" & sShwName & "] ( " & KeyCode & ", " & KEYNAME & ", Show, Sort )"
    sSQL = sSQL & " SELECT '" & SELECTCODE & "' AS " & KeyCode & ", '" & SELECTALL & "' AS " & KEYNAME & ", True AS Show, 0 AS Sort"
    CurrentDb.Execute sSQL
    
    sSQL = "INSERT INTO " & sShwName & " ( [" & KeyCode & "], [" & KEYNAME & "], Show, Sort )"
    sSQL = sSQL & " SELECT [" & sStdName & "].[" & KeyCode & "], '   ' & [" & KEYNAME & "] AS DptName, [" & sStdName & "].[Show], 1 AS DptS"
    sSQL = sSQL & " FROM [" & sStdName & "]"
    CurrentDb.Execute sSQL
    
  End If
  
  sSQL = "SELECT " & KeyCode & ", " & KEYNAME & ", Show, Sort"
  sSQL = sSQL & " FROM " & sShwName
  sSQL = sSQL & " ORDER BY Sort, " & KEYNAME
  Me.RecordSource = sSQL
  
End Sub


Public Function IsOpen(ByVal strformname As String) As Boolean
On Error GoTo Err_IsOpen

    Const conDesignView = 0
    Const conObjStateClosed = 0
    
    IsOpen = False
    
    If SysCmd(acSysCmdGetObjectState, acForm, strformname) <> conObjStateClosed Then
        
        If Forms(strformname).CurrentView <> conDesignView Then
            IsOpen = True
            
        End If
        
    End If
    
Exit_IsOpen:
    Exit Function
    
Err_IsOpen:
    Call Error_Action(Err, Err.description, "modGlobal @ IsOpen", Erl())
    Resume Exit_IsOpen
End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nevermind, I adjusted and added the following to solve my problem:
Any concerns with what I have done?
Public Function IsLoaded(strName As String, _
Optional intType As Integer = acForm) As Boolean
On Error GoTo Err_This
' Get the current state of the named object

  IsLoaded = SysCmd(acSysCmdGetObjectState, _
intType, _
strName) = acObjStateOpen

Exit_This:
  Exit Function

Err_This:
  Call Error_Action(Err, Err.description, "modCritiera @ IsLoaded", Erl())
  Resume Exit_This
End Function

Private Function Tst_Dpt() As Boolean
  
  Dim bMM As Boolean
  Dim bMMLoad As Boolean
  Dim bValue As Boolean

  bMM = IsOpen("frmMainMenu")
  bMMLoad = IsLoaded("frmMainMenu")
  
  If bMM = True And bMMLoad = True Then
    bValue = Nz(Forms!frmMainMenu!sfrmRpts.Form.cDpt, False)
  End If
  bMM = IsOpen("sfrmRpts")
  If bMM = True Then
    bValue = Nz(Forms!sfrmRpts.cDpt, False)
  End If
  
  If g_bLoadDept = True Or bValue = True Then Tst_Dpt = True

End Function

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial