Subform wont update

Question

I have a form with two subforms. Each subform has a field linked to a field on the main form.

One of the forms loads correctly, the other on comes up blank.

If I trigger a change on the main form that causes the linked parent field to change the subform updates and acts normally there after.

If  put a button on the main form and use the button to trigger a requery on the from, then it loads and afterwords behaves normally.

When the form loads, I can see the sub form appear, but subsequently it flashes and goes blank.

It is supposed to ling to a part name field on the main form.

Once I force it to link, using the button or a new part selection on the main form, it links and both subforms select new data properly

as I scroll through the parts on the main form.

Given that the form works properly, how can I force it to reload, refresh,, requery, or whatever after the form is fully loaded?

I cant seem to find An event that will trigger it..

Now,  I also have a listbox, on the main form. It is populated by a query that depends on the partName.

It also does not populate automatically, so I have two command buttons:

Load attributes loads a list of available attributes for the part.

The user can multiselect from the list box and press the save button, which runs som code to save all the info on the form and use it to print a report.

All of this works

Except, when I populate the list box the second subform goes blank again. 

It will repopulate if I push the command button to requery it.

Answer 1

Something is causing your form to requery/refresh obviously, and you'll have to discover what that can be. Check the Form Timer event to insure nothing is happening there.

The main form  Record source is a simple query against a table.

There is code on the open event that loads the user name and date. There is no code on any other event.

Also, check the subforms themselves to insure no code is running in the Load, Open or Current events of those subforms.

There are Actually Five subforms.  There is no code on any of the events. Each one depends on  the Link Child Fields master field feature and three of them work properly. The main form has five dummy fields which just report what record value is current on each of the subforms. Each subsequent subform uses the next dummy field as the parent field to link to.

Can you post the Recordsource you're using for the Mainform and both Subforms?

Main Form:

SELECT tblDataDocs.*, tblDataDocs.VDDGroup FROM tblDataFormatDocs WHERE (((tblDataDocs.DocID)=[forms]![frmMain]![SelectedDocID]));

SubForm 4

qryWPPartIdents4

This query works properly and the form loads as expected.

It basically sys give me two values from the data table based on two attributes.

The SQL is:

SELECT tblTelemetryData.TelemetryValue AS Ident, tblTelemetryData_1.TelemetryValue AS PartName, tblTelemetryData.VDDGroup, tblTelemetryData.TelemetryAttribute, tblTelemetryData_1.TelemetryAttribute, tblTelemetryData.TelemetryDataID

FROM tblTelemetryData LEFT JOIN tblTelemetryData AS tblTelemetryData_1 ON tblTelemetryData.TelemetryID = tblTelemetryData_1.TelemetryID

WHERE (((tblTelemetryData_1.TelemetryValue)=[forms]![frmWPBuilder]![txtPartName]) AND ((tblTelemetryData.VDDGroup)=[forms]![frmWPBuilder]![txtVDDGroup]) AND ((tblTelemetryData.TelemetryAttribute)="Ident") AND ((tblTelemetryData_1.TelemetryAttribute)="PartID"));

Subform5

qryWPOutput2

This query works properly when called manually.

When called by the form, the form loads and then goes blank.

It will then  reload properly if I execute the command button with the command;

Forms!frmWPbuilder.frmWPOutput.Form.Requery

It will also reload if I select any record on subform3, other than the first record.

Actually, both form 4 and 5 load momentarily, then both go blank.

Then form four reloads but form 5 does not.

This query selects part limits and part attributes based on the part identification and Mode or test period.

Where the part name and test mode is selected in the prior subforms (Subform 3 and 4)

The SQL is

SELECT DISTINCT qryWPPartIdents.Ident, qryWPPartIdents.PartName, tblDataAttributes.AttributeTypeName, tblDataLimits.LimitCondition, tblDataLimits.LimitOperator, tblDataLimits.LowerLimitValue, tblDataLimits.[Nominal Value], tblDataLimits.UpperLimitValue, tblDataLimits.LimitUnits

FROM (qryWPPartIdents LEFT JOIN tblDataModes ON qryWPPartIdents.TelemetryDataID = tblDataModes.IdentId) LEFT JOIN (tblDataLimits LEFT JOIN tblDataAttributes ON tblDataLimits.LimitDataID = tblDataAttributes.LimitDataID) ON tblDataModes.ModeDataID = tblDataLimits.ModeDataId;

You might also check the Recordsource of your Subforms when this issue occurs. You can do that by typing this in the Immediate window, while in the Code page of your main form:

?Me.NameOfYourSubformControl.Form.Recordsource

Where "NameOfYourSubformControl" is the name of the Subform Control on the main form. This may or may not be the same name as the form you're using as a Subform, so be sure you use the right name.

The name is the same for the sub form and the control name used on the main form.  But when I run this it returns the message "Variable not yet created in this context."

Answer 2

Something is causing your form to requery/refresh obviously, and you'll have to discover what that can be. Check the Form Timer event to insure nothing is happening there.

Also, check the subforms themselves to insure no code is running in the Load, Open or Current events of those subforms.

Can you post the Recordsource you're using for the Mainform and both Subforms?

You might also check the Recordsource of your Subforms when this issue occurs. You can do that by typing this in the Immediate window, while in the Code page of your main form:

?Me.NameOfYourSubformControl.Form.Recordsource

Where "NameOfYourSubformControl" is the name of the Subform Control on the main form. This may or may not be the same name as the form you're using as a Subform, so be sure you use the right name.

Answer 3

When the form first appears, both sub forms are correctly populated.

Then, something causes a redraw. the forms flash, and when they return, only one is populated.

Answer 4

Yes, the form depends on the master and child links.

There is some code on the Open event of the master code that reads the  username and date into fields on the form.

There is no other code running on other events, until the user takes some action.

I have other forms that are very similar, but based on different tables that do not have this problem. They have the same code to read the user name and date.

The field on the master table the sub forms are linked to is an interim field. It actually reads the value from a third sub form and puts it on the master form so I can use the master and child links.

Originaly, both the sub forms were linked to the same field.  I thought that might be causing the problem , so I put another duplicate dummy field for the second sub form. no difference.

What is strange is that the form always behaves properly if I push the command button to requery it, and it continues to work properly until I execute the code to populate or read the values from  the attribute list box.  This is done from another command button.

I deleted the subform, made a new copy of it and pasted it in, with the same results. The query it depends on runs properly.

I am thoroughly mystified, since I have several other main forms in this app that are very similar.

Answer 5

Have you set the Master and Child links for each Subform? If so, Access will fill them when the mainform loads or changes records.

Do you have any code running that impacts the Recordsource of your subforms? For example, in the Form's Open/Load/Current event?