[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Call Event Handler

Posted on 2014-07-16
Medium Priority
Last Modified: 2014-07-16
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 screen.activecontrol.name 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?
Question by:TownTalk
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40199184
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.

LVL 52

Expert Comment

by:Gustav Brock
ID: 40199196
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.

LVL 85
ID: 40199211
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.

Author Closing Comment

ID: 40199246
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.


Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question