Call Event Handler

MsAccess 2005. I have a form with many controls that the user has to fill in. As a time saver, i've implimented the ability whereby the Ctrl->G keystroke combination gets the corresponding value from another record which the user has identified as being compatible.

So I have one routine which uses to identify the field. It then gets the value and writes it in. The thing which is missing is that I then need to call the control's AfterUpdate event which makes other things happen. So this is a simplified version of the code I would like to get working.

Public Sub GetValueFromOtherRecord
    Dim Fieldname as string
    Dim EventHandlerName as string


    EventHandlerName = FieldName & "_AfterUpdate
    Call EventHandlerName   'This is the line which needs to be fixed

End Sub

The Call statement doesn't work, presumably because the Event Name needs to be explicitly typed in my code. Is there a way I can dynamically call a procedure name?
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If you do:

    Me(FieldName).Text = GetOtherRecordFieldValue()

rather than:


 You will cause the AfterUpdate event of the control to fire.

 Also, I would not use "FieldName", but rather

 Dim  strControlName as String

  Fields and controls are different things.


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
Gustav BrockCIOCommented:
Or you could move the code from the AfterUpdate to a separate subfunction, then call this from both the AfterUpdate of the textbox and your GetValueFromOtherRecord.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Jim's suggestion would work (although I believe you'd have to SetFocus to the control in order to work with the Text property), but take heed of gustav's comment.

In general, if you need to run code from more than one location you should move that code to a Sub or Function and call it as needed. All too often I see code like you're describing and it can get to be a mess very quickly (at least in my opinion), and can be quite difficult to debug.
TownTalkAuthor Commented:
Thanks Jim that works great. I had actually found another solution using CallByName, but this requires me to declare all my Event handlers as public. So your solution is better.

I take your point about fieldname and controlname. In this case I supply the Field/ControlName to the routine which gets the the value. So it looks a bit strange strange in my Sql statement when I say "Select " & ControlName & " From ....". Ill strick with FieldName in this instance.

Thanks again.

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.