Consulting

Results 1 to 5 of 5

Thread: UserForm Controls are Null during Initialize Event

  1. #1
    VBAX Contributor
    Joined
    Aug 2012
    Posts
    103
    Location

    UserForm Controls are Null during Initialize Event

    I have a UserForm that has multiple ListBox controls that I want to fill during the Initialize event.

    I use Dim myControl as Control in declarations.

    In the Initialize Event, I use Set myControl to lstMinimum and then get data from the database and fill the listbox.

    I'm getting an error because the Control, lstMinimum, is Null. I don't understand because the control's properties are available.

    Anybody know what I'm doing wrong? Thanks.

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,075
    Location
    Unless your code is a State secret could you please show it?
    Greg

    Visit my website: http://gregmaxey.com

  3. #3
    VBAX Contributor
    Joined
    Aug 2012
    Posts
    103
    Location
    Of course not, lol. But, it's lengthy. I thought my set-up might be enough.

    Just an interesting additional side note: the ListBox controls are in a MultiPage control. They all seem to come up Null. The checkboxes, however, do not and I can assign them to myControl. It just seems to be the ListBox controls that are Null.

    It basically works like this: from a standard start module I have this:
    Call frmPrograms.Initialize

    Then it goes to the UserForm's Initialize Sub as follows:

    Dim AHA_Array() As Variant
    Dim mltpgAHA_Active As Long
    Public AHA_Array_Col_Count As Long
    Public AHADoc As String
    Public CurrOp As String
    Public CurrSubjNm As String
    Public ElemData As String
    Public ElemPref As String
    Public ElementsInOrder As String
    Public MinElemData As String
    Dim myControl As Control
    Public MyElemData As String
    Public MyElemPref As String
    Public MyFormsData As String
    Public MyFormsPref As String
    Public FormPrefs As String
    Public MyFormPrefs As String
    Public MyMinPrefs As String
    Public MyMSDSData As String
    Public MyMSDSPrefs As String
    Public MyProdMansData As String
    Public MyProdMansPrefs As String
    Public MyPlanPrefs As String
    Public MyPostersData As String
    Public MyPosterPrefs As String
    Public MySuppPrefs As String
    Dim OpRowCountArray() As Variant
    Public PrefField As String
    Dim Skip_Chptr_Event As Boolean
    Dim Skip_Form_Event As Boolean
    Public SortByNm As Boolean
    Public SortBySubj As Boolean
    Public strCertSubjList As String
    Public strElementPath As String
    Public strListBoxNm As String
    Public SubjNm As String
    Public SubjNum As Long
    Public SubjCount As Long
    Public SuppElemData As String
    Public strSelectText As String
    Public strPreference As String
    Public strUpdateText As String
    Dim TCC() As Variant
     
    '*********************************************************************************************************************************************************
    '****************************************************************STARTOFINITIALIZE**********************************************************************
    '*********************************************************************************************************************************************************
     
    Sub Initialize()
        '0 to 1 (if there's no Site-SpecificChapter), or # to #+1 (if there's a Site-Specific Chapter)
        ChptrNum = ChptrNum + 1
        
        Call Get_User_Preferences
        
        'Load Minimum element file names
        'Minimum File Names
        myFile = Dir(MyMinElemPath &"\*.doc*") 'Directory of files for loop
        myPrefs = MyMinPrefs
        Set myControl = lstMinimum
        Call Load_Elements
        'Supplemental File Names
        myFile = Dir(MySuppElemPath &"\*.doc*")
        myPrefs = MySuppPrefs
        Set myControl = lstSupplemental
        Call Load_Elements
        'User's File Names
        myFile = Dir(MyElementsPath &"\Chapters\My Chapters\" & UserCoNm & "\*.doc*")
        myPrefs = MyPlanPrefs
        Set myControl = lstMyElements
        Call Load_Elements
        'Need to Order the List
        '**********FORMS**********
        'TEMPLATE FORMS
        myFile = Dir(MyElementsPath & "\Forms\TemplateForms\*.doc*")
        myPrefs = FormPrefs
        Set myControl = lstForms
        Call Load_Elements
        'USER'S FORMS
        myFile = Dir(MyFormsPath & UserCoNm& "\*.doc*")
        myPrefs = MyFormPrefs
        Set myControl = lstForms
        Call Load_Elements
        '**********SDS**********
        'TEMPLATE SDS
        Call Load_SDS
        '**********POSTERS**********
        myFile = Dir(MyElementsPath &"\Posters\Template Posters\" & ProjectState &"\Required\*.doc*")
        myPrefs = MyPosterPrefs
        Set myControl = lstPosters
        Call Load_Elements
        '*********UTILITY**********
        'User's Company Employee Names comboboxesor listboxes
        cmbPreparedBy.List = EENmArray 'USACE AHA
        cmbPreparedBy3.List = EENmArray 'HenselPhelps AHA
        cmbPreparedBy4.List = EENmArray 'TurnerAHA
        cmbEEName.Clear 'Training
        cmbEEName.AddItem "All"'Training
        cmbEEName.List = EENmArray 'Training
        cmbEEName.ListIndex = 0 'Training
        'Database Retrievals
        strConn ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source= '" & MyDataPath& "';Persist Security Info=False"
        Set conn = New ADODB.Connection
        conn.CursorLocation = adUseClient'Apparently you need this to get the recordcount property
        conn.Open strConn
        'Deteremine if the User has OrderedChapters and Forms saved in Preferences
        Record_Returned = False
        Skip_Chptr_Event = False
        myDBSelectStmt = "SELECT [Elementsin Order] FROM [My Preferences] WHERE [User Co Nm] = '" & UserCoNm& "'"
        Call mySelect
        If Record_Returned = True Then
            Skip_Chptr_Event= True
        End If
       Record_Returned = False
        Skip_Form_Event = False
        myDBSelectStmt = "SELECT [Forms inOrder] FROM [My Preferences] WHERE [User Co Nm] = '" & UserCoNm &"'"
        Call mySelect
        If Record_Returned = True Then
            Skip_Form_Event =True
        End If
        'TRAINING AND CERTIFICATIONS LoadTraining Subjects combobox control
        cmbSubj.Clear
        myDBSelectStmt = "SELECT DISTINCT[Subject] FROM [Training] WHERE [User Co Nm] = '" & UserCoNm &"' ORDER BY [Subject] ASC"
        Set myControl = cmbSubj
        Call mySelect
            'AdditionalTraining control/variable configuration
            IfcmbSubj.ListCount > 0 Then
               cmbSubj.ListIndex = 0
            End If
            SortByNm = False
            SortBySubj =False
        '**********AHAS and TRAINING**********
        'Database Retrievals
        myDBSelectStmt = "SELECT [Last Nm]& ', ' & [First Nm] as [EENm], [Phone] FROM [My Employees] WHERE [UserCo Nm] = '" & UserCoNm & "' ORDER BY [Last Nm], [First Nm]ASC"
        Set myControl = lstCompetentPersonNm
        Call mySelect
        strConn ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source= '" & AHAPath& "';Persist Security Info=False"
        Set conn = New ADODB.Connection
        conn.CursorLocation = adUseClient 'Apparentlyyou need this to get the recordcount property
        conn.Open strConn
        'AHA Format (SD, USACE, Hensel Phelps,etc.)
        myDBSelectStmt = "SELECT Format FROM[Formats]"
        Set myControl = cmbAHAFormat
        Call mySelect
        'AHA Equipment List
        myDBSelectStmt = "SELECT [EquipmentNm] FROM [Equipment]"
        Set myControl = cmbEquipment
        Call mySelect
        myDBSelectStmt = "SELECT[Requirement] FROM [Inspect Requirements]"
        Set myControl = lstInspectionRequirements
        Call mySelect
            'Ancillary AHAcontrol configuration
            'Set default AHAtype for user (Safety Designs)
            IfcmbAHAFormat.ListCount > 0 Then
               cmbAHAFormat.ListIndex = 0
            End If
            txtProjectLoc =ProjectNm 'Fill project location textbox with Project Name (USACE AHA)
           optAHAinMain.Value = False
           optAHAasSection.Value = True
           txtContractNum.Value = ProjectOwnContrNum
            'Signature Pagescontrols
            Dim p As Long
            For p = 0 To 50
               cmbSigners.AddItem a
            Next
           cmbSigners.ListIndex = 0
           cmbSigners.Enabled = False
            optSigYes.Value =False
            optSigNo.Value =True
        myRecordset.Close
        Set myRecordset = Nothing
        conn.Close
        Set conn = Nothing
        'Order ListBox Controls - if the User hassaved the order of chapters and/or forms as preferences in the My Data DB, thenwe fill the Order ListBox
        'Controls separately to preserve theUser's saved order.  Otherwise, the Order ListBox Controls are filled asthe user selects/deselcts Chapters
        Dim x As Long
        If Skip_Chptr_Event = True Then
            xsplit =Split(ElementsInOrder, "^")
            For x = 0 ToUBound(xsplit) - 1
               ysplit = Split(xsplit(x), "|")
               lstChptrOrder.AddItem
               lstChptrOrder.Column(0, x) = ysplit(0)
               lstChptrOrder.Column(1, x) = ysplit(1)
            Next
        End If
        If Skip_Form_Event = True Then
            xsplit =Split(FormsInOrder, "^")
            For x = 0 ToUBound(xsplit) - 1
               ysplit = Split(xsplit(x), "|")
               lstFormsOrder.AddItem
               lstFormsOrder.Column(0, x) = ysplit(0)
               lstFormsOrder.Column(1, x) = ysplit(1)
            Next
        End If
            
        'Configure the Multipages
        mltpagSDS.Value = 0 'Multipage for SDS -one for Templates and one for the User's SDS
        If SingleElement = False Then
            MultiPage1.Value= 0 'sets the focus on the first tab/page from the left
           MultiPage1.Pages(0).Visible= True
        ElseIf SingleElement = True Then
           MultiPage1.Pages(0).Visible = False
            If SingleAHA =True Then
               MultiPage1.Value = 1
               MultiPage1.Pages(1).Visible = True
               MultiPage1.Pages(2).Visible = False 'Forms
               MultiPage1.Pages(3).Visible = False 'Posters
               MultiPage1.Pages(4).Visible = False 'MSDS
               MultiPage1.Pages(5).Visible = False 'Certs
            End If
            If SingleForms =True Then
               MultiPage1.Value = 2
               MultiPage1.Pages(1).Visible = False
               MultiPage1.Pages(2).Visible = True 'Forms
               MultiPage1.Pages(3).Visible = False 'Posters
               MultiPage1.Pages(4).Visible = False 'MSDS
               MultiPage1.Pages(5).Visible = False 'Certs
            End If
            If SinglePostings= True Then
               MultiPage1.Value = 3
               MultiPage1.Pages(1).Visible = False
               MultiPage1.Pages(2).Visible = False 'Forms
               MultiPage1.Pages(3).Visible = True 'Posters
               MultiPage1.Pages(4).Visible = False 'MSDS
               MultiPage1.Pages(5).Visible = False 'Certs
            End If
            If SingleMSDS =True Then
               MultiPage1.Value = 4
               MultiPage1.Pages(1).Visible = False
               MultiPage1.Pages(2).Visible = False 'Forms
               MultiPage1.Pages(3).Visible = False 'Posters
               MultiPage1.Pages(4).Visible = True 'MSDS
               MultiPage1.Pages(5).Visible = False 'Certs
            End If
            If SingleCerts =True Then
               MultiPage1.Value = 5
               MultiPage1.Pages(1).Visible = False
               MultiPage1.Pages(2).Visible = False 'Forms
               MultiPage1.Pages(3).Visible = False 'Posters
               MultiPage1.Pages(4).Visible = False 'MSDS
               MultiPage1.Pages(5).Visible = True 'Certs
            End If
        End If
        frmPrograms.Show
    End Sub
     
    Private Sub mySelect()
        Set myRecordset =conn.Execute(myDBSelectStmt)
        Do Until myRecordset.EOF
            If NotIsNull(myControl) Then
               If myRecordset.Fields.Count = 1 Then 'Only one Field is returned by the SELECTCommand
                   myControl.AddItem myRecordset.Fields(0)
               ElseIf myRecordset.Fields.Count > 1 And myControl.ColumnCount = 2 Then 'More than one Field returned and Control has 2 Columns
                   myControl.AddItem
                   myControl.List(lstCompetentPersonNm.ListCount - 1, 0) = myRecordset.Fields(0)
                   myControl.List(lstCompetentPersonNm.ListCount - 1, 1) = myRecordset.Fields(1)
               End If
               myRecordset.MoveNext
            ElseIfIsNull(myControl) Then 'Not filling a control with data - just retrieving thedata, or confirming the data exists
               If myRecordset.RecordCount > 0 Then
                   Record_Returned = True
                   Exit Sub
               End If
            End If
        Loop
    End Sub
     
    Private Sub Load_Elements()
        Dim a As Long
        Do While myFile <> ""
            'Drop file nameextension
            If myFile Like"*.doc" Then
               ModFileNm = Replace(myFile, ".doc", "")
            ElseIf myFileLike "*.docx" Then
               ModFileNm = Replace(myFile, ".docx", "")
            End If
            myControl.AddItemModFileNm 'Add File/Element Name to the ListBox control
            If myPrefs<> "" Then 'Select those file names that are in the User'sPreferences in the My Data DB which has only the name w/out extension/path
               vsplit =Split(myPrefs, "^")
               For a = 0 To UBound(vsplit) 'Loop through the names saved in User's Preferences
                   If vsplit(a) = ModFileNm Then 'If there is a match...
                       myControl.Selected(myControl.ListCount - 1) = True 'Mark the item in thelistbox control "TRUE"
                       Exit For
                   End If
               Next
            End If
            myFile = Dir()
        Loop
    End Sub
     
    Private Sub Load_SDS()
        'Load MSDS element file names
        'SDS Names are stored in a Databaserather than in document names
        Dim conn3 As ADODB.Connection '*Connection String
        Dim myRecordset3 As ADODB.Recordset '*Recordset Object
        Dim strConn3 As String 'Connection string
        
        strConn3 ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & MyDataPath& "';Persist Security Info=False"
        Set conn3 = New ADODB.Connection
        conn3.CursorLocation = adUseClient'Apparently you need this to get the recordcount property
        conn3.Open strConn3
        
        'Load the combobox control with thenames of the available AHA formats (Safety Designs, USACE, etc.)
        Set myRecordset3 =conn3.Execute("SELECT [FileNm], [ProductNm], [MfrNm] FROM [SDS] WHERE [UserCo]= 'All' ORDER BY [MfrNm]")
        Do Until myRecordset3.EOF
            lstSDS.AddItem
            lstSDS.Column(0,lstSDS.ListCount - 1) = myRecordset3.Fields![FileNm]
            lstSDS.Column(1,lstSDS.ListCount - 1) = myRecordset3.Fields![MfrNm] & "; " &myRecordset3.Fields![ProductNm]
           cmbSDSDetail.AddItem myRecordset3.Fields![MfrNm] & "; " &myRecordset3.Fields![ProductNm]
           myRecordset3.MoveNext
        Loop
    End Sub
     
    Private Sub Get_User_Preferences() 'Retrieve Preferencesfrom the Database
        'Declarations and Set-Up
        Dim conn As ADODB.Connection '*Connection String
        Dim myPrefRecordset As ADODB.Recordset '*Recordset Object
        Dim strConn As String 'Connection string
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;DataSource= '" & MyDataPath & "';Persist SecurityInfo=False"
        Set conn = New ADODB.Connection
        conn.CursorLocation = adUseClient'Apparently you need this to get the recordcount property
        conn.Open strConn
        'Load the combobox control with the namesof the available AHA formats (Safety Designs, USACE, etc.)
        Set myPrefRecordset =conn.Execute("SELECT [Min Plan Elems], [Supp Plan Elems], [My Plan Elems],[Elements in Order], [Forms], [My Forms], [Forms in Order], [My Posters],[MSDS], [My MSDS] FROM [My Preferences] WHERE [User Nm] = '" & UserNm& "' AND [User Co Nm] = '" & UserCoNm & "'")
        If myPrefRecordset.RecordCount > 0Then
            If NotIsNull(myPrefRecordset.Fields![Min Plan Elems]) Then
                MyMinPrefs= myPrefRecordset.Fields![Min Plan Elems]
            End If
            If NotIsNull(myPrefRecordset.Fields![Supp Plan Elems]) Then
               MySuppPrefs = myPrefRecordset.Fields![Supp Plan Elems]
            End If
            If NotIsNull(myPrefRecordset.Fields![My Plan Elems]) Then
               MyPlanPrefs = myPrefRecordset.Fields![My Plan Elems]
            End If
            If NotIsNull(myPrefRecordset.Fields![Elements in Order]) Then
               ElementsInOrder = myPrefRecordset.Fields![Elements in Order]
            End If
            If NotIsNull(myPrefRecordset.Fields![Forms]) Then
               FormPrefs = myPrefRecordset.Fields![Forms]
            End If
            If NotIsNull(myPrefRecordset.Fields![My Forms]) Then
               MyFormPrefs = myPrefRecordset.Fields![My Forms]
            End If
            If NotIsNull(myPrefRecordset.Fields![Forms in Order]) Then
               FormsInOrder = myPrefRecordset.Fields![Forms in Order]
            End If
            If NotIsNull(myPrefRecordset.Fields![My Posters]) Then
               MyPosterPrefs = myPrefRecordset.Fields![My Posters]
            End If
            If NotIsNull(myPrefRecordset.Fields![MSDS]) Then
               MSDSPrefs = myPrefRecordset.Fields![MSDS]
            End If
            If NotIsNull(myPrefRecordset.Fields![My MSDS]) Then
               MyMSDSPrefs = myPrefRecordset.Fields![My MSDS]
            End If
        End If
        'Close Connection
        myPrefRecordset.Close
        Set myPrefRecordset = Nothing
        conn.Close
        Set conn = Nothing
    End Sub
     
    '*********************************************************************************************************************************************************
    '****************************************************************ENDOF INITIALIZE************************************************************************
    '*****************************************************************************************************************************

  4. #4
    VBAX Contributor
    Joined
    Aug 2012
    Posts
    103
    Location
    Another weird thing, it seems to me, is that while the Me. keyword will invoke intellisense as you would expect when typing code, but it creates a compile error ("Invalid Qualifier") at run time.

  5. #5
    VBAX Contributor
    Joined
    Aug 2012
    Posts
    103
    Location
    One more thing. It seems like the problem only affects ListBoxes on that particular UserForm. I created a Test UserForm in the same project and wrote the code for the ListBox the same way and it worked fine (Dim myControl as Control; Set myControl = lstTest). That worked fine.

    I then created a new ListBox on the problem UserForm and it throws the Null error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •