You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Whenever I debug a data macro, one of the most common mistakes I find is misusing aliases, especially in the Where Condition of a ForEachRecord or LookupRecord data block. This blog post is an attempt to demystify this area. Hopefully, by the time you are done reading this you will have a firm grasp of how to use aliases in data macros.
1. What is the default data context?
2. When does the default data context change?
3. What else is in the data context?
4. What is the default data context in a named data macro?
5. Using an alias in the Where Condition of a ForEachRecord or LookupRecord.
6. Using an alias to reference a data context outside of the current default data context.
In a data macro, the "default data context" is what you are using when you refer to a field without using a table name qualifier. When a BeforeChange, BeforeDelete, AfterInsert, AfterUpdate, or AfterDelete event data macro is triggered, a new default data context is created, which can be used inside that block.
This new context is what I like to refer to as the “incoming default data context” or, for brevity, the “incoming record.” Note that in event data macros, the incoming default data context is always read-only. This is a change in behavior from the Beta 2 release.
The data context stack is expanded and the default data context changes whenever you enter a data block (ForEachRecord, LookupRecord, EditRecord, and CreateRecord).
For example, if you are in a data macro in Table1, and you call “ForEachRecord Table2”, and then refer to a field without a table name qualifier, you are referring to a field in Table2.
If you are in Table1.AfterUpdate, then you call “ForEachRecord Table2”, and inside that loop you call “LookupRecord Table3”, the data context at this innermost point contains a record for each of the 3 tables, and can be accessed using the following table name qualifiers:
1. [Table3] contains the record (if any) found by LookupRecord.
2. [Table2] contains the current records in the ForEachRecord loop.
3. [Table1] contains the record that triggered the AfterUpdate event.
4. [Old] contains the record in Table1 from just before the data change that triggered the AfterUpdate event was entered.
At the innermost data block (the LookupRecord on Table3), all of these are available from the data context stack with [Table3] being the current default data context. So, inside the LookupRecord data block, the data context stack looks like this:
When you exit a data block, the data context for that block is removed from the data context stack. So, if the LookupRecord on Table3 is finished, but you are still inside the ForEachRecord on Table2, then [Table2] becomes the current default data context, and the data context stack looks like this:
If you call a named data macro from an event data macro, the entire data context is carried over. So, in the above example, if you called RunDataMacro from the innermost data block, all 4 items would still be available inside the named data macro.
Note that there is no data context if you call a named data macro from a user interface macro (UI macro). In this case, any attempts to refer to a field will fail until you enter a data block, because there is no way to know which record you are referring to.
If you define parameters for data that is consumed by your named data macro, then you can also call the named data macro from a UI macro. Then, if you are viewing a form you can pass the ID of the record being viewed (or any other field in it) to the data macros and then use LookupRecord on the ID to get the same record and perform some processing, such as deleting the record. Note however, that parameter values in data macros are read-only and cannot be edited.
Here is a screenshot illustrating this:
In most cases, you will not need to define an alias in a ForEachRecord or LookupRecord and you can just leave it blank, because the alias defaults to the same name as the table or query you are referring to on the first line.
IMPORTANT: The Where Condition is inside the data block. Therefore, the default data context in the Where Condition argument is whatever table or query the ForEachRecord or LookupRecord refers to.
Suppose you are in Table1’s BeforeChange event, and you want to prevent the change if it would create a duplicate value in the [Text1] field. The easiest way to do it is to create a LookupRecord on Table1 to find out if there is another [Text1] field in the table with the same data as the incoming record, and raise an error if a duplicate is found.
The solution is to define an alias for the LookupRecord block, then use that alias as the table name qualifier for fields in the Where Condition expression, and call the RaiseError action if any record is returned by LookupRecord to prevent the entry of this data.
Here is a screen shot illustrating this:
Let’s say you want to iterate over all the records in Table1 and look for a match in Table2.Text1, and if found, set Table1.Text2 = "Match found in Table2 at row ID=X". To do this, I’ll define an alias at each step. Then, to edit the record from the ForEachRecord instead of the LookupRecord (which would be the default data context for the EditRecord if no alias was specified), I’ll use the alias I defined for the ForEachRecord (T1) in the Alias argument of the EditRecord block. This is how to edit the record from the ForEachRecord loop and instead of the record found by the LookupRecord.
The other benefit of always defining your aliases and then using them in the expressions inside the data block is that if you rename your table, you do not need to update all of your expressions.
That’s it! I hope this clears up any questions you may have had about using aliases or the where condition in data macros.
-James
Comments: (13) Collapse
James,
Thank you very much for this post. I had been really struggling to understand how aliases work, alternating between times when I thought I understood and then thinking I knew nothing! Alan
Hi James, Great article, thanks. Wonderful to get a better understanding of how the Where Condition works ... and to see examples with Alias. Even though the Alias argument is listed below Where Condition, it is nice that the Where Condition intellisense picks up the alias name immediately once it is filled. [ Feature Request: On capitalization ... it would be much appreciated if lowercase was changed to MixedCase when the writer types the whole term instead of picking from intellisense.] I made an example table, Tablename, with the following structure:
- ID, Autonumber
- ID_FK, number (Foreign Key ID)
- Fieldname, text or number or date I expanded the logic of your example to: 1. only check for duplicates if there are values
2. only check if either value in the combination has changed
3. look for duplicate combination of Fieldname and ID_FK 4. exclude the record you are on from the test Here is my macro: '~~~~~~~~~~~~~~~~~
' Tablename.BeforeChange
'~~~~~~~~~~~~~~~~~
/* DO NOT ALLOW DUPLICATE COMBINATION OF [Fieldname] AND [ID_FK] */
/* Crystal 100313 */
/* If either [Fieldname] or [ID_FK] is not filled out, then stop the macro */ IF IsNull([Tablename].[ID_FK]) Or IsNull([Tablename].[FieldName]) Then StopMacro
End If /* determine if either value in the combination to check has changed */ IF Updated("[FieldName]") Or Updated("[ID_FK]") Then /* make sure this combination has not been used on a different record */ Look Up a Record In: Tablename Where Condition: TablenameAlias.ID_FK=[Tablename].ID_FK And [TablenameAlias].[Fieldname]=[Tablename].[Fieldname] And [TablenameAlias].ID<>[Tablename].ID Alias: TablenameAlias / * If another record with the same combination is found, then cancel the update by raising an error (does ErrorNumber have to be negative?) and give the user a message (ErrorDescription) */ RaiseError Error Number: -1000 Error Description: =String(5,"*") & " Duplicate Value " & String(5,"*") & Space(5) & " """ & [Tablename].[FieldName] & """ Already Exists for [Fieldname] when [ID_FK] = " & [Tablename].[ID_FK] '~~~~~~~~~~~~~~~~~ Maybe I am not understanding something correctly? Raising an error when a record IS found is not intuitive. This works but it does not seem logical to me. Is there a way to test for a match on "Look Up a Record"? /* Question: If a match is NOT found, does the macro stop? Otherwise, what prevents it from executing the RaiseError action? */ ~~~ NOTE (please pass this on or tell me where I should send it): The syntax in Expression Builder Help for Updated does not show the required quote marks. This would be a more complete example: Updated("[Fieldname]") ~~~ How come we have Asc (ASCII code from character), and even something like Arctangent of an angle, but we cannot use the Chr function to return a character, given a code, in a data macro expression? It would be very nice to be able to add a line break in messages to the user (Error Description) ... and Chr can be fun too ... ■ ∩ ∞ ▐▄█▄▌░░ ├─┤ :) [ Feature Request: allow Chr function in all macros ] ~~~ Note: the example fieldname you used, [Text1] very much looks like [Text] which is a reserved word. Might I recommend something more distinct? ~~~ James, you have done a great job in this article to impart understanding and introduce us to new terminology. I had to read it a bunch of times to catch it all, but I think I got it, by jove, I think I did (except for the questions I have asked you) ... maybe the light is getting a little brighter... *smile* Warm Regards,
Crystal * (: have an awesome day :) *
Like Crystal I had to read it more than once (and then come back to it again later and then read it again with my highlighter pen to hand). This is good news. If everything was too easy there would be no need left for us Access developers. Dear Microsoft do continue to make difficult things easy so that more end users use Access, but do continue to add new functionality which still requires people with developer skills. On this Access 2010 scores 10 out of 10. As a certain advert for a fast food chain says, "I'm loving it."
Crystal,
You wrote, "Maybe I am not understanding something correctly? Raising an error when a record IS found is not intuitive. This works but it does not seem logical to me." I would say that, yes, it is logical, because it is not really an error. It is a match on what you are searching for and it is only your/our situation which means it is an error. I support your request for the conversion of lower case characters to the correct mixed case when typing. Seeing a lower case word when Intellisense is available, in VBA at least, signifies a problem. It would be good (and consistent) if the macro editor were to do the same. Having Chr available seems logical when we already have Asc. It is also pretty important.
James, this i a 5 star out of 5 stars post. Really great top notch article here. @Crystal: no chr() function. Actually, during beta, we did have the chr() function, but somewhere along the way it was taken away. However, if you working with a form, then just use a text box with rich text, and then inserting a will create a new line. And, you can set bold, color and even use winddings as a font. So, chr() usually not missed, and the only exception and place I think of missing the chr() function is when using the message box command. I suspect the issues is xml + web stuff, and inserting funny chars into the char stream has the potential to mess things up, so, no char function in web macros. @ Is there a way to test for a match on "Look Up a Record"?
@ If a match is NOT found, does the macro stop? Otherwise, what prevents it from executing the RaiseError action? */ This works like a if..then else block. If there is a match, then any nested code inside of the lookup block runs (This is quite nice). If there is no match, then the nested code inside does not run. So, think of this like a if..end block. The macro does not stop, it just skips the nested code for you (I like this). The issue of raising a error is really just a user design choice and is one way to get the browser to display a error message. A data macro or trigger can’t do a message box (not even client only). You can also now pass values back to the UI calling code with the new returnvars collection. This means code in a form can now actually read values from a table with code, and you don’t necessary have to use raiseerror to display or figure out that some reocrd don't exist. Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
@Alan "it is not really an error" thanks -- I actually knew that but it wasn't until I read what you wrote did it click! ... all these exceptions ... pretzel logic. @Albert Once again, you come to my rescue with your vast knowledge. Thank you, Albert :) I did not realize that Lookup is like IF ... well, without a terminator statement -- and that throws me off ... so what exactly happens? the next statement in James' example after RaiseError is an End If. Assuming there would be more statements after End If, If a match is NOT found in the Lookup Record, does execution go there? Or does it simply stop executing? I still don't know. what if Lookup Record is not inside an IF block? ~~~ "So, chr() usually not missed" Speak for yourself, my friend ;) It is rare that I issue a message to a user without a line break. This is important for detailed messages. "inserting funny chars ... potential to mess things up" I see your point [ Feature Request: NewLine constant for string concatenation ... something like vbCrLf ] "returnvars collection" oh thank you for telling me about this! Warm Regards,
@Crystal
So, chr() usually not missed" – Speak for yourself, my friend ;) Don’t get wrong, I VERY much miss and want the chr() function. I too always broke up my msgbox strings. I wish the browswer msg box command suppored rich text, as then we could use . You can build your own msgbox, but they respond slower then the built in ones which run local in the browser (javaScript). >what if Lookup Record is not inside an IF block? Well, actually, the concept here is the lookup command is in fact a block of code, and any code indented as part of the block runs. For example: In the above, the 4 commands right after the lookup will run. (note the indent)
Whoa! Lots of comment data here. Let me try to address what's here so far in one big reply: @Alan, @Crystal, @Albert:
Thanks for the kind words about this post. :-)
However, I'm sorry you had to read it several times to get it. I try to be a stickler for good spelling, grammar, and a lack of ambiguity, so I re-read and re-wrote sections several times to try to keep it as short and clear as possible. But I've now seen at least one grammar error (probably one of many), and the fact you needed to read it several times to "get it" means I probably could've stated it more clearly, so please accept my apologies for that. A favorite saying of mine is, "it takes a long time to write a short message". I will concentrate on this more in any future posts. @Alan, @Crystal, @Albert, RE: calling RaiseError in a data macro, particularly the BeforeChange or BeforeDelete event:
In Before* events, if you call RaiseError, the change is blocked and the RaiseError's Description is displayed to user (unless you called OnError/Next first, say from a UI macro). If you are making the change in an After* event or a named data macro, the change will be blocked and both the RaiseError Number and Description are written to the USysApplicationLog table. The exception to this latter case is if you are calling a named data macro from a UI macro. When an error happens in this case, it is bubbled back up to the UI layer, where OnError/Next can trap it, and the [MacroError] object can be manually inspected to see if something bad happened. @Crystal, RE: [Text1] field name:
Thanks for the feedback. I tend to use [Text1], [Currency1], [Date1], [Memo1], etc. in my own personal test databases because it makes it easy for me to see I have 1 text data type field, 1 Currency data type, 1 Date data type, etc. In this example the data types were irrelevant, so perhaps just [Field1] would have been better. @Crystal, @Alan, RE: lower case conversion of characters from Intellisense:
I can understand your preference, but this suggestion would be a hard sell as a fix in 2010 (perhaps in the next version...maybe...no guarantees). If you want the mixed case, you can get it by hitting TAB as your last character (or, hit backspace on the last character then wait for Intellisense and hit TAB). If you want all upper case we give you that option--by not messing with it if you opt not to use Intellisense. Note that in some situations you get your wish anyway, since if you close and re-open the data macro you will find some of this changed to match the initial definition of the identifier. Ironically, (given the context of this comment) alias casing marches to its own beat in some situations no matter what you do (but is functionally irrelevant). Anyway, I just hit the first few characters that bring up the Intellisense filtered to the item I'm trying to refer to, and then hit TAB. I find this to be very fast and I'm quite happy with being able to type large data macros this way without ever having to use the mouse. :-) @Crystal, RE: LookupRecord execution:
As Albert said, the interior of the LookupRecord block does not execute if no records matching the filter specified in the Where Condition are met. I would also like to point out this is how ForEachRecord works, too.
RELATED TIP: if you need to find out when no matching records are found, call SetLocalVar MatchFound=False just before entering the ForEachRecord or LookupRecord block. Then, inside the block, call SetLocalVar [MatchFound]=True (which will only execute if matching records are found). Then you can call "If [MatchFound]" just after it to do your alternative processing. However, this is probably more of a "retrofit" solution since you could initially design your logic so as to avoid this. @Crystal, @Alan, RE: Chr():
As you are probably aware, VBA is not available to the browser in apps published to Access Services. We tried to make it so data macros would *always* work in the browser, and behave the same in both client and web apps (as much as possible--I think we've gotten 99% there). So it made sense to not support VBA functions in data macros on the client, or else you'd develop your web app only to find your logic wouldn't work with Access Services (actually, it would not even publish). @Crystal, RE: NewLine constant feature request:
We tried to keep the data macro language as clean and simple as possible. Ideally, we will add more string handling functionality in the next version of data macros (like VBA's Chr/ChrW, Asc/AscW, vbCRLF, etc.), but again, I cannot promise anything. Wow! I'd better stop now or this will begin to look like a huge post instead of a comment! Thanks for reading this far! -James
Hi James, thank you for your very thorough response! The hard reading was due more to the new terms that you used in context. It was great! Thanks. Keep it up, we don't want things dumbed down, we want to learn it from the nitty gritty, and that takes looking under the hood. >> "it takes a long time to write a short message" great quote! It takes a long time to write a short program too As I work with the web template I did not get done in time to sumbit for the contest, I am challenged to make it simple so the macros can handle all the funtionality I want to incorporate. You helped in understanding the macro "perspective", and that is a big step toward grasping the logic of them. One of the big issues for a developer is attaining understanding, and being able to document your work -- not just using. I have been searching for a way to document the macros, and am now totally thrilled to learn this: SaveAsText acTableDataMacro, "TableName", "C:\Test.txt" from Wayne Phillips We should be able to find this out from Help. ok, off my soapbox ... >> USysApplicationLog table very nice to know about, thank you >> "When an error happens in this case, it is bubbled back up to the UI layer, where OnError/Next can trap it, and the [MacroError] object can be manually inspected to see if something bad happened." great to know >> "get it by hitting TAB " yes, that is true ... but it is easier and faster to press spacebar ... not to mention that is what programmers are accustomed to doing ... hmmm ... another comment for Neha ;) >> SetLocalVar MatchFound=False thanks for that! Your suggestions are appreciated. Working with macros is like knowing how to swim and then being thrown into water you can't see anything in and trying to swim. The lack of water clarity should not affect the ability to move, but it does. ~~~~~~~~~~~~~` James, I think you should write for the Help team :) Warm Regards,
I'd like to echo what Crystal said. The article was well written. I think my problem was that I had been trying to use aliases without really knowing what I was doing and was probably more confused than someone who had not looked at them before. I had several "Ah, so that's what it does!" moments reading your article. Alan
Thanks for a great post.
A few of questions: What I find confusing is that sometimes you use an alias that you define AFTER you use it. For example in your example above under the heading of “Using an alias to reference a data context outside of the current default data context.”
You set the T2 alias after you already used it for the T2.text1 reference written a couple of lines above it. In VBA Diming a variable in a particular sub always comes before using it, or maybe a more appropriate comparison, setting a variable to reference something must be written before you can use this variable. Quote from Crystal:
“Raising an error when a record IS found is not intuitive. This works but it does not seem logical to me.”
I share Crystal’s opinion. I like the ability to use rst.NoMatch property of a recordset or the rst.eof or rst.bof to make this more intuitive. Is there something similar for Data Macros? Quote from James:
“In Before* events, if you call RaiseError, the change is blocked and the RaiseError's Description is displayed to user (unless you called OnError/Next first, say from a UI macro). If you are making the change in an After* event or a named data macro, the change will be blocked….” So, just to clarify, raising an error either in the Before or in the After change events will block the change? I ask this because in VBA, AFTER events happen after the change has already been committed and thus can not be canceled from there. Gilad
One more question for you James.
I have tried to ask this a few times before here but alas (not alias) with no responses.
What is the story with AccDE files? Are they being phased out? Can they be uploaded to Sharepoint in order to have them be distributed to clients (even if the Sharepoint services will not read their VBA)?
Am I the only one who is interested in distributing a compiled application? Thanks again
Gilad
[quote]
What I find confusing is that sometimes you use an alias that you define AFTER you use it. You set the T2 alias after you already used it for the T2.text1 reference written a couple of lines above it.
[/quote] Actually, it not a few lines of code up, it only one, and in fact it is not a line of code, it is a parameter. That parm comes after the WHERE part (and most times you don't need a Alis). I often give my Alis names like rstData since they are in a sense like a recordset reference. I not sure if I can imbed pictures in this forum, but here is a screen shot of a lookup (Alis is blank)
[img]public.bay.livefilestore.com/.../mcs1.png[/img] However, if I click on lookup command, note this screen shot where you can see how the block of code is highlighted, and you can see that the where and Alis are parameters, not lines of code in this screen shot:
[img]public.bay.livefilestore.com/.../mcts2.jpg[/img] [quote]
“Raising an error when a record IS found is not intuitive. [/quote] This is not a code issue, it is a context issue. If we find a record then we want to NOT allow the update and tell the user about this. We would do the same in VBA and if we found a record, we would set cancel = true. However, in another scenario we might want a error if the record does not exist. So, this is just a choice here for the example and it not that we always raise an error when a record exists. We could raise a error if the record does not exist (and perhaps tell the user they have to go add some account number or whatever that is missing). However, we don' have a nomatch, but a REALLY nice suggeston would be to allow a "else" for all blocked code commands such as lookup and for/each.
Comments: (loading) Collapse