Filtering subform based on control on main form

[Jables]

I have a main form with a subform on it. The main form has a Calendar Control on it. When the main form loads, I set its recordsource and I set the date for the calendar. I then use the calendar's date to filter records in the subform.

Here is what's in the load event of the main form.

Private Sub Form_Load()

   Me.RecordSource = "SELECT EmployeeID FROM " & _
      "tblEmployee WHERE EmployeeID=4"

   'Set a combobox's default value so it displays the employee
   Me.cboEmployee.DefaultValue = 4

   'Set the calendar's date to the current date
   Me.CtlCalendar.Value = FormatDateTime(Now(),vbShortDate)
   'Filter the subform based on the calendar date
   Me.SubformName.Form.Filter = "[Date Worked] = #" & Me.CtlCalendar.Value & "#"
   Me.SubformName.Form.OrderBy = "[Date Worked],[Started]"
   Me.SubformName.Form.FilterOn = True
   Me.SubformName.Form.OrderByOn = True

   Me.CtlCalendar.SetFocus

End Sub

Here's what's in the load event of the subform

Private Sub Form_Load()

   Dim strSQL As String
   strSQL = "SELECT * FROM tblTimeRecord WHERE fkEmployeeID=4"
   Me.RecordSource = strSQL

End Sub

This all works fine the first two times I open the main form. But the third time I open it, I get the error:

Err.Number = 2455
Err.Message = You entered an expression that has an invalid reference to the property Form/Report

The debugger debugs to the line in the main form's Load event:

Me.SubformName.Form.Filter = "[Date Worked] = #" & Me.CtlCalendar.Value & "#"

Now my guess is that something is out of whack with the order of events firing when the main form and subform open. According to Microsoft's documentation the following events fire when a form is opened:

Open --> Load --> Activate --> Current (in that order)

Does that mean that when the main form opens and the Load event fires, that the subform's Load event hasn't fired yet and there is no RecordSource, therefore raising the error when I try to set the subform's filter property? If that is the case, why wouldn't I get the error every time I opent the main form and not just every third time?

Ideas?

 

[Jedi420]

I think this may be able to help you or at least point you in the right direction ... even though it deals with reports .. same topic almost. thread703-605847

-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[Jables]

Thanks Jedi420,

I did a bit more testing of the problem and I have figured out why it is happening, but I still can't figure out how to stop it.

According to the Access 2000 documentation, the following events fire when a form first opens:

Open --> Load --> Activate --> Current

I got curious as to the order of events firing when you open a form containing a subform, so I stuck some MsgBox code in each of the corresponding events for the main form and the sub form. Here are the order of events firing whenever the main form is opened:

1st time the form is opened:[ul][li]Sub Form --> Open event fires[/li]
[li]Sub Form --> Load event fires --Here's where the recordsource property gets set for the sub form[/li]
[li]Sub Form --> Current event fires[/li]
[li]Main Form --> Open event fires[/li]
[li]Main Form --> Load event fires --Here's where the calendar's value is set and the subform's filter is set[/li]
[li]Main Form --> Current event fires[/li]
[li]Sub Form --> Current event fires[/li]
[li]Sub Form --> Current event fires again[/li]
[li]Main Form --> Current event fires[/li][/ul]
2nd time the form is opened:[ul][li]Events fire in the same sequence as the first time[/li][/ul]
3rd time the form is opened:[ul]
[li]Main Form --> Open event fires ?!?!?[/li]
[li]Main Form --> Load event fires -- tries to filter the subform which is not yet opened. Throws the error.[/li[/ul]
What is causing the events to fire in a completely different sequence all of a sudden?

 

[Jedi420]

Couldn't you just filter the subform in its own load event, after you set the recordsource? That is indeed strange behavior for the form and subforms events not to fire the same each time. Silly question, but are you opening and closing it each time exactly the same way?

-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[Jables]

I could filter the form in its own load event, but I would have to make sure that the main form is open if I'm going to filter based on the value of one of the main form's controls (and this seems impossible since the event firing order changes every third time the form is opened). However, since I am always setting the calendar's value to the value of Date(), I suppose I could just filter the subform using the Date() function and then update the filter if the calendar date changes. Then I could avoid these wacky events changing their firing order on me, which I still can't explain.

And yes, I am opening the form (at least I think I am) and closing it the same way every time. I've even tried reinstalling office to get rid of this problem, but to no avail. The behavior can be seen on multiple computers, so I don't think it's a faulty install of MS Office.

Thanks for helping me to think this through, Jedi420. Let me know if you have any other suggestions.