Apply filter problem

[Paul Doree]

Hi,

I'm having trouble with the syntax for applying a filter to a subform, from
a command button on the main form. One of the fields in the subform shows
total hours worked for the job. I want to set a filter that shows only the
rows where the value in the field is Null (there are about 60 rows like
this).

Can anyone help?

Much appreciated

Paul

 

[Paul Doree]

Hi Bonnie,

I'm using

DoCmd.ApplyFilter
Forms!frmJobsForAllocation!frmJobsForAllocationSubform.Form!SumOfHoursWorked,
Null

I've also tried = Null instead of , Null

The error message I get is:

Run time error '2491':
The action or method is invalid because the form or report isn't bound to a
table or query

The subform is bound to a query.

 

[Paul Doree]

No good, I'm afraid - the subform is a datasheet.

The commend button needs to be on the main form - I tried a few variations
to no avail.

Try this Paul:

In the OnClick event of the command button put this VBA code (click on the
...
):

Me.filter = "isnull(SumOfHoursWorked)"
Me.FilterOn = True

This should work if the command button is on the subform.

Bonnie
http://www.dataplus-svc.com

Hi Bonnie,

I'm using

DoCmd.ApplyFilter
Forms!frmJobsForAllocation!frmJobsForAllocationSubform.Form!SumOfHoursWorked,
Null

I've also tried = Null instead of , Null

The error message I get is:

Run time error '2491':
The action or method is invalid because the form or report isn't bound to
a
table or query

The subform is bound to a query.

What syntax are you using that doesn't work?

[quoted text clipped - 20 lines]

Paul

 

[Paul Doree]

Hi Bonnie,

I've used your suggestion:

Forms!frmJobsForAllocation.frmJobsForAllocationSubform!Form.Filter =
"isnull(SumOfHoursWorked)"
Forms!frmJobsForAllocation.frmJobsForAllocationSubform!Form.FilterOn = True

Get the error: Microsoft Access can't find the field 'form'

This is one of those 'It should be simple...'

Paul

How about:

forms!mainform.subform!form.filter = "isnull(SumOfHoursWorked)"
forms!mainform.subform!form.filterOn = True

Change the names.

Bonnie
http://www.dataplus-svc.com

No good, I'm afraid - the subform is a datasheet.

The commend button needs to be on the main form - I tried a few variations
to no avail.

Try this Paul:

[quoted text clipped - 34 lines]

Paul

 

[Lena]

Paul, I get the same error. Did you ever get a response?

Hi Bonnie,

I've used your suggestion:

Forms!frmJobsForAllocation.frmJobsForAllocationSubform!Form.Filter =
"isnull(SumOfHoursWorked)"
Forms!frmJobsForAllocation.frmJobsForAllocationSubform!Form.FilterOn = True

Get the error: Microsoft Access can't find the field 'form'

This is one of those 'It should be simple...'

Paul

How about:

forms!mainform.subform!form.filter = "isnull(SumOfHoursWorked)"
forms!mainform.subform!form.filterOn = True

Change the names.

Bonnie
http://www.dataplus-svc.com

No good, I'm afraid - the subform is a datasheet.

The commend button needs to be on the main form - I tried a few variations
to no avail.

Try this Paul:

[quoted text clipped - 34 lines]

Paul