"Object variable or With block variable not set" runtime error in VB6

instantiation, runtime, vb6

Solution

You code expresses the belief that the identifier `FillComboBoxFromMDB` has acquired a reference to the combobox on the left hand side of the assignment in the Test procedure.

This is not the case the function will execute first with FillCombBoxFromMDB being Nothing once it it would attempt (and fail) to assign the result to the left hand side.

You need to pass the combobox as a parameter.

Private Sub FillComboBoxFromMDB(ByVal sDBName As String, _
                                     ByVal sSQL As String, ByVal cbo As ComboBox)
    '/*
    ' * Execute SQL in MDB and fill the ComboBox with the results
    ' * Returns filled ComboBox
    ' */
    Dim DB As Database
    Dim DBRecordset As Recordset

    On Error GoTo FillComboBoxFromMDB_ErrHandler

    Set DB = OpenDatabase(sDBName, False, False)

    If Not DB Is Nothing Then
        Set DBRecordset = DB.OpenRecordset(sSQL)
        If Not DBRecordset Is Nothing Then
            If DBRecordset.RecordCount > 0 Then
                Call cbo.AddItem(DBRecordset.Fields(0).Value)
                ' ^^ This row gives the "Object variable or With block variable not set"
            End If
        Else
            Call WriteLog("Unable to execute " & sSQL)
        End If
        DB.Close
    Else
        Call WriteLog("Unable to open " & sDBName)
    End If

    Exit Sub
FillComboBoxFromMDB_ErrHandler:
    Call WriteLog("FillComboBoxFromMDB() error: " & Err.Number & " " & Err.Description)
End Sub

Call it like this:-

 Private Function Test()
 ' Fill the combobox
 Call FillComboBoxFromMDB("Database.mdb", _
                          "SELECT MyTable.MyText FROM MyTable", _
                          frmMyForm.cmbMyCombo ) 
 End Function

Problem

I've got a problem with VB6. I have a form with several ComboBox objects on it. I wish to populate the ComboBoxes via a function that takes a SQL query as a parameter. So the code looks like this ``` Private Function FillComboBoxFromMDB(ByVal sDBName As String, _ ByVal sSQL As String) As ComboBox '/* ' * Execute SQL in MDB and fill the ComboBox with the results ' * Returns filled ComboBox ' */ Dim DB As Database Dim DBRecordset As Recordset On Error GoTo FillComboBoxFromMDB_ErrHandler Set DB = OpenDatabase(sDBName, False, False) If Not DB Is Nothing Then Set DBRecordset = DB.OpenRecordset(sSQL) If Not DBRecordset Is Nothing Then If DBRecordset.RecordCount > 0 Then Call FillComboBoxFromMDB.AddItem(DBRecordset.Fields(0).Value) ' ^^ This row gives the "Object variable or With block variable not set" End If Else Call WriteLog("Unable to execute " & sSQL) End If DB.Close Else Call WriteLog("Unable to open " & sDBName) End If Exit Function FillComboBoxFromMDB_ErrHandler: Call WriteLog("FillComboBoxFromMDB() error: " & Err.Number & " " & Err.Description) End Function ``` I call the function like this. ``` Private Function Test() ' Fill the combobox frmMyForm.cmbMyCombo = FillComboBoxFromMDB("Database.mdb", _ "SELECT MyTable.MyText FROM MyTable") End Function ``` So basically I understand that this comes down to instantiation, but I haven't found anything useful about it online. The New keyword doesn't work like it works in VB.Net. How do I instantiate the FillComboBoxFromMDB combobox so that the function will work? Is it even possible? Thanks in advance!

Original source