Solved: useform help filling list from DB VBA

[mercmannick]

Hi

Have been getting some help on Mr Excel

http://www.mrexcel.com/forum/showthread.php?p=1968173#post1968173
to try and resolve this , i am so close to getting results i need

i have a userform that has 1 textbox which is populated by activecell in a range, 1 command button to run code (below), and a listbox to display the results .

at the moment i am getting only first 2 records and they are transposed across 2 rows and approx 8 columns

i need to get my data back in 3 columns and vertically, if anyone could assist in looking at code to see where i am going wrong it would be gratefully appreciated

Public Sub Routeuserform()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim MyConn
Dim i As Long, intLastRow As Long
Dim ShDest As Worksheet
Dim ssSQL As String
Dim rng As Range
Dim Cell As Variant
Dim whereString
Dim x As Integer
Dim varrecords As Variant


ssSQL = " SELECT [Sql Man Plan].[CW Drg] AS [Cw No], [FN Routes].description AS Details, [FN Routes].hours AS [EST Hours]" _
& " FROM [FN Routes] INNER JOIN [Sql Man Plan] ON [FN Routes].[file no] = [Sql Man Plan].[File No]" _
& " WHERE ((([Sql Man Plan].[CW Drg])='" & UserForm1.TextBox1.Value & "'))"


Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=ssSQL, ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
Options:=adCmdText
'i want to populate userform listbox now
rst.MoveLast

'Count the number of records that are being returned and assign that
'number to a variable.
x = rst.RecordCount

'Move the record pointer to the first record. This is required in
'order to return the number of records specified by the RecordCount
'Property. If this is not done, the record pointer will remain on
'the last record and only the last record will be returned.
rst.MoveFirst

'Return the records to an array variable.
varrecords = rst.GetRows(x)

'Fill the list box.
UserForm1.ListBox1.List = varrecords


' Close the connection
'rst.Close
'cnn.Close
'Set rst = Nothing
Set cnn = Nothing

End Sub

Many Thanks

Nick:banghead:

[Jan Karel Pieterse]

I guess this should be:

'Fill the list box.
UserForm1.ListBox1.List = Application.Transpose(varrecords)