Microsoft's Answer to DoCmd.Close Bug

[Guest]

I was looking in on answer a question without you guys help, SHAME ON ME!!!
I discovered the bug where, using the close button DoCmd.Close, does not save
if required fields are left empty.

I found an answer in the forum that led me to Microsoft's site. Microsoft
says to use this code to check if one of your required fields is empty.

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


My problem is I have more than one required field on the form and cannot
figure out how to make this code check them all. The fields are Teacher,
Period, Student, and Reason. How can I edit this code to check them all to
see if they are filled in and give me a personal message box for each.

Your guys are life savers.

 

[Ken Snell [MVP]]

Can you specify what action should be taken if just one is empty? Do you
want all to be empty before you'd prompt the user? Or just any one to be
empty?

One approach could be to use ElseIf..Then statements as additional steps in
the If..Then block, thus you'd test each field/control individually, and you
could close the form without saving on any of the tests.

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
ElseIf 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
Else
Me![Field2].SetFocus
End If
ElseIsNull(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
Else
Me![Field3].SetFocus
End If
Else
DoCmd.Close
End If

 

[Guest]

Thanks for the quick answer Ken. I think I can use the nested If..Then
statements to work.

All of the fields, Teacher, Period, Student, and Reason, need to be filled
in on the form. The error message can be general in this case. I want to
use, "Hey idiot, fill in all the fields before you click the close button,
just like is says on the form." But that wouldn't be too PC for the program.

Thanks As Always
Rip

Can you specify what action should be taken if just one is empty? Do you
want all to be empty before you'd prompt the user? Or just any one to be
empty?

One approach could be to use ElseIf..Then statements as additional steps in
the If..Then block, thus you'd test each field/control individually, and you
could close the form without saving on any of the tests.

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
ElseIf 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
Else
Me![Field2].SetFocus
End If
ElseIsNull(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
Else
Me![Field3].SetFocus
End If
Else
DoCmd.Close
End If

--

Ken Snell
<MS ACCESS MVP>

I was looking in on answer a question without you guys help, SHAME ON ME!!!
I discovered the bug where, using the close button DoCmd.Close, does not
save
if required fields are left empty.

I found an answer in the forum that led me to Microsoft's site. Microsoft
says to use this code to check if one of your required fields is empty.

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


My problem is I have more than one required field on the form and cannot
figure out how to make this code check them all. The fields are Teacher,
Period, Student, and Reason. How can I edit this code to check them all
to
see if they are filled in and give me a personal message box for each.

Your guys are life savers.

 

[Albert D. Kallal]

I use the following "general" code routine to give custom messages for
fields that are not
filled out.

The code below is a great way to verify fields that you want to be requited.

Another nice feature is that after the message, the cursor moves to the
field in question.

The code is used as follows:

in the forms before update event..you go:

Cancel = MyVerify.

And, then the two following routines need be put into the forms module. You
can see how in the first example, you just put in the list of controls that
you want requited, and also the text "error" message to display. Note
carefully how the full string is enclosed in quotes.


Private Function MyVerify() As Boolean

Dim colFields As New Collection

MyVerify = False

colFields.Add "TourDate,Tour date"
colFields.Add "Description,Description"
colFields.Add "City,City"
colFields.Add "cboProvince,Province"
colFields.Add "StartDate,Start date"
colFields.Add "EndDate,end date"

MyVerify = vfields(colFields)


End Function

Private Function vfields(colFields As Collection) As Boolean

Dim strErrorText As String
Dim strControl As String
Dim i As Integer

vfields = False

For i = 1 To colFields.Count
strControl = Split(colFields(i), ",")(0)
strErrorText = Split(colFields(i), ",")(1)
If IsNull(Me(strControl)) = True Then

MsgBox strErrorText & " is required", vbExclamation, AppName
Me(strControl).SetFocus
vfields = True
Exit Function
End If
Next i


End Function

 

[Guest]

That is PERFECT!!!! Thanks again Albert.

You saved my can again.