?
Solved

MS Access 2010 upgrade issue Me.FilterOn=True ignored

Posted on 2011-10-25
9
Medium Priority
?
883 Views
Last Modified: 2012-07-11
Hello,

 I have 2 pcs in a client's office. One has been upgraded to office 2010.  Thus MS Access has been upgraded from 2003 to 2010 on this machine.  The two pcs share the same database but have their own ADE file on their respective pc.    I have simple report that works in 2003 but does not filter in 2010.  The report is a simple transaction report with input boxes for a date range and an account number.  In 2010 the report ignores the account number filter which is an on_load event procedure.    I get transactions for all accounts for the date range rather then transactions for the account in the filter.  The report has no subreports.

I have attached my event procedures below.  I am guessing there is some syntax change needed for Access 2010.   I have tried a few different methods including defining the filter as a string first but that did not work either.  I would appreciate any suggestions.

Thank you,

Option Compare Database
Public dteStart As String, dteEnd As String

Private Sub Report_Activate()
If Len(dteStart) > 0 Then
    Me.DateRange = "Statement date:  " & Format(dteStart, "mm/dd/yyyy") & " - " & Format(dteEnd, "mm/dd/yyyy")
Else
    Me.DateRange = "Statement date:  All dates"
End If
End Sub
Private Sub Report_Load()
If Len(strAcct) > 0 Then
     Me.Filter = "AccountNumber = '" & strAcct & "'"
     Me.FilterOn = True
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim strWhere As String

dteStart = InputBox("Enter Start Date", "Date Range")
dteEnd = InputBox("Enter End Date", "Date Range")
strAcct = InputBox("Enter Account Number", "Account Filter")

DoCmd.RunSQL "DELETE FROM tbd_Lookup_Reports"


If Len(dteStart) > 0 And dteStart = dteEnd Then
    strWhere = "WHERE TransactionDate = '" & dteStart & "'"
ElseIf Len(dteEnd) > 0 Then
    strWhere = "WHERE (TransactionDate >= '" & dteStart & "' And TransactionDate <= '" & dteEnd & "')"
End If

DoCmd.RunSQL "Insert Into tbd_Lookup_Reports(LookupID) " & _
             "SELECT FinID FROM vwd_Copyright_Income_Statement " & strWhere & " " & _
             "GROUP BY FinID"
             
End Sub

Open in new window

0
Comment
Question by:Epistrophy
  • 4
  • 3
  • 2
9 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37024754
The reason is that your critieria string (strAcct) is not defined at the form level, only at the module level.

Every form and report module should start out with two lines.

Option Compare Database
Option Explicit

The "Option Explicit" statment will cause your code to fail if you use a variable that is not defined.  Which is the case in your code.

0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37024789
I'm not sure where to find it in 2010, but in 2007, if you open the VBE window, then select Tools - Options, you will get the Options dialog (see image).  In that dialog, check the "Require Variable Declaration" checkbox and Access will do this for all new forms and reports.  But you will need to go back and add that line to all of your other code modules. options dialog
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 37025110
Definitely make the VBE changes fyed suggests ...

Can you not do all of this in the Open event? In fact, can you not just modify the INSERT statement to account for the AcctNumber filter as well? This would seem to be the best way to manage things.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 49

Accepted Solution

by:
Dale Fye earned 1000 total points
ID: 37025282
I would concur with Scott about the placement of your code.  I was trying to figure you why you were using the Activate event.

The other thing you need to realize is that with 2007 and 2010, reports have a "FilterOnLoad" property, that you need to set to "No" in design view.  Setting this to "Yes" will have undesired consequences.

I would also advise you to use a form for your data input, rather than input boxes.  When you use a form for your criteria, it is easier to ensure that the input meets your requirements (dates, valid account ID, ...).

I've even got several reports that use the Report_Open event to open a parameter entry form, which allows the user to input the necessary parameters and then continue or cancel.  This looks something like:

Private Sub Report_Open (Cancel as integer)

     dim dtStartDate as date, dtEndDate as EndDate, strAcct as string

     docmd.OpenForm "frm_report_parameters",,,,,acDialog

     if forms!frm_report_parameters.tag = "Cancel" then
         Cancel = true
     else
         Cancel = false
         dtStartDate = forms!frm_report_parameters.txt_StartDate
         dtEndDate = Forms!frm_report_parameters.txt_EndDate
         strAccount = forms!frm_report_parameters.cbo_Account
     
         'do something else here.
     end if

     docmd.close acform, "frm_report_parameters"

End Sub

The key to this technique is to open the form using the acDialog window mode.  This will prevent other code in the Form_Open event from firing until frm_report_parameters is closed or hidden.  On frm_report_parameters, I include continue and cancel buttons, in the click event of each of these, I set the Tag property of the form to either "Close" or "Cancel", then instead of closing the form, I hide it (me.visible = false).  This allows the code in the Report_Open event to continue running, to determine whether the user clicked cancel, or to fill get the appropriate values for the other code you will use in the report.  The last thing you have to do is make sure that you close the parameter entry form.
     
0
 

Author Comment

by:Epistrophy
ID: 37029286
Thank  you for the suggestions.

I found the Tools/Options Dialogue box and placed a check next to Require Variable Declaration.
I added the Option Explicit statement which forced me to define "strAcct as a string" which is good.
The FilterOnLoad proeprty was already set to "no"

I run the report and the account number filter works with one problem.  I get prompted twice for the text input boxes and have to enter the same dates and account number again before the report presents the data.   The data is correct.   I am not sure what exactly is forcing the textboxes to present to me twice.

For the record I tried to combine all events into the "On Open event procedure but I got errors.  For the Me.DateRange I get the error  "You can't assign a value to this object."  This may be why the original coder has this in On Activate.

