Filtering a form based on text in subform, OR max length of filter string = 255?

[kcropper]

Hi, I’ve been trolling these forums for a while now, and while I’ve found other related threads (e.g., http://bytes.com/topic/access/answer...using-code-sql), nothing has addressed my problem, so I’m going to have to post. :)

I’m using Access 2007. I have a form with a sub-form. I want to filter for text in the subform and only display records in the main form that have subforms matching the filter text. I have read Allen Brown’s example (ser-28.html) and others, and maybe my problem is how long the filter can be in VBA: is there a 255 character limit? Because I’m getting errors saying the filter is not valid that have obviously cut the filter at 255 characters. If that is true, any suggestions on working around that?

Otherwise, here is my set up:
Tables:
tblProject, with AutoNumber PID (project ID), and other fields
tblGoal, with linked PID, and Number GNum (goal number – non-unique), and GoalText
tblObjective, with linked PID and GNum, and Number ONum (objective number – non-unique), and ObjText
tblActivity, with linked PID, GNum, and ONum, and AutoNumber AID (Activity ID) and Memo ActivityDescription

A Project will have up to five Goals (1, 2, 3, 4, 5). Each Goal will have up to five Objectives (1, 2, 3, 4, 5). Each Objective will have up to five Activities (un-numbered). So project P could look like this:

G1O1
Activity: did somethingA
Activity: did somethingB
Activity: did somethingC
Activity: did somethingD

G1O2
Activity: did somethingA
Activity: did somethingB
Activity: did somethingC

Etc.

Form:
frmGoalsAndObjectives (set to default view = single):
Record Source is a SQL query on the above tables; this is a simplified rendering of it:

SELECT tblProject.field1, tblProject.field2, tblGoal.GNum, tblGoal.GoalText, tblObjective.ONum, tblObjective.ObjText
FROM (tblProject INNER JOIN tblGoal ON tblProject.PID = tblGoal.PID) INNER JOIN tblObjective ON (tblGoal.GNum = tblObjective.GNum) AND (tblGoal.PID = tblObjective.PID); 

(I know this works for everything else in the database: reports, forms, etc)

subForm (set to default view = continuous forms):
subfrmActivities (linked Master/Child via PID, GNum, ONum)
Record Source is:

SELECT tblActivity.ActivityDescription, tblActivity.PID, tblActivity.GNum, tblActivity.ONum
FROM tblActivity;

(I know this works everywhere else in my database, reports, etc)

The Form is set to display one Goal/Objective pair at a time (so with the above example there are 7 records in the form to click through; in reality, there are over 200 projects, which comes to about 1700 Goal/Obj pairs).

I have a text box and button (I’ve also tried it with a pop-up) on the frmGoalsAndObjectives to initiate a filter by calling a VBA sub. My goal is to only show the Goals and Objectives where the text matches in one (or more) of its ActivityDescriptions (e.g., “*ingD*” would mean only G1O1 would be in the filtered record list, after matching the fourth Activity – I’ll worry about only displaying the Activities that match later).

The following SQL worked in a test query (qryFilterActivities) to return the list of Goals and Objectives that contained Activities with the filter text. (It popped up “Text to Find in Activities?” to which I could enter “camp”; I verified by running a text filter on the Activity Description column in tblActivities that it returned the same list.)

 SELECT tblObjectives.PID, tblObjectives.GNum, tblObjectives.ONum
FROM tblObjectives INNER JOIN tblActivities
ON ((tblObjectives.PID = tblActivities.PID) AND (tblObjectives.GNum = tblActivities.GNum) AND (tblObjectives.ONum = tblActivities.ONum))
WHERE (tblActivities.[Activity Description] Like ("*" & [Text to Find in Activities?] & "*")); 

However, when in the VBA code for the filter button, I try

DoCmd.OpenForm “frmGoalsAndObjectives”, , “qryFilterActivities”

, it pops up a request for tblActivities.[Activity Description] in addition to the “Text to Find in Activities”.
When I try

DoCmd.ApplyFilter “qryFilterActivities”

I get the same thing.
When I set the text for the query into “strFilter” and try

Me.Filter = strFilter
Me.FilterOn = True

it fails on a syntax error (3075) in query expression because it cuts the string off at 255 characters (the example SQL above has shorter names than some of the real tables and fields).

So I think my questions are:
• Is there a limit to filter length at 255 characters?
• Which method (OpenForm, ApplyFilter, Me.Filter/FilterOn) should work, if I weren’t encountering the 255 limit?
• Is there a better way to do this? (I wasn’t sure if the other examples were the same or if the fact that they were fewer levels of tables made a difference.)

Sorry for the length of my post; after reading a lot of other posts, it was clear that the more detail and information early on, the easier it was to answer the questions in the specific case. Many thanks in advance.
kevin

[Stewart Ross]

Hi. I think you may be misunderstanding what filtering does. When using Me.Filter = filterstring the filterstring is a WHERE clause without the word WHERE. It does not have (and must not have) a SELECT statement, or any other SQL component other than the WHERE clause. This is not the same as using the Filter component of the DoCmd.OpenForm method, which does require a complete SQL statement, but I would not recommend you use this approach.

In filtering the form from a VBA statement you cannot use a parameter field in the WHERE clause to ask the user for text to find, nor is there any need to do so. Such an approach will fail, as the VBA code opening the form does not use the user interface to ask for parameters - it will simply expect a value and find it is not there, generating a 'missing parameter' error message. If you want to get the user to enter text to match then either use an unbound textbox on your form for this purpose, or pop up an inputbox to ask the user for the text.

If we assume that you use an unbound textbox on your form and you name it txtStringToFind, then the filter WHERE clause (declaring a variable strFilter of type String to hold the filter value) would be put together like this:

Dim strFilter as String
strFilter = "[Activity Description] Like *" & Me.txtStringToFind & "*"
Me.Filter = strFilter
Me.FilterOn = True

For this to work the field Activity Description must be an existing field in the underlying query or table on which you have based your form.

There is no 255 character limit that I know of applied to filter strings, though as you can see if there is no SELECT component you are unlikely to need the 255 anyway.

-Stewart

[kcropper]

Stewart -
Thanks. I was just in the process of finding much of that out through more extensive trial and error and help from a coworker; you're clarification helps keep it straight.

I also revisited Allen Browne's post on filters and subforms (ser-28.html) and realized I'd miss-read something before. I thought I needed to set the RecordSource in the Properties as well (which I couldn't really do in my situation), but I didn't. Once I set the RecordSource in the VBA, it correctly filtered the main and sub forms. From there, it has been a process of tweaking out the fields I need for the rest of the form by INNER JOIN-ing the multiple layers of tables, and now trying not to lose the filter applied when this form was called from the main menu.

I am pretty sure I'm almost there. Thanks to Stewart, Allen's website, and all who tried to understand my long post. Below is a simplified version of the code called when the button is pressed. It's what I've gotten so far (more tweaking to come, I'm sure); the strSQL string is rather lengthy.
kevin

strSQL = "SELECT Objs.*, <<and other necessary fields from tblGoals and tblProject>>" & _
    "FROM (tblActivities AS Acts INNER JOIN tblObjectives AS Objs " & _
    " ON (Acts.ONum = Objs.ONum) AND (Acts.GNum = Objs.GNum) " & _
    " AND (Acts.PID = Objs.PID)) INNER JOIN " & _
    "  (tblProject INNER JOIN tblGoal " & _
    "  ON tblProject.PID = tblGoal.PID) " & _
    "ON (Objs.GNum = tblGoal.GNum) AND (Objs.PID = tblGoal.PID) " & _
    "WHERE (" & _
    "((Acts.[Activity Description]) Like (""" & Me.ActivityDescriptionFilterText & """)) " & _
    "AND (tblProject.FY=" & Forms(strNmMainMenuScreen).Controls(ctrlNameFY).Value & ") " & _
    "AND (tblProject.Region=""" & Forms(strNmMainMenuScreen).Controls(ctrlNameRegion).Value & """) " & _
    "AND (tblProject.Country=""" & Forms(strNmMainMenuScreen).Controls(ctrlNameCountry).Value & """) " & _
    "AND (tblProject.Sector=""" & Forms(strNmMainMenuScreen).Controls(ctrlNameSector).Value & """) " & _
    "AND (tblProject.[Project Title]=""" & Forms(strNmMainMenuScreen).Controls(ctrlNameProjectTitle).Value & """) " & _
    ");"
 
Me.RecordSource = strSQL
 

[Stewart Ross]

Well done for getting this far, and for your well-formatted code.

The approach of building a new recordsource string in code is a good alternative to applying filters, although it does have the disadvantage of being inherently more complex (in that you are supplying the whole SQL string and not just a WHERE clause) . It is particularly appropriate where you may wish to use different SORT BY fields to change the display order of the records shown to users.

In your WHERE clause at present you don't have the wildcard character before and after the text you are testing with LIKE. You may have this in the textbox instead, but if you don't use the wildcard character ("*" in Access SQL, "%" in ANSI SQL) you will be seeking an exact match of the whole field with the text typed - no sub-string matches will be found.

Keep up the good work!

-Stewart

[kcropper]

Thanks Stewart.
Regarding the wildcard, in my situation, not having the "*" is intentional; I debated back and forth, and realized that users might want to have the capability of searching for the start of the field. Auto inserting '*' at the beginning and end prevents that. The users are accustomed to using Access' filter functionality, so having them put the wildcard in won't be a problem, and the label on the text box reminds them.
kevin