1

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.
coge.soft
  • 1,664
  • 17
  • 24
  • 1
    AFAIK, you've found the only way to make the Subdatasheets display properly in that context ... first set focus in the containing subform. There seems to be differing opinions re the usefulness of Subdatasheets. Personally I quit using them because I felt their annoyances outweighed their value. In the situation you described, I would be tempted to substitute another nested subform for the Subdatasheet. Those have been less troublesome, IME. – HansUp Dec 02 '12 at 18:18
  • @HansUp , thank you for the comments. What would another nested subform look like? Would it be a 2nd subform on the main form that would requery upon click on a record in the 1st subform? I did not set up the subdatasheet, and I advised against its use, however my client insisted. – coge.soft Dec 02 '12 at 18:30
  • I would embed the second subform in the first subform. However, if this is about client happiness, you may be SOL! :-) Perhaps they might be less unhappy with the second subform vs. remembering to click the subform row before expanding the subdatasheet? Ahem ... you'll find out. :-) – HansUp Dec 02 '12 at 18:33
  • Something else you could do to allow them to view the drill-down data is substitute a command button for the subdatasheet plus sign. And that button could open a pop-up form to display the detail rows. It may not satisfy your client's wishes. But, if it can, it would be easy to implement. Look at the WhereCondition option to DoCmd.Openform. – HansUp Dec 02 '12 at 18:44
  • @HansUp , do you know if this presumed display bug was corrected in 2003 or better? – coge.soft Dec 03 '12 at 22:37
  • No. I don't even know whether Microsoft treats it as a bug they intend to ever fix. Unfortunately I can't say much useful about subdatasheets because I quit using them. – HansUp Dec 03 '12 at 22:51

1 Answers1

0

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.

coge.soft
  • 1,664
  • 17
  • 24