How to stop access VBA code conditionally?

Question

So I've created a form in Access, which has a combo box and a text box. The form calculates some things after pressing a button but either both the combo box and the text box have to be filled, or neither of them can be filled. What I'd like to do is put in a condition so that if only one of them is filled, a Message Box would popup explaining to fill both boxes and then proceed to stop the code. I understand how to do the message box part, but right now it's showing the message box and then proceeding on with the code which produces an error.

I've done some research but most of what I'm finding has to do with temporarily pausing the form, not completely stopping it.

I have a couple of ideas on how to do this, but I'm having trouble executing them and frankly I'm not even sure they'll work. My first plan was to copy and paste all the calculation parts from the procedure, create a new procedure with them, create an if statement in the button clicking procedure and call the new procedure if it passes the if statement. My second plan is a little more tedious but I'm fairly confident it'll work. It's just surrounding the entire code with an if statement, if it passes execute the code, if it doesn't, do nothing and have it reach the end of the procedure and end itself. My only issue with the second one is I have a LOT of code beforehand, and I'd rather not waste a large amount of time re-indenting lines if I can help it, but I have that as a backup if I can't find a better way.

Solution 1

You have a few options available to you:

1. As you suggested, take the code presently tied to the button click event and move it to a new sub procedure. In the button click event perform your validation and if everything passes invoke the sub procedure. If not, show your messagebox.
2. Assuming you have proper error handling in the button click event, raise an error instead of showing the messagebox. This will move the control flow to your error handler in the procedure.
3. After displaying the message box, use either Exit Sub or Exit Function to stop code execution.

Option 1 is probably the best approach as it promotes better coding practices. If you have long sub routines it could be a sign that you need to break the sub routine into smaller units of work, which makes code reuse and debugging much easier.

Option 2 is appropriate in some cases where the condition truly is an error. One could argue that failing a validation test is a form of an error condition.

Personally, I tend to avoid using Exit Sub and Exit Function (Option 3) in most cases because I like to have a single point in a sub routine where the routine exits and passes control back to the calling procedure. Having multiple exit points within a routine is a sign that you need to refactor your code \ rethink the logic you are applying. Sometimes it can't be helped, but often you create a situation that is more difficult to debug.

Solution 2

When you pop up the message box you should be able to use Exit Sub or Exit Function depending on what you procedure is. It should stop the execution of the code and exit the procedure.

Solution 3

I know it's late but i just wanted to add that if you want to stop running any VBA code, you can just use End in your error handler for example. This way it won't return to the sub/function who called (if there is one).

Hope it helps.