Connecting Tech Pros Worldwide Forums | Help | Site Map

Opening form after error

Member
 
Join Date: Apr 2007
Posts: 72
#1: Apr 30 '07
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
Expand|Select|Wrap|Line Numbers
  1.   DoCmd.OpenForm "frmCSN"
  2.  
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

Expert
 
Join Date: Apr 2007
Posts: 192
#2: Apr 30 '07

re: Opening form after error


Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers
  1.   DoCmd.OpenForm "frmCSN"
  2.  
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 haven't tried this, but, if the form is already open, and having rebuilt the table within your error handling process, try me.requery or me.refresh so that the open form tries again to access your data
Member
 
Join Date: Apr 2007
Posts: 72
#3: Apr 30 '07

re: Opening form after error


It opens in design mode the first time I edit the vba, but after that the result is the same. Click, and no form opens.
msquared's Avatar
Administrator
 
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#4: Apr 30 '07

re: Opening form after error


In the Sub form_OnError code add the line.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close acForm, "frmCSN"
Mary
Member
 
Join Date: Apr 2007
Posts: 72
#5: May 1 '07

re: Opening form after error


Quote:

Originally Posted by mmccarthy

In the Sub form_OnError code add the line.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close acForm, "frmCSN"
Mary

I tried that but nothing happens (see first post) :(
msquared's Avatar
Administrator
 
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#6: May 1 '07

re: Opening form after error


Quote:

Originally Posted by smiler2505

I tried that but nothing happens (see first post) :(

In what event have you got the code checking if the table exists?
Member
 
Join Date: Apr 2007
Posts: 72
#7: May 1 '07

re: Opening form after error


form on error. When I try and open the form without a table associated I get an error saying to effect the table does not exist. I think I tried it under form load, but I still got the error

The code is
If dataerr = (the error code) Then

Build Table

Response = acDataErrContinue

End If
msquared's Avatar
Administrator
 
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#8: May 1 '07

re: Opening form after error


Quote:

Originally Posted by smiler2505

form on error. When I try and open the form without a table associated I get an error saying to effect the table does not exist. I think I tried it under form load, but I still got the error

The code is
If dataerr = (the error code) Then

Build Table

Response = acDataErrContinue

End If

And where exactly did you put the form close in this code.
Member
 
Join Date: Apr 2007
Posts: 72
#9: May 2 '07

re: Opening form after error


If dataerr = (the error code) Then

Build Table

[font=Arial]DoCmd.OpenForm "frmCSN"[/font]
Response = acDataErrContinue

End If


T'was at the end of the build table code. I also tried to launch another form and reopen frmCSN from there, without success.
msquared's Avatar
Administrator
 
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#10: May 2 '07

re: Opening form after error


You need to close the form. Then build the table and then open the form again but you can't do that in the Form_Error event because once you close the form the code is no longer in focus.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Error()
  2.  
  3.    If dataerr = (the error code) Then
  4.        call noTable
  5.    End If
  6.  
  7. End Sub
  8.  
  9. Sub noTable()
  10.  
  11.    DoCmd.Close acForm, "frmCSN"
  12.    Build Table
  13.    DoCmd.OpenForm "frmCSN"
  14.    Response = acDataErrContinue ' not sure how to cater for this as you haven't given me that part of the code
  15.  
  16. End Sub
  17.  
Mary
Member
 
Join Date: Apr 2007
Posts: 72
#11: May 2 '07

re: Opening form after error


I still get the error
The code I have is:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Error(DataErr As Integer, Response As Integer)
  2. If DataErr = "2580" Then
  3.  
  4. Call FormFIX
  5.  
  6. End If
  7.  
  8. End Sub
  9. Sub FormFIX()
  10.  
  11. DoCmd.Close acForm, "frmCSN", acSaveNo
  12.  
  13.  
  14.  
  15.  
  16. DoCmd.SetWarnings False
  17.  
  18. On Error GoTo ExitCreateCwS
  19.  
  20. Dim daodb As DAO.Database
  21. Dim daotdfCwS As DAO.TableDef
  22. Dim daofldCNum As DAO.Field
  23. Dim daofldSNum As DAO.Field
  24. Dim daofldCCanEnt As DAO.Field
  25. Dim fldCNum As Field
  26. Dim fmtffCNum As Property
  27. Dim daoidxCNum As DAO.Index
  28. Dim daofldiCNum As DAO.Field
  29. Dim fldSNum As Field
  30. Dim fmtffSNum As Property
  31. Dim daoidxSNum As DAO.Index
  32. Dim daofldiSNum As DAO.Field
  33. Dim fldCCanEnt As Field
  34. Dim fmtffCCanEnt As Property
  35. Dim daoidxCCanEnt As DAO.Index
  36. Dim daofldiCCanEnt As DAO.Field
  37. Dim tdfCen As TableDef
  38. Dim relCen As Relation
  39. Dim tdfSub As TableDef
  40. Dim relSub As Relation
  41.  
  42. Set daodb = CurrentDb()
  43. Set daotdfCwS = daodb.CreateTableDef("tblCwS")
  44.  
  45.  
  46.  
  47. Set daofldCNum = daotdfCwS.CreateField("CNum", dbLong)
  48.  
  49. daofldCNum.DefaultValue = "10000"
  50. daofldCNum.ValidationRule = "Between 10000 And 79999 And Len([CNum])=5"
  51. daofldCNum.ValidationText = "Must be 5 digits long, lie between 10000 and 79999, and unique"
  52. daofldCNum.Required = True
  53.  
  54.  
  55. Set daofldSNum = daotdfCwS.CreateField("SNum", dbText, 5)
  56.  
  57. daofldSNum.DefaultValue = "11111"
  58. daofldSNum.ValidationRule = "Len([SNum])=5"
  59. daofldSNum.ValidationText = "Must be 5 digits long"
  60. daofldSNum.Required = True
  61. daofldSNum.AllowZeroLength = False
  62.  
  63.  
  64. Set daofldCCanEnt = daotdfCwS.CreateField("CCanEnt", dbLong)
  65.  
  66. daofldCCanEnt.DefaultValue = "0"
  67. daofldCCanEnt.ValidationRule = ">=0"
  68. daofldCCanEnt.ValidationText = "Please enter number of candidates"
  69. daofldCCanEnt.Required = True
  70.  
  71.  
  72.  
  73. daotdfCwS.Fields.Append daofldCNum
  74. daotdfCwS.Fields.Append daofldSNum
  75. daotdfCwS.Fields.Append daofldCCanEnt
  76. daodb.TableDefs.Append daotdfCwS
  77.  
  78.  
  79.  
  80. Set fldCNum = daotdfCwS.Fields("CNum")
  81.  
  82. Set fmtffCNum = fldCNum.CreateProperty("Format", dbText, "General Number")
  83. fldCNum.Properties.Append fmtffCNum
  84. Set fmtffCNum = fldCNum.CreateProperty("DecimalPlaces", dbByte, 0)
  85. fldCNum.Properties.Append fmtffCNum
  86. Set fmtffCNum = fldCNum.CreateProperty("InputMask", dbText, "00000")
  87. fldCNum.Properties.Append fmtffCNum
  88. Set fmtfCNum = fldCNum.CreateProperty("Caption", dbText, "Centre number")
  89. fldCNum.Properties.Append fmtfCNum
  90. Set fmtfCNum = fldCNum.CreateProperty("DisplayControl", dbInteger, 111)
  91. fldCNum.Properties.Append fmtfCNum
  92. Set fmtfCNum = fldCNum.CreateProperty("RowSource", dbText, "SELECT tblCen.CNum FROM tblCen;")
  93. fldCNum.Properties.Append fmtfCNum
  94. Set fmtfCNum = fldCNum.CreateProperty("ListRows", dbInteger, 255)
  95. fldCNum.Properties.Append fmtfCNum
  96. Set fmtfCNum = fldCNum.CreateProperty("LimitToList", dbBoolean, True)
  97. fldCNum.Properties.Append fmtfCNum
  98.  
  99. Set daoidxCNum = daotdfCwS.CreateIndex("CNum")
  100.  
  101. daoidxCNum.Required = True
  102.  
  103. Set daofldiCNum = daoidxCNum.CreateField("CNum")
  104.  
  105. daoidxCNum.Fields.Append daofldiCNum
  106. daotdfCwS.Indexes.Append daoidxCNum
  107.  
  108.  
  109. Set fldSNum = daotdfCwS.Fields("SNum")
  110.  
  111. Set fmtfSNum = fldSNum.CreateProperty("InputMask", dbText, "00000")
  112. fldSNum.Properties.Append fmtfSNum
  113. Set fmtfSNum = fldSNum.CreateProperty("Caption", dbText, "Subject Reference Code")
  114. fldSNum.Properties.Append fmtfSNum
  115. Set fmtfSNum = fldSNum.CreateProperty("UnicodeCompression", dbBoolean, True)
  116. fldSNum.Properties.Append fmtfSNum
  117. Set fmtfSNum = fldSNum.CreateProperty("DisplayControl", dbInteger, 111)
  118. fldSNum.Properties.Append fmtfSNum
  119. Set fmtfSNum = fldSNum.CreateProperty("RowSource", dbText, "SELECT tblSub.SNum FROM tblSub;")
  120. fldSNum.Properties.Append fmtfSNum
  121. Set fmtfSNum = fldSNum.CreateProperty("ListRows", dbInteger, 255)
  122. fldSNum.Properties.Append fmtfSNum
  123. Set fmtfSNum = fldSNum.CreateProperty("LimitToList", dbBoolean, True)
  124. fldSNum.Properties.Append fmtfSNum
  125.  
  126. Set daoidxSNum = daotdfCwS.CreateIndex("SNum")
  127.  
  128. daoidxSNum.Required = True
  129.  
  130. Set daofldiSNum = daoidxSNum.CreateField("SNum")
  131.  
  132. daoidxSNum.Fields.Append daofldiSNum
  133. daotdfCwS.Indexes.Append daoidxSNum
  134.  
  135.  
  136. Set fldCCanEnt = daotdfCwS.Fields("CCanEnt")
  137.  
  138. Set fmtfCCanEnt = fldCCanEnt.CreateProperty("Format", dbText, "General Number")
  139. fldCCanEnt.Properties.Append fmtfCCanEnt
  140. Set fmtfCCanEnt = fldCCanEnt.CreateProperty("DecimalPlaces", dbByte, 0)
  141. fldCCanEnt.Properties.Append fmtfCCanEnt
  142. Set fmtfCCanEnt = fldCCanEnt.CreateProperty("Caption", dbText, "N° of candidates entered")
  143. fldCCanEnt.Properties.Append fmtfCCanEnt
  144.  
  145. Set daoidxCCanEnt = daotdfCwS.CreateIndex("CCanEnt")
  146.  
  147. daoidxCCanEnt.Required = True
  148.  
  149. Set daofldiCCanEnt = daoidxCCanEnt.CreateField("CCanEnt")
  150.  
  151. daoidxCCanEnt.Fields.Append daofldiCCanEnt
  152. daotdfCwS.Indexes.Append daoidxCCanEnt
  153.  
  154.  
  155.  
  156. DoCmd.RunSQL "INSERT INTO tblCwS SELECT tblCen.CNum, tblSub.SNum FROM tblCen, tblSub;"
  157.  
  158.  
  159. Set tdfCen = daodb.TableDefs!tblCen
  160. Set relCen = daodb.CreateRelation("CenCwS", tdfCen.Name, daotdfCwS.Name, dbRelationUpdateCascade + dbRelationDeleteCascade)
  161.  
  162. relCen.Fields.Append relCen.CreateField("CNum")
  163. relCen.Fields!CNum.ForeignName = "CNum"
  164. daodb.Relations.Append relCen
  165.  
  166.  
  167. Set tdfSub = daodb.TableDefs!tblSub
  168. Set relSub = daodb.CreateRelation("SubCwS", tdfSub.Name, daotdfCwS.Name, dbRelationUpdateCascade + dbRelationDeleteCascade)
  169.  
  170. relSub.Fields.Append relSub.CreateField("SNum")
  171. relSub.Fields!SNum.ForeignName = "SNum"
  172. daodb.Relations.Append relSub
  173.  
  174.  
  175.  
  176. ExitCreateCwS:
  177.  
  178.  
  179.  
  180. DoCmd.OpenForm "frmCSN"
  181. Response = acDataErrContinue
  182.  
  183.  
  184. End Sub
  185.  
msquared's Avatar
Administrator
 
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#12: May 2 '07

re: Opening form after error


Sorry take the sub FormFIX procedure out of the form code and put it in it's own module. Once the form closes the procedure is no longer available if it's in the Form code.

Mary
Member
 
Join Date: Apr 2007
Posts: 72
#13: May 2 '07

re: Opening form after error


I already tried that.
I put it into a seperate module completely, and called that sub from the form that has to be closed. I still got the error. Is there anyway to stop a form from procesing anything?
msquared's Avatar
Administrator
 
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#14: May 2 '07

re: Opening form after error


Quote:

Originally Posted by smiler2505

I already tried that.
I put it into a seperate module completely, and called that sub from the form that has to be closed. I still got the error. Is there anyway to stop a form from procesing anything?

Take the call out of the form_error and put it in the on error section of the Form_Load event instead.
Member
 
Join Date: Apr 2007
Posts: 72
#15: May 2 '07

re: Opening form after error


so...
Sub Form_Load(DataErr as Integer) 'or whatever the correct call is for form load
If DataErr = 2580 Then
'Build table
DoCmd.Open "frmCSN"
End If
End Sub
?
msquared's Avatar
Administrator
 
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#16: May 2 '07

re: Opening form after error


No
Expand|Select|Wrap|Line Numbers
  1. Sub Form_Load()
  2. On Error Go To Err_Form_Load
  3.  
  4.    ' your code here
  5.  
  6. Err_Form_Load:
  7.     Select Case Err
  8.     Case 0 
  9.         Resume Next
  10.     Case 2580      ' catch the specific error
  11.         call FormFIX
  12.     Case Else   ' All other errors will trap
  13.         Beep
  14.         MsgBox Err.Description
  15.         Exit Sub
  16.     End Select
  17.     Resume 0 
  18.  
  19. End Sub
nico5038's Avatar
Moderator
 
Join Date: Nov 2006
Location: The Netherlands
Posts: 2,232
#17: May 2 '07

re: Opening form after error


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

Expand|Select|Wrap|Line Numbers
  1. dim rs as DAO.recordset
  2. set rs = currentdb.openrecordset("tblCen")
  3. IF rs.eof and rs.bof then
  4.    ' rebuild the table as nothing is there
  5. endif
  6.  
  7. Docmd.openform "frmCen"
  8.  
  9.  
Nic;o)
msquared's Avatar
Administrator
 
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#18: May 2 '07

re: Opening form after error


Quote:

Originally Posted by nico5038

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

Expand|Select|Wrap|Line Numbers
  1. dim rs as DAO.recordset
  2. set rs = currentdb.openrecordset("tblCen")
  3. IF rs.eof and rs.bof then
  4.    ' rebuild the table as nothing is there
  5. endif
  6.  
  7. Docmd.openform "frmCen"
  8.  
  9.  
Nic;o)

