Need to avoid "field X cannot contain a Null value"

[sjg1314]

I have MS Access 2000.

If I create a new record and fail to fill in a required field, Access
complains when the Form is updated. I can check for this event on a
Before Update event on the form.

If I edit an existing record, Access complains when I try to leave the
field. To avoid the message, I would have to set up a Before Update
handler for each field.

Ideally, I would rather let my users not worry about required fields
until they try to update the form. Is there any way of turning off the
field warning?

Assuming there isn't any way to turn off the warning, is there any way
to programmatically add Before Update event handlers for each type of
field in my form? OK, I actually know how to add event handlers, but
some fields will use the Before Update handler for other reasons--so
the trick is how do I chain events? To clarify, I'd like to know if
anyone has developed code that would allow me to programmatically add a
function call to an event handler such that, if the event already had
an event handler, I could call the existing event handler and the new
event handler?

How do people normally deal with the Access error messages? The
messages expose the underlying field names, which are not what I want
to present to my users.

Thanks for any help.

 

[Allen Browne]

At the most basic level, the user can get out of the field if they either
make no entry, or undo the entry (press Esc.)

If you want to trap the error and replace it with your own error message,
you can do that in the Error event of the form.

If you want to delay the message until the record is being validated instead
of the field, open the table in design view, and set the Required property
to No for each field. Still in table design, open the Properties box (View
menu), and enter a Validation Rule there for the table. Example:
([Surname] Is Not Null) AND ([City] Is Not Null)

I have not tried to programmatically create the event procedures and modify
existing event procedures in the way you desribe. With the form open in
design view, you can determine whether an event procedure or macro is
handling the event of a control by examining its property, e.g.:
? Forms!Form1!Text0.BeforeUpdate
which returns the string "[Event Procedure]" if you have one. See help on
CreateEventProc if you want to follow that approach.

 

[sjg1314]

Allen, thanks so much for taking the time to reply.

At the most basic level, the user can get out of the field if they either
make no entry, or undo the entry (press Esc.)

The problem with this is that you "just have to know" this trick
(Ctrl-Z works too). For maximum usability, this is unacceptable.

If you want to trap the error and replace it with your own error message,
you can do that in the Error event of the form.

I thought of that last night and will try it out today. The main
problem with the error traps is that all you get is a number. You can
convert this number to a string. I wish that, instead, the error
handler would have an event structure that would indicate the object in
which the error occurred. Any ideas?

If you want to delay the message until the record is being validated instead
of the field, open the table in design view, and set the Required property
to No for each field. Still in table design, open the Properties box (View
menu), and enter a Validation Rule there for the table. Example:
([Surname] Is Not Null) AND ([City] Is Not Null)

I've seen this trick mentioned in a number of places. As an experienced
database programmer (who is an MS Access/Visual Basic beginner), I find
this horrifying. Instead of concentrating the knowledge of what fields
are required in one place (the Table), this information has to be
spread out into all the Forms that use the Table.

I realize MS Access is used for small databases designed by a single
programmer, but I can't bring myself to do this. If a field is
required, this information should be part of the Schema.

I have not tried to programmatically create the event procedures and modify
existing event procedures in the way you desribe. With the form open in
design view, you can determine whether an event procedure or macro is
handling the event of a control by examining its property, e.g.:
? Forms!Form1!Text0.BeforeUpdate
which returns the string "[Event Procedure]" if you have one. See help on
CreateEventProc if you want to follow that approach.

I'm aware of this. I'm also aware that what I'm asking for is fairly
difficult. I was hoping either someone already had working code or that
I would be told: "You idiot! Just put a comma in the event field and
add the next event handler." No such luck, it appears.

The important thing here is that if you, a Microsoft MVP, don't have
any better suggestions, then I know I can stop looking.

Thanks again, Allen, and thanks also for your Web page of tips.

One more question, master of VBA: is there any way to tell if an object
has a property? All my attempts caused a run-time or compile error to
occur. Some of my code would be greatly simplified if I could just say
"If IsProperty(x.a) Then ...".

I'm stuck with MS Access 2000, so if this feature has been added later,
it won't help much. If it can be done with some trickery with error
handlers, that would be great, but I wasn't able to get the error
handler to catch this error.

 

[Allen Browne]

Replies embedded (with answered parts cut.)

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

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

If you want to delay the message until the record is being validated
instead
of the field, open the table in design view, and set the Required
property
to No for each field. Still in table design, open the Properties box
(View
menu), and enter a Validation Rule there for the table. Example:
([Surname] Is Not Null) AND ([City] Is Not Null)

I've seen this trick mentioned in a number of places. As an experienced
database programmer (who is an MS Access/Visual Basic beginner), I find
this horrifying. Instead of concentrating the knowledge of what fields
are required in one place (the Table), this information has to be
spread out into all the Forms that use the Table.

But the suggestion *was* to use the Validation Rule of the *table*.

No code is needed in any form.

One more question, master of VBA: is there any way to tell if an object
has a property? All my attempts caused a run-time or compile error to
occur. Some of my code would be greatly simplified if I could just say
"If IsProperty(x.a) Then ...".

If the property is a member of the Properties collection (some are not), you
can use this little wrapper function:

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

Example:
If HasProperty(CurrentDb.TableDefs("MyTable"), "SubdatasheetName") Then

This example creates the property, and sets it, or just sets it if it
already exists:

Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & _
varValue & ". Error " & Err.Number & " - " & Err.Description &
vbCrLf
Resume ExitHandler
End Function

 

[sjg1314]

Replies embedded (with answered parts cut.)

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

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

If you want to delay the message until the record is being validated
instead
of the field, open the table in design view, and set the Required
property
to No for each field. Still in table design, open the Properties box
(View
menu), and enter a Validation Rule there for the table. Example:
([Surname] Is Not Null) AND ([City] Is Not Null)

I've seen this trick mentioned in a number of places. As an experienced
database programmer (who is an MS Access/Visual Basic beginner), I find
this horrifying. Instead of concentrating the knowledge of what fields
are required in one place (the Table), this information has to be
spread out into all the Forms that use the Table.

But the suggestion *was* to use the Validation Rule of the *table*.

No code is needed in any form.

I missed that--thanks!

If the property is a member of the Properties collection (some are not), you
can use this little wrapper function:...

Great--thanks again!