Eval() for calling an Event proc

[Kowalski]

Hi
I've got something that worked in older versions of Access, but Access 2010 (and perhaps 2007) is giving problems.

I've got a generic builder for a text box on a form. The builder will assist the user to enter data, and then add that value to the text box. Under normal circumstances the control's before and after events does not fire when the builder populates the control, so we've added code in the builder to call the procs explicitely of the control with something like...
Eval(Forms.MyForm.UserName_AfterUpdate())
And that works.

But since it is a generic builder, I need to work with some variable or control, so I try the following and it fails.
myVar = "Forms.MyForm.UserName"
Eval (myVar & "_AfterUpdate()") or...
Eval("Forms.MyForm.UserName_AfterUpdate()")
Error: Access failed to evaluate one or more expressions because 'UserName_AfterUpdate' was referenced in an expression.
Which kind of makes sense, but it was working in Access 97 - 2003. Not anymore.

Any hints to fire the Before and AfterUpdate events when using a generic builder?

 

[JamesMcS]

Hi - I would think myvar, in the first example, would need not to be in quotes - otherwise you're populating the variable with the string "forms.myform.username", rather than what that property is referencing.

 

[Kowalski]

Yip, I do agree, but if I go:
Dim myVar as Control
myVar = Forms.MyForm.UserName

Then, to call the AfterUpdate, I'll have to do something like:
Call myVar.AfterUpdate() or
Eval (myVar.AfterUpdate())

The first option give error:
Object doesn't support this property or method
Even though the AfterUpdate do exist and are declared as public.

The second option give error:
Access failed to evaluate one or more expressions because 'UserName_BeforeUpdate' was referenced in an expression.

 

[JamesMcS]

Bit confused about this generic builder thing... can you explain as though to a 5 year old?

 

[spikepl]

If

Eval(Forms.MyForm.UserName_AfterUpdate())

works

then

how about

Eval(Eval("Forms.MyForm.UserName_AfterUpdate()") )

 

[Kowalski]

Hi
1. spikepl: I love your suggestion, but sadly no success. Still get 'Access failed to evaluate one or more expressions because 'UserName_AfterUpdate' was referenced in an expression.' Thanks.

2. JamesMcS: thanks for the effort to try and understand. As an example:
Let's say I've got a large database with 1000's of Personnel loaded. No, I've got a form Tasks and I would like to assign a task to one of these people by changing the control "Assigned To". It's a combobox, but due to the fact that there's so many people, it is difficult for the user to select the correct person. Just to many. So now I add a button next to the 'Assigned To' field that will open up a pop up form where the user can do a search based on name, surname, country, office, etc. The pop up form will show personnel that meet the search criteria and the user can select from a shorter list, click OK and the pop up form will populate the Assigned To control on the Tasks form. But now the Before- and AfterUpdate events of the Assigned To control do not fire because the value was added with code. Thus I would like to fire the events with VBA. I need the solution not only for things like that, but for others like assisting the user with a pop up form to enter GPS coordinates easy and correctly (need more assistance than what masks and regex can give). That's just 2 examples, but the solution needs to be fairly generic. Hope it makes sense.

 

[boblarson]

Just an FYI -

To use a variable to reference I believe you would need to use this method:

Eval("Forms.MyForm.UserName.Form_AfterUpdate")

because the after update is not on the subform control which is what you need to be referring to and not the form, when you have subforms. Then the .Form part is added because it tells Access you want something on the form. This is especially important if the subform control (control on the parent form which houses the subform) is not named the same as the subform itself.

 

[ChrisO]

Eval (Forms.MyForm.UserName_AfterUpdate())

will actually call the UserName_AfterUpdate() subroutine but then errors because the Eval Function is trying to evaluate a Subroutine.
A Subroutine has no value assigned to its name and hence can’t be evaluated.

So when you say it worked in previous versions it probably didn’t unless the error was suppressed with something like:-

On Error Resume Next
Eval (Forms.MyForm.UserName_AfterUpdate())


So to call a Subroutine by variable name using the Eval function then:-

Dim strVariableName As String

strVariableName = "UserName"
On Error Resume Next
Eval ("Forms.MyForm." & strVariableName & "_AfterUpdate()")


where UserName_AfterUpdate is Public as in:-
Public Sub UserName_AfterUpdate()

Chris.

 

[Kowalski]

Hi ChrisO
Your suggestions makes sense, and I've checked. It works fine in Access 97, but not in Access 2010.
Err.Description is "Access failed to evaluate one or more expressions because 'UserName_AfterUpdate' was referenced in an expression."

boblarson: Your suggestion also does not work - sorry. The control is not on a subform, but on the main form.

Thanks

 

[ChrisO]

Just to make sure there is a test Access 2003 database attached.

Chris.

 

[Kowalski]

I managed to find a suitable answer. To call the event proc you should not use Eval(), but CallByName. Eg:
strVariableName = "UserName"
CallByName Forms("MyForm"), Forms("MyForm").Controls(strVariableName).EventProcPrefix & "_AfterUpdate", VbMethod

Thanks for the help.