Opening form after error

**Lysander** · Apr 30 '07, 08:05 AM

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

Code:

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

**smiler2505** · Apr 30 '07, 08:47 AM

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.

**MMcCarthy** · Apr 30 '07, 03:14 PM

In the Sub form_OnError code add the line.

Code:

DoCmd.Close acForm, "frmCSN"

Mary

**smiler2505** · May 1 '07, 05:17 AM

Originally posted by mmccarthy

In the Sub form_OnError code add the line.

Code:

DoCmd.Close acForm, "frmCSN"

Mary

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

**MMcCarthy** · May 1 '07, 09:59 AM

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?

**smiler2505** · May 1 '07, 09:59 PM

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 = acDataErrContin ue

End If

**MMcCarthy** · May 1 '07, 10:06 PM

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 = acDataErrContin ue

End If

And where exactly did you put the form close in this code.

**smiler2505** · May 1 '07, 11:17 PM

If dataerr = (the error code) Then

Build Table

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

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.

**MMcCarthy** · May 2 '07, 12:52 AM

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.

Code:

Private Sub Form_Error()

   If dataerr = (the error code) Then
 	  call noTable
   End If

End Sub

Sub noTable()

   DoCmd.Close acForm, "frmCSN"
   Build Table
   DoCmd.OpenForm "frmCSN"
   Response = acDataErrContinue ' not sure how to cater for this as you haven't given me that part of the code

End Sub

Mary

**smiler2505** · May 2 '07, 08:11 AM

I still get the error
The code I have is:

Code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = "2580" Then
 
Call FormFIX
 
End If
 
End Sub
Sub FormFIX()
 
DoCmd.Close acForm, "frmCSN", acSaveNo
 
 
 
 
DoCmd.SetWarnings False
 
On Error GoTo ExitCreateCwS
 
Dim daodb As DAO.Database
Dim daotdfCwS As DAO.TableDef
Dim daofldCNum As DAO.Field
Dim daofldSNum As DAO.Field
Dim daofldCCanEnt As DAO.Field
Dim fldCNum As Field
Dim fmtffCNum As Property
Dim daoidxCNum As DAO.Index
Dim daofldiCNum As DAO.Field
Dim fldSNum As Field
Dim fmtffSNum As Property
Dim daoidxSNum As DAO.Index
Dim daofldiSNum As DAO.Field
Dim fldCCanEnt As Field
Dim fmtffCCanEnt As Property
Dim daoidxCCanEnt As DAO.Index
Dim daofldiCCanEnt As DAO.Field
Dim tdfCen As TableDef
Dim relCen As Relation
Dim tdfSub As TableDef
Dim relSub As Relation
 
Set daodb = CurrentDb()
Set daotdfCwS = daodb.CreateTableDef("tblCwS")
 
 
 
Set daofldCNum = daotdfCwS.CreateField("CNum", dbLong)
 
daofldCNum.DefaultValue = "10000"
daofldCNum.ValidationRule = "Between 10000 And 79999 And Len([CNum])=5"
daofldCNum.ValidationText = "Must be 5 digits long, lie between 10000 and 79999, and unique"
daofldCNum.Required = True
 
 
Set daofldSNum = daotdfCwS.CreateField("SNum", dbText, 5)
 
daofldSNum.DefaultValue = "11111"
daofldSNum.ValidationRule = "Len([SNum])=5"
daofldSNum.ValidationText = "Must be 5 digits long"
daofldSNum.Required = True
daofldSNum.AllowZeroLength = False
 
 
Set daofldCCanEnt = daotdfCwS.CreateField("CCanEnt", dbLong)
 
daofldCCanEnt.DefaultValue = "0"
daofldCCanEnt.ValidationRule = ">=0"
daofldCCanEnt.ValidationText = "Please enter number of candidates"
daofldCCanEnt.Required = True
 
 
 
daotdfCwS.Fields.Append daofldCNum
daotdfCwS.Fields.Append daofldSNum
daotdfCwS.Fields.Append daofldCCanEnt
daodb.TableDefs.Append daotdfCwS
 
 
 
Set fldCNum = daotdfCwS.Fields("CNum")
 
Set fmtffCNum = fldCNum.CreateProperty("Format", dbText, "General Number")
fldCNum.Properties.Append fmtffCNum
Set fmtffCNum = fldCNum.CreateProperty("DecimalPlaces", dbByte, 0)
fldCNum.Properties.Append fmtffCNum
Set fmtffCNum = fldCNum.CreateProperty("InputMask", dbText, "00000")
fldCNum.Properties.Append fmtffCNum
Set fmtfCNum = fldCNum.CreateProperty("Caption", dbText, "Centre number")
fldCNum.Properties.Append fmtfCNum
Set fmtfCNum = fldCNum.CreateProperty("DisplayControl", dbInteger, 111)
fldCNum.Properties.Append fmtfCNum
Set fmtfCNum = fldCNum.CreateProperty("RowSource", dbText, "SELECT tblCen.CNum FROM tblCen;")
fldCNum.Properties.Append fmtfCNum
Set fmtfCNum = fldCNum.CreateProperty("ListRows", dbInteger, 255)
fldCNum.Properties.Append fmtfCNum
Set fmtfCNum = fldCNum.CreateProperty("LimitToList", dbBoolean, True)
fldCNum.Properties.Append fmtfCNum
 
Set daoidxCNum = daotdfCwS.CreateIndex("CNum")
 
daoidxCNum.Required = True
 
Set daofldiCNum = daoidxCNum.CreateField("CNum")
 
daoidxCNum.Fields.Append daofldiCNum
daotdfCwS.Indexes.Append daoidxCNum
 
 
Set fldSNum = daotdfCwS.Fields("SNum")
 
Set fmtfSNum = fldSNum.CreateProperty("InputMask", dbText, "00000")
fldSNum.Properties.Append fmtfSNum
Set fmtfSNum = fldSNum.CreateProperty("Caption", dbText, "Subject Reference Code")
fldSNum.Properties.Append fmtfSNum
Set fmtfSNum = fldSNum.CreateProperty("UnicodeCompression", dbBoolean, True)
fldSNum.Properties.Append fmtfSNum
Set fmtfSNum = fldSNum.CreateProperty("DisplayControl", dbInteger, 111)
fldSNum.Properties.Append fmtfSNum
Set fmtfSNum = fldSNum.CreateProperty("RowSource", dbText, "SELECT tblSub.SNum FROM tblSub;")
fldSNum.Properties.Append fmtfSNum
Set fmtfSNum = fldSNum.CreateProperty("ListRows", dbInteger, 255)
fldSNum.Properties.Append fmtfSNum
Set fmtfSNum = fldSNum.CreateProperty("LimitToList", dbBoolean, True)
fldSNum.Properties.Append fmtfSNum
 
Set daoidxSNum = daotdfCwS.CreateIndex("SNum")
 
daoidxSNum.Required = True
 
Set daofldiSNum = daoidxSNum.CreateField("SNum")
 
daoidxSNum.Fields.Append daofldiSNum
daotdfCwS.Indexes.Append daoidxSNum
 
 
Set fldCCanEnt = daotdfCwS.Fields("CCanEnt")
 
Set fmtfCCanEnt = fldCCanEnt.CreateProperty("Format", dbText, "General Number")
fldCCanEnt.Properties.Append fmtfCCanEnt
Set fmtfCCanEnt = fldCCanEnt.CreateProperty("DecimalPlaces", dbByte, 0)
fldCCanEnt.Properties.Append fmtfCCanEnt
Set fmtfCCanEnt = fldCCanEnt.CreateProperty("Caption", dbText, "N° of candidates entered")
fldCCanEnt.Properties.Append fmtfCCanEnt
 
Set daoidxCCanEnt = daotdfCwS.CreateIndex("CCanEnt")
 
daoidxCCanEnt.Required = True
 
Set daofldiCCanEnt = daoidxCCanEnt.CreateField("CCanEnt")
 
daoidxCCanEnt.Fields.Append daofldiCCanEnt
daotdfCwS.Indexes.Append daoidxCCanEnt
 
 
 
DoCmd.RunSQL "INSERT INTO tblCwS SELECT tblCen.CNum, tblSub.SNum FROM tblCen, tblSub;"
 
 
Set tdfCen = daodb.TableDefs!tblCen
Set relCen = daodb.CreateRelation("CenCwS", tdfCen.Name, daotdfCwS.Name, dbRelationUpdateCascade + dbRelationDeleteCascade)
 
relCen.Fields.Append relCen.CreateField("CNum")
relCen.Fields!CNum.ForeignName = "CNum"
daodb.Relations.Append relCen
 
 
Set tdfSub = daodb.TableDefs!tblSub
Set relSub = daodb.CreateRelation("SubCwS", tdfSub.Name, daotdfCwS.Name, dbRelationUpdateCascade + dbRelationDeleteCascade)
 
relSub.Fields.Append relSub.CreateField("SNum")
relSub.Fields!SNum.ForeignName = "SNum"
daodb.Relations.Append relSub
 
 
 
ExitCreateCwS:
 
 
 
DoCmd.OpenForm "frmCSN"
Response = acDataErrContinue
 
 
End Sub

**MMcCarthy** · May 2 '07, 04:53 PM

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

**smiler2505** · May 2 '07, 07:13 PM

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?

**MMcCarthy** · May 2 '07, 09:59 PM

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.

**smiler2505** · May 2 '07, 10:02 PM

so...
Sub Form_Load(DataE rr as Integer) 'or whatever the correct call is for form load
If DataErr = 2580 Then
'Build table
DoCmd.Open "frmCSN"
End If
End Sub
?

Opening form after error

Opening form after error

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment