Expression or VBA?

[Banana]

I've made it a personal goal of mine to try and use expressions (in case this isn't really the right terms, I'm referring to what you would construct from the Expression Builder and put in control, without writing any line of VBA or using macros... e.g setting a control to =AFunction().

However, I've always found myself thinking to myself, "Gee whiz, this is sure clunky. I think I'll do it in VBA after all." I thought I'd ask around and see if this is just my prejudices getting in way and would love to know if I'm just missing something.

Now, I think expression builder is great idea. It has all list of values and functions for you, and you don't have to remember all the parameters; it does it for you. (though, it could be better at prompting the parameters whereas in VBE, I can see what datatype is expected, and if I'm not sure, the help box is just there in corner to get a quick refresh of what kind of arguments I need to pass) and the capability to do some functions such as keeping a running total is great, especially if it's done locally (I think?) without running to the backend.

It's usually the domain aggegrate that kills me. I've decided to just write a public function that I can then use in expression instead of default domain aggegrate and various, but the result is basically this: I'm still writing in VBA, though I'm doing a better job of reusing it, I'd think.

So... what are your opinions on expressions, and whether you find them to be more useful than doing the same thing in VBA? How often and what kind of cases you would use, or won't?

Thanks for sharing!

 

[doco]

Depends if its a one time deal or something that I find myself using over and over again. One thing about creating your own functions/modules/classes is maintainability and useablility. They can be saved in .bas/.cls/.txt files and be used over and over. Plus making a change in one spot and every where its used gets updated. Or gathered together into an addin.

 

[Pat Hartman]

There is really very little you can accomplish on a form with an expression in an event property. I wouldn't even consider it except for calculations in reports. The technique is not usable for bound controls which the meat of forms so again, I may add a total to a subform footer but that's about it.

As I have said many times, code is my last choice for a solution but my applications still end up with hundreds or even thousands of lines of code because you always need to edit data.

 

[Banana]

Thanks for sharing.

A while ago, I noticed various people referring to expressions and made me wondered if they were actually used. After all, a good programmers uses all tool in the toolkit, as long they're the right tool for job. (The thing is that we can use a sledgehammer and can still drive a nail... just not as easily and gracefully as a hammer would. )

But every time I tried, I just couldn't find myself seeing making good use of it because, well, VBA/SQL can do it better.

Would be nice if they expanded on this, though. I have to agree with Pat; the less lines you need to write, the better it is for everyone all around. (One notable exception: If the built-in functionality is totally buggy and unreliable, then yes, you want to write more lines of codes....)

 

[The_Doc_Man]

From Ecclesiastes, misquoted: There is a time and purpose for everything under Heaven.

Expressions in queries are truly important when a query is converting something for presentation, export, or update.

Expressions in a text box work if they are like a =DSum() of some kind in the footer of a report or on any visible but non-enabled text box on a form.

Expressions in an event slot are limited since they depend on an event firing from that exact slot.

Expressions in VBA are de rigeur - par for the course - natural.

Expressions in a table are oil and water - they don't mix except in criteria or default value slots for given fields.

I agree with Banana - expressions are in the tool kit. But it is the wise mechanic who knows when to use each tool properly.

 

[doco]

It has been my experience VB to be indispesnsible especially when needing almost any update query with any complexity. ( Access hates complexity in update queries and I absolutely abhor all of those verbose, PITA, parenthesis ) And I also have to deal with the issue brought up in this thread http://www.access-programmers.co.uk/forums/showthread.php?t=153605.

Writing reports from databases that are sufficiently normalized require a lot of 'pivoting' of data from row to column. I would much the rather have one or two well commented VB routines in place of numerous queries that have no commenting and are a PITA to maintain and or keep track on. Now it could be it is more of a skill-level problem than Access But, Pat, why are you VB adverse?

 

[datAdrenaline]

Hello Pat ...

>> There is really very little you can accomplish on a form with an expression in an event property. <<

I disagree ... although, I think I am looking at it differently that you ... you see, I will often create a procedure as a function and then set my event property to that function call, then I don't have TONS of small code snippets in my form module calling the same public function ...

 

[datAdrenaline]

I like to use the tool that is "best suited" (which is a subjective unit of measure eh?).

But first, I wanted to clarify something. An "Expression" is anything that returns something ... so ... a literal number, string, or object reference is an "Expression" to Access. So even if you create UDF's in VBA, then use that UDF in the control source of your text box, you are creating an Expression, you are just using your own functions instead of those Access provides to you. To illustrate my point, just check take a sampling of articles from the help system for some functions or methods. You will often see the use of the identifier expr as a placeholder for an argument of ojbect ... or you will read something like "...refers to a string expression ..."

