Close Form Crashes Database

Hi all,

I have a form that is based on a table where all the fields are set to
required. On the same form I have a Close button. If I do not input data
into a feilds Access informs me correctly that the feild is set to required.
However, if by 'user' mistake half way through the filling process the user
exits via the close button I receive the message, "The fieild cannot contain
Null value because the Required field is set to True. Enter a value for this
field". Problem is I cannot do so, tried the ESC key but it seem to be in a
loop. I there any way to correct this or have the database close correctly
without saving the record.

Thanks for taking the time to read this, any and all help welcome.

Debbie D.

One option would be to define default values for all the fields either in the
table def or using vb code when the user creates a new record. Another
option is reconsider the use of "is required" for the fields. Do you really
need it? Instead, you could do some checking when the user tries to close
the form. If you are not using VB, then setting defaults would be the
simpliest given that in the real world, the user can always find a way of
closing without completing the form.--
JB


"Debbie D." wrote:

Hi all,

I have a form that is based on a table where all the fields are set to
required. On the same form I have a Close button. If I do not input data
into a feilds Access informs me correctly that the feild is set to required.
However, if by 'user' mistake half way through the filling process the user
exits via the close button I receive the message, "The fieild cannot contain
Null value because the Required field is set to True. Enter a value for this
field". Problem is I cannot do so, tried the ESC key but it seem to be in a
loop. I there any way to correct this or have the database close correctly
without saving the record.

Thanks for taking the time to read this, any and all help welcome.

Debbie D.

Hi, Debbie.

I there any way to correct this or have the database close correctly
without saving the record.

I suggest creating a default value for every required field in the table.
If this is not feasible, then open the form in Design View and select the
"Format" tab. Scroll down to the Control Box Property and set it to No. In
the Close button's OnClick procedure add code that checks the fields for NULL
values and when it finds at least one field empty, asks the user whether to
close the form or go back to the record for further editing. For example:

Private Sub CloseBtn_Click()

On Error GoTo ErrHandler

Dim ans As Integer

If ((Nz(Me!txtA.Value, "") = "") Or (Nz(Me!txtB.Value, "") = "")) Then
ans = MsgBox("You must have a value for every field" & vbCrLf & _
"or you cannot save this record." & vbCrLf & vbCrLf & _
"Select OK to exit without saving or" & vbCrLf & _
"select Cancel to return to the record.", _
vbCritical + vbOKCancel, "Cannot Save!")

If (ans = vbOK) Then
Me.Undo
DoCmd.Close acForm, Me.Name
End If
Else
DoCmd.Close acForm, Me.Name
End If

Exit Sub

ErrHandler:

MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

.... where CloseBtn is the name of the button, and txtA and txtB are the
names of the text boxes on the form. Change these names to those of the
form's text boxes and add the other controls that require values in the IF
statement by adding additional "OR" operators between each control's check
for NULL. Save and compile the code, then open the form in Form View.

The user will need to use the Close button to close the form instead of
using the "X" in the Title Bar to close the form. There will still be an
error if the user closes the database when the form is incomplete, but it
won't be a surprise to the user that the record is not saved and that the
database closes.

The fact that the database crashes is a separate issue. This may be caused
by an unstable operating system, an older version of Access, a faulty
installation of Access, invalid VBA code, a corrupt database, et cetera.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Debbie D." wrote:

Hi all,

I have a form that is based on a table where all the fields are set to
required. On the same form I have a Close button. If I do not input data
into a feilds Access informs me correctly that the feild is set to required.
However, if by 'user' mistake half way through the filling process the user
exits via the close button I receive the message, "The fieild cannot contain
Null value because the Required field is set to True. Enter a value for this
field". Problem is I cannot do so, tried the ESC key but it seem to be in a
loop. I there any way to correct this or have the database close correctly
without saving the record.

Thanks for taking the time to read this, any and all help welcome.

Debbie D.

JB,

Many thanks for your help. I reset some of the 'required' defaults. Debbie
D.

"JB" wrote:

One option would be to define default values for all the fields either in the
table def or using vb code when the user creates a new record. Another
option is reconsider the use of "is required" for the fields. Do you really
need it? Instead, you could do some checking when the user tries to close
the form. If you are not using VB, then setting defaults would be the
simpliest given that in the real world, the user can always find a way of
closing without completing the form.--
JB


"Debbie D." wrote:

Hi all,

I have a form that is based on a table where all the fields are set to
required. On the same form I have a Close button. If I do not input data
into a feilds Access informs me correctly that the feild is set to required.
However, if by 'user' mistake half way through the filling process the user
exits via the close button I receive the message, "The fieild cannot contain
Null value because the Required field is set to True. Enter a value for this
field". Problem is I cannot do so, tried the ESC key but it seem to be in a
loop. I there any way to correct this or have the database close correctly
without saving the record.

Thanks for taking the time to read this, any and all help welcome.

Debbie D.

Gunny,

Thanking you very much. The code worked great. Simply perfection. That
for all the help from everyone.

Debbie D. (UK)

"'69 Camaro" wrote:

