UserForm Controls are Null during Initialize Event

**Mavila** · 11-14-2019, 12:12 PM

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.

**gmaxey** · 11-14-2019, 02:13 PM

Unless your code is a State secret could you please show it?

**Mavila** · 11-14-2019, 02:40 PM

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************************************************************************
'*****************************************************************************************************************************

**Mavila** · 11-14-2019, 03:33 PM

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.

**Mavila** · 11-14-2019, 03:54 PM

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.

Thread: UserForm Controls are Null during Initialize Event

Thread Tools

Display

UserForm Controls are Null during Initialize Event

Posting Permissions