Why am I unable to load a subform when it is bound to a specific stored procedure?

Question

Summary

In a Microsoft Access 2010 database (accdb), I have a form that dynamically loads other forms into a subform object on the main form. The forms used in the subform object are bound to ODBC pass-through queries that execute stored procedures to return recordsets. I can't figure out why I can use one sproc and it works perfectly fine, but if I bind the form to another sproc, it fails to load the subform.

Technical Walkthrough

I have two pass-through ODBC queries. qryGood and qryBad. They use identical ODBC connection strings (ODBC;DRIVER=SQL Server;SERVER=MyServer;UID=MyUser;Trusted_Connection=Yes;DATABASE=MyDatabase), and the SQL behind them is identical, but pointing to two different SQL stored procedures on the SQL 2012 database server.

qryGood source: exec spGoodProc 123456

qryBad source: exec spBadProc 123456

The SQL behind the sproc is very simple. Return records from a single table, filtering by the ID passed as a parameter. (Some will do more complex things, but I am just focusing on a simplified example here that demonstrates the problem.)

The RecordSource property of frmMySubform is set to qryBad.

The subform SourceObject is set via VBA code: sfrmMain.SourceObject = "frmMySubform" No errors are thrown at this point. While the SourceObject property now returns frmMySubform, the .Form object does not seem to be set.

I then try to reference a property on the subform: Debug.Print sfrmMain.Form.Name This fails with error 2467: The expression you entered refers to an object that is closed or doesn't exist.

I can then open frmMySubform in design view, change the RecordSource property to qryGood and it works just fine. This seems to point to a problem with spBadProc that only manifests itself when used as the RecordSource on a subform.

What I Have Tried

In an effort to troubleshoot this problem, I have used the process of elimination to narrow this down as far as I can, but I am still not understanding why the one sproc works and the other doesn't. Both return records just fine in SQL and when running the pass-through query directly. Both work fine when opening the form directly. It only becomes a problem when the form is set as a SourceObject in a subform control.

I have used sp_procedure_params_rowset to compare the parameters in the sprocs, and they are identical. I have compared the data types of the columns in SQL and there is nothing new or different in tblBad that isn't in tblGood. I have also tried profiling the SQL server while setting the form, and it seems to call the sproc just fine. I didn't see any clues when comparing the trace between the bad and the good calls.

Setting the RecordSet directly to an ODBC link to tblBad works just fine (and I presume a view would be fine as well) but having the simple stored procedure wrapper somehow triggers the error.

I have also compared the security, properties and extended properties for spGoodProc and spBadProc and they are identical.

My Question

What can I do on the troubleshooting side to reduce this down further? Has anyone out there encountered similar issues with bound sprocs on subforms? I am working on a very complex database with hundreds of forms, tables and queries, so I would really like to understand why this is occurring before I go too far down this path.

Thanks in advance for any insight you are able to share on this perplexing problem. :-)

Answer 1

Found it!

After tracing it back to something with the specific table, I removed all constraints, keys, and then columns from a copy of the table, systematically testing to see if I could pinpoint the problem. Sure enough, it was a specific column name in the stored procedure!

Simply aliasing this column to a different name solved the problem. (See below for expanded details)

Update after Further Testing

After additional testing to further pinpoint the issue, I think I now understand why this was occurring. When you link an ODBC table and specify a unique (key) column, Access will automatically attempt to set the LinkMasterFields and LinkChildFields to the key column name when a subform is loaded and the subform has a column with the same name. While this works fine with linked tables or views, it does not work when the RecordSource of the subform is set to a stored procedure.

If you attempt to do this by manually adding the subform, you will see the following notification:

However, if you set the subform target through VBA code, you don't get any warning or error message. It simply doesn't (fully) load the subform. @Albert D. Kallal, you were right on about this being related to a master/child fields issue!

I was able to consistently reproduce the issue in a test database file in both Access 2010 and Access 2016. If you would like to see this for yourself, you can use the following steps to reproduce it:

1. Create a SQL table with a PrimaryID column.

CREATE TABLE [dbo].[tblBugTest](
    [PrimaryID] [int] NOT NULL,
    [TestColumn] [nchar](10) NULL
) ON [PRIMARY]

2. Add a couple test records to the table you just created.
3. Create a Stored Procedure to return the records.

CREATE PROCEDURE [dbo].[spBugExample]
AS SELECT * FROM tblBugTest

4. Create a blank Microsoft Access database (accdb).
5. Using ODBC, create a linked table to tblBugTest.
   • Important: Select PrimaryID as the unique column.
6. Create a pass-through query named qryPassThrough using ODBC to the same database, and set the SQL to exec spBugExample.
   • Open the query to verify that it returns records.
7. Create three blank forms in the database. frmMain, frmSubForm, and frmBlank.
8. Add frmBlank as a subform to frmMain. Name the subform sfrmSubform.
9. Set the RecordSource of frmMain to the linked table.
10. Add a button to frmMain to switch the subform from frmBlank to frmSubForm.

Private Sub cmdShowBug_Click()
    With Me.sfrmSubform
        .SourceObject = "frmSubForm"
        Debug.Print .LinkMasterFields
        Debug.Print .LinkChildFields
        Debug.Print .Form.Name
        .SourceObject = "frmBlank"
    End With
End Sub

11. Set the RecordSource of frmSubForm to qryPassThrough.
12. Drop a couple bound controls onto frmSubForm.
    • Test frmSubForm by itself. It should load a record from tblBugTest.
13. Open frmMain and click the button. It should throw an error.

If you step through the code, you will see that before setting the SourceObject, the LinkMasterFields property is blank. After setting SourceObject, you can hover over LinkMasterFields and see that it is now set to the PrimaryID column.

Workarounds

Changing any of the following will work around the error by avoiding the problematic auto-linking of the master/child fields.

• Delete and relink the linked table, this time not specifying a unique column.
• Alias the column in the Stored Procedure to a different name than the unique column.
• Clear the RecordSource property of the parent form.
• Clear the subform RecordSource property and set the RecordSet after loading the subform.
• Use a view or linked table instead of a stored procedure in the subform.

Answer 2

Keep in mind that the subform data source will be attempted to be loaded BEFORE the main form loads. What this suggests is that on the main form's load event, you will

First setup the PT query. Then set the OBJECT source of the sub form.

In other words, the source object of the sub form control should be blank.

You code then to set up the PT query will be:

With currentdb.queryDefs("qryGood")
   .SQL = "EXEC spGoodProc " & 123456
end with

Of course you can replace the 123456 with a varible, or even a value from a text box (from the main form).

Now that the PT query is setup, you THEN are to set the form that the sub-form is to load.

So, after above code, we then have:

me.mySubForm.SourceObject = "name of subform goes here"

So, it should be about a total of 4 lines of code. And as above shows, you don't even need any connection string stuff in your VBA code.

So, just keep in mind: Setup the PT query as per above. You can then launch a report, or even a form, or in this case set the form that the sub-form control is to load. This also suggests/hints that you need to remove the source object of the sub form control (leave it blank).

You can have the sub-from source object set, but then this would suggest that you setup the PT query source as per above BEFORE you launch the main form with the sub form based on the PT query. As noted, this set of steps is required since the sub form actually load and resolves it data source BEFORE the main form displays and renders. So, by leaving the source-object blank for the sub form, then you the developer re-gains complete control over the order of loading.