Search code examples
ms-accessdrilldownms-access-2000

Why does MS Access subdatasheet sometimes require additional clicks to render?

I'm using MS Access 2003 to open a MS Access 2000 .mdb that links to a SQL Server 2005 Standard back-end (I've tested and replicated my issue using both MSSQL and local Jet tables so it would be counter-productive to tag this question with SQL Server).

I have a subdatasheet that has 11 records for every record in a subform. If the record count in the subform is low (7-10 records), the subdatasheet little [+]'s behave as expected.

However, when the record count in the subform is on the order of 40-48 records, the behavior changes. I click the little plus sign / expansion icon and Access expands the area as necessary, but it is entirely white/blank and data doesn't show. I must click on the subform record first, then click on the expansion icon before the subdatasheet shows any data. There are other odd behaviors under this circumstance, as well, but this is the only consistent and reliable way I've found to make the subdatasheet's data appear for that subform record.

The following screenshot shows a successful expansion using the trick I discussed as well as the blank data scenario:

screenshot of the subform and subdatasheet

Google searches have proved fruitless, the following is the best I could find:

Also note that adding a subdatasheet to a large table can adversely affect the performance of the table. -- MS Office article, Add a subdatasheet

I don't think 40-48 records qualifies as "large" nor do I believe "performance" is synonymous with "odd behavior".

Things I've tried:

  • Making the subdatasheet's source query ReadOnly.
  • Changing SubDatasheetName from [Auto] to source query explicitly declared.
  • Limiting the number of records in the subdatasheet's source query so that each subform's record only has 2-4 subdatasheet rows.
Solution

  • I believe I have found a suitable workaround.

    Putting:

    Me.Refresh
DoEvents

    in the OnCurrent event of the main form seems to do the trick for most situations. There is still an occasional quirk, but simply changing something in the datasheet (not subdatasheet) seems to make Access load all the data and it displays and behaves as expected.

    Additional fixes/workarounds are appreciated, welcome, and will be voted upon appropriately.