Print the form's list of records

[debsamguru]

Hi,

I have a form which lists orders. I have a Search button which filters the orders in the list. How can I then print these filtered lines? Do I have to create a report and pass the filter as typed in by the user to it? Is there an easier way to do this?

Thanks

Debra

 

[Uncle Gizmo]

In this YouTube video I demonstrate how to pass a filter into a Report from a Form no code required in the Report:-

I have a blog on filtering which may be of interest:-

Easily Apply Filters - Nifty Access

Filtering - MS Access Forms This video should be of interest to anyone who wants to easily apply filters to an MS Access form in datasheet view or continuous form view. In this demonstration I show how you can apply filters in a Form presented in Continuous view and also in Datasheet view. When you

www.niftyaccess.com

 

[Gasman]

Hi,

I have a form which lists orders. I have a Search button which filters the orders in the list. How can I then print these filtered lines? Do I have to create a report and pass the filter as typed in by the user to it? Is there an easier way to do this?

Thanks

Debra

That is the way I would do it.

 

[theDBguy]

That is the way I would do it.

Me too.

 

[bastanu]

You could also try to pass the form's (filtered) recordset as the report's recordset:

Reports: Use a recordset as report's datasource

Cheers,

 

[Pat Hartman]

Depends on how pretty you want it to be. I always create a report. It would actually have taken you less time to do that using the wizard than it has to post this question and analyze the suggestions. Forms are not optimized for printing and they don't paginate the way reports do so they are unsatisfactory for printing list type forms.

 

[debsamguru]

So, my search field is fairly broad as follows:

Code Tags Added by UG

Please use Code Tags when posting VBA Code

Please read this for further information:-

Please use Code Tags when posting VBA Code

To make your code easier to read, please use the Code tag around your code, this will ensure that any code you copy and paste from your DB retains it's formatting making it easier to read and follow; Firstly click on the Code button, that's the button at the top of the posting window with the...

www.access-programmers.co.uk

Please feel free to Remove this Comment

Private Sub Search_Click()

    Dim strFilter As String
    Me.SearchField.SetFocus
    Me.FilterOn = False
    strFilter = "CustomerPONumber Like ""*" & SearchField & "*"" OR TirritPONumber Like ""*" & SearchField & "*"" "

    If strFilter = "" Then Exit Sub

    Me.Filter = strFilter
    Me.FilterOn = True
    Me.Requery

End Sub

I also want the ability to export the form's dataset. I can't work out how to pass this across to the report via the query or to the OutputTo statement.

 

[Pat Hartman]

The openReport method has both a filter and a where clause option. One of them should work for you.

 

[debsamguru]

OK, I've got the report sussed by setting If statements at the correct place:

Private Sub ReportType_Click()
    Dim OrderID As Integer
    OrderID = Me.ID
    If IsNull(ReportType) Then Exit Sub
    
    If ReportType = "OrdersR" Then
        If IsNull(Me.SearchField) Then
            DoCmd.OpenReport ReportType, acViewPreview
        Else
            strFilter = "CustomerPONumber Like ""*" & SearchField & "*"" OR TirritPONumber Like ""*" & SearchField & "*"" "
            DoCmd.OpenReport ReportType, acViewPreview, , strFilter
        End If
    Else
        DoCmd.OpenReport ReportType, acViewPreview
    End If
    Me.ReportType = ""
End Sub

Private Sub Search_Click()

    Dim strFilter As String
    Me.SearchField.SetFocus
    Me.FilterOn = False

    If Me.SearchField = "" Then
        MsgBox "No Search Field entered", , "Search"
        Exit Sub
    Else
        strFilter = "CustomerPONumber Like ""*" & SearchField & "*"" OR TirritPONumber Like ""*" & SearchField & "*"" "
        Me.Filter = strFilter
        Me.FilterOn = True
    End If
    Me.Requery

End Sub

But I also need to export the data. I've set up a query for the data but it doesn't filter on the SearchField because this is a concatenated field. How can I change the query using the concatenated search field from within the code - I don't want to have 2 queries, one without the search and one with.

 

[Pat Hartman]

I never use form filters because most of my apps use SQL Server or some other RDBMS as the BE. Therefore, I always want the server to do the filtering and return as few rows and columns as is possible. So, I use queries with arguments. If a form has one or two possible "filters", I have them in the form's header as combos or text boxes. Then the RecordSource query would be:

Select ...
From ...
Where SomeField = Forms!someform!somecombo OR SomeField LIKE "*" & Forms!someform!sometextbox

That way, I can use the exact same querydef as the RowSource for the report and as the query to export to Excel.