Filtering a Sub Form??? 1

[Moss100]

I am having problems trying to filter records on a sub form.

I have read the following at MS support. Is there really no way to filter the records on a subform???

====

This article was previously published under Q112796
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.

You cannot use the ApplyFilter action to apply a filter to a subform. If you try to do so, the filter is applied to the main form instead. In Microsoft Access version 1.x, you can achieve some of this functionality by basing the subform on a parameter query. In the other versions of Microsoft Access, you can get the same results by changing the subform's RecordSource property.
MORE INFORMATION
The ApplyFilter action dynamically restricts or sorts the records in a table or the records from a form or report's underlying table or query. Although you cannot use this action with a subform, you can change the subform's RecordSource property to achieve the same results.

The following example demonstrates how to change the sort order of a subform by changing the subform's RecordSource property. It uses the Categories form and Product List subform in the sample database Northwind.mdb (or the Categories form and Categories Subform subform in the sample database NWIND.MDB in 2.0).

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file or perform these steps on a copy of the Northwind database.

Open the sample database Northwind.mdb (NWIND.MDB in version 2.0) and create a new query based on the Products table. Include all the fields from the table in the query.
Set the UnitPrice field's Sort row to Ascending.

NOTE: In Microsoft Access 2.0 the UnitPrice field is called Unit Price.
Save the query as UnitPriceSort.
Open the Categories form in Design view, and then add a command button to the form.
Set the command button's OnClick property to the following event procedure.

In Microsoft Access 7.0 and 97: If Me![Product List].Form.RecordSource = "Products" Then
Me![Product List].Form.RecordSource = "UnitPriceSort"
Else
Me![Product List].Form.RecordSource = "Products"
End If

In Microsoft Access 2.0:If Me![Categories Subform].Form.RecordSource = "Products" Then
Me![Categories Subform].Form.RecordSource = "UnitPriceSort"
Else
Me![Categories Subform].Form.RecordSource = "Products"
End If

View the Categories form in Form view.
Click the command button you added in step 4. Note that as you continue to click the command button, the form's sort order is toggled between the Unit Price and the Product ID fields.
REFERENCES
For more information about the RecordSource property, search for "RecordSource Property" using the Microsoft Access 97 Help Index.

 

[willir]

Moss100

There are two ways to can handle this.

My preferred way is to use the RecordSource property of the form or subform.

Scenario...
You have a form / subform - say the main form is for a Supplier, and the subform is Products provided by the supplier and price.

So you hit a big vender such as IBM -- 10,000's of parts. Ouch, you want to do two things
- Search for a product group
- Search for a specific product number

You have two unbound combo boxes on the main form - one for Product groups, and the product number.

Code for the AfterUpdate event procedure for the Product Group combo box may look something like...

'Product group uses a GroupID, and is numeric
Dim strSQL as String

If Nz(Me.SelectGroup, 0) > 0 Then
   strSQL = "Select * from CustProducts Where CustomerID = " _
   & Me.customerID & " and ProductGroup = " & Me.SelectGroup
   Me.ProductSbFrm.Form.RecordSource = strSQL
   Me.ProductSbFrm.Requery
End If

For part number which in this example, is a text string...

'Product group uses a GroupID, and is numeric
Dim strSQL as String, strQ as String

strQ = Chr$(34)

If Nz(Me.SelectProductNumber, 0) > 0 Then
   strSQL = "Select * from CustProducts Where CustomerID = " _
   & Me.customerID & " and ProductNumber = " _
   & strQ & Me.SelectProductNumber & strQ
   Me.ProductSbFrm.Form.RecordSource = strSQL
   Me.ProductSbFrm.Requery
End If

The other approach is to use Filter / FilterOn and/or OrderBy / OrderByOn

me.ProductSbFrm.Form.Filter = "ProductGroup = " & Me.SelectGroup
me.ProductSbFrm.Form.FilterOn = True

Ditto for me.ProductSbFrm.Form.Orderby / ...OrderBy = True

Richard

 

[barit]