Thanks Nico

Why didn't I think of that ... Doh!
Rabbit's Avatar
Expert
 
Join Date: Jan 2007
Location: California
Posts: 3,835
#19: May 2 '07

re: Opening form after error


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.
Member
 
Join Date: Apr 2007
Posts: 72
#20: May 2 '07

re: Opening form after error


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's Avatar
Moderator
 
Join Date: Mar 2007
Location: Louisiana
Posts: 1,218
#21: May 2 '07

re: Opening form after error


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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. If Err Then Cancel = True
  3. End Sub
  4.  
Rabbit's Avatar
Expert
 
Join Date: Jan 2007
Location: California
Posts: 3,835
#22: May 2 '07

re: Opening form after error


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.
msquared's Avatar
Administrator
 
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#23: May 2 '07

re: Opening form after error


Quote:

Originally Posted by 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.

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's Avatar
Moderator
 
Join Date: Mar 2007
Location: Louisiana
Posts: 1,218
#24: May 2 '07

re: Opening form after error


I know you are getting flooded with things to try however I just created a form the following worked and hopefully might help.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub Form_Error(DataErr As Integer, Response As Integer)
  4. Call myBad
  5. End Sub
  6.  
  7. Private Sub Form_Open(Cancel As Integer)
  8. If Err Then Cancel = True
  9. End Sub
  10.  
  11. Sub myBad()
  12. MsgBox "Darn It"
  13. End Sub
  14.  

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.
Member
 
