Access vba form filter question

[Ranthalion]

Hello,

I am working on an Access app that has a button on a form which changes the contents of a subform and applies a filter to the form in the subform. This all works fine, but the problem I have is that the filter sometimes takes a couple of seconds to complete. I would like to be able to change the cursor to hourglass while the filter is running and then change it back to normal once the filter has completed. Any ideas on which event to use?

By the way, I'm using Access 2003. Here is the code I'm currently using to open the form:


If (cbxNav.Value <> "Please select a destination") Then

If (cbxNav.Value = "Management") Then
Forms("frmMain").Controls("sbfrmContent").SourceObject = "frmManagement"
Else
DoCmd.Hourglass True
Forms("frmMain").Controls("sbfrmContent").SourceObject = "frmDaily"
Forms("frmMain").Controls("sbfrmContent").Form.Filter = "Status = '" & cbxNav.Value & "'"
Forms("frmMain").Controls("sbfrmContent").Form.FilterOn = True
DoCmd.Hourglass False 'this happens too soon
End If
End If


Thanks,
Ranthalion

[RobDog888]

Try it in your subforms Form_Load or in the procedure where your main form is changing its content on with, like a command button click or a record navigation change event like Form_Current.

[Ranthalion]

Thanks for the recommendations. I've tried OnLoad and OnCurrent, but the events fire before the filter is finished, causing the cursor to change back to normal before I want it to...

I want the cursor to stay hourglass until the record navigation section shows how many records are in the recordset.

[RobDog888]

Those events from the subform or parent form?

[Ranthalion]

Sorry, those are the events on the subform.

[RobDog888]

Try it in the parent form as its the one initiating the filter.

Also, try the Form_Filter and possibly the Form_AfterUpdate events.

[Ranthalion]

Thanks for your help. I just gave that a shot... No dice.

I tried just making a function and calling it during each event that fires on the parent form and on the sub form, but it doesn't seem that any event fires when the filter is finished and the navigation control is finished updating...

The apply filter and on filter events fire when I right click a field and filter on it from the subform, but not when I apply the filter through code...

The current work around I'm using now is to have a timer event on the subform that fires after 1.5 seconds, which changes the mouse pointer and disables the timer...

Let me know if you have any other ideas.

[RobDog888]

Why not code a macro to do it and then in your form you can call the macro and it can change the mouse, apply the filter, and then change the mouse back? May be a more synchronous operation.

[Ranthalion]

Good idea, but that one didn't work either. It seems that the filter is applied and completed, but the record navigation control takes a longer time to complete than the filter takes to complete. So, the data in my form changes, but the navigation control takes a little longer to calculate the total number of records and no event is raised from that control...

I suppose I could make my own navigation controls, but that just doesn't really seem worth it for this project...

Thanks again!