Subform control blank after form opened

Question

My problem is that the subform control remains blank after opening form, but it is populated when the view is changed from design to form view. This subform is located on a tab control; another subform located on different page of the same tab control loads just fine. The difference is that the one the doesn't load when the form is opened is based on the third query in a cascade of queries; the first query selects records in part based on input from an option group, two combo boxes and one textbox on the main form, the second query then calculates the cumulative product of a field and the third query selects certain records from the second query.

The following steps failed to resolve the problem:

Link Master/Child Fields relationship was established (the subform doesn't need to have this link))

On Open & On Load events of the form:        Me.Requery

Me.QuantATRSumSub.Requery

I think that the problem may be that the form attempts to populate the subform before the default values of controls that provide the criteria are loaded and the underlying query can generate the required dataset. Please let me know if you have a solution.

Answer 1

Sometimes developers get a bit too adventurous with the form's AllowAdditions and DataEntry properties. Make sure the former is set to True, the latter to False. The reverse causes a blank form.

Answer 2

I find a solution with the help of Tom van Stiphout, who in response to another question about VBA code syntax designed to delay the loading of the subform until the main form is loaded, suggested to delete the Record Source on property sheet of the subforms and add it into the Form_Load of the main form with a sytax like this:

Me.mySubformControl.Form.RecordSource = "myqueryreferencingparentform"

This is really simple and work very well.

Answer 3

You may need to Refresh that subform when the form opens. To do that:

Me.NameOfYourSubformCONTROL.Form.Refresh

Where NameOfYourSubformCONTROL is the Name of the Subform Control on your form, which may or may not be the name of the form you're using as a Subform.

Answer 4

Hi Scott, I inserted the line that you suggested into the 'On Open' event of the main form. It resulted in a Run-time error '2467'.

Answer 5

Hi Tom,

Thank you for response. I have looked into this and now I checked again, the Data Entry was set to No and the Allow Additions/Deletions/Edits/Filters were all set to Yes. Unfortunately, I need another solution.

Andras