User Form Help Please

[mercmannick]

Hi

i would like to create a userform with a textbox to enter a product code , a command button to run VBA and a big text box to show output of VBA from Textbox 1 which i will connect to DB and pull back information relevant to that product code , is this possible i can create the userform no problem and already have code to pull back from DB into Excel , can anyone give me a idea how to do this if its atall possible

Thanks

Nick

 

[Norie]

Nick

One word - Transpose.

Well more than one actually, two, Application.Transpose.

Or just to be on the safe side Application.WorksheetFunction.Transpose({yourarray}).

UserForm1.ListBox1.List = Application.WorksheetFunction.Transpose(varrecords)

PS I think you might need to look at some other words for your SQL statement - you seem to be repeating some data.

 

[mercmannick]

Nick

One word - Transpose.

Well more than one actually, two, Application.Transpose.

Or just to be on the safe side Application.WorksheetFunction.Transpose({yourarray}).

UserForm1.ListBox1.List = Application.WorksheetFunction.Transpose(varrecords)

PS I think you might need to look at some other words for your SQL statement - you seem to be repeating some data.

Wow Norie i tuly beleive you are a genious hehe , works a treat just one little problem it gives me about 4 to 6 instances of each returned record, , on the ssSql how do you mean check the words ?

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 & "'))"

i dont follow

Regards

Nick

 

[Norie]

Nick

The reason your are getting repeated records is because of your SQL query.

Try experimenting with the query you have in Access.

When in query Design mode take a look at Query Properties in particular Unique Values/Records.

Also look at View>Totals and Group By.

 

[mercmannick]

Norie

Thanks for all your help , solved now Below is code if anyone needs in future

Kind Regards

Nick

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 & "'))" _
         & " GROUP BY [Sql Man Plan].[CW Drg],[FN Routes].[sort order], [FN Routes].description, [FN Routes].hours" _
         & " ORDER BY [FN Routes].[sort order];"
         




'ORDER BY  [FN Routes].[sort order];


    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 = Application.WorksheetFunction.Transpose(varrecords)
        
      
    ' Close the connection
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

End Sub

 

[mercmannick]

Hi all Just one quick question regarding above userbox well listbox to be more precise, if 1 of my rows had more than 150 characters is there a way of word wrapping at all

Thanks

Nick

 

[Norie]

Nick

One quick answer - no.

If you have data that is text of that length you shouldn't be trying to show
it in a listbox.

Listboxs aren't really intended to show all the data for a record.

What you should show in a listbox is data that can identify a record.

Then you can let the user select the record they are interested in and display the rest of the data in textboxes or whatever.

 

[mercmannick]

Nick

One quick answer - no.

If you have data that is text of that length you shouldn't be trying to show
it in a listbox.

Listboxs aren't really intended to show all the data for a record.

What you should show in a listbox is data that can identify a record.

Then you can let the user select the record they are interested in and display the rest of the data in textboxes or whatever.

Hiya Norie

How would i do that is there a way if they click on a line in listbox to show full text

Nick

 

[Norie]

Nick

Well it depends where you are storing the data.

There is a way but you need to code it.

Let's say you've got this starting in A1.

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 width=64>Field1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64>Field2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>DataA1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>DataA2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>DataA3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>DataA4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>DataA5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>DataA6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>DataA7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>DataA8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>DataA9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>DataA10</TD></TR></TBODY></TABLE>

Now you've also got a userform with a listbox and a textbox.

Add this code and give it a whirl.

Option Explicit
Private Sub ListBox1_Click()
    If ListBox1.ListIndex <> -1 Then
        TextBox1.Value = Range("B" & ListBox1.ListIndex + 2).Value
        'TextBox2.Value = ListBox1.Value
        TextBox2.Value = ListBox1.List(ListBox1.ListIndex, 1)
    End If
End Sub

Private Sub UserForm_Initialize()
Dim lbl As MSForms.Label
    
    ListBox1.BoundColumn = 2
    ListBox1.ColumnCount = 2
    ListBox1.ColumnWidths = "14;0"
    ListBox1.List = Range("A2:B11").Value
    
    Set lbl = Me.Controls.Add("Forms.Label.1")
    
    lbl.Top = TextBox1.Top
    lbl.Left = TextBox1.Left + TextBox1.Width + 10
    lbl.Caption = "Populated from worksheet"
    
    Set lbl = Me.Controls.Add("Forms.Label.1")
    
    lbl.Top = TextBox2.Top
    lbl.Left = TextBox2.Left + TextBox1.Width + 10
    lbl.Caption = "Populated from list in listbox"
    
End Sub

 

[mercmannick]

Norie thats great , i will be storing my data in 1st listbox1

UserForm1.ListBox1 = Application.WorksheetFunction.Transpose(ssSQL)

so a user will dbl click row and it will show full text in another form or textbox

Thanks

Nick

 

[Norie]

Nick

No problem.

One thing though isn't ssSQL the string for the SQL code?

Oh and you should have a play with column widths so that the listbox only shows relevant data.