Continuous Form

[Guest]

A continuous form has five fields and upto eight records. How can I determine
if an entry has been made in all the fields of whatever number of records
have been entered?

 

[Guest]

Mary Jane,

You can either set the Required property to Yes at the table level, or use
the Form's BeforeUpdate event procedure to loop through the controls. I like
to set the Tag property to "R" for all required fields:

' Form BeforeUpdate event procedure
On Error Resume Next
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "R" Then
If IsNull(ctl.Value) Then
Cancel = True
Beep
MsgBox ctl.Name & " is a required field. Please enter a
value."
ctl.SetFocus
Exit Loop
End If
End If
End For

Sprinks

 

[Steve Schapel]

Mary Jane,

In addition to Sprinks's suggestion, if it really is a requirement that
data is entered into the field and this requirement is to be enforced,
my preferred approach would be this: In design view of the table, set
the Validation Rule property of the fields in question to...
Is Not Null
.... and the Validation Text property to the message to display if no
data is entered.

However, your original question did not say this, but just how to
determine if there were any blank fields. There are a number of
approaches that could be made to this. Here's one...
1. In the query that the form is based on, make a calculated field by
entering the equivalent of the following into the Field row of a blank
column in the query design grid:
BlankTest: Abs(([FirstField]+[SecondField]+...+[FifthField]) Is Not Null)
2. Add a textbox to the form bound to this field. Set its Visible
property to No if you like.
3. In the Form Footer section of your form, put an unbound checkbox,
with the label 'Data complete', and set the Control Source property of
the checkbox set to:
=Sum([BlankTest])=Count(*)
The checkbox will be unticked if any fields are blank in any of the records.