Continue after Form_Error

[Rob Parker]

I suspect that I'm missing something simple here, but I can't figure
out exactly what.

I have the following code in the Form_Error event of the menu form
(frmMenu) which is set to open automatically via Startup options.
This code catches the error when the back-end file is missing, and
runs the code in the ConnectLinks function to re-connect (includes
prompt to user for datafile). The reconnect is successful, but the
frmMenu does not open after the code runs. How can I get this to
happen?

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 0 'no error
Response = acDataErrContinue
Case 3024 'broken links
MsgBox "The back-end datafile cannot be found.", , ""
ConnectLinks
Response = acDataErrContinue
Case 2580 'incorrect table(s)
Response = acDataErrContinue
MsgBox "The selected back-end datafile does not contain the
correct tables.", , ""
If Not ConnectLinks Then DoCmd.Quit
Case Else
Response = acDataErrDisplay
End Select
End Sub

There is other code in the Form-Open event (with error trapping), but
this is not causing the form to fail to open; a Msgbox statement as
the first executable line fails to run (even with error-trapping
turned off).

What am I missing?

TIA,

Rob

 

[Allen Browne]

Rob you need to take a different approah here. The timing won't work if you
use a bound form.

Create a little *unbound* form, and declare it as your Startup form. In its
Open event, try an OpenRecordset() on a table from the back end. Include
error handling, so it recovers and runs the reconnect code if the
OpenRecordset() fails. Once that's succeeded, OpenForm to get your real
startup form to open, and finally cancel the fake startup form's Open event
(so it never appears.)

 

[Rob Parker]

Thanks for that, Allen.

I've changed my forms as you suggested and it works well.

I've a couple of follow-up questions, if you've got the time to
spare ...

What is it that prevents the bound form from opening when its
recordsource becomes available? Is it simply a timing issue (ie.
before the reconnect completes, the form has decided that its
recordsource doesn't exist so it closes), or something more involved?

I originally had my menu (startup) form as an unbound form. I added a
single bound field to force a persistent connection to be maintained
to the back-end over the network, since I've seen that advocated as
being good practice. Is that correct? I bound a control on that form
to a single-field/single-record table in the backend containing the
version number, which I use to check that the latest front-end is
being used.

Rob

 

[Allen Browne]

Yes: it's a timing problem.

When Access loads the bound form, it realises that the source data is bad
and fires the Error event *before* running your reconnect code. Using an
unbound form is a simple workaround.

Holding a connection to the back end open can help performance. I think Tony
advocates that approach here:
http://www.granite.ab.ca/access/performancefaq.htm

It's not something that I find necessary. Perhaps it could increase the
chance of corrupting the file, if the connection is lost while still open?

 

[Rob Parker]

Thanks again Allen,

Rob

Yes: it's a timing problem.

When Access loads the bound form, it realises that the source data is bad
and fires the Error event *before* running your reconnect code. Using an
unbound form is a simple workaround.

Holding a connection to the back end open can help performance. I think Tony
advocates that approach here:
http://www.granite.ab.ca/access/performancefaq.htm

It's not something that I find necessary. Perhaps it could increase the
chance of corrupting the file, if the connection is lost while still open?