How do I resume VBA afer user input?

With this procedure, how do I resume the "DateAddWorkDays" public function when the cboPriority field was empty and I had the user go to the Priority field to select a value?

Private Sub OpenDate_AfterUpdate()

 If Len(Me.cboPriority.Column(2) & "") = 0 Then
        MsgBox "Priority may not be blank.", vbinformational
        Me.cboPriority.SetFocus
        Exit Sub
    End If
   
    Me!FollowupDate = DateAddWorkdays(Me!cboPriority.Column(2), Me!OpenDate.Value)

End Sub
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You can't.  The AfterUpdate event only runs when a control has been updated.  You will need to find another way to trigger the code.

I would suggest moving the If statement to the OpenDate onChange event.  That way, it will run as soon as you type a single character into the OpenDate field.  You can leave the Me.FollupDate expression where it is.  However, since the onChange event doesn't have a cancel argument, you will need to back out the change and cancel the event so you can move focus to the priority combo.
0
Rey Obrero (Capricorn1)Commented:
place this codes in the afterupdate event of the combo ".cboPriority"

private sub .cboPriority_afterupdate()

if me.cboPriority & ""<> "" and  Me.OpenDate & ""<> "" then
   
   Me.FollowupDate = DateAddWorkdays(Me.cboPriority.Column(2), Me.OpenDate.Value)
 
end if

end sub
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David BigelowStaff Operations SpecialistAuthor Commented:
Too cool! Thank you!

By the way, I have a few relatives up in the Seattle area, in Brier. Beautiful country! My aunt showed Tennessee Walker horses there for years.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David BigelowStaff Operations SpecialistAuthor Commented:
@Rey,

Should I read the code as "After the Priority field is updated, if the Priority is not empty and the Open Date is not empty, then run the DateAddWorkdays function in the Follow Up Date field?
0
Rey Obrero (Capricorn1)Commented:
that is correct..
0
David BigelowStaff Operations SpecialistAuthor Commented:
Great. Thank you!
0
PatHartmanCommented:
I see that you have selected an answer but I don't believe it will work in all cases.

When you have two controls that are required for some action, putting the code into the AfterUpdate event of either will not work reliably even when you validate both fields as Rey has.  You can assume that the data will be entered into fldA first and so put the code into fldB but what happens if the user doesn't follow the expected pattern?  What if he puts the data in fldB first?  To account for the user going off script, you need code in both controls and you have to somehow figure out when to throw the error.

If you don't mind waiting until all the data entry is done, the best place to put code that validates the presence of fields is the FORM's BeforeUpdate event.  This event is the LAST event that runs PRIOR to the record being saved so at this point all the data entry is done.  If at this time something is missing then, it is appropriate to cancel the update so the bad record will not be saved and inform the user of the missing data.
If Me.somefield & "" = "" Then
    msgbox "some field is required.",vbOKOnly
    Cancel = True
    Me.somefield.SetFocus = True
    Exit Sub
End If

Open in new window

Even when I put notification code into an event level control to get it closer to the point of entry, I frequently repeat it in the Form's BeforeUpdate event because I want the edits at the control level to be gentle and allow the user some flexibility in how he chooses to populate the form.

Even though you may put validation code into control level AfterUpdate events, that same code at the Form level should always go into the BeforeUpdate event.  Remember, the BeforeUpdate event fires BEFORE the bad data is saved and so you can prevent it from being saved by cancelling the event whereas the AfterUpdate event fires AFTER the bad data is saved and at that point it is too late.  With controls, the Before/After events only control whether the value has been moved to the buffer area where Access keeps values that it WILL save when the form level event fires.
1
David BigelowStaff Operations SpecialistAuthor Commented:
@Pat,
The situation you described is the one I was addressing. Field B was being updated before Field A, trying to run a Public Function, and giving me an error message. To resolve this, I caused that error message to put the focus on Field A with a message to the user to update Field A. Once he does that, since Field B was just populated prior to Field A, the AfterUpdate event on Field A resumes the code that had previously given an error message due to an empty Field A. If he doesn't update field A, then nothing happens because there is no logic to trigger a further event. Maybe I'll put an additional reminder on the Form's BeforeUpdate as an additional safeguard. Thanks for the code for that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.