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

[Brandon M]

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.

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

 

[xenou]

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?

 

[Brandon M]

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

 

[xenou]

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?

 

[Brandon M]

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.

 

[xenou]

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.

 

[Brandon M]

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.