Join Date: Apr 2007
Posts: 72
#25: May 2 '07

re: Opening form after error


just tried the form open thing on its own, with form_onerror, with and without DoEvents...nothing
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's Avatar
Moderator
 
Join Date: Mar 2007
Location: Louisiana
Posts: 1,218
#26: May 2 '07

re: Opening form after error


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.
msquared's Avatar
Administrator
 
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#27: May 2 '07

re: Opening form after error


Quote:

Originally Posted by smiler2505

just tried the form open thing on its own, with form_onerror, with and without DoEvents...nothing
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's Avatar
Moderator
 
Join Date: Mar 2007
Location: Louisiana
Posts: 1,218
#28: May 2 '07

re: Opening form after error


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's Avatar
Expert
 
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,214
#29: May 3 '07

re: Opening form after error


Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers
  1.   DoCmd.OpenForm "frmCSN"
  2.  
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_Table 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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fCheckForFormCSN()
  2. Dim MyDB As DAO.Database, Mytdf As DAO.TableDef
  3. Dim blnTableFound As Boolean
  4.  
  5. blnTableFound = False
  6.  
  7. Set MyDB = CurrentDb()
  8.  
  9. For Each Mytdf In MyDB.TableDefs
  10.   If Mytdf.Name = "tblCwS" Then
  11.     blnTableFound = True
  12.      Exit For
  13.   Else
  14.   End If
  15. Next
  16.  
  17. If blnTableFound Then
  18.   DoCmd.OpenForm "frmCSN", acNormal, , , acFormEdit, acWindowNormal
  19. Else
  20.   Call Rebuild_Test_Table
  21.   DoCmd.OpenForm "frmCSN", acNormal, , , acFormEdit, acWindowNormal
  22. End If
  23. End Function
  24.  
