Correct syntax for multiple If statements (1 Viewer)

[hilian]

I need to control invalid data for several text boxes. For example, If a user enters a 0 in text box TR_2, then text box TR_3 becomes invisible and focus shifts to TR_4. However, if the user enters a value in TR_3 first and then enters a 0 in TR_2, then the (invalid) value in TR_3 is saved. It prevent this, I added a before update event to make any value in TR_3 null.

This scenario occurs a number of times on my form. Do I enter a separate event for each, or do I use one event with successive If statements?

Thanks,

Henry

 

[static]

If it's a relatively simple form otherwise, I'd disconnect it and update the table after all validation is complete.

 

[The_Doc_Man]

Do I enter a separate event for each, or do I use one event with successive If statements?

To answer the direct question, you could have one LostFocus event for each text box and put your test code there. It is imperative to remember that the exact moment that your LostFocus code returns via Exit Sub or End Sub that the next thing executed will be the GotFocus code for whatever control is next in Tab Order. So to prevent stepping from TR2 to TR3, you have to disable TR3 before the LostFocus event. But that can lead to hair-tearing because of rules about setting focus to disabled controls and disabling controls that already have focus.

The trick will be that the controls can lose focus in several different ways (TAB key, mouse click, other program action) so you MIGHT need to have some sort of common routine that you can call to do validation as a whole, and that might call might better be served in your form's BeforeUpdate event.

As a form-design issue, have a button that does the save when clicked. You could put the validation code in that button's OnClick code and then if there is something wrong, don't let the Save action occur. Instead, assure that the required boxes are visible and then set focus to whichever one is the right one for the situation.

 

[Cronk]

I'd use either the BeforeUpdate or AfterUpdate events of the controls involved in the algorithm.

Have the code for the algorithm in a separate sub, rather than duplicating the code in each control's event.

And if there are a lot of controls involved, use a macro to call the subroutine. You can multi select multiple controls and set the macro in one design operation. This is faster than having to enter code in each control's event.

This is the one use I have made of macros, other than AutoExec for initialization and a Relink macro to facilitate linking when moving to and from Design and production.

 

[hilian]

Thanks to Cronk, The_Doc_Man and static,

The problem was to prevent invalid data from getting into the table, and I realized there was a simple solution. Right now each text box that that requires the next text box or boxes to be skipped, depending on the value, has code that makes the boxes that should be skipped invisible on the after update event when the value that requires skipping is entered. For example, if a 0 is entered in TR_2, TR_3 should be skipped, otherwise it requires an entry. That works reasonably well except when the user makes an entry in the dependent text box (the one to be skipped) before entering the code into the text box that governs it. I realized that if the dependent text boxes were not enabled when the form loads and only enabled on the after update event in the prior boxes, the problem would be solved.

Again thanks,

Henry