VBA to Turn Filtering Off for Subform

[whdyck]

I'm using MS Access 2003.

I have a form containing a subform control sub1 whose Source Object is frm1. I also have (on the same form) another subform control sub2 whose Source Object is frm2. Both subforms display in Continuous view.

Subform sub2 offers the user the ability to filter its list based on the record clicked in sub1. But when the user clicks on a different record in sub1, I want filtering removed from sub2.

So I'm trying to figure out the syntax to turn filtering off for frm2 from within the OnCurrent event of frm1.

Can anyone help with this?

Thanks for any help you can give.

Wayne

 

[boblarson]

With Me.Parent.Sub2subformControlNameHere.Form
   .Filter = vbNullString
   .FilterOn = False
End With

 

[whdyck]

With Me.Parent.Sub2subformControlNameHere.Form
   .Filter = vbNullString
   .FilterOn = False
End With

I get the following error when I try your code:

Run-time error '2455':
You entered an expression that has an invalid reference to the property Form/Report.

Any ideas?

 

[boblarson]

I get the following error when I try your code:



Any ideas?

Remember - you have to use the subform control name(name of the control that houses the subform) and not the name of the subform itself unless they are exactly the same. Based on the error message I suspect that they are not the same.

 

[whdyck]

Remember - you have to use the subform control name(name of the control that houses the subform) and not the name of the subform itself unless they are exactly the same. Based on the error message I suspect that they are not the same.

The subform control is named
subCsrArchiveDetailFilter

Its Source Object is named:
frmCsrArchiveDetailFilter

I'm using the code:

With Me.Parent.subCsrArchiveDetailFilter.Form
   .Filter = vbNullString
   .FilterOn = False
End With

But I still get the error.

 

[boblarson]

Is this code in the subform1's event or the main form?

 

[whdyck]

Is this code in the subform1's event or the main form?

It's in the OnCurrent event of subform1 (or more precisely, in the OnCurrent event of the Source Object housed in the subform1 control).

 

[boblarson]

Is it in a Navigation tab control?

 

[whdyck]

Is it in a Navigation tab control?

Yes, the subform control that I want to filter is on a tab control. The OnCurrent event in which I'm putting the code to turn filtering off is on a subform that is not on a tab control (i.e., sitting right on the main form).

But I thought that placing a subform on a tab control was equivalent to having it on the main form, as far as referencing was concerned.

 

[boblarson]

But I thought that placing a subform on a tab control was equivalent to having it on the main form, as far as referencing was concerned.

It is when using a simple tab control. But it is NOT when using one of the newer NAVIGATION tabs in Access 2010. So, which one are you using?

 

[whdyck]

I'm using Access 2003.

 

[whdyck]

Here's a bit more info.

If I add the following command to the Form_Current event, I get error 2455:

Forms!frmTugErrorCorrection.subCsrArchiveDetailFilter.Form.FilterOn = False

But if I remove the command from Form_Current, then open the main form in View mode and go to the Immediate window (Ctrl+G), I am able to run this command no problem.

So it seems like Form_Current is too early to run this command??

Wayne