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

Tech-Archive recommends: Speed Up your PC by fixing your registry

Replies embedded (with answered parts cut.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sjg1314@xxxxxxxxx> wrote in message
news:1152021776.025595.66450@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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


.

Quantcast