CODEWITHSUNDEEP
vbams-accessms-access-2007startupdesignmode
BiigJiim
BiigJiim

Reputation: 307

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

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!

Upvotes: 1

Views: 218

Answers (1)

Andre
Andre

Reputation: 27644

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.

Upvotes: 1

Related Questions