Export Filtered Form Data to Excel (1 Viewer)

JHB

Have been here a while
Local time
Today, 12:28
Joined
Jun 17, 2012
Messages
7,732
I don't know why you say it doesn't work, (look at the attached picture), but maybe I misunderstand your requirement. Tell me where I'm wrong.
 
Last edited:

dilemma908

Registered User.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
18
Hi JHB -
Thanks for responding. If I select 'Intracompany' in the Drug Manufacturer field and 'Contract Manufacturer,Repackager/Relabeler' the results come back with any records that matches both criteria.

My question is it possible if you select any of the drop down listing fields that it will narrow down the search.

Please see attached image. I hope that makes sense.

One more request, is it possible that you can remove the images from your post on 6/7/2018.

Thank you for your assistance. I truly appreciate it.
 

Attachments

  • 2018-06-08_131231.jpg
    2018-06-08_131231.jpg
    99.2 KB · Views: 100
Last edited:

dilemma908

Registered User.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
18
Hi JHB -
One more thing, there are blank records in both the 'Drug Manufacturer' and 'Device Manufacturer' columns. When the blanks are selected, it is possible for the blank records to come back in the results. When I select the blank field in 'Drug Manufacturer' no records return in the results.

Thanks again.
 

Attachments

  • 2018-06-08_159303.jpg
    2018-06-08_159303.jpg
    86.3 KB · Views: 103

JHB

Have been here a while
Local time
Today, 12:28
Joined
Jun 17, 2012
Messages
7,732
Okay, then you've to surround each block of criteria with () and then have an AND between each block.
To get blanks in the result use IsNull function.
Database attached, so you can see how it is done.

Only a note, you have to declare the type of each variable else it will be a variant type:
You've:
Code:
Dim strWhere, SearchDateRange As String
Only the SearchDateRange will be a String variable.
Below both are String variable.
Code:
Dim strWhere As String, SearchDateRange As String
 
Last edited:

dilemma908

Registered User.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
18
Thanks so much, JHB. You're the man. That is awesome.

Can you remove the attachment?

I truly, truly appreciate all your time and efforts with my database.

:)
 

dilemma908

Registered User.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
18
Hi JHB,
I hope you are doing well. I am in need of your expertise again.

I have a 'HIDE/SHOW' button on my Form that allows the user to select the columns they want to see or hide. The 'Export Query' button allows the user to export the results of their search. If the user decides to show certain columns, is there a way that I can export only the columns selected in 'HIDE/SHOW' listing? I want to create a macro or button that lets them export the results of the columns they want to see.

Hopes this makes sense. Thanks for any assistance you can provide.
 

Attachments

  • Vendor_Listing_v5.zip
    255 KB · Views: 107

dilemma908

Registered User.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
18
Attached is a picture of the columns selected in 'HIDE/SHOW' button.
 

Attachments

  • 2018-06-16_231454.jpg
    2018-06-16_231454.jpg
    29.4 KB · Views: 100

JHB

Have been here a while
Local time
Today, 12:28
Joined
Jun 17, 2012
Messages
7,732
..I want to create a macro or button that lets them export the results of the columns they want to see.
The below code gives you a list (queryString) of columns shown in the form.
Code:
  Dim queryString As String
  Dim ctrl As Control
  Dim fld As DAO.Field
  Dim rst As DAO.Recordset
  
  Set rst = Me.RecordsetClone
  For Each fld In rst.Fields
    For Each ctrl In Me.Controls
      If ctrl.ControlType = 109 Then
        If fld.SourceField = ctrl.ControlSource Then
          If Not ctrl.ColumnHidden Then
            queryString = queryString & fld.SourceField & ","
          End If
        End If
      End If
    Next
  Next
  queryString = Left(queryString, Len(queryString) - 1)
 

dilemma908

Registered User.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
18
Thanks JHB. I think I may have stated what I was trying to do incorrectly.

I am trying to create a command button that will only export the results of the search and the columns selected or checked in the "HIDE/SHOW" fields.
 

dilemma908

Registered User.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
18
Not sure I am doing this right, JHB. Please see below. Thank you very much for your assistance.


Code:
Private Sub cmdQUERY2_Click()
Call Send2Excel(Forms!VendorListing, "VendorList")
End Sub

