Solved: Opening a form after an error.

[smiler2505]

I have a situation where there may be no table for a form; on error, the table is rebuilt and all is good; except to open the form, I have to click the form again. I tried

DoCmd.OpenForm "frmCSN"

but nothing happens. I think its because the form is already open, with the Sub form_OnError. But if I try and close the form, it crashes access and I get asked to send an error report, because the code is running from the form presumably. So how do I get round it? I've tried another form, which if it isn't called from the error works fine. I've tried to run the close function from another sub, and another macro, but every time I try and close the form where the error occurs it fails

[JimmyTheHand]

Hi :hi:

If I got it right, you want the form to stay open after the error handler routine has finished.

I think there might be a way to go around the problem. I don't want to go into detailed explanation, because I'm not sure my idea will work. I have some questions, instead, because what I have in mind depends on how you open the form. By doubleclick in the DB window? Or by clicking a button on another form? If it's by doubleclick, is there another form, which is surely open at the time when you try to open this one?

The perfect answer would be if you could upload a sample DB to experiment with.

Jimmy

[smiler2505]

I will in a bit, just give you a bit more background if it helps...
If I open one of the forms I have constructed, if a table does not exist I want it to build the table. The most obvious way is (I thought) to use the Form_onerror sub. If there is no table available, it will give a '2580' error, and on this event I wanted to build the table. Then, I wanted to reopen the form, preferably automatically. If I try and open the form straight away, after building the table, by using the code DoCmd.OpenForm "frmCSN", nothing happens. If I use the code DoCmd.Close acForm, "frmCSN, followed by DoCmd.OpenForm "frmCSN", Access crashes :(.

[JimmyTheHand]

Yes, thanks for confirming that I comprehended your problem. However, you have not answered any of my questions...
Nevermind.
The workaround I propose is this.

1. Create a new form with no controls on it. (In my example, it's called "frm2".) Create these event procedures for the form:
Private Sub Form_Load()
Me.Visible = False
Me.TimerInterval = 10
End Sub

Private Sub Form_Timer()
DoCmd.OpenForm "frmCSN"
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

2. Put this single line at the very end of the OnError procedure of frmCSN
Private Sub Form_Error(DataErr As Integer, Response As Integer)
'.... body of error handler
DoCmd.OpenForm "frm2"
End Sub
With this trick you open a second form, whose sole purpose is to open frmCSN again, after the missing table is rebuilt. When loading, frm2 will appear for a blink, then disappear again, when its Visible property is set to False. If you want to minimize the flashing effect, you may want to reduce frm2 to a very small size.

I'm not sure about the theory, but my experience is that the error handler routine of frmCSN must be finished before frmCSN is reopened. That is why I delay it with frm2's Timer. A TimeInterval of 10 milliseconds is an estimation. It should be enough for the error handler to finish. If needed, it can be increased to 100 or 200 ms, of course.

Jimmy

[smiler2505]

Sorry didn't notice your questions! it was a form opened from the database window. And your solution worked. Thanks a lot for your time, sorry about my lack of clarity.