Subform data sometimes missing

[egghead]

In a user's Access database, the data is in one .mdb file and the form and reports are in another. There is a customer form with two subforms (one of them is for Orders). The data in the subforms sometimes does not display. If the user exits the database and reopens it, the data will normally appear. The issue is becoming more frequent and sometimes the database has to be closed and reopened multiple times before the subdata reappears.

I have run Compact and Repair on the forms/reports database, and converted it to the new .accdb format. I have not done either to the data .mdb file.

Looking for ideas as to what is causing this and potential solutions. Thanks in advance!

[MarkLiquorman]

I would assume it is a problem with the linking fields in the subform control, and/or another filter is being applied to the subform.

[LasVegan]

If the problem is intermittent as you describe it, the linkage is unlikely to be the problem. With bad linkage it would never work at all.

I have this problem in a form that is very busy - three subforms against three tables that have several hundred thousand records each and are growing daily. In previous applications the problem has always been solved by removing a subform, or by making it execute more efficiently. If it is already optimized, as in my case, my own choice is to remove a subform, make it available as a popup. The users are unenthusiastic about that, but they have to make a choice and then quit complaining about their choice. In the end, it's all about how much stuff we're trying to cram into memory. The fewer demands you can make, the more reliable the screen will be and the white bubbles, as one guy calls them, will no longer appear.

[WendellB]

Another trick we've found that helps to minimize the delay in displaying subform data is to dynamically link the subform to its parent when the parent form is loaded. Using that approach we've successfully displayed sibsets of data from multi-million record tables based on user selected criteria with subsecond response times (using a SQL Server backend).

[LasVegan]

Wendell, could you elaborate on that solution? When you say dynamically linking the subform at load time, what specific commands do you issue? Could you give us an example?

Dale

[WendellB]

What we do with the subform control is set the Link Master Fields and Link Child Fields properties to their appropriate indexes - the master has the name of the Main table index, and the Child has the name of the foreign key in the related table. However we leave the Source Object property blank. Then on the Lodge event of the main form we have code that looks like this:

Me.subformControlName.SourceObject="subformFormName"

If you have only one subform you can do it on the main form OnLoad event. In the example I cited, we actually do it on a tab control Change event as we have 6 separate subforms. By doing that the main form loads quickly, and when the user clicks on a tab, then we check to see if the Source Object property has been set, and if not, we execute the code above. We also start with the subform control not being visible and set it to visible after setting the Source Object property. In the example, we have some 400K main form records, and in the worst case about 3 million subform records, and the user sees sub-second response times virtually all the time. Of course having all the indexes in the right places is a key factor. Hope that clarifies how we approach it.