Access 2010: ReturnVars in data macros

I noted that ReturnVars seems to be a collection... So could we return more than one return vars or maybe call several data macros and get all return vals from the same collection? How are the members of collection disposed of?

This is very slick. Can we assume this collection ONLY exists in the current macro AFTER the call to the data macro? And, I assume the collection comes back to the calling routine even if the caller was another data macro? In other words, returnVars works for returns to other data macros? And, I assume this is not a global collection? Wow, this is just super and sounds good to me, I can already come up with a zillion uses for this. A real great late feature to sneak in by the way.

In case of split databases will value of ReturnVars be available in frontend if datamacros are in backend table.

Good afternoon, Is there anyway to use this in an After Insert macro to return the value of an Autonumber column in a newly inserted row. This is normally my primary key. I have no problem using it to pass values that I know up to my form, but I just can't find a way to get the value from the newly inserted row. I am looking for the something similar to OLD. Bye Ian p.s. I can't get on with the macro editor at all, I am mainly a C++ person and still create my SQL Server tables with a script file! Please let there be a simple text editor equivalent (Web Database) that I am missing.

>In case of split databases will value of ReturnVars be available in frontend if datamacros are in backend table. If the database is split, this will NOT work. (for non split, it will work). The reason for the above is that a UI macro on the front end can’t call NAMED table macro on the back end, so there no way that values can be returned. Keep in mind that the data triggers (table macros) do fire and function as normal and even run normal in a split environment (even for a non web application). However, the return vars feature is only for named table macros NOT table trigger macros. Those named macros can’t be called in split environment (non web application). On the other hand, since any table value changed or updated by trigger is available via regular code in a form, it not really much of a problem here. It does mean however you should avoid use of named table macros if your intention is to run your application as split on a network. However, that is the way you always run things, and all your code was always client side anyway. So, just keep in mind that named table macros can’t be used in a split environment (we talking about a non web application here). The issue and use of this feature is really intended for web based applications. Remember, in a web based form you don’t have the ability to do a dlookup(), or create a recordset. So, this returnvars feature allows you to call a named table macro from a form, and that named table macro can do record processing or whatever (and it can even call data macros). And, with this return var feature, that named table macro can now return values back to that web form macro code. While this feature is primarily for web based applications, it does work for non split non web based applications. Albert D. Kallal

Edmonton, Alberta Canada

kallal@msn.com

> Is there anyway to use this in an After Insert macro to return the value of an Autonumber column in a newly inserted row Yes, you can do this, but you don’t use a trigger (the trigger will be firing as a result of the macro creating a record, not the other way around). However, it is not clear what record and when and where you are talking about? If you want to pick up the ID (PK) of the current form record you are in, then in the current form just execute a SaveRecord command in the form. That will create the ID and then any code in the form can pick up the [ID] of the current record. If you adding a record in your named table macro, then the table triggers will fire, but it is the named macro that would be thus creating the record in this case. To get the ID of a record created in that named table macro, you will be using the Create Record command (I assume?). Just use the [LastCreateRecordIdentity] property after the createRecord data block. So, the create record block has to finish before the ID is generated. So, on the next line of code right after the create record code block, you could go: SetReturnVars (lastID, [LastCreateRecordIdentity]) This would set our return var called lastID to the ID just created, and it would thus be returned to the calling UI macro. And, keep in mind using CreateRecord is not restricted to creating a record in the same table where the named table macro resides. So, yes, you can pick up the last ID created using [LastCreateRecordIdentity]. However as mentioned, if you are talking about the record in the current form, then just execute a SaveRecord in the forms UI code and from that point on you can pick up the ID anywhere in the forms code. The only case I see as problem would be if your adding a record by using the after update event to another table. In that case, if you really needed the ID in the UI code on the form, then I think the only approach would be to move the after update code that creates this record to a named table macro and then call it after you execute a SaveRecord in your current form. I guess this really depends on what your scenario is, but the above gives solutions to several different cases I can think of.

