You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s guest writer is Kerry Westphal—our resident macro expert.
A common feature in well-designed applications is giving users the ability to quickly focus on the data they care about. A search or filter form allows users to specify criteria to limit the records returned. This also enhances performance because the entire recordset is not brought in.
A typical filter form provides unbound text boxes, combo boxes, and other controls where users can build and refine requirements for records to meet in order to be displayed on the form. Allen Browne has a great VBA example here where he uses a filter form to search for clients by name, type, city, level and/or start and end dates. In Allen’s example, the form Filter property is set to a string that is built based on user criteria, and the FilterOn action is invoked.
To accomplish the same goals using macros that run on the Web, let’s walk through an example together. This demonstrates using the BrowseTo action to swap out the subform, TempVars are used to store form criteria and If statements decide which criteria is relevant.
For tracking issues that arise at work, I have a database where I filter issues by words in the summary, person it is assigned to, and the state of their resolution. When I first browse to the form, instead of showing data, the subform is populated with a form which displays a message that says “Select a filter using the Summary, Assigned To and Resolution fields to view the open issues.”
The On Click event of the search button calls the BrowseTo macro action, which replaces the Issues datasheet contained by the subform with only showing only data that meets the specified criteria.
In the example above, I have specified criteria to limit issues displayed to those with “macro” in the Summary that have been assigned to Kerry Westphal. When I click the search button my macro will run and the filtered records will show.
Before we consider all the criteria, let’s construct a simple macro that considers only the Summary criteria textbox (txtSummary). Once our macro is working, we will add in more logic.
The first thing we will do in the macro is set up the error handler using the OnError action to specify a Submacro named ErrorHandler to be called in case there is an error message to display. Next we will use SetTempVar to assign the value contained by the txtSummary control to a TempVar. If the txtSummary control contains a value, the Like operator is used with wildcards in the Where Condition argument of the BrowseTo action to show the proper records. Otherwise, the BrowseTo action will show all the records. More information about the BrowseTo action can be found here
You can grab the XML here (Note: to copy this XML to paste into the macro designer, use Page->View Source and copy the XML from there).
Now that our basic macro is tested (I did that for us and it works J), we will add another condition to see only the issues assigned to the person selected in the dropdown. We can use the same logic to filter on the Assigned To field that we used for filtering on the Summary field.
In our assignments section at the top of the macro, we will add another SetTempVar action to store the AssignedTo criteria on the form.
Get the XML here.
We can do a similar check for Resolution criteria. This can continue for as many fields as we want to validate in our filter form to show as many or few records as needed. The macro is below and you can grab the XML for the entire macro here.
What macros have you been making?
Comments: (5) Collapse
Two questions: 1) What is the significance of the ">" keyword? Main.NavigationSubform>Issues.DS It seems to be required to step into a form contained in a subform container? Why not the usual bang operator? 2) Why isn't the WhereCondition a string? This seems to be at odds at what usually has been done for all other WhereConditions. One benefit of treating it as a string was that we could do something like this: ("[Summary] Like ""*" + [TempVars]![Summary] + "*"" And ") & ("[AssignedTo] Like ""*" + [TempVars]![AssignedTo] + "*"" And ") & ("[Resolution] Like ""*" + [TempVars]![Resolution] + "*"") Thus enabling us to do everything in a single macro action without all the If/Then. A quick test in the macro designer indicates to me that the above wouldn't be a valid syntax, though it's possible I may need to jiggle it a bit, maybe? Thanks for the info!
As WhereCondition is not a string expression, if you have many fields to filter, it may be impractical to build such a long "If" block. As an alternative approach, you can create a Yes/No field in the table and run as many "For Each Record" blocks as necessary (one block per filter field) to set the boolean field = true. Then, you can filter the table by the Yes/No field.
The > is used to separate the different form and subform pairs in the BrowseTo path argument. Since the form I am displaying with the BrowseTo macro action is nested inside a subform's subform, I need to have 2 form and subform pairs. The post below has more details on the syntax. blogs.msdn.com/.../access-2010-browseto-docmd-and-macro-action.aspx
@Banana
>One benefit of treating it as a string was that we could do something like this: It would really nice if the where could be a string, but the problem part due to the is running client side in a browser and then the WHERE clause has to run server side. I still think a "double" evaluation woudld be great (one client side and hten one server side). However, the functon set you get in the editor is VERY MUCH differnt for the where clause, as the macro editor dynamic changes the functions we have (you get LOTS of functions in the where clause becuase you get server side functions, not UI browser side functons). It is VERY cool that the editor figures this stuff out. Note that you can do a dymamic filter with out having to code each possbile filter This works: value. Eg: SetTempVar (s1,[Summary] & "*") SetTempVar (s2,[AssignedTo] & "*") SetTempVar (s3,[Resolution] & "*") SetFilter ( ([Summary] like [TempVars]!s1) and ([AssignedTo] like [TempVars]!s2) and ([Resolution] like [TempVars]!s3) ) In the above I assume assignedTo is a string. If that combo box was a ID, then I would remove the Assigned to from the above and then just go: If isnull(cboAssingedTo) then SetFilter as above Else setFilter as above + filter for Assigned to
end if @Luiz
>it may be impractical to build such a long "If" block. See above, as it handles the combinations quite well without needed a lot of code and without having to seperate code each case Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Luiz Cláudio said: "As an alternative approach, you can create a Yes/No field in the table and run as many "For Each Record" blocks as necessary (one block per filter field) to set the boolean field = true. Then, you can filter the table by the Yes/No field. " Since these are macros that run on the Web I don't believe this will work if more then one person will be using the application simultaneously. The tables in such a case reside on the Server. If two or more people run the procedure simultaneously the same table with your Yes/No field will be updated by both and will become ineffective for filtering each user's application separately. Gilad
Comments: (loading) Collapse