Solved

How to - Reference a form control's text as the source to apply filter? Prevent truncating?

Posted on 2011-03-07
5
287 Views
Last Modified: 2012-05-11
Here we go. I don't really know how to put this. I'm an amateur so bare with me.

I have a complicated filter menu form built at the top of a query based form, that I've built for the user to do adhoc reporting off of a basic query.
-user selects and populates a number of boxes which will become the parameters.
-unbound box calculates the exact filter text dependent on which parameter boxes are populated by the user.
-user pushes a button which references this one master text box as the filter text and applies it to the active form as a filter.
- this can be a compound filter.

NOW. This works as long as I use an Access macro with ApplyFilter and the expression referencing =[Forms]![frmWeeklySummaryPrepare]![FilterText] AND as long as the user only selects a couple of parameter.
BUT if the user selects over 3 parameters the filter it applies is too long and gets truncated.

I'm trying to get the VBA docmd.apply filter to work to see if it will work any better than the Access macro but my code isn't working.This is the active part of my code which pertains to my current issue. What can I do to make this filter even function? Currently it does nothing.

    Dim stFilter As String
    stFilter = [Forms]![frmWeeklySummaryPrepare]![FilterText].Value
    DoCmd.ApplyFilter (stFilter)

Do you have any suggestions for applying the complicated filter and preventing it from truncating?
0
Comment
Question by:culpees
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35059777
Questions:

1.  After running the code, does the form's Filter property show the value of stFilter?

2.  If the answer above is true, have you done a manual Refresh to see if the filter works?  If so, perhaps adding Form.Refresh will complete the process.

J.

0
 

Author Comment

by:culpees
ID: 35060093
JAMcDo,

1. no. nothing. Filter is still blank. ugh
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35060583
This is the issue with these "Ad-Hoc" reporting systems.

You will constantly get requests to "refine" the parameters (AND/OR logic, Nesting, Wildcards, ...etc)

AFAIC, the only dependable way to do this is if you build the Recordsource for the report in code.
Declaring each "Criteria" as a string and concatenating it to the SQL Where clause.
(Perhaps pulling all the individual "Criterion" from a listbox)

However, you must be fairly well versed in VBA...
0
 

Author Comment

by:culpees
ID: 35060652
Thanks,
That point aside.

I at least need to get my docmd.apply filter to work.  

??
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 35061467
First, your filter should take the form of an "Expression", not just simply a value as you have it.
Second, Most developers us the Filter keyword, not Docmd.ApplyFilter.
(Not sure but perhaps ApplyFilter has a limit?)
Third, you never stated if this filter was for a string or a number.

For example, something like this for a number:
    Dim stFilter As String
    stFilter = "EmployeeID =" & [Forms]![frmEmployees]![FilterText].Value
    [Forms]![frmEmployees].Filter = stFilter
    [Forms]![frmEmployees].FilterOn = True

or something like this for a string:
    Dim stFilter As String
    stFilter = "LastName =" & "'" & [Forms]![frmEmployees]![FilterText].Value & "'"
    [Forms]![frmEmployees].Filter = stFilter
    [Forms]![frmEmployees].FilterOn = True


To turn off the filter use:
xxx.FliterOn=false

(Or set the filter to "")

JeffCoachman
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now