User form for query and data dump into excel (code included)

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: User form for query and data dump into excel (code included)

  1. #1
    Board Regular
    Join Date
    Sep 2015
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default User form for query and data dump into excel (code included)

     
    Hello all,

    I'm trying to create a code in excel vba that will prompt the user to browse for a .mdb file, open a user form allowing them to filter the data in 3 fields (there are 19 total fields) and drop the filtered data into excel along with all 19 of the field names.

    Currently I have some of the code done. Right now it prompts the user to search for the file and populates the combo boxes with the correct data. I haven't been able to get much further.

    Do all of the subs in the userform need to be private? It seems cumbersome to have to define the connection and the recordset again and again and again in each of the subs.

    I do realize that the subs for the combo boxes haven't been coded yet. I'm still working on figuring all of that out.

    I'm a rookie at all of this. Any help on what I'm missing would be much appreciated.



    Code:
    Public Sub UserForm_Initialize()
    
    
    ChDrive "U:\"
    ChDir "U:\Engineering\DesignCommon\VALVETRAIN\SPRING DESIGN AND CALCULATION\VALKIN SPRING MODELS\01-DATA FOR CALCULATION SPECIFIC SPRINGS\SAS"
    strDBpath = Application.GetOpenFilename(FileFilter:=strFilt, FilterIndex:=intFilterIndex, Title:=strDialogueFileTitle)
    
    
        On Error GoTo UserForm_Initialize_Err
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
    
    
        cnn.Open "Provider=Microsoft.ace.OLEDB.12.0;" & "Data Source=" & strDBpath
        
        
    rst.Open "SELECT DISTINCT [PartNumber] FROM [All Data Combined];", _
            cnn, adOpenStatic
        rst.MoveFirst
        
        With Me.Part_Number_Box
            .Clear
            Do
                .AddItem rst![PartNumber]
                rst.MoveNext
            Loop Until rst.EOF
        End With
    rst.Close
    
    
    
    
    rst.Open "SELECT DISTINCT [PartName] FROM [All Data Combined];", _
            cnn, adOpenStatic
            
        rst.MoveFirst
          With Me.Part_Name_Box
            .Clear
            Do
                .AddItem rst![PartName]
                rst.MoveNext
            Loop Until rst.EOF
        End With
    rst.Close
    
    
    
    
    rst.Open "SELECT DISTINCT [OrderNum] FROM [All Data Combined];", _
            cnn, adOpenStatic
            
        rst.MoveFirst
          With Me.Order_Number_Box
            .Clear
            Do
                .AddItem rst![OrderNum]
                rst.MoveNext
            Loop Until rst.EOF
        End With
    
    
        
    UserForm_Initialize_Exit:
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    UserForm_Initialize_Err:
        MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
        Resume UserForm_Initialize_Exit
        
        
    DataArray = rst.GetRows()
    
    
    
    
    'show the contents of the array
    Sheet1.Range("A4:Z65550").Value = DataArray
        
        
    End Sub
    
    
    Private Sub ComboBox1_Change()
    
    
    End Sub
    
    
    Private Sub ComboBox2_Change()
    
    
    End Sub
    
    Private Sub ComboBox3_Change()
    
    
    End Sub
    
    
    
    
    Private Sub Order_Number_Box_Change()
    
    
    End Sub
    
    
    Private Sub Part_Number_Box_Change()
    
    
    End Sub
    Private Sub OK_Button_Click()
    
    
    Dim emptyRow As Long
    
    
    Set Rng = Range("A1")
    
    
    
    
    'Make Sheet1 active
    Sheet1.Activate
    
    
    'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
    
    
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0;" & "Data Source=" & strDBpath
    
    
    rst.Open "FROM *[All Data Combined];", _
            cnn, adOpenStatic
            
            []
    
    
    For Each fld In rst.Fields
        Rng.Value = fld.Name
        Set Rng = Rng.Offset(0, 1)
    Next fld
    
    
    
    
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User form for query and data dump into excel (code included)

    Why wouldn't you just create a form based on your [All Data Combined] table or query? I don't see the gain here in coding recordsets at all. What's the purpose of this? What's in All Data Combined and what does the user need to get?
    Last edited by xenou; Jun 20th, 2017 at 09:17 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Sep 2015
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User form for query and data dump into excel (code included)

    If I'm not seeing an easier way to do this, it's because I'm inexperienced with vba and access. Please feel free to toss suggestions at me. My goal is to pull data from mdb files based on part number, part name and order number. I would like the user to have drop downs for those three fields allowing them to select what they want for each. I'm trying to use the form to filter the access data. It would be amazing if the list was updated every time an option is selected from the hierarchy of drop downs. Once a selection has been made in each drop down, I would like all of the fields from that query (not just the initial 3) to be brought into excel. Post processing of the data will take place in excel.

    I hope what I'm trying to accomplish is clear. Please let me know if you need more information.

    Thanks,
    Brandon
    Last edited by Brandon M; Jun 20th, 2017 at 11:48 AM.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User form for query and data dump into excel (code included)

    It would be amazing if the list was updated every time an option is selected from the hierarchy of drop downs.
    search for "msaccess cascading comboboxes" or "msaccess dependent comboboxes"

    the only example I have implemented is this one, but there are other good tutorials, probably with slightly different methods:
    Access Tips: Cascading Lists for Access Forms

    I would ditch the recordset code in Access forms. You really only need to set the record source property of the comboboxes. It's not something you need to use vba for. I personally would want to do it all in Excel, because it will probably feel clunky for users to open Access just to get to a form. But that's subjective. Are you looking for order(s) based on a part or part(s) based on an order? Do you have a part table? Do you have an order table? What is that [All Data Combined] table or query?
    Last edited by xenou; Jun 20th, 2017 at 12:35 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Sep 2015
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User form for query and data dump into excel (code included)

    The reason that the macro sends the user to browse for the file is because this tool will be used to retrieve similar information from multiple mdb files in multiple locations. The connection to the access file must be dynamic as to accommodate multiple file locations. The user will only connect to one database at a time, but the next time they'll be looking for a different group of parts which will be located in a different database and be saved as a different workbook.

    There is no part name part number or order table. The [All Data Combined] Table contains all of fields I would like to "paste" into my workbook, including fields I would like to use to filter the data. The hierarchy of the filtered fields would be: 1. Part Number, 2. Part Name, 3 Order Number.

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User form for query and data dump into excel (code included)

    Part number and part name should be dependent on each other (i.e., a part number has a name, right - only one name?). So if you have filtered for part number I don't see needing name information for any more filtering - that's the typical case with part numbers and names, anyway.

    So that just leaves part number and order number. But you still have to pick one as the primary right (first parts, then orders under that part, or the reverse orders then parts under that order)? Note that I'm still thinking in terms of the cascading/dependent selection. At the end, it's just filtered on all the fields, viewed in terms of SQL where conditions.
    Last edited by xenou; Jun 20th, 2017 at 03:54 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    Board Regular
    Join Date
    Sep 2015
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User form for query and data dump into excel (code included)

      
    In my case all three matter. The part number gets the user to the correct part, the part name gets the user to a subset of that part number (for example; left vs. right), the order number allows the user to see the batch number of the part. The user needs to be able to select a part number and then the subset of that part number and also filter by the batch.

    I'm working on understanding the cascading lists now.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com