Good morning SetReturnVars (lastID, [LastCreateRecordIdentity] Thank you, that is exactly what I was looking, for it works perfectly. Just to clarify for anyone reading this thread, the form is unbound (not based on a database table). When a button is pressed, a data macro is called to insert the data. I don't feel too stupid, as I have just done a Google search on LastCreateRecordIdentity and it returns one row. Bye Ian

@Albert D. Kallal

Could you please explain the difference amongst the definitions I've read in your reply:

named table macro

UI macro

table trigger macro

data macro

web form macro

> please explain the difference Sure, my sorry. When I say web form macro, I was taking about code that runs in a form. So, I was trying to use both terms interchangeable to help give you the meaning here. However, that effort on my part might have confused the issue. So: UI macro = Macro code that runs in a form. And, there different features in web UI macros as opposed to client forms UI macros. The code editor automatic restricts features depending on what kind of macro you are editing, so you really don’t have to remember this stuff. So, for example, a UI macro in a web form does not have the ability to open and process records. But, a web data macro does. (And, so does a UI macro in a client form). And, for all intensive purposes when you create stand alone macro that is NOT attached (embedded) in a form, the function and feature set is identical to UI macros (and, this is the case for both web UI macros, and client UI macros). So, you could well use the term UI macro interchangeable with that of a standard macro you create that is not necessarily imbedded in a form. Data macro: There is 3 types of data macros Before Events (before change, before delete) – a trigger macro After Events (Insert, Update, after delete). - a trigger macro Named macro (is run by being called from a trigger or UI macro) The above macros are all attached to a particular table. And, the after events and named macros have the same feature set. In general terms, the only real difference between in macros is how and when they are run. Some of them run as a result of a trigger (insert, update etc). Those named data macros can be called by your UI macros, or other data macros, or event macros that are firing as a result of the triggers. Last but not least, one reason that explains why the function set is quite limited in a web UI macro is that this code actually gets converted into JavaScript, and actually runs in your browser on you desktop. And, that is beyond cool IMHO. It means this system is building a real web application, and this is not fake!

Thanks Albert.

Named table macro=named data macro?

Isn't "you could well use the term UI macro interchangeable with that of a standard macro you create that is NOT necessarily imbedded in a form." in contradiction with "UI macro = Macro code that runs in a form."

>in contradiction with Ok, I have 5 minutes here: Ok, it is bit in contradiction with out more info: Remember, you can still create macros in the client side that are not embedded in a form. I guess the real correct term here is embedded UI macro, and simply a UI macro. Referring to both of these types of macros as UI macros is convenient and makes sense since it conveys to you the limitations as to the type of macro we are talking about. And it also tells you where these macros are going to run (both of these macros run in the browser, or access client side). I suppose in place of UI you could use the term client side macros. There certainly no one terminology here, but so far, the term UI macro seems to have caught on from being used by the Access team. Remember the variable scope and even use of the forms column names without qualifying a referencing form name works in a non embedded UI macro when called from a embedded UI macro. So any code that works in an embedded UI macro can generally be copied and moved into a regular macro (a UI macro), and then called from that UI embedded macro. Considering that scope, state and even column names don’t need qualifying in that non embedded UI macro, then it follows that those non embedded UI macros behave identical to embedded UI macros. And they both run client side. So, I was just saying for all sake of this discussion a UI macro and a non embedded UI macro is very much a UI macro, aassuming we are NOT talking about any table type of macros. So, better is: UI macro = client side macro. Two types (embedded, and non embedded) Data macro = table level macro Three types (before (Validation), After, Named). Note that Named and After are identical feature wise. Change macros are really striped down feature wise (and often are referred to as validation macros). Albert D. Kallal (Access MVP) Edmonton, Alberta Canada kallal@msn.com

Thank you guys. This helped and I will be referring to it. Bon