“Object variable or With block variable not set” r

Question

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!

Answer 1

Q: What's FillComboBoxFromMDB set to before you call AddItem?
A: Nothing, that is why you get the error

Try defining a variable like

Dim Value as ComboBox

Then calling the AddItem on this

Value.AddItem(...)

then at the end of the function have

FillComboBoxFromMDB = Value

Or as the other answer if you don't want to use a return type like you were trying to use.

Answer 2

You have a function which claims that its return type is ComboBox, but I can't see anywhere where you ever actually set the return value. Since the return value is never set, it will be Nothing, hence your error when you access it.

From the use case you supply, I think what you want is a helper subroutine that works on an existing combobox. So you would call it like this:

' Fill the combobox
FillComboBoxFromMDB(frmMyForm.cmbMyCombo, _
                    "Database.mdb", _
                    "SELECT MyTable.MyText FROM MyTable")

and the subroutine itself would have a signature like this:

Private Sub FillComboBoxFromMDB(ByVal cbo As ComboBox, _ ByVal sDBName As String, _ ByVal sSQL As String)

(note that it is a Sub not a Function). Within the body of the subroutine, where you have

 Call FillComboBoxFromMDB.AddItem(DBRecordset.Fields(0).Value)

instead have

 cbo.AddItem(DBRecordset.Fields(0).Value)

to act on the ComboBox that was passed into the subroutine.

Answer 3

That the problem with working with vb6 form controls, they can only be instantiated in a form. What freakin' horseshit! Oh yeah you can register the DLL that the controls reside in. Have fun with that! I ran into this with the tcp/ip socket.

My solution was to create a SocketDriver interface. Create a form and put the socket on the form. Make the form invisible. Implement the SocketDriver interface on the form. Now you can pass the SocketDriver around.

I like Anthony's answer, except that I would have created an interface named 'DataFiller' with one method.

Public Sub AddItem(item As String)
End Sub

Then implement on your form.

Public Sub AddItem(item As String)
   cmbMyCombo.AddItem(item)
End Sub

Now use Signature

Private Sub FillComboBoxFromMDB(ByVal sDBName As String, _
                                 ByVal sSQL As String, ByVal injectWith As DataFiller)
   'yada yada code

   injectWith.AddItem(DBRecordset.Fields(0).Value)

   'yada yada code

End Sub

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

By using the interface you can have some separation of concerns. Your data access knows nothing about forms or controls and your froms and controls does not know where that data came from because the dependency is on an interface

Answer 4

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

Answer 5

I faced same problem in vb6 and found a solution too.

The reason behind the issue was,

my stored procedure had multiple select statements.

Solution : I used SET NOCOUNT ON in the starting of stored procedure and SET NOCOUNT OFF just before the final selection (output) statement.