Userform Listbox not displaying last item value in the first column

[mvyosh11]

I am trying to display items in a ListBox for a user to reference. The ListBox is given data using ListBox.List = Some_Array 'a 2 dimensional array I generate from a database.
The following code shows what I am trying to do. The first sub I use to update this list given information from the txtIndex textbox in my userform. The red text is the line where this is done. The other two sets of code are functions that I use in this line to add column names the the array data selected from the record set and then transpose that array to pass it into the Listbox in the correct orientation. I had to use a custom transpose function as application.transpose will not allow null values in the array for some reason.

My problem is that the listbox wont display the value for lstdata.List(1,0). It is always blank. If I use the immediate window I see that the value does exist. For some reason the Listbox won't display it. I am out of ideas to figure out what is going on here. I need somebody else to give me a new direction. Thanks in advance. I hope this is enough information.

Private Sub Update_Controls()
Dim Myrecset As Object
Dim SQLConn As Object
Dim IndexField As String
Dim Index As Integer
Dim F As Object


Select Case gTableSelected
Case Is = ""
Exit Sub
Case Is = "SlushFip.dbo.Scrap"
IndexField = "Scrap_Index"
Case Is = "SlushFip.dbo.Pitch_Attainment"
IndexField = "Pitch_Index"
Case Is = "SlushFip.dbo.Downtime"
IndexField = "DT_Index"
End Select


lstData.Clear
cboColumn.Clear


If IsNumeric(txtIndex) Then Index = CInt(txtIndex)


Set SQLConn = CreateObject("ADODB.Connection")


SQLConn.connectionstring = SQLConStr


SQLConn.Open


On Error GoTo Handler

Set Myrecset = CreateObject("ADODB.RECORDSET")

With Myrecset
.ActiveConnection = SQLConn
.Source = gTableSelected
.LockType = 1 'adlockReadOnly
.CursorType = 0 'adOpenForwardOnly
.Open

Do Until .EOF
If .fields(IndexField) = Index Then
lstData.ColumnCount = .fields.Count
lstData.List = Custom_Transpose(AddColumnNames(Myrecset.getrows(1), Myrecset))
Exit Do
End If
.movenext
Loop

For Each F In .fields
cboColumn.AddItem F.Name
Next F

End With


Myrecset.Close

SQLConn.Close


Exit Sub


Handler:
MsgBox "An error occured"
Myrecset.Close
SQLConn.Close
End Sub



Private Function AddColumnNames(RecordArray As Variant, Recset As Object) As Variant
Dim Temp_Array As Variant
Dim count1, Count2 As Integer


Temp_Array = RecordArray
ReDim RecordArray(0 To UBound(Temp_Array, 1), 0 To UBound(Temp_Array, 2) + 1)


For count1 = 0 To UBound(Temp_Array, 1)
For Count2 = 0 To UBound(Temp_Array, 2)
RecordArray(count1, Count2 + 1) = Temp_Array(count1, Count2)
Next
Next


For count1 = 0 To UBound(RecordArray, 1)
RecordArray(count1, 0) = Recset.fields(count1).Name
Next


AddColumnNames = RecordArray


Erase RecordArray
Erase Temp_Array
Set RecordArray = Nothing
Set Temp_Array = Nothing


End Function




Private Function Custom_Transpose(GivenArray As Variant) As Variant
'Fixes problem with application.transpose not allowing null values in an array
Dim Temp_Array As Variant
Dim count1, Count2 As Integer


ReDim Temp_Array(LBound(GivenArray, 2) To UBound(GivenArray, 2), LBound(GivenArray, 1) To UBound(GivenArray, 1))


For count1 = LBound(GivenArray, 1) To UBound(GivenArray, 1)
For Count2 = LBound(GivenArray, 2) To UBound(GivenArray, 2)
Temp_Array(Count2, count1) = GivenArray(count1, Count2)
Next
Next


Custom_Transpose = Temp_Array
End Function

 

[RedBeard]

Just a thought, but (and maybe I'm just too tired and I'm reading this wrong), you start counting from 1, not 0 in your list.

 

[mvyosh11]

Just a thought, but (and maybe I'm just too tired and I'm reading this wrong), you start counting from 1, not 0 in your list.

Thanks for the response RedBeard. I am a little confused what you mean by counting from 1 instead of zero. Did you mean my function or the literal top index of the ListBox? In any case, I have stumbled across what I believe is the culprit. When I retrieve the data from the recordset and put it into my array, it gets stored as a variant/decimal data type. This is likely because the column data type is Bigint for the SQL database. I have a feeling that the Listbox does not support the display of values in that data type.

I put a break in my code and manually inserted a numerical value into the array and it was taken as an integer data type. I then resumed and the number displayed just fine. I should consider displaying the value as a string, as the box will be used as a reference really. I need to find a method of determining if a value in the array is a decimal data type. Suggestions?

 

[mvyosh11]

The decimal data type was indeed the culprit. Although the listbox does recognize the value it is given, it will not display items in a decimal data type for some reason. The work around was for me to convert anything from this type into a string as I do not need this to stay in the numerical format. I added the red colored line into my custom transpose function to check for this.

Private Function Custom_Transpose(GivenArray As Variant) As Variant
'Fixes problem with application.transpose not allowing null values in an array
Dim Temp_Array As Variant
Dim count1, Count2 As Integer


ReDim Temp_Array(LBound(GivenArray, 2) To UBound(GivenArray, 2), LBound(GivenArray, 1) To UBound(GivenArray, 1))


For count1 = LBound(GivenArray, 1) To UBound(GivenArray, 1)
For Count2 = LBound(GivenArray, 2) To UBound(GivenArray, 2)
If TypeName(GivenArray(count1, Count2)) = "Decimal" Then GivenArray(count1, Count2) = CStr(GivenArray(count1, Count2))
Temp_Array(Count2, count1) = GivenArray(count1, Count2)
Next
Next


Custom_Transpose = Temp_Array
End Function