Opening form after error

[MMcCarthy]

No

Sub Form_Load()
On Error Go To Err_Form_Load

   ' your code here

Err_Form_Load:
	Select Case Err
	Case 0 
		Resume Next
	Case 2580 	 ' catch the specific error
		call FormFIX
	Case Else   ' All other errors will trap
		Beep
		MsgBox Err.Description
		Exit Sub
	End Select
	Resume 0 

End Sub

[nico5038]

Hmm, why not test the table *before* opening the form?
Use e.g.:

dim rs as DAO.recordset
set rs = currentdb.openrecordset("tblCen")
IF rs.eof and rs.bof then
   ' rebuild the table as nothing is there
endif

Docmd.openform "frmCen"

Nic;o)

[MMcCarthy]

Hmm, why not test the table *before* opening the form?
Use e.g.:

dim rs as DAO.recordset
set rs = currentdb.openrecordset("tblCen")
IF rs.eof and rs.bof then
   ' rebuild the table as nothing is there
endif

Docmd.openform "frmCen"

Nic;o)

Thanks Nico

Why didn't I think of that ... Doh!

[Rabbit]

What about an intermediary form? The original form builds the table and as the last thing it does, it opens the intermediary form. The intermediary form's On Load event closes the original form and then reopens it. The original form's On Load event closes the intermediary form.

[smiler2505]

tried it. It still crashes :(.

Is there anyway to check if a form is doing something? I can only assume that I'm trying to close it too early; it opens, causes an error, and is caught in an endless loop. But we seem to have tried everything.

[Denburt]

I think you are really REALLY close.

The Load event occurs when a form is opened and its records are displayed.

The Open event occurs when a form is opened, but before the first record is displayed.

No records error on open:

Try this:

Private Sub Form_Open(Cancel As Integer)
If Err Then Cancel = True
End Sub

[Rabbit]

Try compiling the database. Debug > Compile

Also, if you go to a line of code and press F9, it inserts a break point. When the code is running, when it gets to that line it stops and the VBA editor opens and highlights that line. F8 let's you step through the code line by line as it executes. F5 continues execution without stopping.

I often use this when I can't figure out what's wrong with my code, it allows me to pause at each step and see what's going on. A neat feature is if you hover the cursor over a variable or control name, it tells you its current value.

[MMcCarthy]

tried it. It still crashes :(.

Is there anyway to check if a form is doing something? I can only assume that I'm trying to close it too early; it opens, causes an error, and is caught in an endless loop. But we seem to have tried everything.

Try sticking a DoEvents command at the crisis point. In other words straight before the close form command. This should process all code prior to this point before attempting to close the form

Just out of curiousity have you any code in the Form Close event?

[Denburt]

I know you are getting flooded with things to try however I just created a form the following worked and hopefully might help.

Option Compare Database
Option Explicit
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Call myBad
End Sub

Private Sub Form_Open(Cancel As Integer)
If Err Then Cancel = True
End Sub

Sub myBad()
MsgBox "Darn It"
End Sub

Although I was unable to get the error message not to show everything else seemed to work. I am in a pinch got to go good luck and I will check back later. I hope some of this helps. :)

Oh and I put the sub myBad in both the form and in a separate module with no problem.

[smiler2505]

just tried the form open thing on its own, with form_onerror, with and without DoEvents...noth ing
It is definetly the DoCmd.Close acForm, "frmCSN" causing the error; without it there is no crash, but the one problem still remains, the form doesn't open automatically after the table has been recompiled.

[Denburt]

Utilizing the code in post 24 you will not need to close it, simply because it never opens and that is why you get an error if you are going to attempt to reopen it place you build table sub in a separate module follow Rabbits instructions (Post 22) and add the DoEvents as Mary suggested and it should work.

[MMcCarthy]

just tried the form open thing on its own, with form_onerror, with and without DoEvents...noth ing
It is definetly the DoCmd.Close acForm, "frmCSN" causing the error; without it there is no crash, but the one problem still remains, the form doesn't open automatically after the table has been recompiled.

Did you try Nico's suggestion of checking to see if the table exists without opening the form at all?

[Denburt]

1 more before I go I just noticed nico's suggestion and it is a good one uh post 17 also when you click the button, or whatever event you use to open this form run his routine before even trying to open the form. If needed build it then open. Sounds like a good plan there.

LOL Mary got in when I wasn't looking :)

[ADezii]

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

I don't mean to over simplify matters, and I have only had a short glimpse at the code, but to me the answer seems fairly obvious. You cannot trap this Critical Error in the Form's Error() Event. The condition of the non-existant Table which is the Record Source for the Form should be checked as soon as the Database opens. I've scaled down your code and I have arrived at a solution, hopefully it is one which you can implement:

1. Create an AutoExec Macro and have it call a Public Function, in this case it would be fCheckForfrmCSN ().
2. This Function will loop through the TableDefs Collection, and if it finds tblCwS, it sets a Boolean Variable (blnTableFound = True). This Variable is initially initialized to False though it need not be.
3. The value of blnTableFound is next examined: if it is True, meaning tblCwS does exist, frmCSN is simply opened.
4. If blnTableFound = False, meaning tblCwS does not exist, the Public Sub-Routine Rebuild_Test_Ta ble is called, then frmCSN is opened.
5. Again, if I have oversimplified the problem or did not read enough into it I apologize, but the code below has been tested and is fully functional. Remember, I tested it on a scaled version of what you have displayed.
6. Please let me know what you think - I'm very interested in seeing if this solution is acceptable.

Call from the AutoExec Macro, or from an Initial Display Form if more practical:

[B]Public Function fCheckForFormCSN()[/B]
Dim MyDB As DAO.Database, Mytdf As DAO.TableDef
Dim blnTableFound As Boolean

blnTableFound = False

Set MyDB = CurrentDb()

For Each Mytdf In MyDB.TableDefs
  If Mytdf.Name = "tblCwS" Then
    blnTableFound = True
     Exit For
  Else
  End If
Next

If blnTableFound Then
  DoCmd.OpenForm "frmCSN", acNormal, , , acFormEdit, acWindowNormal
Else
  Call Rebuild_Test_Table
  DoCmd.OpenForm "frmCSN", acNormal, , , acFormEdit, acWindowNormal
End If
[B]End Function[/B]

[smiler2505]

Missed post 16, 17 last night. loads of responses and stuff to try out!

Going to try out Nico's idea, then play around with azii's and mary's idea

I'll be back in a few minutes!