My mistake - here's what Im trying to achieve to prevent user to close the form

[Morris]

The form is linked to a table, with four records. Each record needs to
have three fields updated (by a user who types text into the textfield)

I want it to error and not close the form if all three text fields in
_all_four_records are not populated and that's where I stop.


Private Sub Form_Unload(Cancel As Integer)
If IsNull(Me.txtField1.Value) Or IsNull(Me.txtField2.Value) Or _
IsNull(Me.txtField3.Value) Then
MsgBox "Not all of the three required fields have been
populated!", vbCritical, "Cannot continue"
Cancel = True
End If

Now, it means that if user populates all three fields but just on one
page (record) the code will allow him to close the form, and I want it
to be able to check those fields for all pages (records).


Any help greatly appreciated,
Morris

 

[John Smith]

Make this a sub-form if it is not already one, then put three fields on the
main form with Control Sources of:

=Sum(IsNull(SubFormControl.Form.TxtField1))

Where you replace SubFormControl with the name of the subform control (which
may or may not be the same as the name of the form in it).

If any of these controls is not zero then a record has not been completed so
on the *Main* form:

Private Sub Form_Unload(Cancel As Integer)
If txtField1Count > 0 Or txtField2Count > 0 Or txtFieldCount > 0 Then
MsgBox "Not all of the three required fields have been populated!", _
vbCritical, "Cannot continue"
Cancel = True
End If

HTH
John
##################################
Don't Print - Save trees

 

[Steve Schapel]

Morris,

One option may be to consider using the form's Before Update event,
rather than the Unload event, in which case no record would be allowed
to be completed without being completed.

But anyway, here's one way to do it like you want...

Private Sub Form_Unload(Cancel As Integer)
If DCount("*","YourTable","([txtField1]+[txtField2]+[txtField3])
Is Null") > 0 Then
MsgBox "Not all of the three required fields have been
populated!", vbCritical, "Cannot continue"
Cancel = True
End If
End sub