By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

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

P: 8
Hello there,

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

Expand|Select|Wrap|Line Numbers
  1. 'Purpose:   This module illustrates how to create a search form, _
  2.             where the user can enter as many or few criteria as they wish, _
  3.             and results are shown one per line.
  4. 'Note:      Only records matching ALL of the criteria are returned.
  5. 'Author:    Allen Browne (allen@allenbrowne.com), June 2006.
  6. Option Compare Database
  7. Option Explicit
  8.  
  9. Private Sub cmdFilter_Click()
  10.     Dim strWhere As String                  'The criteria string.
  11.     Dim lngLen As Long                      'Length of the criteria string to append to.
  12.  
  13.     '***********************************************************************
  14.     'Look at each search box, and build up the criteria string from the non-blank ones.
  15.     '***********************************************************************
  16.     'Text field example. Use quotes around the value in the string.
  17.     If Not IsNull(Me.filtermtr) Then
  18.         strWhere = strWhere & "([materialtrackingnumber] = """ & Me.filtermtr & """) AND "
  19.     End If
  20.  
  21.  
  22.     If Not IsNull(Me.filterdia) Then
  23.         strWhere = strWhere & "([diameter] = """ & Me.filterdia & """) AND "
  24.     End If
  25.  
  26.     If Not IsNull(Me.filtersch) Then
  27.         strWhere = strWhere & "([schedule] = """ & Me.filtersch & """) AND "
  28.     End If
  29.  
  30.     If Not IsNull(Me.filterjob) Then
  31.         strWhere = strWhere & "([jobnumber] = """ & Me.filterjob & """) AND "
  32.     End If
  33.  
  34.     If Not IsNull(Me.filterpo) Then
  35.         strWhere = strWhere & "([ponumber] = """ & Me.filterpo & """) AND "
  36.     End If
  37.  
  38.     If Not IsNull(Me.filterheat) Then
  39.         strWhere = strWhere & "([heatnumber] Like ""*" & Me.filterheat & "*"") AND "
  40.     End If
  41.  
  42.     If Not IsNull(Me.filterplate) Then
  43.         strWhere = strWhere & "([platenumber] Like ""*" & Me.filterplate & "*"") AND "
  44.     End If
  45.  
  46.  
  47.  
  48.     '***********************************************************************
  49.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  50.     '***********************************************************************
  51.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  52.     lngLen = Len(strWhere) - 5
  53.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  54.         MsgBox "No criteria", vbInformation, "Nothing to do."
  55.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  56.         strWhere = Left$(strWhere, lngLen)
  57.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  58.         'Debug.Print strWhere
  59.  
  60.         'Finally, apply the string as the form's Filter.
  61.  
  62.         Me.Filter = strWhere
  63.         Me.FilterOn = True
  64.     End If
  65. End Sub
  66.  
  67. Private Sub cmdReset_Click()
  68.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  69.     Dim ctl As Control
  70.  
  71.     'Clear all the controls in the Form Header section.
  72.     For Each ctl In Me.Section(acHeader).Controls
  73.         Select Case ctl.ControlType
  74.         Case acTextBox, acComboBox
  75.             ctl.Value = Null
  76.         Case acCheckBox
  77.             ctl.Value = False
  78.         End Select
  79.     Next
  80.  
  81.     'Remove the form's filter.
  82.     Me.FilterOn = False
  83. End Sub
  84.  
  85. Private Sub Command152_Click()
  86.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  87.     Dim ctl As Control
  88.  
  89.     'Clear all the controls in the Form Header section.
  90.     For Each ctl In Me.Section(acHeader).Controls
  91.         Select Case ctl.ControlType
  92.         Case acTextBox, acComboBox
  93.             ctl.Value = Null
  94.         Case acCheckBox
  95.             ctl.Value = False
  96.         End Select
  97.     Next
  98.  
  99.     'Remove the form's filter.
  100.     Me.FilterOn = False
  101. End Sub
  102.  
  103.  
  104. Private Sub datefilter_Click()
  105.  
  106. Me.Filter = "[Date_Entered] Between " & _
  107.  Format$(startdate, "\#mm\/dd\/yyyy\#") & _
  108.  " And  " & _
  109.  Format$(enddate, "\#mm\/dd\/yyyy\#")
  110. Me.FilterOn = True
  111.  
  112. End Sub
  113.  
  114.  
  115.  
  116.  
  117. Private Sub Form_BeforeInsert(Cancel As Integer)
  118.     'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
  119.     'We prevent new records by cancelling the form's BeforeInsert event instead.
  120.     'The problems are explained at http://allenbrowne.com/bug-06.html
  121.     Cancel = True
  122.     MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
  123. End Sub
  124.  
  125.  
  126.  
  127. Private Sub Form_Open(Cancel As Integer)
  128.     'Remove the single quote from these lines if you want to initially show no records.
  129.     Me.Filter = "(False)"
  130.     'Me.FilterOn = True
  131. End Sub
  132.  
  133.  
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.

So i tried this,


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOpenReport_Click()
  2.       DoCmd.OpenReport "materialreceiving_rpt", acViewReport, , Me.Filter
  3.  
  4.     End Sub
  5.  
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 that is being filtered on teh continuous form.

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


Expand|Select|Wrap|Line Numbers
  1. Private Sub datefilter_Click()
  2.  
  3. Me.Filter = "[Date_Entered] Between " & _
  4.  Format$(startdate, "\#mm\/dd\/yyyy\#") & _
  5.  " And  " & _
  6.  Format$(enddate, "\#mm\/dd\/yyyy\#")
  7. Me.FilterOn = True
  8.  
  9. 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?

Ian
Apr 12 '11 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,476
If you desire the filtering of the report to match that of the form then you will need to use the formm :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "materialreceiving_rpt", acViewReport, , Me.Filter
If that's not working correctly for you, we'd need to see what's in Me.Filter to be able to help you (as an alternative to spending a deal of time trying to reverse engineer your code and determine what is going on for you).
Apr 13 '11 #2

NeoPa
Expert Mod 15k+
P: 31,476
I'll stick my neck out here and say that almost certainly the record source for the form is different from that of the report. The former includes a field called [Date_Entered], whereas the latter doesn't. It may be a typo somewhere, but that's what I see as the probable issue. Obviously, still post the requested filter string if that doesn't solve your problem.
Apr 13 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.