VBA - NULL values in Listview

Question

I've created a simple VBA interface to connect Excel to a MySQL DB. The VBA part acts as a preview of data for the user to choose what item he wants to import to the Excel sheet.

Until now I've work with a very complete set of data, but I got to a Table which (because of the nature of the items) some fields are NULL.

Now every time I try to check the values in the VBA I get the Run-time error 13 Type mismatch in the listview component. At first I though it was a field with DECIMAL typing, but after changing it to a DOUBLE (for testing) the problem persisted, and it was until I notice that if only checks columns with no NULL value, the problem disappears. Off course I can't omit this values.

I tried some .Tostring functions but with no success. And I failed to implement a IF to check for NULL in the obj.

This is my code:

Private Sub Exec_search_Click()

'Define variables related to Form
Dim Prod_input As MSForms.TextBox
Dim QueryResults As ListView

' Connection variables
Dim rs As ADODB.Recordset
Dim conn As New ADODB.Connection
Dim ConnCmd As ADODB.Command
Dim ColNames As ADODB.Fields
Dim sqlstr As String ' SQL to perform various actions

Dim server_name As String
Dim database_name As String
Dim user_id As String
Dim password As String
Dim ProductName As String

Dim Count As Integer


'Grab the product
Set Prod_inputText = Me.Controls.Item("Prod_input")
ProductName = Prod_inputText.Value
    
Set QueryResults = Me.Controls.Item("QueryResults")

server_name = "localhost"
database_name = "#####"
user_id = "########"
password = "#########"

Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 8.0 ANSI Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password & ""



' Extract MySQL table data to first worksheet in the workbook
Set rs = New ADODB.Recordset
Set ConnCmd = New ADODB.Command

sqlstr = "SELECT referencia, descricao, pvp FROM produtos WHERE referencia LIKE '" & ProductName & "%'" ' extracts all data
rs.Open sqlstr, conn


'This will allow the command object to use the active connection
ConnCmd.ActiveConnection = conn

'Define the query string (Comes from our textbox) & the command type
ConnCmd.CommandText = sqlstr
ConnCmd.CommandType = adCmdText

'Exectue the query & get the column names (Fields)
Set rs = ConnCmd.Execute
Set ColNames = rs.Fields

'Write the results to query section
With QueryResults
    
    'Lets clear the old results
    .ColumnHeaders.Clear
    .ListItems.Clear

    'For each field name, add it to the list view as a column header
    For Each ColName In ColNames
        
        'Go to the column headers collection, and use the add method to add a new header
        .ColumnHeaders.Add Text:=ColName.Name
        
    Next
    
   ' If the recordset is empty, display a message
   If rs.EOF Then
        
        'Display the message
        MsgBox Prompt:="The query returned no results. No Data to display.", Buttons:=vbInformation, Title:="Query Status"
        
        'Close the connection
        conn.Close
        
        'Exit the sub
        Exit Sub
        
   Else 'Otherwise begin populating the list view
        
        'Grab the recordset
        With rs
        
            'Keep going until you've reach the end of the recordset
            Do Until .EOF
            
            'Initialize a count, this will help to determine whether to add a new row vice a new column
            Count = 1
            
                'Loop through all the fields in the recordset
                For Each fld In .Fields
                
                    'If it's the first field of the recordset, that means we have the first column of a new row
                    If Count = 1 Then
                    
                        'If it's a new row, then we will add a new ListItems (ROW) object
                        Set ListItm = QueryResults.ListItems.Add(Text:=fld.Value)
                    
                    Else
                    
                        'If it's not a new row, then add a ListSubItem (ELEMENT) instead
                        ListItm.ListSubItems.Add Text:=fld.Value
                    
                    End If
                    
                    'Make sure to increment the count, or else EVERYONE will be a "New Row"
                    Count = Count + 1
                
                Next
            
            'Move to the next recordset
            .MoveNext
            
            Loop
        End With
        
        'When you're done with all the recordsets close the connection
        conn.Close

    End If
    
End With

End Sub

I get the error in the line : ListItm.ListSubItems.Add Text:=fld.Value

I only started working on VBA very recently, so I might be missing something simple, and I didn't find anything related to this problem in here, but also, I'm very new to SO.

Add isnull checks to the SQL, so isnull(referencia,0) would replace nulls for 0 ? Or a similar check before putting the value in — Nathan_Sav
– Nathan_Sav, Commented Apr 13, 2021 at 10:08
@Nathan_Sav OMG I was so focus on the VBA that i forgot the SQL part! It worked perfectly. Just one thing, the column name is presented as : IFNULL(pvp, 0) because of the SELECT. I tried to make a "AS pvp" but it crashes. — Rwolf27
– Rwolf27, Commented Apr 13, 2021 at 10:36
@Nathan_Sav Nope, my bad, my sntax was not right, i manage to get the column name right, thanks!!!! — Rwolf27
– Rwolf27, Commented Apr 13, 2021 at 11:01

FunThomas · Accepted Answer · 2021-04-13 10:28:34Z

1

If you don't want to add a IsNull-function in you SQL (as Nathan_Sav suggested as a comment): There is a IsNull-function in VBA. With that, you can create a simple function that returns for example an empty string (or a 0 or whatever you prefer):

Function getFieldValue(fVal As Variant, Optional defaultValue = "") As String
    If IsNull(fVal) Then
        getFieldValue = defaultValue
    Else
        getFieldValue = CStr(fVal)
    End If
End Function

Your code, btw, could be simpler if you loop over the fields of the recordset using a counter rather than a for each-loop. Using the getFieldValue-function:

    Do Until rs.EOF
        Set ListItm = QueryResults.ListItems.Add(getFieldValue(rs(1).Value))
        For Count = 2 To rs.Fields.Count
            ListItm.ListSubItems.Add Text:=getFieldValue(rs(Count).Value)
        Nex
        rs.MoveNext
    Loop

answered Apr 13, 2021 at 10:28

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rwolf27 Over a year ago

1.Sorry for my ignorance, but how could you integrate a similar code of the first sample in my code? 2.Ye, my code is just a bunch of snippets of various tutorials of VBA that I saw ! I'm really new to this. But this code can make more than one column in the listview ?

FunThomas Over a year ago

Simply replace the Do Until-Loop with the code I posted (you can also remove the With-statement) and and the function somewhere in your module. Sure, it will add all columns - the first column is used to add a new entry and inside the loop (starting with the 2nd column), it will add a subitem.

Rwolf27 Over a year ago

Ok, so other than that i have to add the function getFieldValue right? How can i add that functon ?

FunThomas Over a year ago

Just Copy&Paste it into the module, where ever you want.

Collectives™ on Stack Overflow

VBA - NULL values in Listview

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related