Print the form's list of records (1 Viewer)

debsamguru

Member
Local time
Today, 08:27
Joined
Oct 24, 2010
Messages
82
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

Nifty Access Guy
Staff member
Local time
Today, 08:27
Joined
Jul 9, 2003
Messages
16,245
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:-

 

Gasman

Enthusiastic Amateur
Local time
Today, 08:27
Joined
Sep 21, 2011
Messages
14,050
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:27
Joined
Feb 19, 2002
Messages
42,981
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

Member
Local time
Today, 08:27
Joined
Oct 24, 2010
Messages
82
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 feel free to Remove this Comment

Code:
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.
 
Last edited by a moderator:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:27
Joined
Feb 19, 2002
Messages
42,981
The openReport method has both a filter and a where clause option. One of them should work for you.
 

debsamguru

Member
Local time
Today, 08:27
Joined
Oct 24, 2010
Messages
82
OK, I've got the report sussed by setting If statements at the correct place:

Code:
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

Super Moderator
Staff member
Local time
Today, 04:27
Joined
Feb 19, 2002
Messages
42,981
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.
 

Users who are viewing this thread

Top Bottom