Validation

[cable]

Some fields are set to required in our backend and if you try to save the form with out filling these in then Access brings up its defualt, not very clear, error message.

Is there a simple way of intercepting this and replacing it?

Or if not then is it possible to loop through the fields, checking the required property etc? If so which event would this code be best placed?

 

[Jeff Bailey]

Put some code in the Before Update event of the Form to check the required controls and pop up a message if a control is null (i.e. empty)

Set Cancel = True (as well) to cancel the update and the user will be returned to the form to fill in the missing data.

Jeff

 

[indesisiv]

I came across something a while ago that was posted by Dave Eyley and goes something like this.

IsNull will only detect null fields. I had this problem when users entered and then deleted the field leaving a "" string which wasn't detected with the IsNull function.
Here is a small routine which I use as a function which detects empty fields of all types -

It goes like this -

' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing = False
End Select

Create a new module and call the function IsNothing()

To test a field you simply type IsNothing(Field)

Hope this helps you

 

[cable]

thanks for the help...eventually came up with this:

Private Function ValidateForm() As Boolean
'validates the form fields based upon the required setting of the field
'returns true if ok, false otherwise
Dim myDB As DAO.Database
Dim myTD As DAO.TableDef
Dim myFD As DAO.Field

Set myDB = CurrentDb
Set myTD = myDB.TableDefs(Me.RecordSource)

ValidateForm = True

'loop through each field in the forms tabledef
For Each myFD In myTD.Fields
'check its required property and if it is null
If myFD.Required = True And IsNull(Me(myFD.Name)) Then
'it is...warn the user and focus on that field
msgbox myFD.Name & " must be completed!", vbExclamation

'could also use
'msgbox myFD.ValidationText

Me(myFD.Name).SetFocus
ValidateForm = False
Exit For
End If
Next myFD

End Function

Which should be put anywhere on the form in question (or it could be modified so that it could be put in a general module)

and call in the before update event similar to this:
If ValidateForm = False Then Cancel = True: Exit Sub

It then checks all the fields (the isnull could be replaced by the isnothing function in indesisiv's post) and if they aren't filled in then warns and sets focus.

So as far as I can tell it does allow for required fields that are independant of the program and are purely the choice of the user (as all the programs I write are frontend/backend)