Public Function Send2Excel(VendorListing As Form, Optional VendorList As String)
' frm is the name of the form you want to send to Excel
' strSheetName is the name of the sheet you want to name it to
  
  Dim queryString As String
  Dim ctrl As Control
  Dim fld As DAO.Field
  Dim rst As DAO.Recordset
  
  Set rst = Me.RecordsetClone
  For Each fld In rst.Fields
    For Each ctrl In Me.Controls
      If ctrl.ControlType = 109 Then
        If fld.SourceField = ctrl.ControlSource Then
          If Not ctrl.ColumnHidden Then
            queryString = queryString & fld.SourceField & ","
  
   Set rst = VendorListing.RecordsetClone
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Add
    ApXL.Visible = True
         
    Set xlWSh = xlWBk.Worksheets("Sheet1")
    If Len(VendorList) > 0 Then
        xlWSh.Name = Left(VendorList, 34)
    End If
    xlWSh.Activate
    xlWSh.Range("A1").Select
    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
        Next
    rst.MoveFirst
    xlWSh.Range("A2").CopyFromRecordset rst
    xlWSh.Range("1:1").Select
    ' This is included to show some of what you can do about formatting.
    ' You can comment out or delete any of this below that you don't want to use in your own export.
    With ApXL.Selection.Font
        .Name = "Calibri"  'Font
        .Size = 11  'Font Size
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With
    ApXL.Selection.Font.Bold = True
    With ApXL.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select
    ' does the "autofit" for all columns
    ' ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
    ' selects the first cell to unselect all cells
    xlWSh.Range("A1").Select
  
    rst.Close
    Set rst = Nothing
    Exit Function
err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
  
  queryString = Left(queryString, Len(queryString) - 1)
End Function
 
Last edited:

JHB

Have been here a while
Local time
Today, 12:28
Joined
Jun 17, 2012
Messages
7,732
I've changed your code, try the database now.
 

Attachments

  • Vendor_Listing_v6.accdb
    696 KB · Views: 116

dilemma908

Registered User.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
18
Hey JHB, you're the man. Thanks!! Much appreciated.

Can I buy you a drink? Private msg me if there is a way. Thanks again!
 

JHB

Have been here a while
Local time
Today, 12:28
Joined
Jun 17, 2012
Messages
7,732
You're welcome, good luck with your project. :)
 

dilemma908

Registered User.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
18
hi JHB, I need your expertise once again. Is it possible to have one search field in the Form be able to lookup multiple columns. For example I want to lookup if Company A is present in Vendor, FKA and DBA fields.

Thanks for any assistance you can provide. Many thanks!
 
Last edited:

JHB

Have been here a while
Local time
Today, 12:28
Joined
Jun 17, 2012
Messages
7,732
Yes it is possible, but do you not think the users get confused when suddenly other vendors appear in the search result, I would, but you've to decide it!
I've put a check-box on the search form if you want to include the DBA and FKA in the search result.
Database attached.
 

Attachments

  • Vendor_Listing - Copy.zip
    101.4 KB · Views: 105

dilemma908

Registered User.
Local time
Today, 07:28
Joined
May 21, 2018
Messages
18
Hey JHB, Many thanks! You are correct. When I interviewed my customer that will be using the database, it was confusing. Thank you for your suggestion. It will give them the option to use it by click on the checkbox.
You're the man!
 

jamescullis

Registered User.
Local time
Today, 19:28
Joined
Jun 2, 2014
Messages
38
Hi All,

Taking this form to the next level, I'd like to have a combobox and checkbox in the search fields, i've got no issue at the moment in getting the Controls onto the form, its the VBA that isn't working

I've manged to get a checkbox partially working (i'm sure its not totally correct)
Code:
    ''First Yes/No field example. Use to turn on or off in the field.
    If Not IsNull(Me.chkIsManager) Then
        strWhere = strWhere & "([isManager] = True) And "
    End If
Can someone assist in getting a combobox working as a filter?


thanks
 

isladogs

MVP / VIP
Local time
Today, 11:28
Joined
Jan 14, 2017
Messages
18,186
Add the line Debug.Print strWhere
Run the code and look at the output in the immediate window.
You should then be able to work out what to alter
 

Users who are viewing this thread

Top Bottom