Filtering a Recordset with another on same page

How to filter one recordset with another on the same page? Here is a quick tutorial.

Why do this? It's handy when you have e.g. Products and Categories. Instead of needing 2 filter variables for each RS, you can control one with the other. So you only need 1 variable to control 2 recordsets :)

Do this:

1. Create the first recordset
2. Create the second recordset (the one you want to be filtered with a value from Rs1)
3. Open up your second Recordset definition window for the second Rs. (In simple mode you might already use the filter utility so that UD already generates some code for you)
4. Go to "Advanced Mode"
5. You 'll see something like:
   SELECT *
   FROM Category
   WHERE CategoryID = MMColParam
6. Change the line MMColParam Run-time Value to something like:
   FirstRecordSetNAme.Fieds.Item("FilterFieldNameOfRS1").value
   e.g. Products.Fields.Item("CategoryID").Value 
   This will filter the category RS with the field "CategoryID" from the Products Recordset in the example above. 
7. Click OK

NOTE: be sure to put the first RS before the second one, that you want to filter. This is because else the Filter Variable is not defined for your other RS yet. Logically...