So ... with that I will add that I use Access inherent functions, outside of VBA, when it is best suited for the task at hand. Again, "best suited" is a subject measure that is developer dependant and probably includes factors like re-usability and the process needed to arrive at the final value required.

Also note that many times my Expressions, utilize BOTH Access functions and UDF's, so my Control Source expression may look like this:

=fMyFunction([SomeControl]) * DLookup("MyFactor","tblMyFactors","[FactorID]=" & [FactorID])

...

Basically, use what you need to accomplish the task in an efficient manner!

 

[wazz]

I will often create a procedure as a function and then set my event property to that function call, then I don't have TONS of small code snippets in my form module calling the same public function ...

i was thinking about going that route recently, as an experiment of sorts, but then i thought, what's the difference between all those snippets behind the form, and all those snippets in the event property? i'm not sure the snippets in the event properties would be easier to deal with, having to open a form to change things (if more than just a function is called), but my jury's still out ...

 

[Banana]

Actually, I came to same conclusions as datAdrenaline did.

It made perfect sense to have a one module to hold all UDFs. An prime example is End of Fiscal Year which I need as a default value for several different controls. I just set it to =EOFY() and BOOM! it's there! No VBA at all and added benefit is that if my company all of sudden decided that fiscal year should be something else, I only have one snippet to change.

This could be also why Allen Browne came up with E<insert aggregate functions> collections as he was also dissatisfied with the D<insert aggregate functions>. As for the built-in functions, I usually find myself thinking, "That will easily require another round trip to the server for same pieces of data that form has already gotten." and therefore ended up using VBA to try and get that information using what is already via form's recordset or something local.

 

[datAdrenaline]

Hello Wazz ...

An example of where using a function call instead of an event procedure is useful is something like a Contact database and you want to filter out a list of contacts by the first letter ... so you have 26 buttons on a form labeled "A" through "Z" ... you would create a function behind the form that would have a procedure header like this:

Private Function pfFilterByLetter(strLetter) As Byte
    .... Do Stuff ....
End Function

Then in your OnClick property of each button you would have ...

=fFilterByLetter([Screen].[ActiveControl].[Caption])

....

By doing this, you can actully set up ONE button, then copy it 26 times (Properties COPY with the control, event procedures do not!!), and the only think you HAVE to change is the Caption ... you can event keep the TERRIBLE names of Text1, Text2 .... if you wish because there is no code refering to it, so ... no big deal if it has terrible names (kinda like labels, do you rename all your lables?) ... granted I would end up renaming my buttons simply because if I needed additional code, I could then switch to an Event Procedure easily and just call fFilterByLetter() code when appropriate in that procedure ...

----

Also ... I will often use this technique to provide great flexibility with opening detail forms ... For example ... if you have a sub-form that lists related items you can have an "ID" column of sorts, well you can put a dbl-click event function call on that control that opens the detail form of that related item... Now you can use that same function call on ANY control that shows that ID ... a single line to type with out haveing to switch into the VBA editor ...

Like anything, I think this type of technique is a great tool to know how to utilize it ... is it ALWAYS the best way? ... Nope ... but is sure is handy to know how to use it!

 

[Banana]

Like anything, I think this type of technique is a great tool to know how to utilize it ... is it ALWAYS the best way? ... Nope ... but is sure is handy to know how to use it!

Exactly why I made it a personal goal to at least familiarize with the functionality.

Would be doing myself a disservice if I just diss expressions out of snobbery. I'm sure we've all seen our shares of programmers like this:

(To be 100% fair & honest, I do diss macros and will outright refuse to use it, with AutoExec being a possible exception but am content with a startup form taking the place.)

 

[datAdrenaline]

>> I do diss macros and will outright refuse to use it <<

