snowmantle
Programmer
Hi I have got the below code, I want a function that passes the values to my list box or combo box on a user form. I didnt like the idea of passing a recordset so i put it in an array.. problem is how do i reuse it as an array to populate the listbox?? the below doesnt work
Code:
Function GetWarehouses() As Variant
'getting the warehouse list out of the database for use in the user form drop down list
'if no records are found then the function returns null
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim myDB As String
Dim sqlStr As String
Dim i As Integer
Dim iRows As Integer
myDB = ThisWorkbook.path & "\" & DBNAME
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myDB & ";"
sqlStr = "SELECT [warehouse_key], UCASE([warehouse_name]) FROM [Warehouses] ORDER BY [warehouse_name]"
Set rs = New ADODB.Recordset
rs.Open sqlStr, conn, adOpenStatic, adLockReadOnly
iRows = rs.RecordCount
If iRows > 0 Then
'creating array, its dimensions are zero based
'ReDim is used to create a dynamic array by passing in a variable for its size
'this can't be done without doing a ReDim
Dim warehouses() As Variant
ReDim warehouses(rs.RecordCount - 1, 1)
i = 0
Do While Not rs.EOF
warehouses(i, 0) = CStr(rs.Fields(0))
warehouses(i, 1) = CStr(rs.Fields(1))
rs.MoveNext
i = i + 1
Loop
MsgBox IsArray(warehouses)
GetWarehouses = warehouses
End If
GetWarehouses = Null
rs.Close
conn.Close
rs = Nothing
conn = Nothing
End Function
'UserForm Code
Private Sub UserForm_Initialize()
'needs brackets to keep this as an array
Dim warehouses
Dim i As Integer
warehouses = DbaseMod.GetWarehouses()
'MsgBox IsArray(DbaseMod.GetWarehouses())
MsgBox IsArray(warehouses)
If Not warehouses Is Null Then
ListBox1.ColumnCount = 2
ListBox1.List() = warehouses
' For i = 0 To UBound(warehouses)
' CBWarehouses.AddItem warehouses(i, 1), warehouses(i, 0)
' Next
End If
End Sub