This code does not quite give exactly what I'm looking for. It still
checks
each field one by one. I see that under this code the event will stop
once
it finds the first null value field, however you would be repeating the
procedure for each null field. I'm looking for something that will give
me
one single list of all null value fields which are required. I have used
your earlier suggestion of writing a do...loop for each field and simply
remembering the name of each null field, however I do not know how to
limit
it to check only required fields. Here's my current code
Set rst = Me.RecordsetClone
For Each fld In rst.Fields
If Controls(fld.nam).Visible = False Then GoTo skip
ctr = IsNull(Controls(fld.name))
If ctr = True Then
msg = msg + Chr(13) + fld.name
End If
skip:
Next
MsgBox msg
This code will go through all the fields of the underlying table and add
all
null fields to a list (msg). At the end it will msgbox the entire list of
null value fields. What I need to do is limit it to only those that have
the
required property set to yes. Error handling is also set up to avoid the
access generated error messages. Would it be simpler to use your
suggestion
of changing the validation rule for each desired field on the form, rather
than the underlying table, to is not null and then have it check only
those
fields with that validation rule? Users will not have access to change
the
records through any other means other than through the form.
Allen Browne said:
Setting the Required property to Yes does give a warning if the user
enters
something in the control, and then backspaces it out. Pressing the Esc
key
returns the control to its previous state, and allow them out of the
control.
Canceling the entry is the form is less safe: for example it does not
handle
the case where the entry comes from an Append query, or the user edits
the
table/query directly.
Some people prefer to set the Validation Rule of the field in the table
to:
Is Not Null
which has the same effect as setting the Required property to Yes.
Another
option would be to set the Validation Rule for the table (in the
Properties
box in table design view) instead of for the fields. Example:
([Field1] Is Not Null) AND (Field2] Is Not Null) AND ...
If you want to use Form_BeforeUpdate anyway, try something like this:
Private Sub Form_BeforeUpdate(Cancel As integer)
Dim strMsg As String
If IsNull(Me.Text1) Then
Cancel = True
strMsg = strMsg & "Enter a value for Text1." & vbCrLf
End If
If IsNull(Me.Text2) Then
Cancel = True
strMsg = strMsg & "Enter a value for Text2." & vbCrLf
End If
'etc
If Cancel Then
strMsg = strMsg & vbClLf & "Complete the record, or press <Esc>
to
undo."
MsgBox strMsg, vbExclamation, "Invalid data"
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Stevkev said:
Will changing the required property of the desired controls to yes not
give
me a separate warning for each control?... I want to have only one
warning
msgbox with a listing of all null controls.
:
If the Required property of the field in the table is Yes, then Access
will
give a warning and refuse the write the record. No action required on
your
part.
If you want to check the fields yourself, use the BeforeUpdate event
of
the
Form to test if each one IsNull().
If you want this code to automatically generate your own validation
message,
it would be possible to do something like this:
- Loop through all the Controls in Me.Controls.
- Ignore the ones that have no ControlSource property.
- Ignore those where the ControlSource is blank (unbound).
- Ignore those where the ControlSource starts with = (bound to an
expression).
- For the field named in the ControlSource, examine its properties in
the
RecordsetClone of the form.
- If the Required property it True, and the Value of the control
IsNull(),
cancel the Form_BeforeUpdate event, and concatenate the information
into
a
warning string to be displayed at the end of the loop.
I am trying to make a list of all controls on a form which are empty.
The
reason is obviously to ensure all required fields are filled before
adding
the record. I would like to list all the empty fields into a
message
box
to
alert the user what is still required to be filled. Is there an
easy
way
to
do this other than using an if isnull expression for each control?