Hi, Debbie.

I there any way to correct this or have the database close correctly
without saving the record.

I suggest creating a default value for every required field in the table.
If this is not feasible, then open the form in Design View and select the
"Format" tab. Scroll down to the Control Box Property and set it to No. In
the Close button's OnClick procedure add code that checks the fields for NULL
values and when it finds at least one field empty, asks the user whether to
close the form or go back to the record for further editing. For example:

Private Sub CloseBtn_Click()

On Error GoTo ErrHandler

Dim ans As Integer

If ((Nz(Me!txtA.Value, "") = "") Or (Nz(Me!txtB.Value, "") = "")) Then
ans = MsgBox("You must have a value for every field" & vbCrLf & _
"or you cannot save this record." & vbCrLf & vbCrLf & _
"Select OK to exit without saving or" & vbCrLf & _
"select Cancel to return to the record.", _
vbCritical + vbOKCancel, "Cannot Save!")

If (ans = vbOK) Then
Me.Undo
DoCmd.Close acForm, Me.Name
End If
Else
DoCmd.Close acForm, Me.Name
End If

Exit Sub

ErrHandler:

MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

... where CloseBtn is the name of the button, and txtA and txtB are the
names of the text boxes on the form. Change these names to those of the
form's text boxes and add the other controls that require values in the IF
statement by adding additional "OR" operators between each control's check
for NULL. Save and compile the code, then open the form in Form View.

The user will need to use the Close button to close the form instead of
using the "X" in the Title Bar to close the form. There will still be an
error if the user closes the database when the form is incomplete, but it
won't be a surprise to the user that the record is not saved and that the
database closes.

The fact that the database crashes is a separate issue. This may be caused
by an unstable operating system, an older version of Access, a faulty
installation of Access, invalid VBA code, a corrupt database, et cetera.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Debbie D." wrote:

Hi all,

I have a form that is based on a table where all the fields are set to
required. On the same form I have a Close button. If I do not input data
into a feilds Access informs me correctly that the feild is set to required.
However, if by 'user' mistake half way through the filling process the user
exits via the close button I receive the message, "The fieild cannot contain
Null value because the Required field is set to True. Enter a value for this
field". Problem is I cannot do so, tried the ESC key but it seem to be in a
loop. I there any way to correct this or have the database close correctly
without saving the record.

Thanks for taking the time to read this, any and all help welcome.

Debbie D.

Hi, Debbie.

You're very welcome. Glad it helped.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


"Debbie D." wrote in message
...
Gunny,

Thanking you very much. The code worked great. Simply perfection. That
for all the help from everyone.

Debbie D. (UK)

"'69 Camaro" wrote:

Hi, Debbie.

I there any way to correct this or have the database close correctly
without saving the record.

I suggest creating a default value for every required field in the table.
If this is not feasible, then open the form in Design View and select the
"Format" tab. Scroll down to the Control Box Property and set it to No.
In
the Close button's OnClick procedure add code that checks the fields for
NULL
values and when it finds at least one field empty, asks the user whether
to
close the form or go back to the record for further editing. For
example:

Private Sub CloseBtn_Click()

On Error GoTo ErrHandler

Dim ans As Integer

If ((Nz(Me!txtA.Value, "") = "") Or (Nz(Me!txtB.Value, "") = ""))
Then
ans = MsgBox("You must have a value for every field" & vbCrLf & _
"or you cannot save this record." & vbCrLf & vbCrLf & _
"Select OK to exit without saving or" & vbCrLf & _
"select Cancel to return to the record.", _
vbCritical + vbOKCancel, "Cannot Save!")

If (ans = vbOK) Then
Me.Undo
DoCmd.Close acForm, Me.Name
End If
Else
DoCmd.Close acForm, Me.Name
End If

Exit Sub

ErrHandler:

MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

... where CloseBtn is the name of the button, and txtA and txtB are the
names of the text boxes on the form. Change these names to those of the
form's text boxes and add the other controls that require values in the
IF
statement by adding additional "OR" operators between each control's
check
for NULL. Save and compile the code, then open the form in Form View.

The user will need to use the Close button to close the form instead of
using the "X" in the Title Bar to close the form. There will still be an
error if the user closes the database when the form is incomplete, but it
won't be a surprise to the user that the record is not saved and that the
database closes.

The fact that the database crashes is a separate issue. This may be
caused
by an unstable operating system, an older version of Access, a faulty
installation of Access, invalid VBA code, a corrupt database, et cetera.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


"Debbie D." wrote:

Hi all,

I have a form that is based on a table where all the fields are set to
required. On the same form I have a Close button. If I do not input
data
into a feilds Access informs me correctly that the feild is set to
required.
However, if by 'user' mistake half way through the filling process the
user
exits via the close button I receive the message, "The fieild cannot
contain
Null value because the Required field is set to True. Enter a value
for this
field". Problem is I cannot do so, tried the ESC key but it seem to be
in a
loop. I there any way to correct this or have the database close
correctly
without saving the record.

Thanks for taking the time to read this, any and all help welcome.

Debbie D.