Datasheet filtering

[devQuestion]

Hello, I am using Access 2010. I have a subform(continuous form) that I display as a datasheet and allow filtering. I display the record count by using Me.Recordset.RecordCount in the subform's form_current event. This value updates properly based on the column filtering except when the filtering displays 0 records. When there are 0 records, the form_current event does not fire and I cannot figure out how to trap when this occurs so that I can display '0 records found'. Does anyone know how I can tell when the column filtering causes 0 records to display in the datasheet? Is there an event that fires when the user filters using the column headings arrow?

thanks for your help.

 

[CJ_London]

forms have two events which you can try - On Filter and On Filter Apply.

 

[devQuestion]

Thanks for your reply CJ. Unfortunately I tried both of those events. The On Filter doesn't fire at all when filtering by column and the On Apply Filter appears to fire before it actually filters the data. When I check Me.Recordset.RecordCount in the On Apply Filter it gives me the number of records before the filter.

 

[CJ_London]

OK, then I suggest the following - you'll need to try different events to see if any do the job

In the vba window, top left dropdown, select Form, then in right dropdown you will see all the events that occur for a form - quite a few more than appear in properties.

Some that look like they are worth investigating include

applyfilter
query
datasetchange
afterrender
afterfinalrender

there may be others

I've also found this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=48190

Post #12 provides and alternative solution

 

[spikepl]

In the subform footer, have a textbox txtCount with =Count(*) as record source

In the main form, have a textbox showing the count like this:
=IIf(Not IsError([NameOfSubform].[Form]![txtCount]),[NameOfSubform].[Form]![txtCount],0)

 

[devQuestion]

Thank you spikepl this worked perfectly!