I too am trying to use a filter on a subform. On the subform I have an option group which is used to filter records alphabetically (since there are potentially hundreds of records.)

Through much trial and error I have gotten most of this to work (and who really knows if I am doing this right). I click on the appropriate letter and only those records with LastNames corresponding to the letter are shown. I do however have two problems.

I am trying to get the recordset to default to showing all records if a letter is selected and there are no records for that letter. Currently the message box comes up as designed, the unbound textbox "TxtlastNameFilter" is populated with "*" but not all records are displayed and the "ALL" button is not depressed.

Secondly, I am trying to get the subform recordset to default to showing all records when the form/subform are opened regardless of what the last selection in the option group was last time the form/subform were closed. Currently, if "ALL" was the last button used, then all records are displayed when the form is opened again. If lets say "M" was the last letter selected before the form closed, then only the "M" records show when the form is reopened.

The following is part of the code I am using in the AfterUpdate event of the option group.

Me.RecordSource = "Select * FROM Person WHERE LastName Like """ & txtLastNameFilter & "*"" ORDER BY Person.LastName, Person.FirstName;"
Me.Requery
            
If (RecordsetClone.RecordCount = 0) Then
Beep
MsgBox "There are no records for that letter.", vbInformation, "No Records Returned"
       DoCmd.ShowAllRecords
       Me.txtLastNameFilter = "*"
       Me.grpLastNameFilter = 27
       Me.grpLastNameFilter = "*"
       Me.Requery
        
    End If

This code works fine when I just am using it as a form and not a subform. I am unsure of what I am doing wrong.

This is driving me crazy and fixing this is quite important to several other aspects of the database.

I really could use your advice and suggestions. Thanks is advance for any suggestions andd help.

 

[Moss100]

I'e got a few feelers out on this one, so when I get any onfo, it'll be here.

Regards

Mark, and yhank you to willir

 

[willir]

Moss

What is the issue?

I have read the following at MS support. Is there really no way to filter the records on a subform???

I use these techniques for filtering subforms all the time. Both...
Me.YourSubForm.Form.RecordSource = strYourSQLStatement
Me.stItemConFrm.Requery
and
Me.YourSubForm.Form.Filter = "[FieldName] =" & Me.YourComboBox
Me.YourSubForm.Form.FilterOn = True

If you are having problems, post the specifics.

Richard

 

[JaredTaylor74]

this code was very helpful. thank you Richard.
but i would like to have the sub form go to the last record after it is filtered and requeried. ... can't seem to find the right code to do that.

i have tried placing "DoCmd.GoToRecord , , acLast" in the onload of the sub form as well as many other places, but can't seem to find the place where it works.

thanks again for your help.

Jared

 

[willir]

Jared

If you are using the SQL method, you can use the Order By DESC clause in your Select statement.

Or you can use the OrderBy property of the form which must the same as the FilterOn property...
[tt]
me.ProductSbFrm.Form.OrderBy = "YourDateField"
me.ProductSbFrm.Form.OrderByOn = True[/tt]

Access help can help you with syntax if you still have probelms.

Richard

 

[Betsvigi9]

I have a form which has several subforms contained in a tab control. I am trying to filter the data in one of the subforms and whilst I can get the method described above to work when the form has a single subform, it doesn't work when the subform is part of a tab control.

I get the error message 2465 - Microsoft Access can't find the field "frmAbsenceCleaningSubForm". I assume this is because the referencing is incorrect. Does anyone know how to reference the sub form when it is part of a tab control?

 

[JaredTaylor74]

Hi Richard,

Yes, I am using the SQL method, I thought about using the order by statement, but for my form, it just looks better for the dates to be sorted ascending ... is there certain code in the
"me.ProductSbFrm.Form." that i can use to have it select the last record?
I've tried everything in the sub form actions i can think of...

Thanks for your help
Jared

 

[PHV]

Provided your subform has focus you may consider something like this:
DoCmd.GoToRecord , , acLast
Otherwise you may take a look at the RecordsetClone, MoveLast and Bookmark methods/properties of the Form/DAO.Recordset objects.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244