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

Can you check that MyConn is what you expect/want it to?

It should, I think, be the full path and file name for your BOM database.

Also check the SQL string.

 

[mercmannick]

Hi Norie , this code works on another project , it is definately the Ssql string , can you see where it is going wrong

Thanks

Nick

 

[Norie]

Nick

I checked the SQL with a dummy value and it looked fine to me.

Didn't take it as far as testing it in Access or anything mind you.

If there is a problem with the SQL string it might not really be the SQL itself, perhaps it's the value from the worksheet that's the problem.

 

[mercmannick]

sSQL = " 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])='" & ShDest.Range("g3").Value & "'))"

Hi Norie

it works ok now , how do i get my textbox1.value inplace of '" & ShDest.Range("g3").Value & "'))

and then the sql put back into listbox1

Thanks

Nick

 

[Norie]

Nick

Well the first bit's easy, replace & ShDest... & with & TextBox1.Value &.

And for the 2nd part, if you mean literally put the SQL in the listbox.

Listbox1.AddItem sSQL

But I've got a feeling that's not what you want.

By the way does this really need to be in Excel?

Why not create an Access database?

It would have all the functionality (and more) you seem to want built-in.

 

[mercmannick]

Hi Norie

Says textbox1 varible not defined

Private Sub CommandButton1_Click()
Call Routestestuserform
End Sub


Private Sub ListBox1_Enter()
ListBox1.AddItem sSQL
End Sub


Private Sub TextBox1_Enter()
ListBox1.AddItem sSQL
End Sub

Public Sub Routestestuserform()
    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 Selectstring
    Dim Fromstring
    Dim Groupstring
    Dim HavingString
    Set ShDest = Sheets("TEST")
            
    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])='" & 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
    
    ' Close the connection
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

End Sub

i want user to enter Cw no in textbox1 then when they click comm button run VBA and populate listbox with Ssql from DB,

Norie other users who will be using this sheet want this info in Excel so i have to call it from here , ideally i would love to be able to double click cell in range I7:I35 and that No will be inserted in Textbox 1 and then call recordset in from VBA


Thanks

Nick

 

[Norie]

Nick

What do you mean the users want the info in Excel?

If they do that can easily be done from Access.

As for Textbox1 - I used that because you used that name in post #14, so I assumed you had a textbox called that.

And I really think you need to clarify what you actually want in the listbox.

First you said you wanted the SQL, so I posted the code for that in post #15.

If you want the results from the query in the listbox that's a different matter.

Also I thought you said the users wanted the data in Excel on a worksheet, not a listbox on a userform.

If you want the userform to appear when cells in that range are clicked try this.

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Intersect(Target, Range("I7:I35")) Is Nothing Then Exit Sub
        
    Cancel = True
    
    Load UserForm1
    
    UserForm1.TextBox1 = ActiveCell.Text
    
    UserForm1.Show
    
End Sub

 

[mercmannick]

Hi Norie ,

yes i was trying to get the results from the query in the listbox not just th text Ssql , my apologies for not explaining properly , is it easy to do , i have no idea how or where to begin

Thanks

Nick

 

[Norie]

Nick

Well it depends what you mean by easy.

There are various ways you could do it, one of which would be to put the results on a worksheet and populate the listbox from there.

Or you could use AddItem, but that's probably not practical since you are returning more than one field.

By the way can you give more details on the listbox? Is it multicolumn? Multiselect?

What do you actually want to use it for?

 

[mercmannick]

Nick

Well it depends what you mean by easy.

There are various ways you could do it, one of which would be to put the results on a worksheet and populate the listbox from there.

Or you could use AddItem, but that's probably not practical since you are returning more than one field.

By the way can you give more details on the listbox? Is it multicolumn? Multiselect?

What do you actually want to use it for?

Hi Norie the list box will be multirows , and 3 to 4 columns

hope this helps

Nick