Getting a subform to Show All records

[Guest]

I have a single table with a main form and sub form. A combobox on the main
form filters the subform - works fine.

I have added a command button to "Show All" records - in essence, removing
the filter condition.

I've tried setting the FilterOn property of the main form to False, setting
the FilterOn property of the sub form to False, and setting the filter
property to
"[FieldName] LIKE '*'"

None of these methods work!

Is there a better way to "Show All" records in the subform?

 

[ruralguy via AccessMonster.com]

Have you also included a Requery of the SubForm?

I have a single table with a main form and sub form. A combobox on the main
form filters the subform - works fine.

I have added a command button to "Show All" records - in essence, removing
the filter condition.

I've tried setting the FilterOn property of the main form to False, setting
the FilterOn property of the sub form to False, and setting the filter
property to
"[FieldName] LIKE '*'"

None of these methods work!

Is there a better way to "Show All" records in the subform?

 

[Guest]

Like this?

===================


Private Sub cmdShowAll_Click() ' cmdShowAll is on the Main Form

Forms!fmFilter.Filter = "" ' fmFilter is the Main Form
Forms!fmFilter.FilterOn = False

Me.ctrlSubForm.Form.Requery ' ctrlSubForm is the Sub-Form

End Sub

======================

It does not result in all records visible - in fact, it causes the result
set to be one of the data sets in the middle of the table named
"HardwareAssemblies". There are about 15 other sets of data that are not
showing.

Have you also included a Requery of the SubForm?

I have a single table with a main form and sub form. A combobox on the main
form filters the subform - works fine.

I have added a command button to "Show All" records - in essence, removing
the filter condition.

I've tried setting the FilterOn property of the main form to False, setting
the FilterOn property of the sub form to False, and setting the filter
property to
"[FieldName] LIKE '*'"

None of these methods work!

Is there a better way to "Show All" records in the subform?

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

You are adjusting the filter on the MainForm and I thought you wanted to
remove the filter on the SubForm.

Like this?

===================

Private Sub cmdShowAll_Click() ' cmdShowAll is on the Main Form

Forms!fmFilter.Filter = "" ' fmFilter is the Main Form
Forms!fmFilter.FilterOn = False

Me.ctrlSubForm.Form.Requery ' ctrlSubForm is the Sub-Form

End Sub

======================

It does not result in all records visible - in fact, it causes the result
set to be one of the data sets in the middle of the table named
"HardwareAssemblies". There are about 15 other sets of data that are not
showing.

Have you also included a Requery of the SubForm?

[quoted text clipped - 12 lines]

 

[Guest]

If I seem to be confused, I guess I am. When I filter using my combobox on
the Main Form, I'm setting the Filter and FilterOn properties of the Main
Form to get the SubForm to respond. (And that part is working fine). I
figured that I would need to requery the Main Form to Show All.

I've tried both ways, but with no success.

When is it appropriate to act on the Sub Form and when do you act on the
Main Form? There must be some simple some logic that I can take hold of here.

By the way, thanks for responding to my question. Sorry I'm so slow . . . ;-}

You are adjusting the filter on the MainForm and I thought you wanted to
remove the filter on the SubForm.

Like this?

===================

Private Sub cmdShowAll_Click() ' cmdShowAll is on the Main Form

Forms!fmFilter.Filter = "" ' fmFilter is the Main Form
Forms!fmFilter.FilterOn = False

Me.ctrlSubForm.Form.Requery ' ctrlSubForm is the Sub-Form

End Sub

======================

It does not result in all records visible - in fact, it causes the result
set to be one of the data sets in the middle of the table named
"HardwareAssemblies". There are about 15 other sets of data that are not
showing.

Have you also included a Requery of the SubForm?

[quoted text clipped - 12 lines]

Is there a better way to "Show All" records in the subform?

 

[ruralguy via AccessMonster.com]

How about posting the AfterUpdate event code of the ComboBox on you MainForm
so we can see how you are doing the filtering? What field are you using as
the LinkChild/MasterFields of the SubForm?

If I seem to be confused, I guess I am. When I filter using my combobox on
the Main Form, I'm setting the Filter and FilterOn properties of the Main
Form to get the SubForm to respond. (And that part is working fine). I
figured that I would need to requery the Main Form to Show All.

I've tried both ways, but with no success.

When is it appropriate to act on the Sub Form and when do you act on the
Main Form? There must be some simple some logic that I can take hold of here.

By the way, thanks for responding to my question. Sorry I'm so slow . . . ;-}

You are adjusting the filter on the MainForm and I thought you wanted to
remove the filter on the SubForm.

[quoted text clipped - 24 lines]

 

[Guest]

Great idea . . . Here's the After-Update event for the combobox:

==============================
Private Sub cboMatrixName_AfterUpdate()

thisFilter = "[MatrixName] = '" & Me.cboMatrixName.Value & "' "

Forms.fmFilter.Filter = thisFilter

Forms.fmFilter.FilterOn = True

End Sub
========================================

The Properties for the Main Form (fmFilter) look like this:

Record Source: tblMatrixSummary (the only table in the database)
Allow Filters: Yes
Default View: Single Form


The Properties of the Sub Form (fmSubForm - housed in ctrlSubForm) look like
this:

Record Source: tblMatrixSummary
Allow Filters: Yes
Default View: DataSheet

The Sub-Form Control (ctrlSubForm) has the following properties:
Source Object: fmSubForm
Link Child Fields: MatrixName
Link Master Fields: MatrixName


The combobox (cboMatrixName) is in the Form Header of the Main Form
The command button (cmdShowAll) is also in the Form Header of the Main Form


I think this is about as simple as it can get.
======================================================

How about posting the AfterUpdate event code of the ComboBox on you MainForm
so we can see how you are doing the filtering? What field are you using as
the LinkChild/MasterFields of the SubForm?

If I seem to be confused, I guess I am. When I filter using my combobox on
the Main Form, I'm setting the Filter and FilterOn properties of the Main
Form to get the SubForm to respond. (And that part is working fine). I
figured that I would need to requery the Main Form to Show All.

I've tried both ways, but with no success.

When is it appropriate to act on the Sub Form and when do you act on the
Main Form? There must be some simple some logic that I can take hold of here.

By the way, thanks for responding to my question. Sorry I'm so slow . . . ;-}

You are adjusting the filter on the MainForm and I thought you wanted to
remove the filter on the SubForm.

[quoted text clipped - 24 lines]

Is there a better way to "Show All" records in the subform?

 

[ruralguy via AccessMonster.com]

So you have a MainForm in single form mode that has a table as its
RecordSource and a
SubForm on that MainForm in Datasheet mode that has the same table as its
RecordSource.
Can you move to a different record on the MainForm after you remove the
filter and does that
change the SubForm at all?

Great idea . . . Here's the After-Update event for the combobox:

==============================
Private Sub cboMatrixName_AfterUpdate()

thisFilter = "[MatrixName] = '" & Me.cboMatrixName.Value & "' "

Forms.fmFilter.Filter = thisFilter

Forms.fmFilter.FilterOn = True

End Sub
========================================

The Properties for the Main Form (fmFilter) look like this:

Record Source: tblMatrixSummary (the only table in the database)
Allow Filters: Yes
Default View: Single Form

The Properties of the Sub Form (fmSubForm - housed in ctrlSubForm) look like
this:

Record Source: tblMatrixSummary
Allow Filters: Yes
Default View: DataSheet

The Sub-Form Control (ctrlSubForm) has the following properties:
Source Object: fmSubForm
Link Child Fields: MatrixName
Link Master Fields: MatrixName

The combobox (cboMatrixName) is in the Form Header of the Main Form
The command button (cmdShowAll) is also in the Form Header of the Main Form

I think this is about as simple as it can get.
======================================================

How about posting the AfterUpdate event code of the ComboBox on you MainForm
so we can see how you are doing the filtering? What field are you using as

[quoted text clipped - 17 lines]

 

[Guest]

Yes - you've accurately described my Access database form.

With no filter input, I can select any of the 4900+ records using the record
selectors on the main-form, and the sub-form updates to show the selected
record - plus its neighbors since I am in a datasheet view.

I have about 20 "MatrixNames" - each with about 250 matching records. (In
the filtered mode, the combobox is used to select a MatrixName, and all the
records that share that MatrixName are displayed in the sub-form)

I am intrigued with your questions. I'd like to know your thought process as
you deduce what the root of my problem is.

So you have a MainForm in single form mode that has a table as its
RecordSource and a
SubForm on that MainForm in Datasheet mode that has the same table as its
RecordSource.
Can you move to a different record on the MainForm after you remove the
filter and does that
change the SubForm at all?

Great idea . . . Here's the After-Update event for the combobox:

==============================
Private Sub cboMatrixName_AfterUpdate()

thisFilter = "[MatrixName] = '" & Me.cboMatrixName.Value & "' "

Forms.fmFilter.Filter = thisFilter

Forms.fmFilter.FilterOn = True

End Sub
========================================

The Properties for the Main Form (fmFilter) look like this:

Record Source: tblMatrixSummary (the only table in the database)
Allow Filters: Yes
Default View: Single Form

The Properties of the Sub Form (fmSubForm - housed in ctrlSubForm) look like
this:

Record Source: tblMatrixSummary
Allow Filters: Yes
Default View: DataSheet

The Sub-Form Control (ctrlSubForm) has the following properties:
Source Object: fmSubForm
Link Child Fields: MatrixName
Link Master Fields: MatrixName

The combobox (cboMatrixName) is in the Form Header of the Main Form
The command button (cmdShowAll) is also in the Form Header of the Main Form

I think this is about as simple as it can get.
======================================================

How about posting the AfterUpdate event code of the ComboBox on you MainForm
so we can see how you are doing the filtering? What field are you using as

[quoted text clipped - 17 lines]

Is there a better way to "Show All" records in the subform?

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

As I asked earlier:
Can you move to a different record on the MainForm after you remove the
filter and does that change the SubForm at all?

