We help IT Professionals succeed at work.
Get Started

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

stephenlecomptejr
on
4,292 Views
Last Modified: 2013-11-28
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:
https://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
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE