Filtering

[Alex]

Hi,

I have a button on a main form which when pressed I want to filter the
records available, based on the criteria on a Yes/No field that is contained
within the sub form

The code I am using is:
Me.Filter = "Forms!frmOrganisations!frmRequests.FollowUpFlag = 0"
Me.FilterOn = True

But this is bringing up a parameter request box, and if I enter a zero, I am
getting all 37000 records instead of the 771 I am expecting.

Can someone tell me what is wrong, and why my code is brining up the
parameter request box - I have checked the control name and its correct

Thanks

Alex

 

[Wayne Morgan]

Me.Filter = "Forms!frmOrganisations!frmRequests.FollowUpFlag = 0"

This line needs to refer to a field on the main form that contains the value
that you want to filter on. You can retrieve the value for the filter from
the subform control, but it needs to be applied to the main form's field.

Me.Filter = "[YesNoField]=" & Me!frmRequests.Form!FollowUpFlag

Also, to refer to the control on the subform, frmRequests needs to be the
name of the subform control on the main form that holds the subform, not the
name of the subform itself. They may both be the same, but if not, you'll
need to make sure that you get the correct name.

 

[APH]

Wayne - thanks for your input

if I understand you correctly, I need to place a Yes/No hidden field on the
main form and set it to 0 - and then the filter does a comparison?
Alex

Me.Filter = "Forms!frmOrganisations!frmRequests.FollowUpFlag = 0"

This line needs to refer to a field on the main form that contains the value
that you want to filter on. You can retrieve the value for the filter from
the subform control, but it needs to be applied to the main form's field.

Me.Filter = "[YesNoField]=" & Me!frmRequests.Form!FollowUpFlag

Also, to refer to the control on the subform, frmRequests needs to be the
name of the subform control on the main form that holds the subform, not the
name of the subform itself. They may both be the same, but if not, you'll
need to make sure that you get the correct name.

--
Wayne Morgan
Microsoft Access MVP

Hi,

I have a button on a main form which when pressed I want to filter the
records available, based on the criteria on a Yes/No field that is contained
within the sub form

The code I am using is:
Me.Filter = "Forms!frmOrganisations!frmRequests.FollowUpFlag = 0"
Me.FilterOn = True

But this is bringing up a parameter request box, and if I enter a zero,

I

am
getting all 37000 records instead of the 771 I am expecting.

Can someone tell me what is wrong, and why my code is brining up the
parameter request box - I have checked the control name and its correct

Thanks

Alex

 

[Wayne Morgan]

To filter on a field, it needs to exist. The field will need to come from
the same recordset as the rest of the data in the records so that it has a
logical relationship with the other data.

 

[APH]

Hi Wayne, I am sorry I am being particularly thick today and not got the
concepts clear in my mind.

My Main table had a field RecordId which is an autonumber. This links to
the table in the subform where there is also a field RecordId. The
relationship is a One to Many.

This presumably is the logigal relationship

In the table in my subform i have a Yes/No field [FollowUpFlag]

From the button on the main form, I want to filter out the records where
MainForm!RecordId = SubForm!RecordId and where SubForm![FollowUpFlag] = 0

Have I got the concept correct? and if so how do I write the syntax?

Thanks for your help on this - it is much appreciated

Alex

 

[Wayne Morgan]

Ok, are you wanting to filter the records that show in the main form that
have subform records with a No value or do you only want the records in the
subform that have No set to show in the subform?

--
Wayne Morgan
MS Access MVP

Hi Wayne, I am sorry I am being particularly thick today and not got the
concepts clear in my mind.

My Main table had a field RecordId which is an autonumber. This links to
the table in the subform where there is also a field RecordId. The
relationship is a One to Many.

This presumably is the logigal relationship

In the table in my subform i have a Yes/No field [FollowUpFlag]

From the button on the main form, I want to filter out the records where
MainForm!RecordId = SubForm!RecordId and where SubForm![FollowUpFlag] = 0

Have I got the concept correct? and if so how do I write the syntax?

Thanks for your help on this - it is much appreciated

Alex

To filter on a field, it needs to exist. The field will need to come from
the same recordset as the rest of the data in the records so that it has a
logical relationship with the other data.

--
Wayne Morgan
MS Access MVP


on
the

 

[Alex]

Wayne
I'm wanting to filter the records that show in the main form that
have subform records with a No value

Alex

Ok, are you wanting to filter the records that show in the main form that
have subform records with a No value or do you only want the records in the
subform that have No set to show in the subform?

--
Wayne Morgan
MS Access MVP

Hi Wayne, I am sorry I am being particularly thick today and not got the
concepts clear in my mind.

My Main table had a field RecordId which is an autonumber. This links to
the table in the subform where there is also a field RecordId. The
relationship is a One to Many.

This presumably is the logigal relationship

In the table in my subform i have a Yes/No field [FollowUpFlag]

From the button on the main form, I want to filter out the records where
MainForm!RecordId = SubForm!RecordId and where SubForm![FollowUpFlag] = 0

Have I got the concept correct? and if so how do I write the syntax?

Thanks for your help on this - it is much appreciated

Alex

To filter on a field, it needs to exist. The field will need to come from
the same recordset as the rest of the data in the records so that it

has

a

 

[Wayne Morgan]

Ok, let's start over.

In the query feeding the main form, create a calculated field using the
DCount function. For the criteria of the DCount function you will use the
current record id field And the field in the subforms table that contains
the Yes/No value. Place a hidden textbox on the main form and bind it to
this calculated field. For the main form's filter, you want to see records
where the value of this calculated field is >=1.

To create the calculated field place the following (adjusted for your field
and table names) in the Field box in the query design view of the main
form's query. Also, if the ID field is text, you will need to include
apostrophes ( ' ) around the value by placing them inside the quotes on each
side of the value.

FilterFieldCount("*", "SubformsTable", "[SubformsRecordID]=" &
[RecordIDField] & " And [YesNoField]=False")

If you have more than one table in the main form's query, then the table
name may also need to be specified along with [RecordIDField].

[TableName].[RecordIDField]

 

[Alex]

Thanks Wayne understand it all - sorry to have been so dumb.

Alex

Ok, let's start over.

In the query feeding the main form, create a calculated field using the
DCount function. For the criteria of the DCount function you will use the
current record id field And the field in the subforms table that contains
the Yes/No value. Place a hidden textbox on the main form and bind it to
this calculated field. For the main form's filter, you want to see records
where the value of this calculated field is >=1.

To create the calculated field place the following (adjusted for your field
and table names) in the Field box in the query design view of the main
form's query. Also, if the ID field is text, you will need to include
apostrophes ( ' ) around the value by placing them inside the quotes on each
side of the value.

FilterFieldCount("*", "SubformsTable", "[SubformsRecordID]=" &
[RecordIDField] & " And [YesNoField]=False")

If you have more than one table in the main form's query, then the table
name may also need to be specified along with [RecordIDField].

[TableName].[RecordIDField]

--
Wayne Morgan
MS Access MVP

Wayne
I'm wanting to filter the records that show in the main form that
have subform records with a No value

 

[Wayne Morgan]

No, this one needed a little work and doing it by passing messages back and
forth rather than both sitting at the computer makes things much harder.