Data entry validation

[peev2001]

Hi every body,

I have some basic queries regarding validation of data entered by different users:

I have a form from where the users are going to enter the data. I have tried to put some validation rule for the data entered and I am checking at the EXIT procedure of the objects. With this, whenever an incorrect data is entered a message pops up and the focus is set to the text box again for another entry. This thing works ok but I want to set such rules for many data entry boxes. If I put in all of these codes on EXIT procedures, some times some of these rules do not work. I was surprised to note that if I change the procedure to private and vice-versa it starts working again.
Any suggestions for a better way to deal with this?

Also, if the data entry is done directly from the table, it does not look at the rules. How should this type of validation be done?

[comteck]

You can set validation rules for all forms, or for individual forms. I'm not sure that you can do it for a select number of forms.

comteck

[MMcCarthy]

I interpret from your message that you are validating entry using VBA code in the On Exit events of fields.

Does this have to be done each time. I would normally put all validation into a Function. The function can then be called from the Save button of a form.

As long as a Function is written in the class (VBA code) of the form it can be called anywhere on the form and can use the values in any field on the form using the Me.Field.Value

For example:

Function Validation() As Boolean

' Initialise Validation Return
Validation = True

' Enter all your validation code here

' If at any time validation rules have been broken set the following
Validation = False

' Add a message box and reset the appropriate field value

' When the funtion ends it will return a True or False value

End Function


See the following procedure as an example of calling the function.

Private Sub cmdSave_Click(C ancel As Integer)
Dim rslt as Boolean

rslt = Validation
If rslt = True Then
' Your code, e.g. close the form
Else
'Return to the form
Me.SetFocus

End Sub

Hi every body,

I have some basic queries regarding validation of data entered by different users:

I have a form from where the users are going to enter the data. I have tried to put some validation rule for the data entered and I am checking at the EXIT procedure of the objects. With this, whenever an incorrect data is entered a message pops up and the focus is set to the text box again for another entry. This thing works ok but I want to set such rules for many data entry boxes. If I put in all of these codes on EXIT procedures, some times some of these rules do not work. I was surprised to note that if I change the procedure to private and vice-versa it starts working again.
Any suggestions for a better way to deal with this?

Also, if the data entry is done directly from the table, it does not look at the rules. How should this type of validation be done?

[peev2001]

Thanks for your prompt replies. I am going to try writing these rules to a function and then checking on a save event. But actually, my requirement is to validate the data as and when it is entered. E.g if a wrong data is entered, I want a message to pop up and not accept the data entered and bring focus back to that field and ask for a correct entry. I want to do this for several fields.
Following is the script that I have now. I have a similar script for 6 fields e.g. and some of the validation rules do not work every time.
---------------------------------
Sub CL_path_Exit(Ca ncel As Integer)
Me.CL_modified_ new_CL.SetFocus
If (CL_modified_ne w_CL.Text = "YES") And IsNull(Me.CL_pa th) = True Then
MsgBox "You must enter CL path."
'Me.CL_path.Set Focus
Else
If (CL_modified_ne w_CL.Text = "NO CHANGE") And Not IsNull(Me.CL_pa th) = True Then
MsgBox "CL path should be blank."
Exit Sub
End If
End If
If (CL_modified_ne w_CL.Text = "YES") And IsNull(Me.CL_pa th) = True Or (CL_modified_ne w_CL.Text = "NO CHANGE") And Not IsNull(Me.CL_pa th) = True Then
Me.CL_path.SetF ocus
Else
Me.Tuning_param eters_changed.S etFocus
End If
End Sub
--------------------------------------------------

[MMcCarthy]

Try this code instead.

Firstly, the '= True' at the end of an IsNull statement is not required.
Secondly, you haven't got the logic right on your last if statement you have to bracket both sides off. However, I think the reworking I've done on the logic will capture all events and eliminate the problems you've been having.

Sub CL_path_exit(Ca ncel As Integer)

Me.CL_modified_ new_CL.SetFocus
If (CL_modified_ne w_CL.Text = "YES") And IsNull(Me.CL_pa th) Then
MsgBox "You must enter CL path."
Me.CL_path.SetF ocus
ElseIf (CL_modified_ne w_CL.Text = "NO CHANGE") And Not IsNull(Me.CL_pa th) Then
MsgBox "CL path should be blank."
Me.CL_path.SetF ocus
Exit Sub
Else
Me.Tuning_param eters_changed.S etFocus
End If

End Sub

If you want to change this to a function you can pass all three parameters in as strings.

Thanks for your prompt replies. I am going to try writing these rules to a function and then checking on a save event. But actually, my requirement is to validate the data as and when it is entered. E.g if a wrong data is entered, I want a message to pop up and not accept the data entered and bring focus back to that field and ask for a correct entry. I want to do this for several fields.
Following is the script that I have now. I have a similar script for 6 fields e.g. and some of the validation rules do not work every time.
---------------------------------
Sub CL_path_Exit(Ca ncel As Integer)
Me.CL_modified_ new_CL.SetFocus
If (CL_modified_ne w_CL.Text = "YES") And IsNull(Me.CL_pa th) = True Then
MsgBox "You must enter CL path."
'Me.CL_path.Set Focus
Else
If (CL_modified_ne w_CL.Text = "NO CHANGE") And Not IsNull(Me.CL_pa th) = True Then
MsgBox "CL path should be blank."
Exit Sub
End If
End If
If (CL_modified_ne w_CL.Text = "YES") And IsNull(Me.CL_pa th) = True Or (CL_modified_ne w_CL.Text = "NO CHANGE") And Not IsNull(Me.CL_pa th) = True Then
Me.CL_path.SetF ocus
Else
Me.Tuning_param eters_changed.S etFocus
End If
End Sub
--------------------------------------------------

[peev2001]

Great!!Thanks, I will try this tonight.