Dale, I like your suggestion to use forms for input going forward but I probably can't do that for this particular client. I need to get the input boxes working for various reasons.


I included my updated Event Procedure code below. This code works but prompts me twice for my text  boxes.

Thank you,
Option Compare Database
Option Explicit

Public dteStart As String, dteEnd As String, strAcct As String
Private Sub Report_Activate()
If Len(dteStart) > 0 Then
    Me.DateRange = "Statement date:  " & Format(dteStart, "mm/dd/yyyy") & " - " & Format(dteEnd, "mm/dd/yyyy")
Else
    Me.DateRange = "Statement date:  All dates"
End If
End Sub
Private Sub Report_Load()
Dim strFilter As String
strFilter = "AccountNumber = '" & strAcct & "'"
If Len(strAcct) > 0 Then
     Me.Filter = strFilter
     Me.FilterOn = True
End If
End Sub
Private Sub Report_Open(Cancel As Integer)
Dim strWhere As String

dteStart = InputBox("Enter Start Date", "Date Range")
dteEnd = InputBox("Enter End Date", "Date Range")
strAcct = InputBox("Enter Account Number", "Account Filter")

DoCmd.RunSQL "DELETE FROM tbd_Lookup_Reports"


If Len(dteStart) > 0 And dteStart = dteEnd Then
    strWhere = "WHERE TransactionDate = '" & dteStart & "'"
ElseIf Len(dteEnd) > 0 Then
    strWhere = "WHERE (TransactionDate >= '" & dteStart & "' And TransactionDate <= '" & dteEnd & "')"
End If

DoCmd.RunSQL "Insert Into tbd_Lookup_Reports(LookupID) " & _
             "SELECT FinID FROM vwd_Copyright_Income_Statement " & strWhere & " " & _
             "GROUP BY FinID"
             
End Sub

Open in new window

0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 37030008
Did you try all the events in the Load event? Data events like this should take place in the Load event. The Open event fires before data is loaded to the report.

I can't see any reason to use the Acivate event, which fires every time the report receives focus, but if you're satisfied with it ....

Is your report based on a Query? IF so, are you certain that query does not prompt you for additional date entries?

Otherwise I don't see any reason why your input boxes would fire twice.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37030289
I agree with LSM.

You might also want to consider putting this all in the Click event of the button that is opening the report.  If you did that, it might look like:

Note that I have replace the delimiters surrounding your dates with # instead of '.

I've also included fnDateRange (in case you would like to use it) which accepts one or two dates and provides an output that looks like:

12 June 10
or
12-14 June 10
or
12 June - 4 July 10
or
12 Dec 10 - 5 Jan 11

Private Sub cmd_Print_Click

    Dim dteStart As String, dteEnd As String, strAcct As String
    Dim strWhere As String

    dteStart = InputBox("Enter Start Date", "Date Range")
    dteEnd = InputBox("Enter End Date", "Date Range")
    strAcct = InputBox("Enter Account Number", "Account Filter")

    DoCmd.RunSQL "DELETE FROM tbd_Lookup_Reports"

    If Len(dteStart) > 0 And dteStart = dteEnd Then
        strWhere = "WHERE TransactionDate = #" & dteStart & "#"
    ElseIf Len(dteEnd) > 0 Then
        strWhere = "WHERE (TransactionDate >= #" & dteStart & "# And  TransactionDate <= #" & dteEnd & "#)"
    End If

    DoCmd.RunSQL "Insert Into tbd_Lookup_Reports(LookupID) " & _
                 "SELECT FinID FROM vwd_Copyright_Income_Statement " & strWhere & " " & _
                 "GROUP BY FinID"

    If Len(strAcct) > 0 Then strFilter = "AccountNumber = '" & strAcct & "'"
    docmd.openreport "yourReportName", acViewPreview,,strFilter

    If Len(dteStart) > 0 Then Reports(yourReportName).DateRange = fnDateRange(dteStart, dteEnd)         

End Sub 

Public Function fnDateRange(StartDate As Variant, Optional EndDate As Variant = NULL) As String

    'Make sure the date values are valid
    If IsNull(EndDate) Then EndDate = StartDate
    If IsNullOrBlank(StartDate) And IsNullOrBlank(EndDate) Then
        fnDateRange = ""
        Exit Function
    ElseIf IsNull(StartDate) = False And IsNull(EndDate) Then
        EndDate = StartDate
    ElseIf IsNull(StartDate) And IsNull(EndDate) = False Then
        StartDate = EndDate
    End If
    
    If StartDate = EndDate Then
        fnDateRange = Format(StartDate, "d mmm yy")
    ElseIf (Month(StartDate) = Month(EndDate)) And _
       (Year(StartDate) = Year(EndDate)) Then
       fnDateRange = Day(StartDate) & "-" & Day(EndDate) & " " _
                   & Format(StartDate, "mmm yy")
    ElseIf (Month(StartDate) <> Month(EndDate)) And _
       (Year(StartDate) = Year(EndDate)) Then
       fnDateRange = Format(StartDate, "d mmm") & " - " _
                   & Format(EndDate, "d mmm yy")
    Else
       fnDateRange = Format(StartDate, "d mmm 'yy") & " - " _
                   & Format(EndDate, "d mmm yy")
        
    End If
                   
End Function

Open in new window

0
 

Author Comment

by:Epistrophy
ID: 37030348
LSM,

Thanks. That was the last step I needed.  I moved what I had in the on_open event to the on_load but the filter would not fire.  I then realized I had left the filter where it was. Once I moved the filter code to the end of the on_load, where it logically needs to be,  the filter worked and I no double prompts.  I am going to test it a bit more.

Thanks again.
0
 

Author Closing Comment

by:Epistrophy
ID: 37056705
Thanks for your help
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question