By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,919 Members | 1,625 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,919 IT Pros & Developers. It's quick & easy.

Data entry validation

P: 5
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?
Aug 22 '06 #1
Share this Question
Share on Google+
5 Replies


100+
P: 179
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
Aug 22 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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(Cancel 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?
Aug 22 '06 #3

P: 5
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(Cancel As Integer)
Me.CL_modified_new_CL.SetFocus
If (CL_modified_new_CL.Text = "YES") And IsNull(Me.CL_path) = True Then
MsgBox "You must enter CL path."
'Me.CL_path.SetFocus
Else
If (CL_modified_new_CL.Text = "NO CHANGE") And Not IsNull(Me.CL_path) = True Then
MsgBox "CL path should be blank."
Exit Sub
End If
End If
If (CL_modified_new_CL.Text = "YES") And IsNull(Me.CL_path) = True Or (CL_modified_new_CL.Text = "NO CHANGE") And Not IsNull(Me.CL_path) = True Then
Me.CL_path.SetFocus
Else
Me.Tuning_parameters_changed.SetFocus
End If
End Sub
--------------------------------------------------
Aug 23 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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(Cancel As Integer)

Me.CL_modified_new_CL.SetFocus
If (CL_modified_new_CL.Text = "YES") And IsNull(Me.CL_path) Then
MsgBox "You must enter CL path."
Me.CL_path.SetFocus
ElseIf (CL_modified_new_CL.Text = "NO CHANGE") And Not IsNull(Me.CL_path) Then
MsgBox "CL path should be blank."
Me.CL_path.SetFocus
Exit Sub
Else
Me.Tuning_parameters_changed.SetFocus
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(Cancel As Integer)
Me.CL_modified_new_CL.SetFocus
If (CL_modified_new_CL.Text = "YES") And IsNull(Me.CL_path) = True Then
MsgBox "You must enter CL path."
'Me.CL_path.SetFocus
Else
If (CL_modified_new_CL.Text = "NO CHANGE") And Not IsNull(Me.CL_path) = True Then
MsgBox "CL path should be blank."
Exit Sub
End If
End If
If (CL_modified_new_CL.Text = "YES") And IsNull(Me.CL_path) = True Or (CL_modified_new_CL.Text = "NO CHANGE") And Not IsNull(Me.CL_path) = True Then
Me.CL_path.SetFocus
Else
Me.Tuning_parameters_changed.SetFocus
End If
End Sub
--------------------------------------------------
Aug 23 '06 #5

P: 5
Great!!Thanks, I will try this tonight.
Aug 24 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.