How to - Reference a form control's text as the source to apply filter? Prevent truncating?
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]![frmWeeklySummary
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]![frmWeeklySummaryP
DoCmd.ApplyFilter (stFilter)
Do you have any suggestions for applying the complicated filter and preventing it from truncating?
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]![frmWeeklySummary
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]
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...
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...
ASKER
Thanks,
That point aside.
I at least need to get my docmd.apply filter to work.
??
That point aside.
I at least need to get my docmd.apply filter to work.
??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.