Opening form after error

[smiler2505]

Sub Form_Load()
 On Error GoTo Notable

Notable:
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

Error still comes up, and the table is not built. FormFIX is a Sub (by default, Public) in another module called nonformsubs

[smiler2505]

Form open, form load etc do not work. The error of there being no record source for the form comes up first.

With the 'checking on database load'; is there anyway to loop it, so it always checks for the presence of the tables; if they don't exist, build them? Or to make a preliminary check before forms are opened?

[NeoPa]

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'm afraid I haven't had a chance to go through the whole thread yet, but I had an idea which may help you. Apologies if this has been propounded already.

First, before I go on, have you tried creating the table then trying a .Requery? I'll assume you have considered that already.

Otherwise, think about creating another form, whose job it is simply to check for the existence of the table (and create it if/where necessary) then opens up your form and closes itself. You may even get away with having an invisible form to do the job if you prefer. I'm not sure that would work but you could try it if that appeals.

[smiler2505]

Tried it!

Basically, I can't open a form using DoCmd.OpenForm, because no form opens, and if I close the form then open it, using DoCmd.Close, access crashes, even if it is in a seperate sub, form, or module.

So, I think I'll just put an MsgBox at the end of the script and ask the user to reopen the form because an error has occured and has now been fixed.

That works at least! It doesn't solve the problem, but it'll do for an alternate solution at least. Unless anyone has any better ideas?

[NeoPa]

Tried it!

Basically, I can't open a form using DoCmd.OpenForm, because no form opens, and if I close the form then open it, using DoCmd.Close, access crashes, even if it is in a seperate sub, form, or module.

So, I think I'll just put an MsgBox at the end of the script and ask the user to reopen the form because an error has occured and has now been fixed.

That works at least! It doesn't solve the problem, but it'll do for an alternate solution at least. Unless anyone has any better ideas?

You leave us all guessing what exactly it was that you tried.
I find it hard to believe you've tried my suggestion and got those results, but if you can be a little clearer in what you're saying, I'm sure we can all understand.

[Rabbit]

Did you try compiling and setting a break to step through the code line by line yet?

[smiler2505]

I did the compiling thing, that's how I found out what was causing the crash.

On the form_error event, I included the code to build the table, and then put 'Me.Requery', 'Me.Repaint', and 'Me.Refresh', all to no effect; the table could not refresh when the form was opening.

[NeoPa]

Thank you. That's much clearer :)

Perhaps you'd like to give post #33 a try?

[smiler2505]

I have tried:

1. Building the table, and reopening the form after this has been done.
This built the table, but did not reopen the form.
2. Building the table, closing the form, and reopening it after.
This caused Access to crash, but if the form was closed after the table was built, the table existed.
3. Building the table, refreshing the form.
Table is built, but no form appears.
4. Building the table, and using 'DoEvents' before closing the form, and reopening it.
Access crashed, but built the table.


All of these were tried in various order, under various different form events.


Over the course of it I have learnt:
1. It is definetly the DoCmd.Close acForm, "frmCSN" that causes access to break.
2. Any action must start in the Form_OnError event.


Hope that helps clarify things. I'm sure inevitably there are things I have missed. I haven't as yet done the database (on load) idea.

[smiler2505]

Thank you. That's much clearer :)

Perhaps you'd like to give post #33 a try?

will do.
does anyone know if there is a timer function in vba?

[MMcCarthy]

I have tried:

1. Building the table, and reopening the form after this has been done.
This built the table, but did not reopen the form.
2. Building the table, closing the form, and reopening it after.
This caused Access to crash, but if the form was closed after the table was built, the table existed.
3. Building the table, refreshing the form.
Table is built, but no form appears.
4. Building the table, and using 'DoEvents' before closing the form, and reopening it.
Access crashed, but built the table.


All of these were tried in various order, under various different form events.


Over the course of it I have learnt:
1. It is definetly the DoCmd.Close acForm, "frmCSN" that causes access to break.
2. Any action must start in the Form_OnError event.


Hope that helps clarify things. I'm sure inevitably there are things I have missed. I haven't as yet done the database (on load) idea.

Have you tried Nicos suggestion of checking for the tables existance without opening the form at all and only opening the form after the table is found or built?

[smiler2505]

I figured it out!
DoEvents wasn't working, but it had something to do with time; I had to wait for the error code to finish.

So...

Sub Form_Error(DataErr As Integer, Response As Integer)
 If DataErr = "2580" Then
  
  Response = acDataErrContinue
  DoCmd.OpenForm "FormFix"
  
 End If
 
End Sub

Sub Form_Load()
	Me.Visible = False
	Me.TimerInterval = 2
	
End Sub
Sub Form_Timer()
 DoCmd.SetWarnings False
 
 On Error GoTo ExitCreateCwS
 
'Build table
 
ExitCreateCwS:
 
 DoCmd.OpenForm "frmCSN"
 DoCmd.Close acForm, Me.Name, acSaveNo
	
End Sub

[NeoPa]

Good for you.
It's great when it all comes together :)