Datasheet with dynamic recordsource shows no records in Access 2007, but works in Access 2003

Question

I have an Access 2003 database which uses a main form with a datasheet in a subform. The main form allows the user to select from a menu which updates the recordsource of the subform. The subform also updates the number and type of fields available for editing based on the number and types of fields in the form's recordsource. It's basically a dynamic datasheet generator. This works just fine in Access 2003 and has been for years. If I open the same database in Access 2007 (full or runtime), most of the menu selections work. However, if I choose any menu option that references one particular table, the subform shows column headers, but does not show any rows of data or display any errors. It's as if the query is returning zero rows. Why would there be a difference in Access 2007? Did they add new reserved words?

Things I've tried:

• Updated every field in the table to ensure there are no null values (no change)
• Renamed every field and the table name just in case there are new reserved words (no change)
• Compacted and repaired the front end and back end (no change)
• Tried including and excluding fields one by one to see if anything changed (no change)
• Put the form's recordsource in a new query. (it returned the expected number of rows)
• Checked to make sure the form is not set to data entry mode. (It's not)
• Checked to ensure that no filters were being applied in code. (none were)
• Checked to make sure the query is updateable in Access 2007. (It is)
• Selectively deleted chunks of data from the source table. (no change)

I'm stumped.

Answer 1

As you mentioned, this one is a stumper. All I can offer is some WAG type thoughts.

Regarding that list of items you've checked, did you check them from design mode or at runtime while the form was running?

If not the latter, think I would run the form in break mode for debugging to verify the properties are still what you expect. Or you could add some Debug.Print statements to the subform's Current event. Among the first I'd look at are:

Debug.Print Me.Recordset.RecordCount
Debug.Print Me.RecordsetClone.RecordCount
Debug.Print Me.RecordSource

I don't know that you actually need to inspect RecordCount for both Recordset and RecordsetClone; I'd just do both to cover the bases.

Your dynamic datasheet generator code must be doing some complex stuff. Maybe it would help to show us that code.

Are you able to build a regular static form which operates normally based on the same query which causes trouble for the dynamic datasheet subform?

Answer 2

I was able to finally resolve the issue. As I noted above, my datasheet is a subform. The parent form sets the options that determine how the subform will be built. While the datasheet is being built, the subform's sourceobject is replaced with a blank form to hide the prior datasheet and present a smooth transition to the new datasheet. It works beautifully in Access 2000/2003.

When the blank form is replaced with the new datasheet in Access 2007, I found that Access is automatically assigning the primary key of the subform data to the LinkMasterFields and LinkChildFields properties of the unbound parent form. Those properties had previously been blank, and I never set them in code. Perhaps this is as an attempt by Access 2007 to be helpful rather than a bug, but the behavior difference is not noted in any tech reference I can find, including the built-in help file. Since the parent form is unbound, it has the effect of filtering out all of my subform records. If I explicitly set LinkMasterFields="" and LinkMChildFields="" during the form substitution step, everything works as it did before. Hooray!

Again, the solution is that when setting the sourceobject of a subform, be sure to explicitly set the linkmastfields and linkchildfields properties to prevent Access from doing it for you. I hope this saves someone the hours of frustration I experienced.