Validation Check Using VBA - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Validation Check Using VBA

Dan Jackson

4 years ago

Hi, Hopefully a quick easy one

I have a combo called Combo_Status and another combo called Combo_AdvisorID.
AdvisorID must not be IsNotNull unless the Status is "56". So i've put this code in the after update on AdvisorID

==========================
If (Combo_Status) = 56 Then GoTo SkipValidationCheck
        If IsNull(AdvisorID) Then
                If MsgBox("Advisor Required", vbOKOnly) = vbOK Then
                Forms!SalesIndividualViewMORTGAGEF!Combo_AdvisorID.SetFocus
            End If

SkipValidationCheck:

End If
===================================

The main part works - it'll allow a blank if set to 56, otherwise i get the messagebox.
The setfocus doesn't work - the cursor moves to the next field. What am i doing wrong? Cheers

	Scott Axton @Reply 4 years ago
There really isn't a reason to use the message box as a function when you are only using vbOK (the default) because there isn't a second choice to cancel - you are just displaying a message. Try If IsNull(AdvisorID) Then MsgBox "Advisor Required" Forms!SalesIndividualViewMORTGAGEF!Combo_AdvisorID.SetFocus End If

	Dan Jackson OP @Reply 4 years ago
I had that originally but again, the set focus didn't work. Figured if i put the ok inthere, i could program it...

	Dan Jackson OP @Reply 4 years ago
No probs, found another way around. Million different ways... lol

Scott Axton @Reply

4 years ago

Dan I've looked and looked but cant find the thread I had quite some time ago. I had a similar problem.
Long story short you may have to put the check on the OnGotFocus of the next field in the tab order.

My situation was a little different possibly because I was working between two different forms.
So to try it out - look at where the focus goes where the message box fires and when you hit OK.

Something else that is sticking out at me is what you said above, "AdvisorID must not be IsNotNull unless the Status is "56". " That is a double negative statement and a little confusing. Is that something you set at the Table Level?

	Dan Jackson OP @Reply 4 years ago
No worries. That's just my bad typing - Its essentially IsNotNull.(AdvisorID) Cheers

	Richard Rost @Reply 4 years ago
Consider using BeforeUpdate. You can cancel the event.

Kevin Yip @Reply

4 years ago

Dan, it seems you were trying to have your combo box set its focus back on itself, inside its own event procedure. That won't work in this case, because the AfterUpdate event will end up taking the focus somewhere else, after SetFocus is run.

Richard is right: BeforeUpdate is for situations like this. If the user doesn't enter the right data, set Cancel = True inside BeforeUpdate, and the user can't leave the combo box until the right entry is made. Note that this won't work if the user simply tabs through an empty combo box without entering anything. If the user doesn't actually make an update, BeforeUpdate won't run. So you may have to add a Form BeforeUpdate event too.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View