Form not saving data

[Iram]

Hello.
I have a Continuous Form with about 15 fields, 4 of which need to be entered
as a minimum for our in house data purposes. This form is connected to a
Sub-Table called "tbl_CalwinMainSub" and is the "many" part of the
relationship.The four fields that need to be filled out are marked as
"Required" in the table properties. The above form is opened by a button on a
different form with Primary data from the "One" side of the relationship.
Any how, if folks don't enter data into 'any' of the four "required" fields
and enter data in the non-required fields and close out of the form, they are
not prompted with a 'such field cannot contain a null value' error. The form
closes without prompting the user anything. Then when they go back in there
is no data. However if they fill in at least one of the four required fields
and try to close out then they get prompted with the 'such field cannot
contain a null value' error.

Can you help me with a solution that will verify that all required fields
are filled in before they close the form?

Thanks.
Iram/mcp

 

[Allen Browne]

The problem you describe is a major problem with the way Access works. It is
supposed to save by default, but if you use the Close method (in code) or
the Close action (in a macro, except in A2007), you get no warning if the
record cannot be saved.

The problem is documented here:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

The link above explains how to get your button to explicitly save the record
before closing the form, so you do get a message if the record cannot be
saved.

 

[Iram]

Thanks for the info.


Iram/mcp

The problem you describe is a major problem with the way Access works. It is
supposed to save by default, but if you use the Close method (in code) or
the Close action (in a macro, except in A2007), you get no warning if the
record cannot be saved.

The problem is documented here:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

The link above explains how to get your button to explicitly save the record
before closing the form, so you do get a message if the record cannot be
saved.

 

[Iram]

Hello Allen, I clicked on the first link at the bottom of the page you sent
me to and I liked the Resolution Microsoft has. However their resolution
shows how to mitigate one required field (in their example below) but how do
I modify their example to help me with four required fields?

1. Start Microsoft Access and create a new database.
2. Create the following new table:
Field Name: Field1
Data Type: Text
Required: Yes

Field Name: Field2
Data Type: Text
Required: No


If IsNull(Me![Field1]) Then
If MsgBox("'Field1' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close

Else
Me![Field1].SetFocus
End If
Else
DoCmd.Close
End If




Your help is greatly appreaciated.
Iram/mcp

 

[Allen Browne]

You will need to code four If blocks to handle the 4 fields, so you don't
perform the DoCmd.Close unless they are all okay.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen, I clicked on the first link at the bottom of the page you
sent
me to and I liked the Resolution Microsoft has. However their resolution
shows how to mitigate one required field (in their example below) but how
do
I modify their example to help me with four required fields?

1. Start Microsoft Access and create a new database.
2. Create the following new table:
Field Name: Field1
Data Type: Text
Required: Yes

Field Name: Field2
Data Type: Text
Required: No


If IsNull(Me![Field1]) Then
If MsgBox("'Field1' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close

Else
Me![Field1].SetFocus
End If
Else
DoCmd.Close
End If




Your help is greatly appreaciated.
Iram/mcp

The problem you describe is a major problem with the way Access works. It
is
supposed to save by default, but if you use the Close method (in code) or
the Close action (in a macro, except in A2007), you get no warning if the
record cannot be saved.

The problem is documented here:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

The link above explains how to get your button to explicitly save the
record
before closing the form, so you do get a message if the record cannot be
saved.

 

[Iram]

Thanks.
Can you veify the below code to see if I am correct? If it is wrong how
would you write it?

If IsNull(Me![Field1]) Then

If MsgBox("'Field1' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close
If IsNull(Me![Field2]) Then
If MsgBox("'Field2' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close

If IsNull(Me![Field3]) Then

If MsgBox("'Field3' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close

If IsNull(Me![Field4]) Then

If MsgBox("'Field4' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close

Else
Me![Field1].SetFocus

Else
Me![Field2].SetFocus

Else
Me![Field3].SetFocus

Else
Me![Field4].SetFocus

End If
Else
DoCmd.Close
End If

Iram/mcp

You will need to code four If blocks to handle the 4 fields, so you don't
perform the DoCmd.Close unless they are all okay.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen, I clicked on the first link at the bottom of the page you
sent
me to and I liked the Resolution Microsoft has. However their resolution
shows how to mitigate one required field (in their example below) but how
do
I modify their example to help me with four required fields?

1. Start Microsoft Access and create a new database.
2. Create the following new table:
Field Name: Field1
Data Type: Text
Required: Yes

Field Name: Field2
Data Type: Text
Required: No


If IsNull(Me![Field1]) Then
If MsgBox("'Field1' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close

Else
Me![Field1].SetFocus
End If
Else
DoCmd.Close
End If




Your help is greatly appreaciated.
Iram/mcp

The problem you describe is a major problem with the way Access works. It
is
supposed to save by default, but if you use the Close method (in code) or
the Close action (in a macro, except in A2007), you get no warning if the
record cannot be saved.

The problem is documented here:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

The link above explains how to get your button to explicitly save the
record
before closing the form, so you do get a message if the record cannot be
saved.

Hello.
I have a Continuous Form with about 15 fields, 4 of which need to be
entered
as a minimum for our in house data purposes. This form is connected to
a
Sub-Table called "tbl_CalwinMainSub" and is the "many" part of the
relationship.The four fields that need to be filled out are marked as
"Required" in the table properties. The above form is opened by a
button
on a
different form with Primary data from the "One" side of the
relationship.
Any how, if folks don't enter data into 'any' of the four "required"
fields
and enter data in the non-required fields and close out of the form,
they
are
not prompted with a 'such field cannot contain a null value' error. The
form
closes without prompting the user anything. Then when they go back in
there
is no data. However if they fill in at least one of the four required
fields
and try to close out then they get prompted with the 'such field cannot
contain a null value' error.

Can you help me with a solution that will verify that all required
fields
are filled in before they close the form?

 

[Allen Browne]

I would not do it like that. My preference would be to run the validation in
the Form_BeforeUpate event. The button's code just forces the save (which
triggers Form_BeforeUpdate.)

This kind of thing (but with error handling):

Private Sub Button1_Click()
If Me.Dirty Then Me.Dirty = False
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim strMsg As String
Dim strControl As string

With Me.Field1
If IsNull(.Value) Then
Cancel = True
strMsg = strMsg & "Field1 required" & vbCrLf
strControl = .Name
End If
With Me.Field2
If IsNull(.Value) Then
Cancel = True
strMsg = strMsg & "Field2 required" & vbCrLf
strControl = .Name
End If
'etc
If Cancel And strMsg <> vbNullString Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press Esc to
undo."
MsgBox strMsg, vbExclamation, "Cannot save"
If strControl <> vbNullString Then
Me(strControl).SetFocus
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks.
Can you veify the below code to see if I am correct? If it is wrong how
would you write it?

If IsNull(Me![Field1]) Then

If MsgBox("'Field1' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close
If IsNull(Me![Field2]) Then
If MsgBox("'Field2' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close

If IsNull(Me![Field3]) Then

If MsgBox("'Field3' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close

If IsNull(Me![Field4]) Then

If MsgBox("'Field4' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close

Else
Me![Field1].SetFocus

Else
Me![Field2].SetFocus

Else
Me![Field3].SetFocus

Else
Me![Field4].SetFocus

End If
Else
DoCmd.Close
End If