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

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Uncategorized and tagged , , , , , . Bookmark the permalink.

23 Responses to TEMPVARS Look Like The Secret In Access Services Web Forms

  1. Giorgio says:

    Hi Dick, in the second example, how do you make the control in the Parent pick up the TempVar value for display and what’s the name of the control in the Parent?

  2. DickM says:

    I think what you’re looking for is in the picture. The control name is whatever you ewant to call it but the controlsource is “=[Tempvars]!TotCAD” which shows the toal value of the subform in Canadian Dollars.

    Does this help?

    Dick

  3. Giorgio says:

    Thanks Dick, I think I’ve got it; txtCAD is on the subform while txt103 is on the parent form.

  4. Dale Logan says:

    Dick, you’ve sold me on Hybrid apps and they are working very well. I’ve mentioned web functionality as an option but have never created one. Besides this TempVar idea, can you think of any other useful tips as I embark into this new territory?

    60k+ records really fast? That’s very encouraging.

    • DickM says:

      Hey Dale ….

      There are lots of useful tips I’m sure … I just havent figured them out yet😉 (??).

      When talking about making Browser-only forms in Access Services in 2010 my advice to you is “keep it simple”. This is not a majorly capable technology BUT it does what it does pretty well once you figure out how to make it work.

      The one tip I have for you is that if you want to export data from a Browser-only form in Access you gotta use a Report not a Form because as far as I know there is no way to export Access Web form data to Excel but you Access inherits that capability from SQL Reporting Services when it is a Report object. You can make them look pretty much the same as a form but of course they’re not editable.

      If I think of anything else I’ll post it here🙂.

      Dick

      • dlogan4121 says:

        Well exporting data to Excel from a browser form is where I am now. You mentioned doing it using a report, but I can’t see how. Any other additional tips for this step?

      • dlogan4121 says:

        Wait a minute. Just saw the button in the navigation control. Sweet. Exports nicely formatted too.

      • Biggus Dickus says:

        Beat me to it !! That’s a free present from SQL Server Reorting Services. It is NOT available in Office65 and will not be available apparently😦.

  5. Dale Logan says:

    Dick,

    I got into this only a little bit last night. Wow are things different. One thing I use quite often are parameter queries that point to a control on a form. Would this be where I use TempVars instead?

    Dale

    • Biggus Dickus says:

      Pretty much ….. You should be able to use parameteized queries but I have found that unreliable and inconsistent. Using TempVars seems to be much more reliable.

      Dick

  6. Dale Logan says:

    Dick,

    I’m pulling my hair out and can’t find ANY help online. You’d think Microsoft would have at least a little bit.

    I have a simple form on a navigation control. Tried using [TempVars]![txtDealer] on the criteria line in the query that feeds the form. Everything works just fine in the client. When I publish and go to the browser I get a “Site is down” error. One or more of the site’s Queries, Tables, or Macros were recently changed and failed to compile. Clearing the criteria line and republishing makes the error go away.

    Any ideas?

    Dale

  7. dgmoore says:

    Dick,
    I’m frustrated by the lack of information on data/form macro logic. The methods are not too hard to comprehend, but the logic in general is pretty opaque. For example, I have a ComboBox on a form that filters the form’s recordsource to display a record on AfterUpdate. If the form’s recordsource doesn’t include the ComboBox value I want to add a record and populate it with some values from a separate table, which is identified there by the ComboBox value. For the life of me I can’t figure out how to get the data from the second table and populate it to the form’s recordsource.

    In general, do you know of any good sources for info on macro logic??

    Thanks
    Dave

    • Biggus Dickus says:

      Hey Dave …. There sure isn’t a lot out there about how to use Web Forms in Access 2010 isn’t there?

      I personally only know a fraction of how to use these Forms and I am as frustrated as you about this. Not much I can say about this fact though – sorry.

      In answer to your question I suggest you consider once again using TEMPVARS by grabbing the source value into a TEMPVAR and then applying it to the target. That’s the best I can do because I have NEVER been able to get Comboboxes and Forms working with standard forms!frmName!ctrlName references. Just don’t work.

      You can play with the Browseto as well but it is quirky as well.

      Sorry I can’t help more and sorry that there aren’t any more references out there either.

      Dick

      • dlogan4121 says:

        Dick,

        I agree with your comments regarding using TEMPVARS. Dave should be able to get things to work using this methodology. It is very quirky though.

        NOTE: Have your tried to run a summary query yet? I don’t think it’s possible. The only way I can see to do something similar is using reports. Which are fairly quirky themselves.

        I’m sure you don’t want your blog to be a question and answer session for hybrid apps. We sure do need one though.🙂

        Dale

      • Biggus Dickus says:

        There is no capability for Aggregate queries (I assume that’s what you mean by summaries) in Access Services for Access 2010. Yes – you can emulate this in a report but that really is a kludgy solution.

        I believe the “official” way to do summaries in Access 2010 Access Services is to create a summary table and use Data Macros to push the summarized results into there and then expose that data-set on a form as a summary. But to print this you still would need to use a Report.

        Also the performance of Data Macros is suspect unless you’re doing a very small procedure. In a perfect world you would update all the Summary tables when you enter data one record at a time. To do this in a batch instead can take a long time if there are a lot of records.

        Alternatively you can create a client-side process to update your summary tables periodically. This will still take time but I’m pretty sure it’ll be quicker and a whole lot easier to do for traditional Access user (who else would be trying to do this but a traditional user is my big question that I haven’t received an answer on😉 ).

        Hope this helps.
        Dick

  8. Pingback: web access 2010 database - main nav, form droplist, requery subform need help

  9. Jim Stiles says:

    I’ve been doing Access for over 25 years and it appears the MS programmers keep making it harder and harder to program in Access. When, after a struggle to learn a new process, they change it, making it necessary to learn it again with the the familiar not continuing to work, I have to again learn it from scratch. Often the new way is isn’t as good as what already worked. Some of the new procedures and screens are just awful: cumbersome and I can’t change them.

    • Biggus Dickus says:

      I wouldn’t waste a lot of time on Access Web … You described it well bit it also is not capable of anything serious.. Stick with Client Access🙂

    • Biggus Dickus says:

      Yes … That was long ago .. Mi h has changed. For example Microsoft has abandoned the technology I wrote about there … Hard to believe but true…

  10. grovelli says:

    What were the advantages of that technology over Access Web Apps?

    • Biggus Dickus says:

      It allowed you to use full-featured Access Client solutions (i.e. VBA, Forms, Reports, etc.) which connected to data that was stored in SharePoint Server. Yes this meant the size of your tables would be limited (but not as much as they indicated in the end – and more than large enough for many, many, many departmental solutions) but on the other hand your Frontend was also stored up on SP thereby deployment was dirt simple, each user had their own local copy of the data (thus increasing performance dramatically), synching only that which changed, and allowed changes to the Frontend to propagate instanctly to all userews (a good and a bad thing but mostly good from my experience).

      I was assuming that in 2013 MS would simply build an interface to allow the files to actually reside in the SQL Server that works behind SP but that was not to be .. instead they went yet another direction, split the Desktop completely from the Web interface and most notably .. made the AWA forms completely basic and useless. AWA is a total waste of time unless you just want to keep your Xams card list😉 ..

      So Access is NEVER going to be a Web App IMHO .. I suggest you look into Access running in Azure Remoteapps instead. They offer what we’re all looking for right out of the box. And I doubt it is going away anytime soon for various reasons.

      Dick

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s