Filter SubForm using button from Main Form

[Guest]

I am having difficulty finding the best method to do this so any help would
be appreciated.

Main form name is FrmReviewAccuracy312
SubForm name is FrmNCLocAccuracy312 or Child0 in the main form.
SubForm gets its data from a query named QryNCLocAccuracy312

What I would like is to have a command button on the main form that when
clicked filters a field on the subform to exclude all null values. The
specific field in the query is called ExternalEvents.

Please steer me in the right direction, thanks in advance!

 

[Allen Browne]

This should do it:

With Me.Child0.Form
.Filter = "ExternalEvents Is Not Null"
.FilterOn = True
End With

If there is any chance that the main form could also have a filter, see:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html
The next version of Access (2007) will have this bug fixed.

 

[Guest]

Thanks for your help Allen and to answer your question their is no filter on
the main page.

 

[Guest]

One more question. Is it possible to apply that filter to two subforms at the
same time? Child0 and Child2 both have the same field names and search
criteria.

 

[Allen Browne]

Sure. Exactly the same again for Child2.

 

[Guest]

I promise as soon as possible I am going to start taking programming classes.
Any suggestions would be appreciated as well.

That said is this how it should look to filter both subforms simultaneously?

Private Sub Command15_Click()
With Me.Child0.Form
.Filter = "SumOfExternalEvents IS NOT NULL"
.FilterOn = True
End With
With Me.Child2.Form
.Filter = "SumOfExternalEvents IS NOT NULL"
.FilterOn = True
End With
End Sub

 

[Douglas J Steele]

Yes, that should work.

 

[Guest]

That does work great except when I try to close the form I get a pop-up.
The pop-up says:
This action will reset the current code in break mode.
Do you want to stop running the code?

No matter what I select I eventually have to hit [ctrl][shift][esc] and
force the application to close.

 

[Douglas J Steele]

Do you have the same problem when you've only set one filter? Does it matter
which of the two subforms?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That does work great except when I try to close the form I get a pop-up.
The pop-up says:
This action will reset the current code in break mode.
Do you want to stop running the code?

No matter what I select I eventually have to hit [ctrl][shift][esc] and
force the application to close.

Yes, that should work.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


subforms
at filter,
see: values.
The

 

[Guest]

I can filter either subform separately without incident but when I filter
them both together I get the before mentioned pop-up.

Do you have the same problem when you've only set one filter? Does it matter
which of the two subforms?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That does work great except when I try to close the form I get a pop-up.
The pop-up says:
This action will reset the current code in break mode.
Do you want to stop running the code?

No matter what I select I eventually have to hit [ctrl][shift][esc] and
force the application to close.

Yes, that should work.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I promise as soon as possible I am going to start taking programming
classes.
Any suggestions would be appreciated as well.

That said is this how it should look to filter both subforms
simultaneously?

Private Sub Command15_Click()
With Me.Child0.Form
.Filter = "SumOfExternalEvents IS NOT NULL"
.FilterOn = True
End With
With Me.Child2.Form
.Filter = "SumOfExternalEvents IS NOT NULL"
.FilterOn = True
End With
End Sub


:

Sure. Exactly the same again for Child2.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

One more question. Is it possible to apply that filter to two subforms
at
the
same time? Child0 and Child2 both have the same field names and search
criteria.

:

This should do it:

With Me.Child0.Form
.Filter = "ExternalEvents Is Not Null"
.FilterOn = True
End With

If there is any chance that the main form could also have a filter,
see:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html
The next version of Access (2007) will have this bug fixed.

I am having difficulty finding the best method to do this so any
help
would
be appreciated.

Main form name is FrmReviewAccuracy312
SubForm name is FrmNCLocAccuracy312 or Child0 in the main form.
SubForm gets its data from a query named QryNCLocAccuracy312

What I would like is to have a command button on the main form that
when
clicked filters a field on the subform to exclude all null values.
The
specific field in the query is called ExternalEvents.

 

[Allen Browne]

Possible problems:

1. You have made design changes to the subform since opening it.
This could be intentional, or programmatic. If you made any changes, close
it, and reopen it before trying to apply this filter.

2. The subform is dirty with a record that cannot be saved.
Typically this happens if you dirty it programmatically. The worst case is
code in the form's Current event that dirties it as soon as it arrives at a
new record, or in its AfterUpdate that dirties it again the instant it
saves.

3. The database is corrupting.
3.1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

3.2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3.3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

3.4. Open Access, and compact again.

4. You have multiple copies of the same data open for editing at once.
This is particularly a problem with memo fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I can filter either subform separately without incident but when I filter
them both together I get the before mentioned pop-up.

