Test Filtered Records Greater than zero

[94Sport5sp]

Hi:

I have designed a from which populates a list box for the user to select and item from. After selecting and testing that an item is selected I set a filter to restrict the listed records and then set the focus to the first field which the user can modify. The problem I have is when the filtered list is empty then the set focus triggers an error. Is there a way to test if the filter is returning more than zero records?

Thanks

 

[John Big Booty]

You could test;

Me.MyListBox.listcount > 0

 

[94Sport5sp]

Hi:

Thanks for the reply.

My error, I did not express myself correctly. The form is attached to TableA and will list records showing sales for different departments. The ListBox is populated from TableB which list the available departments. The filter applies to the detail records from TableA limited to the department selected from the list box and I would like to test if the filter on TableA has any sales record to display.

Thanks

 

[John Big Booty]

You could try the DCount() function.

 

[94Sport5sp]

Hi:

Yes that would work to tell me that the filter has zero records displayed. But then, testing with on err to trap the error that occurs on setfocus also works. I just thought there would be a more elegant way to display the count of records available on a filtered table.

Thanks for the suggestions

 

[John Big Booty]

You could test the DCount() before you apply the filter, if it comes up as Zero you advise the user through a pop up message and exit the sub without apply the filter.

 

[94Sport5sp]

Hi John:

Now that is an eye opening thought. Yes I could count the records before applying the filter and then I would know if the filter would work. I'll combine this thought with some code sample from the "Code Repository " to have an sql count the records. If count < 1 then take evasive action. Nice simply solution. According to other members here and on other sites that I have read, the DFunctions are slow and resource heavy. Therefor, the code from RX which is lngRecordCount = CurrentDb.OpenRecordset ("SELECT Count([ID]) AS [CountALL] FROM TableName;")![CountAll]). Combine that with a where clause, which is the filter, and that would do the job nicely.

Many thanks