Member
 
Join Date: Apr 2007
Posts: 72
#30: May 3 '07

re: Opening form after error


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!
Member
 
Join Date: Apr 2007
Posts: 72
#31: May 3 '07

re: Opening form after error


Expand|Select|Wrap|Line Numbers
  1. Sub Form_Load()
  2.  On Error GoTo Notable
  3.  
  4. Notable:
  5. Select Case Err
  6.  Case 0
  7.   Resume Next
  8.  Case 2580   ' catch the specific error
  9.   Call FormFIX
  10.  Case Else   ' All other errors will trap
  11.   Beep
  12.   MsgBox Err.Description
  13.   Exit Sub
  14.  End Select
  15.  Resume 0
  16. End Sub

Error still comes up, and the table is not built. FormFIX is a Sub (by default, Public) in another module called nonformsubs
Member
 
Join Date: Apr 2007
Posts: 72
#32: May 3 '07

re: Opening form after error


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's Avatar
Administrator
 
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#33: May 3 '07

re: Opening form after error


Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers
  1.   DoCmd.OpenForm "frmCSN"
  2.  
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.
Member
 
Join Date: Apr 2007
Posts: 72
#34: May 3 '07

re: Opening form after error


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's Avatar
Administrator
 
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#35: May 3 '07

re: Opening form after error