I am pretty much with you on that one!!! ... AutoKeys, however, is in just about all my apps. AutoExec ... well, I am okay with it, but I use a Startup form more often. But there are security holes with each.... but ... "say-la-vee" {don't know the proper french spelling!}

PS>
Does you previous post mean I can let all my butterflies go?

 

[Banana]

Okay, I'm curious- Autokeys are for?


Edit: Just looked at the help- I can understand why- they define key combinations for you to give your custom menu a shortcut. I guess there's nothing in VBA to do that, eh?

Edit #2: Another reason. It seems that Access help and VBA help are a different beast and that Access help always leave me wanting more, while VBA is reasonably more detailed and can answer 90% of my questions. Therefore, I've unconsciously learned to shun anything within Access environment because of the lack of documentations compared to doing the same thing in VBA.

 

[The_Doc_Man]

(Raise shields for inevitable firestorm...)

And the truth is, REAL programmers use assembly language. Or worse, BINARY via depositing bit patterns from the front console switches. (OK, am I showing my age yet...?)

(Lower shields...)

Seriously, at some point the bit about functions vs. event procedures becomes blurred when you have underlying code in the user-defined function that really DOES something that affects other things on the form via a programming side effect. It might as well have been an event procedure. And I believe (but don't quote me) that event procedures actually COULD return a value (i.e. act like a function) because the event dispatcher doesn't care to look for return values anyway. Just discards them.

The only reason I am not so sanquine about using functions in place of event procedures is precisely because you are creating "hidden" side effects by doing so. With an explicit event procedure, people EXPECT to look in the code to see what it is doing to other things. With a function call, that expectation is less obvious and could lead to confusion. But that isn't an issue of legality, it is an issue of "best practices."

Revisiting the "use the tool where it is appropriate" comment:

With Macros, don't downplay their usefulness. For instance, the trick of activating a database at a specific time of day via a Windows Task Scheduler entry to run a command-line function that activates the Macro to run some nightly thing on a scheduler and the Macro ends with a QUIT step. Try to do that any other way that doesn't involve leaving the database open on somebody's terminal at night, which is a major security risk.

 

[georgedwilkinson]

"say-la-vee" {don't know the proper french spelling!}

"c'est la vie"

 

[datAdrenaline]

Hello Bannana ...

>> they define key combinations for you to give your custom menu a shortcut. <<

The custom keystroke will execute ANY macro action you wish ... For example ... my AutoKeys macro has 3 named macros ...

<ctrl><f12> : performs the actions of setting warnings to True, turns the Echo back on, and turns OFF the hour glass.... This is invaluable when developing and you code halts prior to turning all that stuff back on!

<ctrl><f11> : performs the RunCode action. The code executed is a function I used to toggle between "Development" mode {what I see} and "Run" mode {what the user sees}

<ctrl><shift><f12>: Opens a form I use for Administration type settings

Please note that NONE of those things are on my custom menu/tool/command bars.

....

 

[datAdrenaline]

George ...

>> "c'est la vie" <<

Thanks! ... you'd think that after 2 years of French, and the commonality of such an expression, I would remember that .... but alas I did not at the time ... ...

 

[doco]

say-la-vee" {don't know the proper french spelling!}

C'est le Vie

guess Geo beat me to it ...

 

[Banana]

(Raise shields for inevitable firestorm...)

And the truth is, REAL programmers use assembly language. Or worse, BINARY via depositing bit patterns from the front console switches. (OK, am I showing my age yet...?)

(Lower shields...)

I don't think you're far off the mark. Even though it's less common nowadays, it still doesn't change the fact that we're still work with bits and using it in a certain way to represent something. This, IMO, is source for 90% of bugs. They basically boils down to abusing a representation of data in some other form that it shouldn't be in. Endianness, floating & rounding errors, ASCII/Unicode, size of datatype... you name it. There is no real "native" datatypes! And to drive the point home, we're really using pointers. It may not look like it, but we are. Microsoft just has took upon the cumbersome mantle of maintaining the pointer for us so we can be bit more productive. This is not without its cost as whenever we do something and thus lose the pointer, we may get weird results.

Seriously, at some point the bit about functions vs. event procedures becomes blurred when you have underlying code in the user-defined function that really DOES something that affects other things on the form via a programming side effect. It might as well have been an event procedure. And I believe (but don't quote me) that event procedures actually COULD return a value (i.e. act like a function) because the event dispatcher doesn't care to look for return values anyway. Just discards them.

That's a good point, and as I said earlier, they didn't feel like "documented" to me, for lack of better word. I only have a vague notion of what they could do, but it's basically a gamble that they will act the way I expect it to. OTOH, writing my own code enables me to know exactly what it did.

I wonder if this would help if VBA allowed overloading.... (though I'm sure that a novice programmer will shriek with insanity if they saw overloaded functions)

Revisiting the "use the tool where it is appropriate" comment:

With Macros, don't downplay their usefulness. For instance, the trick of activating a database at a specific time of day via a Windows Task Scheduler entry to run a command-line function that activates the Macro to run some nightly thing on a scheduler and the Macro ends with a QUIT step. Try to do that any other way that doesn't involve leaving the database open on somebody's terminal at night, which is a major security risk.

Thanks for sharing. Good to know that they're actually useful for the tasks you described. It may just be that I've yet to find a need for macros, and to be honest, I do fear macros for much same reason- they're totally undocumented and opaque to me. It's weird, though, because people talk about black-box programming, but I would rather see reams of documentations so I can start to understand what I can do with it instead of just a one piddly paragraph about how it can be Useful! Helpful! Save your time! with one impractical example that just raises more questions than answer them.