There are events that occur when you change records and I'm curious if the
LinkFields reactivate.

Yes - you've accurately described my Access database form.

With no filter input, I can select any of the 4900+ records using the record
selectors on the main-form, and the sub-form updates to show the selected
record - plus its neighbors since I am in a datasheet view.

I have about 20 "MatrixNames" - each with about 250 matching records. (In
the filtered mode, the combobox is used to select a MatrixName, and all the
records that share that MatrixName are displayed in the sub-form)

I am intrigued with your questions. I'd like to know your thought process as
you deduce what the root of my problem is.

So you have a MainForm in single form mode that has a table as its
RecordSource and a

[quoted text clipped - 47 lines]

 

[Guest]

Yes - I can move to a different record on the Main Form, and doing so updates
the Sub Form.

As I asked earlier:
Can you move to a different record on the MainForm after you remove the
filter and does that change the SubForm at all?

There are events that occur when you change records and I'm curious if the
LinkFields reactivate.

Yes - you've accurately described my Access database form.

With no filter input, I can select any of the 4900+ records using the record
selectors on the main-form, and the sub-form updates to show the selected
record - plus its neighbors since I am in a datasheet view.

I have about 20 "MatrixNames" - each with about 250 matching records. (In
the filtered mode, the combobox is used to select a MatrixName, and all the
records that share that MatrixName are displayed in the sub-form)

I am intrigued with your questions. I'd like to know your thought process as
you deduce what the root of my problem is.

So you have a MainForm in single form mode that has a table as its
RecordSource and a

[quoted text clipped - 47 lines]

Is there a better way to "Show All" records in the subform?

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Could it be that when you do a:
Me.ctrlSubForm.Form.Requery ' ctrlSubForm is the Sub-Form
...nothing appears to change on the SubForm because it is on the correct
record?

Yes - I can move to a different record on the Main Form, and doing so updates
the Sub Form.

As I asked earlier:
Can you move to a different record on the MainForm after you remove the

[quoted text clipped - 21 lines]

 

[Guest]

I don't think this is the root problem, Allan.

The database is in a filtered state - I can successfully filter using
cboMatrixName to display a record set iin the subform that matches the
combobox criteria.

I am unable to programmatically "unfilter" so that the subform displays all
records.

I've tried setting the FilterOn property of the Main Form and Sub Form to
False, and I've tried using a WHERE clause like this: "[MatrixName] LIKE
'*'" on both the Main Form and the Sub Form. Neither approach will Show All
of the records.

I'm accustomed to working in Excel VBA, and this is so easy to do
programmatically.
In Access, I feel like I'm tripping over myself.

I was researching this issue last night, and came across this posting by
Allen Browne:

"If a form and its subform are both filtered and you remove the filter from
the subform, Access no longer recognises that the main form is filtered. The
word "(Filtered)" disappears from the form's navigation buttons. It does not
show all the records, but attempting to turn off the main form's filter
fails, and falsely reports its FilterOn property is False." (see more at
http://allenbrowne.com/bug-02.html)

Maybe this is the root cause of my difficulties . . .

Could it be that when you do a:
Me.ctrlSubForm.Form.Requery ' ctrlSubForm is the Sub-Form
...nothing appears to change on the SubForm because it is on the correct
record?

Yes - I can move to a different record on the Main Form, and doing so updates
the Sub Form.

As I asked earlier:
Can you move to a different record on the MainForm after you remove the

[quoted text clipped - 21 lines]

Is there a better way to "Show All" records in the subform?

 

[ruralguy via AccessMonster.com]

It is entirly possible you are struggling against a bug. Allen knows of what
he speaks. I personally do not use filters except for WhereCondition
arguments. I also use queries rather than tables as a RecordSource for a
form. You could probably work around this issue by using a query and
dynamically changing the RecordSource as a filter.

I don't think this is the root problem, Allan.

The database is in a filtered state - I can successfully filter using
cboMatrixName to display a record set iin the subform that matches the
combobox criteria.

I am unable to programmatically "unfilter" so that the subform displays all
records.

I've tried setting the FilterOn property of the Main Form and Sub Form to
False, and I've tried using a WHERE clause like this: "[MatrixName] LIKE
'*'" on both the Main Form and the Sub Form. Neither approach will Show All
of the records.

I'm accustomed to working in Excel VBA, and this is so easy to do
programmatically.
In Access, I feel like I'm tripping over myself.

I was researching this issue last night, and came across this posting by
Allen Browne:

"If a form and its subform are both filtered and you remove the filter from
the subform, Access no longer recognises that the main form is filtered. The
word "(Filtered)" disappears from the form's navigation buttons. It does not
show all the records, but attempting to turn off the main form's filter
fails, and falsely reports its FilterOn property is False." (see more at
http://allenbrowne.com/bug-02.html)

Maybe this is the root cause of my difficulties . . .

Could it be that when you do a:
Me.ctrlSubForm.Form.Requery ' ctrlSubForm is the Sub-Form

[quoted text clipped - 9 lines]