Solved: How do I prevent the user from entering null values and creating redundant data?

[wedd]

Hi, I would like to prevent the user from entering an earlier date in the end time field than the start time field. Is there a validation rule, expression, macro, query or vba I can use to allow this quality data security to check through the database to prevent users from entering data that is null? Also, do you have an examples or websites were I can serach through a range of validation checks? The help feature on my access version is a bit limited in terms of examples setting valadtion rules on my customer records database.



Thanks for your contributions:friends:

[CreganTur]

You can create your own validation using VBA. For this example I tied my validation routines to the clicking of a "Next" button on my form:

'input validation
If IsNull(Me.EMail) Then '<<<E-Mail address
MsgBox "All required fields must be filled in.", vbExclamation, "Invalid Entry"
Me.EMail.SetFocus
Exit Sub
End If
If IsNull(Me.NewPmntDte) Then '<<<New payment date
MsgBox "All required fields must be filled in.", vbExclamation, "Invalid Entry"
Me.NewPmntDte.SetFocus
Exit Sub
End If
If Me.NewPmntAmnt = 0 Then '<<<New payment amount
MsgBox "All required fields must be filled in.", vbExclamation, "Invalid Entry"
Me.NewPmntAmnt.SetFocus
Exit Sub
End If
If IsNull(Me.NewMonths) Then '<<<New months of loan
MsgBox "All required fields must be filled in.", vbExclamation, "Invalid Entry"
Me.NewMonths.SetFocus
Exit Sub
End If
If IsNull(Me.PmtDefer) Then '<<<Number of payments deferred
Me.PmtDefer = 0
End If

By using the SetFocus method I'm able to point the user to the field where there is a problem and ExitSub allows me to exit from the sub early.

You can do a number of advanced things for validation- it just depends on what you need.

[wedd]

Thanks!

[CreganTur]

If this has solved your problem, then please mark the thread as solved using Thread Tools at the top of this page.

[geekgirlau]

As well as doing an overall validation check as Randy suggests (and a good event to use for this is BeforeUpdate) you can also use the BeforeUpdate event on specific fields, so you are checking the value before the user moves on to another field.

This is no use for checking nulls (as it will never be triggered) but for something like your date validation, you check the value, display an error message, then use "Cancel = True" to prevent the invalid value from being written to the field.


If NZ(Me.dtmEnd,#1/1/1980#) < NZ(Me.dtmStart, #1/1/1980#) Then
MsgBox "The end date must be later than the start date.", vbExclamation, "Invalid Entry"
Me.dtmEnd.SetFocus
Cancel = True
End If

[wedd]

Thanks!