Using a continuous form's filter to generate a report.

[Ian Anderson]

Hello there,

SO i have the followign VB code in my continuous form...

'Purpose:   This module illustrates how to create a search form, _
            where the user can enter as many or few criteria as they wish, _
            and results are shown one per line.
'Note:      Only records matching ALL of the criteria are returned.
'Author:    Allen Browne (allen@allenbrowne.com), June 2006.
Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
   
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.filtermtr) Then
        strWhere = strWhere & "([materialtrackingnumber] = """ & Me.filtermtr & """) AND "
    End If
    
   
    If Not IsNull(Me.filterdia) Then
        strWhere = strWhere & "([diameter] = """ & Me.filterdia & """) AND "
    End If
    
    If Not IsNull(Me.filtersch) Then
        strWhere = strWhere & "([schedule] = """ & Me.filtersch & """) AND "
    End If
    
    If Not IsNull(Me.filterjob) Then
        strWhere = strWhere & "([jobnumber] = """ & Me.filterjob & """) AND "
    End If
    
    If Not IsNull(Me.filterpo) Then
        strWhere = strWhere & "([ponumber] = """ & Me.filterpo & """) AND "
    End If
    
    If Not IsNull(Me.filterheat) Then
        strWhere = strWhere & "([heatnumber] Like ""*" & Me.filterheat & "*"") AND "
    End If
    
    If Not IsNull(Me.filterplate) Then
        strWhere = strWhere & "([platenumber] Like ""*" & Me.filterplate & "*"") AND "
    End If
        
    
    
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
End Sub

Private Sub Command152_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
End Sub


Private Sub datefilter_Click()

Me.Filter = "[Date_Entered] Between " & _
 Format$(startdate, "\#mm\/dd\/yyyy\#") & _
 " And  " & _
 Format$(enddate, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True

End Sub




Private Sub Form_BeforeInsert(Cancel As Integer)
    'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
    'We prevent new records by cancelling the form's BeforeInsert event instead.
    'The problems are explained at http://allenbrowne.com/bug-06.html
    Cancel = True
    MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub



Private Sub Form_Open(Cancel As Integer)
    'Remove the single quote from these lines if you want to initially show no records.
    Me.Filter = "(False)"
    'Me.FilterOn = True
End Sub

I am trying desperately to achieve one thing. Print it. The Form has combo boxes up top that filter the continuous form rather nicely, and I also got a date between two text boxes working, however, I can't get it to print to my report properly using the filters.

I have set a button on the form, that on click does the following.

DoCmd.OpenReport "materialreceiving_rpt"



but this doesn't seem to grab the filter's as they exist on the form. Instead it grabs all recordsets.

If someone would be so kind to help me use the DoCmd command to grab not only the strWhere clauses that are chosen by the user, but also the Me.filter (Private Sub datefilter_Click()) that the date fields use, and then produce the report that way i would be greatly in your debt. For reference not all options to search the continuous form are chosen at any given time, so it needs to be dynamic, if thats possible.

Ian

[Ian Anderson]

I have figured this out to work with all my combo boxes, and filters.




Private Sub cmdOpenReport_Click()
DoCmd.OpenReport "materialreceiving_rpt", acViewReport, , Me.Filter

End Sub


Works great, however,

When i use the date fields on the form, i get a query popup box asking me to input the "Date_entered" which is in fact a column in the database.

Near as i can tell, the above code doesn't work with the


Private Sub datefilter_Click()

Me.Filter = "[Date_Entered] Between " & _
Format$(startdate, "\#mm\/dd\/yyyy\#") & _
" And " & _
Format$(enddate, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True

End Sub



portion of my code, and i can't understand why. Can anyone help me understand why it wont accept the filter that the dates apply to the form, and all the others?

[orange]

There are some examples of DoCmd.OPenreport with various criteria here
http://www.blueclaw-db.com/docmd_openreport.htm

Have you tried using the Me.Filter on the DoCmd line such as (untested)

DoCmd.OpenReport "materialreceiving_rpt",,Me.Filter
Edit: Have just noticed you have tried this

Can you attach a non confidential copy of your database in mdb format?
I don't have Acc2010 or 2007 so can not open an accdb.