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

 

[Tom Urtis]

Is this question related to this:
http://www.mrexcel.com/forum/showthread.php?t=394461

You never got back to us about that after receiving the last replies.

 

[Arcticwarrio]

the code to open a userform is

Private Sub OpenIt()
Set frmUserForm = New UserForm1
frmUserForm.Show
End Sub

what else with it do you need to know?

 

[mercmannick]

Is this question related to this:
http://www.mrexcel.com/forum/showthread.php?t=394461

You never got back to us about that after receiving the last replies.

Hi Tom my sincerest apologies i used the code you sent and it works perfect , i meant to say TY i allways do,

This is for same sheet but for a diffrent thing i am trying

Thanks

Nick

 

[mercmannick]

the code to open a userform is

Private Sub OpenIt()
Set frmUserForm = New UserForm1
frmUserForm.Show
End Sub

what else with it do you need to know?

Hi Artic

To get info from Txtbox 1 into my code and the output back in userform after they enter command button

Thanks

Nick

 

[mercmannick]

Sub Routes()
    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 sSQL 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("BOM To Order")
    
        
                    
            sSQL = "SELECT [Sql Man Plan].[CW Drg], [FN Routes].[sort order], [FN Routes].opref, [FN Routes].description, [FN Routes].hours" _
& "FROM [FN Routes] INNER JOIN [Sql Man Plan] ON [FN Routes].[file no] = [Sql Man Plan].[File No]" _
& "GROUP BY [Sql Man Plan].[CW Drg], [FN Routes].[sort order], [FN Routes].opref, [FN Routes].description, [FN Routes].hours" _
& " WHERE ((([Sql Man Plan].[CW Drg])'" & ShDest.Range("O1").Value & "'))" '="7334")))]"
'this what i would like into my list box based on this ('" & ShDest.Range("O1").Value & "'))")being my Textbox
      

    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:=sSQL, ActiveConnection:=cnn, _
             CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
             Options:=adCmdText
    
    'clear existing data on the sheet
    ShDest.Activate
    'Range("A1").CurrentRegion.Clear
    
    'create field headers
    'i = 0
    With Range("A1")
        For Each fld In rst.Fields
            .Offset(0, i).Value = fld.Name
            i = i + 1
        Next fld
 End With
     
    'transfer data to Excel
    intLastRow = ShDest.Cells(Rows.Count, 1).End(xlUp).Row 'find last row in column A
Range("A" & intLastRow + 1).CopyFromRecordset rst  ' Copy from the recordset starting at the Row after the last row of data in column A
   
    ' Close the connection
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

End Sub

this is code i am using at moment to pull back to Excel sheet , i am trying to have userform with text box where they enter CW No press enter and then listbox will fill with this SQL Data ,

Can anyone help me to resolve this please

Thanks

Nick

 

[mercmannick]

Hi

Can anyone give me little help on best way to do this

Thanks

Nick

 

[mercmannick]

sSQL = "SELECT [Sql Man Plan].[CW Drg] AS [Cw No], [FN Routes].[sort order] AS [OP Order], [FN Routes].opref AS [OP Code], [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("D38").Value & "'))" _
         & " GROUP BY [Sql Man Plan].[CW Drg], [FN Routes].[sort order], [FN Routes].opref, [FN Routes].description, [FN Routes].hours;"

    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:=sSQL, ActiveConnection:=cnn, _
             CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
             Options:=adCmdText
    'clear existing data on the sheet
    ShDest.Activate
    
    
    'create field headers
   
    With Range("A40")
        For Each fld In rst.Fields
            .Offset(0, I).Value = fld.Name
            I = I + 1

Hi can anyone see where i am going wrong here it fails at this point

rst.Open Source:=sSQL, ActiveConnection:=cnn, _
             CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
             Options:=adCmdText

Method 'open' of object '_recordset' failed


Many Thanks

Nick

 

[Norie]

Nick

What's TARGET_DB?

What database application, if any, are you using?

Do you really need to do this in Excel?

 

[mercmannick]

Hi Norie,

Option Explicit
Const TARGET_DB = "BOM.mdb"

Access 2000

Yes it need doing in excel as i will need to enter this Cw no & " WHERE ((([Sql Man Plan].[CW Drg])='" & ShDest.Range("D38").Value & "'))"

when i get it working into userform , eventually i wnt to have userform and when the enter Cw no it will pull back Route with all info i need

Thanks

Nick