Validation rule

[VPK_Access]

Hi,

I have created a valdiation rule in VB to validate one field based on the value of anotehr field. The code works fine and I am displaying a message also to the user to enter the value.

Now this is the issue........the user can click Ok on the message I display and go to the next record without entering the value.

How can I restrict the user not to goto the next record if they have not entered the field value.

 

[Galaxiom]

Instead of letting the validation rule take control you need to validate using code in an appropriate event. The AfterUpdate of the Control or the BeforeUpdate of the Form can be used.

When this validation fails, direct the processing to stop by sending the focus back to the control in the case of the AfterUpdate of the control or Cancel the update in the case of the BeforeUpdate of the Form.

Instead of simply poping up a message you can use an Input Dialog or a form to ask for the value.

 

[VPK_Access]

I am currently calling the code from the AfterUpdate event of the form and is passing the focus on the control.

However my problem is that the user can goto the next record by clicking OK to the message I have dispalyed.

Is there any way to mandate that the user enter the value of the field and then only can goto the next record.

I want this to be function just liek if I had given a validation in the field validation rule. Any help?

 

[VPK_Access]

I had tried inputting the below vlaidation in the validation rule. However it didnt work(though didnt error also)

([Status]="Completed") Or ([Closed Date] Is Not Null)

I want to user to enter the Closed Date field...mandatorily if they chose the Status as Completed.

Since this was not working I wrote the VB code with teh validation and put it in the Afterupdate event.

 

[Galaxiom]

Are you sure you don't have a normalization error? Could the presence of a closed date itself indicate the Status rather than having a separate field to indicate it?

 

[VPK_Access]

The Status field contains other values(not only Completed). I need the Closed Date value to be entered as there are some reports and stats based on the date.

 

[Galaxiom]

However my problem is that the user can goto the next record by clicking OK to the message I have dispalyed.

After the message use the line:
Me.DateCompleted.SetFocus

This will take them back there and trigger the AfterUpdate again if they try to leave without entering a value.

BTW. It is generally more efficient to store the status as a number. The word can be displayed on the forms and reports using a lookup.

Also I would recommend you stop including spaces in names. Then you don't need square brackets around them. It is also easier to read code without the spaces in names. Most experienced developers use CamelCase.

 

[VPK_Access]

Hi

I tried what you have suggested. However still I am able to goto the enxt record.

I am calling the code in the Afterupdate event and in that I gave the below.

[Forms]![SubForm]![ClosedDate].SetFocus


Any help?

 

[PeterF]

You should use the before update event of the form to prevent saving of incomplete data. The after update event is after saving it in the DB so a user can go to the next record or exit the DB leaving incomplete records.

 

[Galaxiom]

You should use the before update event of the form to prevent saving of incomplete data. The after update event is after saving it in the DB so a user can go to the next record or exit the DB leaving incomplete records.

As I said earlier:

The AfterUpdate of the Control or the BeforeUpdate of the Form can be used.

The difference is that the AfterUpdate of the Control triggers when the user leaves the control while the BeforeUpdate of the form happens when the user leaves the record.

The Control_AfterUpdate event is good when you have several independent validations because it deals with them as they are entered instead of all at once on the Form_BeforeUpdate.

However the Form_BeforeUpdate is good for validations where that depend on multiple controls. It is also required to prevent an unvalidated record being saved with empty fields for example since the control's validation requires the user to visit it.

In practice a combination of both events is very useful. In many cases the detailed validation can be placed on the control with the form validation simply checking the controls are not Null or Zero Length String and dealing with the combined validations. Sometimes the form validation can simply retrigger some of the control validations.

The developer just has to be conscious of the overall logic and validation goals.