Access runs function in control's RowSource when form opened in Design View

Question

I have an Access 2007 form which contains a combo box with the following Rowsource:

SELECT qryProjectsIHaveAccessTo.projID, qryProjectsIHaveAccessTo.projName 
FROM qryProjectsIHaveAccessTo 
WHERE (((qryProjectsIHaveAccessTo.projSupportTracker)=False));

The query qryProjectsIHaveAccessTo uses a user-defined function as the criteria for one of the columns. This function detects if a Startup routine has been run, and if not runs it.

The problem I have is this: if I SHIFT+open the database and open the form in DESIGN mode, for some reason the user-defined function starts running. This then causes errors as the Startup routine it calls tries to open a form (presumably Access cannot open a form while it is in the process of opening another form in Design mode) and sometimes I am not able to CTRL+BREAK out of it.

The same thing happens when I go to save the form in Design mode.

If I remove the RowSource string from the combo box, this no longer happens. But why would a function which is called in a query included in a control RowSource get fired when the form opens in Design Mode?

Any ideas anyone? Thanks for reading!

Why not simply move the call of that function to where it belongs: The OnLoad event of the form? — Gustav
Indeed this is odd, but would need more information to troubleshoot. It may not be a good idea to have a query running startup procedures. Perhaps instead of getting your query to run code in an unsupported way, it would be a better idea to use an AutoExec macro or the default form's on-open event, to check and/or run your startup procedures. — ashleedawg
Or assign the control's RowSource in the OnLoad event? Still doesn't address the "Why" of doing what you're doing. — dbmitch

Andre Andre · Accepted Answer · 2018-07-06T16:02:09

When you open a form in design view, Access validates that the form record source is still there, and that all the query fields that are bound to form controls are still there.

If some of these checks fail, Access shows the small green triangle on the bound controls with problems.

While doing this check, it runs the UDF in the query.

As was said in the comments, calling a startup function does very much not belong into UDFs that are called from a query. Put it into a function that is called by an AutoExec macro.

Access runs function in control's RowSource when form opened in Design View

1 Answers