General Networking/Lan/Wan/Report Filter

Advertisement


Question
QUESTION: Hi Scott,

I am currently creating a database that will allow the end user to choose/filter which items are to be placed on the report.

I currently have a main table with four lookup tables. The main table is linked to a query.

In the query, I have setup parameters, such as:
Between [Forms]![frmFilter]![txtStartDate] And [Forms]![frmFilter]![txtEndDate]

This query is linked to a form where the end user can choose the items, such as start and end date, staff member, or reason.

The coding I have for the form is:

Private Sub Cancel_Click()
   DoCmd.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
   If Not bInReportOpenEvent Then
      ' If we're not called from the report
     MsgBox "For use from the Student Tracking Report only", vbOKOnly
     Cancel = True
   End If
Form_Open_Exit:
    Exit Sub
End Sub

Private Sub OK_Click()
   Me.Visible = False
End Sub


The coding for the report is:

Private Sub Report_Close()
DoCmd.Close acForm, "frmFilter"
End Sub

Private Sub Report_Open(Cancel As Integer)
  bInReportOpenEvent = True
  
   DoCmd.OpenForm "frmFilter", , , , , acDialog

  
  If IsLoaded("frmFilter") = False Then Cancel = True

  
  bInReportOpenEvent = False
End Sub

My module coding is:

Option Compare Database

Option Explicit
Public bInReportOpenEvent As Boolean

Function IsLoaded(ByVal strFormName As String) As Boolean
 Dim oAccessObject As AccessObject
 Set oAccessObject = CurrentProject.AllForms(strFormName)
 
 If oAccessObject.IsLoaded Then
   If oAccessObject.CurrentView <> acCurViewDesign Then
     IsLoaded = True
   End If
 End If
End Function

Basically, I want the end user to be able to choose which filter, so either they can filter by start/end date, or by staff member, or by reason.  

Am I over complicating it?

Please advise
Thanks

ANSWER: No offense, but I think so. I would do it (and have) this way.

First I would set the default values of txtStartDate and txtEndDate to:

=DMin("[datefield]","queryname") and
=DMax("[datefield]","queryname")

This way, they show, by default the earliest and latest dates in the query so if the user doesn't want to filter by date, they will get all the dates and you handle the date criteria that way.

Next, I would put code in the button you use to launch the report like so:

Dim strFilter as String

strFilter = ""

If NOT IsNull(Me.cboStaff) Then
   strFilter = "[StaffID} = " & Me.cboStaff
End If

If Not IsNull(Me.cboReason) Then
   If strFilter = "" Then
       strFilter = "[Reason] = " & Me.cboReason
   Else
       strFilter = strFilter & " AND [Reason] = " & Me.cboReason
   End If
End If

DoCmd.OpenReport "reportname",acPreview,,strFilter

So the second part is now handled. If the user only chooses to filter by staff then strFilter is set for staff. If only a reason is selected, then the filter is only by reason, otherwise it's both. I'm assuming here that the user can choose Staff an Reason from combos that return a numeric ID. If not, I think you can figure out how to adjust the code accordingly.

This is the only code you need. All the rest of what you have (except maybe to check if the form is opened by accident) can be removed.

I think this is better because a) all the code is concentrated in one place for ease of debugging and b) its simpler and easier to follow.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi Scott

The coding you provided worked fantastically; however, I need to add another search criteria.

The coding I have, which was provided by you is:

Private Sub Command5_Click()
Dim strFilter As String

strFilter = ""

If Not IsNull(Me.cboStaff) Then
   strFilter = "[PCName] = " & Me.cboStaff
End If

If Not IsNull(Me.cboReasons) Then
   If strFilter = "" Then
       strFilter = "[Reason] = " & Me.cboReasons
   Else
       strFilter = strFilter & " AND [Reason] = " & Me.cboReasons
   End If
End If

DoCmd.OpenReport "StudentReport", acViewPreview, , strFilter
End Sub


NOW - I need to add another criteria to be able to search by Student Number.

I tried adding the following code in between, but I know I am missing something.   

If Not IsNull(Me.txtNumber) Then
   strFilter = "[StudentNumber] = " & Me.txtNumber
End If


I also would like to add a MSGBOX that would appear only if no data was generated from the search criteria chosen.

Are you able to assist?

Thanks
Kelly

ANSWER: Its not quite that simple. If you want to ADD Student Number to your filter. You have to do something like the second IF:

If Not IsNull(Me.txtNumber) Then
  If strFilter = "" Then
      strFilter = "[StudentNumber] = " & Me.txtNumber
  Else
      strFilter = strFilter & " AND [StudentNumber] = " & Me.txtNumber
  End If
End If

So, for each criteria you add, you have to test to see if a filter already exists. If it doesn't, you set the filter, if it does you append the new filter to the string.

As for if No data is returned, you can use the No Data event of the report to display a message instead of a blank report.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA



---------- FOLLOW-UP ----------

QUESTION: Hi Scott,

Thank you again for your assistance.  I attempted to input the coding; however, no data will generate, and it does not give me any errors.

I have tried coding two ways.  They are:

Version1:

Private Sub Command5_Click()
Dim strFilter As String

strFilter = ""


If Not IsNull(Me.cboStaff) Then
   strFilter = "[PCName] = " & Me.cboStaff
End If

If Not IsNull(Me.txtNumber) Then
   If strFilter = "" Then
       strFilter = "[StudentName] = " & Me.txtNumber
   Else
       strFilter = strFilter & " AND [StudentName] = " & Me.txtNumber
   End If
End If

If Not IsNull(Me.cboReasons) Then
   If strFilter = "" Then
       strFilter = "[Reason] = " & Me.cboReasons
   Else
       strFilter = strFilter & " AND [Reason] = " & Me.cboReasons
   End If
End If

DoCmd.OpenReport "StudentReport", acViewPreview, , strFilter
End Sub

Version 2:

Private Sub Command5_Click()
Dim strFilter As String

strFilter = ""


If Not IsNull(Me.txtNumber) Then
   strFilter = "[StudentNumber] = " & Me.txtNumber
End If

If Not IsNull(Me.cboStaff) Then
   If strFilter = "" Then
       strFilter = "[PCName] = " & Me.cboStaff
   Else
       strFilter = strFilter & " AND [PCName] = " & Me.cboStaff
   End If
End If

If Not IsNull(Me.cboReasons) Then
   If strFilter = "" Then
       strFilter = "[Reason] = " & Me.cboReasons
   Else
       strFilter = strFilter & " AND [Reason] = " & Me.cboReasons
   End If
End If

DoCmd.OpenReport "StudentReport", acViewPreview, , strFilter
End Sub

Both ways run the report, but no data if I enter in a student number.  However, it will generate data if I choose PCName and/or Reason.

Also, I have a start and end date, and if I only want to search by date and nothing else, do I need to add code for this as well?  Currently, I am using the Default Value of DMin and DMax; however, it will not filter any other dates.

Your help is always greatly appreciated.
Thanks
Kelly

Answer
You need to step through the code in Debug mode to see what strFilter is. You then need to check to make sure there are matching records. The Code looks OK so you need to make sure what the filter looks like.

As to the Date criteria, set that up in the query. Set the Criteria for your date column to:

BETWEEN Forms!formname!start AND Forms!formname!end

use the actual form and control names.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

General Networking/Lan/Wan

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

Most general questions on LANs, especially home networks. Some admin and design issue.

Experience

I've been an IT Professional for more then 16 years. I've accumulated a larege store of knowledge that allows me to answer a wide variety of IT related questions.

©2016 About.com. All rights reserved.