mercmannick
06-14-2009, 11:04 AM
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:
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: