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.
Today’s guest writer is Kerry Westphal the PM for the macro designer and browser logic.
Here is Kerry’s introduction on The Access Show on Channel 9:
We expect many advanced Access Services applications will have client and web objects—we call these hybrid applications. Hybrid applications are interesting because you can create client forms that leverage the full power of link tables, advanced queries, rich forms/reports, and VBA. See this previous post for more background on Web and client objects.
The IsClient() expression allows you to conditionally branch your logic depending on whether the form running inside Access or the browser. In this way you can have one button that runs a different macro based on the context it is called from. This allows you to take advantage of the full power of VBA and macros when the Web form is in the rich client while also using the same form to the browser. It makes application development much easier as you don’t have to create the same form twice (one for the Web and another for the client) to have different logic running based on the environment. I’m going to show three different examples how you can use this functionality in hybrid applications:
Using isClient(), you can open a different form based on whether the form is running in the client or browser. The design experience for Web objects and the compatibility checker allows you to open client and Web forms alike from your Web form. In the following example, you can drill through to a different contact detail form from the button of a contact ID depending on the environment. This is especially useful since the details form on the client takes advantage of functionalities that are unsupported on the Web details form.
The following example below is an example of extending the 2010 Contacts template to Save a Contact to Outlook using the IsClient() expression to run a client macro from a Web form.
The Save to Outlook button allows you to quickly save contacts to your Outlook address book from your Access database.
In the OnClick event for the Add Contact from Outlook button, check to see if the app is running in the Client and if so, then run the client macro AddFromOutlook that can run the client-only macro SaveToOutlook to open the dialog to save a new Outlook contact. Otherwise, when the button is clicked on the Web, throw a MessageBox saying that the functionality is unsupported.
You can also run VBA from the Web form using the same syntax. The example below extends the Projects 2010 template and adds a task to Outlook every time a new task is added.
In the Save & Close button on the Task Details form, add logic to check if the form is running on the client. If so, then set up TempVars with the values from the form so it can be passed to the VBA function. We will call the VBA function from the onLoad event of a hidden form to get the VBA to add the task to Outlook to run. If the form is running on the Web, this code will not get called.
The onClick event on the Save & Close button opens a client form that triggers the RunCode call in the Onload event to run the AddOutlookTask code.
Hopefully, this post gives you some ideas about how you can combine Web objects with client objects and call VBA code from inside Web forms if the form is run in the Access client.
Enjoy!
Edited 1/19/2010 -- This expression is not available in beta 2. It will be available in RTM. Sorry for any confusion.
Comments: (31) Collapse
That is very neat-o little trick to run VBA code like that. Thanks for sharing this tip. You given me a whole bunch of great ideas here. I should point out I don't see the isClient() function in the current beta build that the public has right now. I assume the public beta builds don't have this isClient() function as of yet, or perhaps I am looking in the wrong place... Albert D. Kallal
Edmonton, Alberta Canada
Thank you for such a thorough and concise article, Lois! I read a question in one of the blog comments and started to wonder myself ... now IsClient() provides the answer. Like Albert, though, I don't see it in Help -- I tried ?IsClient() in the Immediate window and got "Compile error: Sub or Function not defined" -- but nice to know it will be there in the final release so we can plan :) I agree with Albert -- these blogs are opening up so many different ideas! Thank you for demonstrating VBA! And nice to see the "handshake" via TempVars with Macros. I like that TempVars don't lose their value with unhandled errors -- and you can use them in queries ... but why call them TEMPvars? because they are lost when Access terminates? ~~~ On your procedure -- what happens if the task has already been added? Will a duplicate be added? If this is the case, how would one go about seeing if the information is already there? Is there a key Access can store? Are the RunCommands enumerated anywhere? Do you have a URL for the Outlook object model? If these questions are too far off-topic, I understand. ~~~~~~~~~~~~~ macros -- I have had a heck of a time moving blocks of lines because of indentation. Ctrl-dragging to where I wanted the block to land did not work (I recorded this with Camtasia)-- hoping this will be more streamlined in the FR but I am guessing there are tricks that I don't know about ... (smile) A video with tips for editing macros by rearranging logic (we all change our minds as we code) would be very nice :) -- and where we can dump and where we can't ... oh a thought! Can we copy and paste? I didn't even try that because I normally ctrl-drag to make copies Also, how do you get rid of an outer IF level that is no longer needed? ... I saw no way to delete that line without losing the whole code block -- and this goes back to me not being able to move lines to a new indentation level somewhere else. I ended up deleting the macro and creating it a different way. I love the table triggers though! so I will get this figured out :) I set up calculated fields after I populated my table with data. Since we cannot do update queries, I wrote this VBA code which filled the calculated fields for my existing data nicely: '~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub TriggerDataMacros() 'click HERE 'press F5 to Run! ' Crystal ' Jan 2010 'loop through all records in a table 'and trigger data macros to calculate Dim db As Database _ , r As DAO.Recordset Set db = CurrentDb Set r = db.OpenRecordset("MyTableName", dbOpenDynaset) Do While Not r.EOF r.Edit r.Update r.MoveNext Loop r.Close Set r = Nothing Set db = Nothing MsgBox "Done" End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~ I tried to Modify one of my calculated fields ... Oh now "Modify Expression" is not available (our internet goes in and out all day long; this creates problems modifying table design, which is mostly all I have done so far) ... well I guess that makes sense, I am in a different session now (doing testing as I write) The other day, when I was working on my table design it seems I could not "Modify Expression" for a calculated field without first synchronizing (which I had a hard time finding for some strange reason -- now it pops out -- I think subconsciously the beautiful shading to accent it made it look like a header and I mentally skipped over it before I knew where it was -- the first tab!) -- but I had not closed the table. I created a new text field that is calculated by the table triggers to enable sorting by a memo field -- or the first x (I used 30) characters anyway -- this is nice :) Not (IsNull([PropValue])) calc_Left30 Left([PropValue],30) well, I tried to format just the pertinent part of the XML -- here is the logic for what goes into the BeforeChange event: If Not IsNull([Memofield]) then SetField Text_SortFieldname, Left([Memofield],30)
End If
~~~~~~~~~~~~~~~~~~~~~~~~ I really liked your article on compatibility between 2007 and 2010 (smile) Thanks, Lois Warm Regards,
Crystal * (: have an awesome day :) *
Thanks, its a nice clear demo.
Like Albert, I too cannot find an isClient function. Is there another beta coming out which will have it in? Alan
Thank you for such another great article, Kerry! I read a question in one of the blog comments and started to wonder myself ... now IsClient() provides the answer. Like Albert and others, though, I don't see it in Help or the Object Browser -- I tried ?IsClient() in the Immediate window and got "Compile error: Sub or Function not defined" -- but nice to know it will be there in the final release so we can plan :) I agree with Albert -- these blogs are opening up so many different ideas! Thank you for showing VBA code! And nice to see the "handshake" via TempVars with Macros. I like that TempVars don't lose their value with unhandled errors -- and you can use them in queries ... but why call them TEMPvars? because they are lost when Access terminates? ~~~ On your procedure -- what happens if the task has already been added? Will a duplicate be added? If this is the case, how would one go about seeing if the information is already there? Is there a key Access can store? Are the RunCommands enumerated anywhere? Do you have a URL for the Outlook object model? If these questions are too far off-topic, I understand. ~~~~~~~~~~~~~ Macros -- I have had hard time getting blocks of lines to land where I want them to go when I drag them ... is there some trick to get them to stay? Or an alternate method to dragging (and ctrl-dragging) when you are changing indentation level or just dumping them somewhere until you build the rest of the logic structure? Also, how do you get rid of an outer IF level that is no longer needed? ... I saw no way to delete that line without losing the whole code block -- and this goes back to me not being able to move lines to a different indentation level somewhere else. I ended up deleting the macro and creating it a different way. I love the table triggers! ...so I will get this figured out :) ~~~ I set up calculated fields after I populated my table with data. Since we cannot do update queries, I wrote this VBA code which filled the calculated fields for my existing data nicely: '~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~~~~~~~~~~~~~~~~~~~~~~ I tried to Modify one of my calculated fields ... Oh now "Modify Expression" is not available (our internet goes in and out all day long; this creates problems modifying table design, which is mostly all I have done so far) ... well I guess that makes sense, I am in a different session now (doing testing as I write) The other day, when I was working on my table design it seems I could not "Modify Expression" for a calculated field without first synchronizing (which I had a hard time finding for some strange reason -- now it pops out -- I think subconsciously the beautiful shading to accent it made it look like a header and I mentally skipped over it before I knew where it was -- the first tab!) -- but I had not closed the table or added any data. I created a new text field that is calculated by the table triggers to enable sorting by a memo field -- or the first x (I used 30) characters anyway -- this is nice :) Not (IsNull([PropValue])) calc_Left30 Left([PropValue],30) well, I tried to format just the pertinent part of the XML -- here is the logic for what goes into the BeforeChange event: '~~~~~~~~~~~~~~~~~
If Not IsNull([Memofield]) then SetField Text_SortFieldname, Left([Memofield],30)
Else SetField Text_SortFieldname, " "
~~~
for those who look closely, you will see the 'else' is not in the xml ... added it just now so if the memo field is deleted after it had something, then the sort field will clear. My preference would be to assign a zero-length-string (ZLS) but I haven't tested to see if that is allowed.
~~~~~~~~~~~~~~~~~~~~~~~~ Thanks, Kerry Warm Regards,
Do I understand that you can include VBA in a module of a web database front-end, but it will simply be ignored by the web form?
>Do I understand that you can include VBA in a module of a web database front-end, but it will simply be ignored by the web form? Yes. More specifically your application can be a hybrid application. That means the forms and report's can have all kinds of regular VBA code. Note that these forms cannot be launched or run on the web site. So you can't mix VBA into a web form. However you can most certainly have a mix of web forms and non web forms in the one application. Either type of form can be launched and used inside of ms-access. This means a VBA form can launch a web form. When you do this, then that web form runs inside of the access client. In fact it is really no different then clicking on the form from the nav pane. So this works from macro code or even VBA code. At first this seems a little bit confusing, but after thinking about this, it does all start to make sense.. In fact when you launch and use a web form in the access client (on your desktop) you'll be hard pressed to tell any difference in look and feel than that of any regular access form you are using.
I have a question about the syntax of the VBA shown in the last screen shot. Items in the TempVars collection are shown thus TempVars(Task)
TempVars(Description)
TempVars(DueDate)
TempVars(StartDate) I would have expected them to need double quotes around the TempVars name, e.g. TempVars("Task") Has something changed since the public beta or have I misunderstood what is going on here? Alan
Thank you for such another great article, Kerry! Like Albert and others, I don't see IsClient() in Help or the Object Browser -- I tried ?IsClient() in the Immediate window and got "Compile error: Sub or Function not defined" -- but nice to know it will be there in the final release so we can plan :) Thank you for showing VBA code -- and nice to see the "handshake" via TempVars with Macros. I like that TempVars don't lose their value with unhandled errors -- and you can use them in queries ... but why call them TEMPvars? because they are lost when Access terminates? ~~~~~~~~~~~~~ Macros -- I have had hard time getting blocks of lines to land where I want them to go when I drag them ... is there some trick to get them to stay? Or an alternate method to dragging (and ctrl-dragging) when you are changing indentation level or just dumping them somewhere until you build the rest of the logic structure? Also, how do you get rid of an outer IF level that is no longer needed? ... I saw no way to delete that line without losing the whole code block -- and this goes back to me not being able to move lines to a different indentation level somewhere else. I ended up deleting the macro and creating it a different way. I do like the table triggers! ...so I will get this figured out :) ~~~ I set up calculated fields after I populated my table with data. Since we cannot do update queries, I wrote this VBA code which filled the calculated fields for my existing data nicely: '~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~~~~~~~~~~~~~~~~~~~~~~ I created a new text field that is calculated by the table triggers to enable sorting by a memo field -- or the first x (I used 30) characters anyway -- this is nice :) here is the logic for what goes into the table BeforeChange event: '~~~~~~~~~~~~~~~~~
Else 'didn't test to see if "" is okay or if it can be set to null SetField Text_SortFieldname, " "
End If ~~~~~~~~~~~~~~~~~~~~~~~~ Thanks, Kerry Warm Regards,
Very nice - so glad to know we don't have to throw out all the VBA. Sort of calling it.
The IsClient() function is not available in the Beta2 builds, but will ship when we release. Alan- you're right :) I need to put the TempVars in quotes to have it work correctly- it looks like I attached the wrong image. Thank you for pointing this out. Crystal- I'm glad to hear you've been enjoying working with data macros. Dragging and copy/pasting actions is the best way to move your actions around. The orange insert line should indicate where the actions are going to drop. To get rid of your outer If level, I suggest the below two ways of getting the behavior you want. We do not allow you to drag and drop multiple actions or to just delete the If block so perhaps this can help.
1. You can copy the contents of the If block, then delete the If block and paste them back in where you want them. 2. Otherwise, you can put the contents of the If block into a group and then move the group out of the If block.
>I set up calculated fields after I populated my table with data. Crystal, you should not need to "touch" each record. If you add a calculated column to an client table, then it should populate just fine when you save the column in the table designer. This should work EVEN with data inserted already. If you add an calc column to an un-published web table, then again, you should not need to "touch" each record. In fact, I found even adding an calculated column to published tables (live web data), then again the calculated column populated correctly for me. However, I found that if I modify an EXISTING calculated column that already been published, then yes, it does NOT correctly update the column data. This is an beta bug. So, in this case, I would delete the column and re-add it again with the new expression to advoid this problem. Perhaps you changed an existing calculated column? So, the calculated column should populate even when there is data in the table. In most cases it does, and in the one case where it does not that is beta bug. >Also, how do you get rid of an outer IF level that is no longer needed? I see Kerry jumped in, but you going to buy me a coffee at the summit! There is a number of approaches, and I had written this up and when I came back low and behold Kerry suggests EXACLTY what I do now. Here what I wrote anyway! -- I think the most common case is to delete the whole if block. That is easy, perhaps too easy as you found out! However in the case where you want the code, but NOT the if block? I suggest you cut + paste out the middle part. So, use the mouse (shift click) on each line you want to keep. Or use down arrow key while holding shift key to highlight text you want to keep. You then hit ctrl-x to cut out, and then you left with empty if block. Now to delete this if block, you need to hit down arrow (to get your cursor OUT of the "if" edit text box), and hit del key, and then ctrl-v. The above occurs quite fast for me..and I can do it 100% keyboard. Note that the reverse of the above is far more easy, and if you highlight 2-3 lines of code, you can then right click and choose "make if" block. So it is a snap to create if blocks of code. Note that you can then use ctrl-drag to copy this code. You can also use shift drag to move, but I quite much prefer cut + paste in this case. Note that you can also click on an if block to highlight it, and then tap the up or down arrow key WHILE holding down the ctrl key. This will jump the whole code block of code up or down together – this is my near favorite feature and I miss this one in the VBA editor the most. There is more tricks yet, but I have to run. Albert D. Kallal
@ Kerry thanks for responding and for the information :) @ Albert "Crystal, you should not need to "touch" each record" Oh I confusingly called them "calculated fields" -- yes the fields were calculated -- but not by using equations for the definition -- by the table triggers. So yes, I did have to touch the records to get the table triggers to do their job on the records that were already there. You can't use a memo field in an equation for a calculated column. I was taking the left 30 characters so the memo field could be used for sorting. The only way is with table triggers. ~~~ ok, now for calculated fields...
I found out you have to check for Null in equations for calculated fields (can't use NZ) because if the data is later deleted, the calculated field won't clear. ie:
Iif(Isnull([f_Text]),"",Ucase(Left([f_Text],1))) It would be nice if this was not necessary for every single equation but without NZ I am not sure what else to do. even this won't refresh if the value in the field it depends on is deleted ... if [f_Number] contains null, the calculation is not done and the calculated column stays as it was before, which would be incorrect. [f_Number]*2 ~~~
thanks for the moving tips :)
~~~ coffee? You are so helpful -- how about a truckload of coffee? ... maybe even with something leaded :) ... or we can just skip the coffee part and go straight to the interesting stuff (smile). well, you might need coffee because no one is going to let you sleep -- you'll be too busy doing demos, answering questions, and making handouts (smile) Warm Regards,
Is using TempVars in the above example necessary? The In the example the TemVars are filled by referencing controls on a form. Then as a second form opens it uses the values from these TempVars. Can't these references be used in the VBA directly from the second form as it opens? Crystal, Did you try setting the 'Alow Zero Lengh' property to false? then you can us the NZ function. Gilad
>by using equations for the definition -- by the table triggers. So yes, I did have to touch the records to get the table triggers to do their job Ah, Great! thanks Crystal! That means your little trick to get table triggers to run is thus well noted! A very neat-o idea and I had not thought of doing that to get a new table trigger to run! (so, I guess I am buying you coffee for this good little tip!). Albert D. Kallal
kallal@msn.com
Comments: (loading) Collapse