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

Brandon M

Board Regular
Joined
Sep 18, 2015
Messages
66
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top