Edit field criteria in subform

[Back2Basics]

Hi

I have a subform that displays rows linked to a field in the form it is placed in. I have now realised that I need to edit the criteria of one of the fields so that it only displays 'effective' records but I can't find any way of adding a criteria for that field.

I have limited knowledge of access but am used to adding criteria into queries and hoped it would be similar, but struggling to find away of doing it?

Any help would be greatly appreciated.

TIA

[Bulzie]

So you want the subform to only show records based on the field in the main form and also only those records that has effective value in one of the subform fields?

[NTC]

the sub form should be bound to a query that uses the criteria you seek. therefore it will only display the appropriate records

[Back2Basics]

Hi Bulzie

That is right. The main form has a record for each customer and the subform has the relevant items for that customer. Some items are set as 'expired' or 'superseded' so I only want to show the 'effective' items in the 'Status' field so that it's clear to users what items are live and relevant for that customer. I thought I could simply create a query with the correct criteria and then create the subform again, but wondered if there was a way of updating rather than doing it from scratch again?

[Back2Basics]

Hi NTC

I thought would be the case, how do I find that query so I can edit it?

[NTC]

a saved query is an object found in the navigation pane on left

[Bulzie]

Or maybe put this in the OnOpen event of the subform. Change YourField to the name of the field on your form.

Me.Filter = "YourField = 'Effective'"
Me.FilterOn = True

[Back2Basics]

the subform wasn't created from a saved query. the only queries that I have in the navigation pane are those that I have created but none of them relate to the subform I have created. I created the subform using the subform button and going through the wizard to select a table and define the linked fields between the main form and the table I selected.

[Back2Basics]

Many thanks Bulzie this seems to have done the job!

I was sure there must be a query in the background somewhere but not being able to find it puzzled me. Thank for the help!