Quote:

Originally Posted by 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?

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's Avatar
Expert
 
Join Date: Jan 2007
Location: California
Posts: 3,835
#36: May 3 '07

re: Opening form after error


Did you try compiling and setting a break to step through the code line by line yet?
Member
 
Join Date: Apr 2007
Posts: 72
#37: May 3 '07

re: Opening form after error


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's Avatar
Administrator
 
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#38: May 3 '07

re: Opening form after error


Thank you. That's much clearer :)

Perhaps you'd like to give post #33 a try?
Member
 
Join Date: Apr 2007
Posts: 72
#39: May 3 '07

re: Opening form after error


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.
Member
 
Join Date: Apr 2007
Posts: 72
#40: May 3 '07

re: Opening form after error


Quote:

Originally Posted by NeoPa

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?
msquared's Avatar
Administrator
 
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#41: May 3 '07

re: Opening form after error


Quote:

Originally Posted by 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.

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?
Member
 
Join Date: Apr 2007
Posts: 72
#42: May 4 '07

re: Opening form after error


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...

Expand|Select|Wrap|Line Numbers
  1. Sub Form_Error(DataErr As Integer, Response As Integer)
  2.  If DataErr = "2580" Then
  3.  
  4.   Response = acDataErrContinue
  5.   DoCmd.OpenForm "FormFix"
  6.  
  7.  End If
  8.  
  9. End Sub
  10.  
Expand|Select|Wrap|Line Numbers
  1. Sub Form_Load()
  2.     Me.Visible = False
  3.     Me.TimerInterval = 2
  4.  
  5. End Sub
  6. Sub Form_Timer()
  7.  DoCmd.SetWarnings False
  8.  
  9.  On Error GoTo ExitCreateCwS
  10.  
  11. 'Build table
  12.  
  13. ExitCreateCwS:
  14.  
  15.  DoCmd.OpenForm "frmCSN"
  16.  DoCmd.Close acForm, Me.Name, acSaveNo
  17.  
  18. End Sub
  19.  
NeoPa's Avatar
Administrator
 
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#43: May 4 '07

re: Opening form after error


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