Do you have the same problem when you've only set one filter? Does it
matter
which of the two subforms?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That does work great except when I try to close the form I get a
pop-up.
The pop-up says:
This action will reset the current code in break mode.
Do you want to stop running the code?

No matter what I select I eventually have to hit [ctrl][shift][esc] and
force the application to close.

:

Yes, that should work.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I promise as soon as possible I am going to start taking
programming
classes.
Any suggestions would be appreciated as well.

That said is this how it should look to filter both subforms
simultaneously?

Private Sub Command15_Click()
With Me.Child0.Form
.Filter = "SumOfExternalEvents IS NOT NULL"
.FilterOn = True
End With
With Me.Child2.Form
.Filter = "SumOfExternalEvents IS NOT NULL"
.FilterOn = True
End With
End Sub


:

Sure. Exactly the same again for Child2.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
One more question. Is it possible to apply that filter to two subforms
at
the
same time? Child0 and Child2 both have the same field names and search
criteria.

:

This should do it:

With Me.Child0.Form
.Filter = "ExternalEvents Is Not Null"
.FilterOn = True
End With

If there is any chance that the main form could also have a filter,
see:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html
The next version of Access (2007) will have this bug fixed.

I am having difficulty finding the best method to do this so
any
help
would
be appreciated.

Main form name is FrmReviewAccuracy312
SubForm name is FrmNCLocAccuracy312 or Child0 in the main
form.
SubForm gets its data from a query named QryNCLocAccuracy312

What I would like is to have a command button on the main
form that
when
clicked filters a field on the subform to exclude all null values.
The
specific field in the query is called ExternalEvents.

 

[Guest]

I did all you suggested and it still gives me the pop-up. I should have
mentioned earlier that this is a data access form only, there are no changes
made from this form. Also both sub-forms, while using different queries, both
access the same table. And finally when I filter each sub-form at the same
time using different command buttons, I get no errors. I am going to assume
that I am not writing the code correctly.

Possible problems:

1. You have made design changes to the subform since opening it.
This could be intentional, or programmatic. If you made any changes, close
it, and reopen it before trying to apply this filter.

2. The subform is dirty with a record that cannot be saved.
Typically this happens if you dirty it programmatically. The worst case is
code in the form's Current event that dirties it as soon as it arrives at a
new record, or in its AfterUpdate that dirties it again the instant it
saves.

3. The database is corrupting.
3.1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

3.2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3.3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

3.4. Open Access, and compact again.

4. You have multiple copies of the same data open for editing at once.
This is particularly a problem with memo fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I can filter either subform separately without incident but when I filter
them both together I get the before mentioned pop-up.

Do you have the same problem when you've only set one filter? Does it
matter
which of the two subforms?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That does work great except when I try to close the form I get a
pop-up.
The pop-up says:
This action will reset the current code in break mode.
Do you want to stop running the code?

No matter what I select I eventually have to hit [ctrl][shift][esc] and
force the application to close.

:

Yes, that should work.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I promise as soon as possible I am going to start taking
programming
classes.
Any suggestions would be appreciated as well.

That said is this how it should look to filter both subforms
simultaneously?

Private Sub Command15_Click()
With Me.Child0.Form
.Filter = "SumOfExternalEvents IS NOT NULL"
.FilterOn = True
End With
With Me.Child2.Form
.Filter = "SumOfExternalEvents IS NOT NULL"
.FilterOn = True
End With
End Sub


:

Sure. Exactly the same again for Child2.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
One more question. Is it possible to apply that filter to two
subforms
at
the
same time? Child0 and Child2 both have the same field names and
search
criteria.

:

This should do it:

With Me.Child0.Form
.Filter = "ExternalEvents Is Not Null"
.FilterOn = True
End With

If there is any chance that the main form could also have a
filter,
see:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html
The next version of Access (2007) will have this bug fixed.

message
I am having difficulty finding the best method to do this so
any
help
would
be appreciated.

Main form name is FrmReviewAccuracy312
SubForm name is FrmNCLocAccuracy312 or Child0 in the main
form.
SubForm gets its data from a query named QryNCLocAccuracy312

What I would like is to have a command button on the main
form
that
when
clicked filters a field on the subform to exclude all null
values.
The
specific field in the query is called ExternalEvents.

 

[tekinka]

What's work around for subForm filter bug?

I read Allen's article on his outstanding web but I can't see where the solution to this bug is.

I have a frmMain and then subFrmMain and then a subSubFrmMain (the subform of the subform of the main form). None of them are conected via child/parent type thing link. Anyways, my issue is that my subSubFrmMain loses the filter when I click on form. When it opens, it's got a filter on (on open event) but then once I click on it the filter goes away.

How can i prevent from not losing the filter in this subSubForm?

http://allenbrowne.com/bug-02.html

Thanks for any help/tip provided.
Te