TEMPVARS Look Like The Secret In Access Services Web Forms

Anyone who comes here for Access stuff knows that I am a BIG fan of the Hybrid capability of Access 2010 with SharePoint 2010 and Access Services.  But I am getting dragged kicking and screaming into more Browser-only stuff as clients insist that we provide browser-only capababilities (for various mostly specious reasons – but that’s another story and they ARE the Clients 🙂 ).

I have spent far too much time in the last few weeks trying to build what is actually a fairly straight-forward Access 2010 Browser-Only form for a client, and I seem to have finally come up with the magic trick to make things work – but it has been unnecessarily difficult IMHO.

I would create a nice-looking Web Project form that would run perfectly on the client that would then fail when published to the Web … in other words simply making a form work on the client does NOT mean it will run in your Browser – in fact more often than not it won’t – and the reason is never explained and always looks like your fault until you exhaust all possible combination of Master/Child relationships, parameterized rowsources, or the incredibly powerful but cryptic BrowseTo function in your Macro code.  It all ended up failing for me over and over to the point I was thinking for the first time in years, that this simply wasn’t going to work.

Then I tried the TEMPVARS method which I knew was one that I would try eventually but only after I exhausted all the usual techniques that I as an Access Developer would think might work.  And it solved everything !!

Simple fact:

You can create a TEMP variable in code (say on the AfterUpdate or OnCurrent of a form or subform)  and then refer to that as a variable in any code or open form on display in a formula like this =[Tempvars]![varName]

Here are 3 places I used the TEMPVARS in my app:

1. Redefined the variable in Code for the AfterUpdate of a Text Box:

2.  Referenced in as the Source of a TextBox Control:

What’s interesting is that this TEMPVAR is referring to a field on a subform that is calcing the total for a field (“[CAD]”) in that subform like this:

This Control is on the subform and is a hidden field.  Then that form has an OnCurrent Event like this that resets the TEMPVAR being used by the Control in the Parent which immediately picks up that value for display:

I’m sure that this could probably be done with another event on the subform but I KNOW that it will work in the OnCurrent so I am happy with that .. (“Get ‘er Done !!!”)

3. In the Query driving the Rowsource of a Listbox:

Every one of these usages ofTEMPVARS solved ALL my problems.  It simply means that by redefining TEMPVARS as events fire in my various Forms, Subforms and Controls and with a few well timed requeries in code, I can effectively do all the same things I am used to doing with Master/Child relationships, with Query parameters or as Formulae in informational Contols such as aggregations.  And BTW the table source for this Form and Subform has 60,000+ records and it is REALLY fast at refreshing … faster than when run on the client actually.

Maybe this technique is posted somewhere else on the “Internets” or it’s in somebody’s book (which you should then go out and buy immediately) but I had to figure this out the hard way.  What this does is open me up to do many, many more things in Access Services Web Forms.  Too bad I had to learn it the hard way (like I used to all the time :-))..

Dick