Use the Form property when referencing subforms in your code

There's a good post from back in May on the Microsoft office team Access blog that talks about some best practices for referring to forms and controls in code: Power Tip: Use the Form property when referencing subforms in your code. If you're serious about building applications using Access forms you should understand the concepts in the article.
The title is a little misleading. As well as talking about using subforms, it talks about using from variables to make referring to your controls easier.

Is the treeview control going away?

You may have noticed I haven't posted in a while. I have not been working in VBA/MS Access and have neglected the blog. But I'm still here....

I was doing some reading today and noticed that we now are given to expect that the treeview control is not supported in the 64-bit version of Access 2010. Sounds like most folk should be installing the 32-bit version and so will continue to have access to the treeview. There's a long discussion of this on UtterAccess here.

There's always been a healthy discussion on the merits of using ActiveX controls and other elements fro outside Access in Access applications. It has always been understood that long time support for these controls was not guaranteed, and that using them could make deploying and managing your application more complex.

Sounds like for now running the 32-bit version of Access should keep your treeview running well. #

API: Copy variables/control contents to memory

I used to have this bookmarked but somewhere along the line I lost it: API: Copy variables/control contents to memory from The Access Web. I stumbled across it today when reading this post on UtterAccess.com.

I have found copying things to and from the clipboard to be a useful feature in several of my applications. Here's one I built in Word. This little macro copies the current document path to the clipboard.

Public Sub PathToClipboard()
    With Application.ActiveDocument
        ClipBoard_SetText .Path & "\" & .Name
    End With
End Sub

(You'll need the code from The Access Web link above in addition to this little snippet.) I found I often had to give a colleague the path to a document I had just updated on a shared drive somewhere. Using this macro I would just hit the keyboard shortcut I had set up, pop over to email, and hit ctrl-v to paste. And there I'd have the full path to the document in my email.

There are lots of other applications for the putting data on the clipboard, so it's a good thing to have in your toolkit- bookmark the post on The Access Web and you'll have it when you need it.

Macros and Access 2007: What happened to all the extra Macro Actions in Access 2007?

"Access Junkie" has a quick tip here about macros in Access 2007: What happened to all the extra Macro Actions in Access 2007? When I drop down the list of macro actions in the macro design window, I do not see all the options present in previous versions. Where are they?

Access 2007's security enhancements include a distinction between trusted mode and non-trusted mode. It seems some Macro actions are allowed when your database is running in trusted mode, while some aren't. By default Access only shows you the trusted actions. The article shows you how to see the rest.

There's an overview of what else changes depending on whether your database is trusted or not here: How database objects behave when trusted and untrusted and an overview of the new security provisions in Access 2007 here: Get started with Access 2007 security

Until Access 2007 most serious Access developers believed that macros were rarely the right way to go- that VBA was a better choice. There's a article here from UtterAcess.com that reviews the differences: Macros vs VBA - Why use Macros? With the introduction of trusted and non-trusted mode and some additional capabilities in macros in Access 2007, it seems there may be a case for macros after all- I'll have to explore the new features and see if they provide enough functionality to create an application. There's a review of the additional macro features here: New macro features in Office Access 2007.

Case Study: Improving Visibility & Control for Mission Critical Spreadsheets in Energy

In their company blog the folks from Prodiance have posted a case study that discusses spreadsheet and EUC risk management in a major US enegry company: Case Study: Improving Visibility & Control for Mission Critical Spreadsheets in Energy.

While they're coming at it from the perspective of selling their specific software solution, the issue facing many businesses is very real. Whether compliance is an issue for you or not, you need to understand and manage the risk to your company from EUC that is not being managed. Any time end-users are creating tools that sustain you business, you need to know that it's being managed properly- this applies to everything from spreadsheets with formulas to simple macros to complex applications that have been built by savvy end-users. Managed well these tools are a key part of your organization's toolset. Managed well and leveraged to their fullest they can become a real cometitive advantage- manking you more efficient and more nimble. Managed poorly or not well they can be a real risk.

The real key is to understand how your organization is leveraging business technologies. If you were to wander around you organization to do a quick scan, here are three questions you could ask:

• Where did that neat little macro come from? Does someone who's still here know how it works? What would we do if it stopped working?
• Did someone from finance take a look at the complex formulas in that spreadsheet? How do we know those projections include everything we agreed we would include in the model?
• Do the expert users who built that handly little departmental application know the best ways to manage and deploy that kind of technology? Where's the master copy kept? Who's okaying changes to make sure the users don't see down time?

Check back here from time-to-time as I follow this post up with a mini-series on managing business technology.

My Treeview Project Episode 7 | Another Way To Read Through Your Data

In Episode 7 of My Treeview Project we're going to look at another approach to reading through our data and adding nodes to the treeview.

Read more »

Running a Command as Administrator in VBA

As Windows and Office become more and more security aware we've had to learn about things like trusted folders and macro security. Here's another one- when you shell out to run a command, that command may need to be run as an admin. Running a Command as Administrator in VBA from the Microsoft Access Team Blog tells how to make this work.

Link Textbox Contents to Worksheet Data

Link Textbox Contents to Worksheet Data on Glenn Lloyd's "OfficeTipsAndMethods" blog is a nice discussion of some really good techniques for working with charts in Excel.

There's a big difference between doing a chart and doing a chart well. For instance I've seen charts done lots of times that couldn't easily be updated to the next months data. When discussing one of his techniques, Glenn says "Now you ask, why would I want to take the time to set this up when I could simply edit the title every month? For me it’s a no brainer. Yes, it took some time to set up – probably less than five minutes, to be honest with you. What is the payback on that investment of my time. Sure it is small but let’s say it takes me 1-1/2 to 2 minutes each month to edit the title. In the first three months I have more than recovered my time investment.Then there is the intangible benefit of saving me the embarrassment of forgetting to change the title or occasionally misspelling something in the title." Hear, hear, Glenn!

Gantt Chart using the Microsoft Chart Control

"How can I show a Gantt chart on my forms (or reports) using data in my database?"

Here's one solution that uses the built-in chart control. Gantt Chart using the Microsoft Chart Control.

If I wanted a sophisticated Gantt chart I'd use MS Project (hopefully I'd already be using MS Project if I was working with tasks and timelines.) But for a simple Gantt the built in chart control works great!

The example database shows the Gantt in a report but would work find in a form too, I think.

Search Criteria Form Example From Allen Browne

Many Access developers set out to create a search form: a form where the user can enter or pick criteria and matching records will be shown. Often they find themselves trolling the Internet for a good example to get them going.

You'd be hard pressed to find a better starting point than this article by Allen Browne: Search criteria. The example works you through creating a form with criteria entry boxes at the top and matching records at the bottom. Once you understand the methodology, you can easily extend it to forms with different layouts, pop-up forms, reports, etc.

Subqueries Primer: Find Employees With Salary Higher Than Their Department Average

This is a classic SQL problem- probably more a textbook exercise than a real-life business problem, but a classic nonetheless. "Find all employees whose salary is higher than the average salary for their department."

In Access, the most straightforward approach is a multi-query solution. However it can be also be accomplished in a single query using subqueries. I'll discuss both approahces here

Read more »

Functions for Calculating and for Displaying Date/Time Values in Access

Functions for Calculating and for Displaying Date/Time Values in Access from Microsoft covers a whole bunch of data and time topics.

It has functions for everything from the last day of the current quarter to the first day of the current week. It also has some functions for working with intervals, although you'll see they differ slightly from my advice on the right way to store intervals.

Read more »