I have an access application with a backend Sql Server. All data is passed
from the back-end (remote Sql Server) to unbound forms via stored
procedure. I then set the recordsource of the form via vba to the recordset
returned by the stored procedure. If an unexpected error occurs (I need to
add more error trapping but it is hard to imagine every scenario that can be
created by an end user) or the form is closed under certain conditions, the
recordsource property shows a reference to the stored procedure and the next
time the form loads it will generate an error as a result.
Is there a way to ensure that this does not happen?
There are two ways I thought of but not sure if they will work.
One is to set each of the form and subform properties to allow design
changes only in design view and convert the database to an mde file. I am
not sure if this will prevent access from filling in the values itself or if
it will prevent me from assigning the recordsource to the recordset in vba.
I will be converting it to an mde file anyway so no one has access to the
underlying code.
The other idea I came up with is to set the recordsource of each form to
null just before opening. This way when the form first opens there is no
recordsource and then it is assigned programmatically after it is open. I
would just need to get the timing right.
I have over 100 forms/subforms in the app and it occurs somewhat randomly so
I was hoping someone could provide some insight before I make all the
modifications in the hope that it would solve my problem. Has anyone
encountered this before and would any of these two solutions work? Any
other ideas?
