Excel to Access Help (1 Viewer)

[Tark221]

Hi all,

Apologies if I've posted this in the wrong section of the forum, I wasn't sure if it was more of an Access or Excel question.

Just a bit of background on my problem. I have a combobox and a listbox on an Excel Form, on the combobox change event I query an Access Database which populates this listbox. The criteria of the query is the value of the combo box. It all seems to be working fine except when the list box populates with a record instead of going from left to right, it just stacks like in a list. I don't know if anyone has had any trouble with this before.

This is my code:

'~~> Database NameConst mdbPath = "C:\Users\bill\Documents\Current Projects\Knowledge\Excel Version\Database\db1.mdb"
'~~> Connection String
Const strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbPath & ";"


Dim cn      As New ADODB.Connection
Dim rs      As New ADODB.Recordset
Dim MyArray As Variant
Dim sSQL    As String


sSQL = "SELECT * from Process where parentname ='Registering'"
cn.Open strCon

'~~> Open recordset and copy to an array
rs.Open sSQL, cn

MyArray = rs.GetRows

'~~> Update listbox
With Me.lbSearch
.Clear
.ColumnCount = 5
.List = Application.Transpose(MyArray)
.ListIndex = -1
End With

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

Any help would be much appreciated.

 

[pr2-eugin]

Why could you simply not set the ListBox rowsource?

Me.lbSearch.Rowsource = "SELECT * FROM Process WHERE parentname = 'Registering'"

 

[Tark221]

Which part of that code would what you suggest replace. Thanks for the reply pulling my hair out with this one

 

[pr2-eugin]

Okay, I must apologize, I thought you were using Access. Well in the case you want to populate the Excel listbox, I think it should be something like.

[COLOR=Green]'~~> Database NameConst mdbPath = "C:\Users\bill\Documents\Current Projects\Knowledge\Excel Version\Database\db1.mdb"
'~~> Connection String[/COLOR]
Const strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbPath & ";"

Dim cn      As New ADODB.Connection
Dim rs      As New ADODB.Recordset
Dim MyArray As Variant
Dim sSQL    As String

cn.Open strCon
sSQL = "SELECT field1, field2, field3, field4, field5 FROM Process WHERE parentname ='Registering'"
[COLOR=Green]'~~> Open recordset[/COLOR]
rs.Open sSQL, cn
rs.MoveFirst

[COLOR=Green]'~~> Update listbox[/COLOR]
With Me.lbSearch
    .Clear
    .ColumnCount = 5
    Do While Not rs.EOF
        .AddItem
        .List(i, 0) = rs.Fields(0)
        .List(i, 1) = rs.Fields(1)
        .List(i, 2) = rs.Fields(2)
        .List(i, 3) = rs.Fields(3)
        .List(i, 4) = rs.Fields(4)
        i = i + 1
        rst.MoveNext
    Loop
End With

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

 

[Tark221]

That works!!! Thank you.

 

[pr2-eugin]

Not a problem ! Good luck