Error 3071 triggered by subform

Question

I'm running out of ideas on this.  have an unbound form with a combo box and 3 sub forms.  The On Load event of the form selects the default value of the combo box and requeries the 1st sub form. The On Current event of the 1st sub form has a code to requery the 2nd and 3rd  sub forms.  I get  Error 307 when I first open the form, but once the form is open and I change from design to form view, It all works well. 

I have tried to requery all the subforms in the On Load event of the form to no avail. The error is somehow related to the 3rd subform becauseDeleting the source object of 3rd sub form frame prevents  the error.

Answer 1

It makes a lot of sense that Access loads the controls in the order set in the Tab Order. Since the the 2nd and 3rd subforms get some of their input values from the 1st subform, I thought that  the 1st subform had to be loaded before the 2nd and 3rd subforms and they must be listed in this order in Tab Order. It turned out this was the way I had it. However, you suggested that they must be in "descending" order. I played around with the Tab Order and moved the the 3rd subform before the 1st or the 2nd or I completely reversed the order of the subform controls, but this did not eliminate the error message or make any other difference.

I also added the code that you suggested to the On Load event of the form and while I still get the annoying error message, the 3rd subform now gets populated with values when I open the form without having to go from Form to Design view and then back to Form view again. This is a significant improvement. Thank you, Scott.

Answer 2

I don't really know exactly how Access determines which to load first, but I'd assume it has something to do with the manner and sequence in which those controls were added.

Check the Tab order of those controls to insure they're in "descending" order, with the 1st subform have a "lower" tab index than the second, and the second having a "lower" tab number than the third.

If that doesn't work, try removing the 3 subform controls, and re-inserting them in the right sequence.

Even simpler might be to do something like this in the main form Load event:

Me.SubformControl1.SourceObject = "subform1"

Me.SubformControl2.SourceObject = "subform2"

Me.SubformControl3.SourceObject = "subform3"

Note too that you probably want to clear those values when the form closes:

Me.SubformControl1.SourceObject = ""

Me.SubformControl2.SourceObject = ""

Me.SubformControl3.SourceObject = ""

Answer 3

Scott, thank you for your response. The error message I'm getting is exactly as you described it. I couldn't fix the issue by moving to code to set the default value of the combo box and to requery the 1st subform to the On Open or On Current event of the main form.

I think that you are absolutely right pointing out that the cause of the error is that Access is trying to load the 3rd subform before 2nd subform is populated. What determines the order in which the subforms are loaded?

I have another form which follows exactly the same format i.e. the 1st sumbform gets it's data based on the value selected in the combo box and has an On Current event to requery the 2nd and 3rd subforms. My other form works fine, while this  one generates error 3071 when opened. This shows that there is a solution. I cannot figure out what's the difference between the two forms, why one of them tries to load the subforms in the wrong order.

Answer 4

So you're getting this error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

This may be caused by the way Access loads Subforms - it may be trying to load your 3rd subform before the others are fully populated, and Access can throw errors like this (which are somewhat meaningless in this context).

You might try moving the code to the Open event to see if it works, or to the Form's Current event. If you use the Current event, you'll probably need to use a flag of some sort to only run it one time (assuming, that is, that it only needs to run once). To do that:

1. In the General Declarations section of your form, add a variable named fRunOnce (boolean)
2. In the Form_Current event, do this:

If Not fRunOnce Then

  '/ load your subforms here

  fRunOnce = True

End If