tag:blogger.com,1999:blog-84673969421835182142022-11-20T07:04:39.070-05:00My MS Access BlogTips, tricks, best practices and more about Microsoft Office Access and Visual Basic for Applications (VBA). Links to great articles and posts around the web plus some things I've learned that I'd like to share.Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.comBlogger158125tag:blogger.com,1999:blog-8467396942183518214.post-1054999527579640972011-09-05T20:12:00.000-04:002011-09-05T20:12:27.686-04:00Use the Form property when referencing subforms in your codeThere'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: <a href="http://blogs.office.com/b/microsoft-access/archive/2011/05/23/power-tip-use-the-form-property-when-referencing-subforms-in-your-code.aspx">Power Tip: Use the Form property when referencing subforms in your code</a>. If you're serious about building applications using Access forms you should understand the concepts in the article.<br />
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.Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com1tag:blogger.com,1999:blog-8467396942183518214.post-13824255242962157712011-09-04T14:45:00.003-04:002011-09-04T14:48:24.578-04:00Is the treeview control going away?<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-TlY0STfBJ-I/TmPHA--vvlI/AAAAAAAAAG8/JkzaNWmVctI/s1600/1301904232_m_0.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="200" src="http://1.bp.blogspot.com/-TlY0STfBJ-I/TmPHA--vvlI/AAAAAAAAAG8/JkzaNWmVctI/s200/1301904232_m_0.jpg" width="167" /></a></div>
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....<br />
<br />
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 <a href="http://www.utteraccess.com/forum/Treeview-64-bit-t1945862.html">here</a>.<br />
<br />
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.<br />
<br />
Sounds like for now running the 32-bit version of Access should keep your treeview running well. <span style="font-size: 85%;"><a href="http://mymsaccessblog.blogspot.com/2011/09/is-treeview-control-going-away.html">#</a></span>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-41959801824721763812009-11-20T06:00:00.002-05:002009-11-20T06:00:01.622-05:00API: Copy variables/control contents to memory<div style="padding-left:5px;float:right">
<a href="http://www.mvps.org/access/">
<img src="http://www.mvps.org/access/images/sitelogo.gif" border=0 />
</a>
</div>
<p>I used to have this bookmarked but somewhere along the line I lost it: <a href="http://www.mvps.org/access/api/api0049.htm">API: Copy variables/control contents to memory from The Access Web</a>. I stumbled across it today when reading <a href="http://www.utteraccess.com/forums/showflat.php?Number=1906193">
this post on UtterAccess.com</a>.</p>
<p>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.<pre>Public Sub PathToClipboard()
With Application.ActiveDocument
ClipBoard_SetText .Path & "\" & .Name
End With
End Sub</pre> (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.</p>
<p>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.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com6tag:blogger.com,1999:blog-8467396942183518214.post-52235994207802713382009-11-19T07:19:00.001-05:002009-11-19T07:20:01.867-05:00Macros and Access 2007: What happened to all the extra Macro Actions in Access 2007?<p>"Access Junkie" has a quick tip here about macros in Access 2007: <a href="http://accessjunkie.com/faq_02.aspx">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?</a></p>
<img style="float:right" src="http://accessjunkie.com/images/FAQ_02Screenshot1.jpg" width=350 /><p>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.</p>
<p>There's an overview of what else changes depending on whether your database is trusted or not here: <a href="http://office.microsoft.com/en-us/access/HA012301881033.aspx">How database objects behave when trusted and untrusted</a> and an overview of the new security provisions in Access 2007 here: <a href="http://office.microsoft.com/en-us/access/HA101980471033.aspx?pid=CH100621891033">Get started with Access 2007 security</a></p>
<p>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: <a href=
"http://www.utteraccess.com/forums/showflat.php?Number=547442">Macros vs VBA - Why use Macros?</a> 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: <a href="http://office.microsoft.com/en-us/access/HA012142021033.aspx#newmacrofeatures">New macro features in Office Access 2007</a>.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com1tag:blogger.com,1999:blog-8467396942183518214.post-61703725362244577152009-11-18T06:00:00.000-05:002009-11-18T06:00:00.063-05:00Case Study: Improving Visibility & Control for Mission Critical Spreadsheets in Energy<p>In their <a href="http://www.prodiance.com/">company blog</a> the folks from <a href="http://www.prodiance.com/">Prodiance</a> have posted a case study that discusses spreadsheet and EUC risk management in a major US enegry company: <a href="http://endusercomputing.org/2009/11/06/case-study-improving-visibility-control-for-mission-critical-spreadsheets-in-energy/">Case Study: Improving Visibility & Control for Mission Critical Spreadsheets in Energy</a>.</p>
<p>While they're coming at it from the perspective of selling <a href="http://www.prodiance.com/products/spreadsheet.asp">their specific software solution</a>, 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.</p>
<p>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:<ul><li>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?</li><li>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?</li><li>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?</li></ul></p>
<p>Check back here from time-to-time as I follow this post up with a mini-series on managing business technology.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-83389258887229763082009-11-17T06:00:00.000-05:002009-11-16T22:23:39.910-05:00My Treeview Project Episode 7 | Another Way To Read Through Your Data<span class="fullpost"><div style="float:right;margin-left:5px;margin-bottom:5px;padding-left:3px;padding-right:opx;padding-top:3px;border:1px solid;background-color:#d5eaff">
<a href="http://sites.google.com/site/mymsaccessblog/my-treeview-project-episode-7-another-way-to-read-through-your-data"><img src="http://sites.google.com/site/mymsaccessblog/DOCUMENT.gif" Title="View this post as a full page article"></a>
</div>
</span>
<p>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.</p>
<a name='more'></a>
<H4>Different ways to get data for the treeview nodes</h4>
<p>In <a href="http://mymsaccessblog.blogspot.com/2008/02/my-treeview-project-episode-1-hello.html">Episode 1</a> we didn't use a table at all; the nodes to be added were defined in the code. That approach may be suitable for treeviews that are used for navigation where the nodes don't depend on your data.</p><p>
In <a href="http://mymsaccessblog.blogspot.com/2008/02/my-treeview-project-episode-two.html">Episode 2</a> we first used the Northwind product and category data to build our treeview. We had one subroutine to add all the categories and another to add all the products. That approach was clean and simple and will perform quite well in most cases.</p>
<p>In <a href="http://mymsaccessblog.blogspot.com/2009/03/my-treeview-project-episode-6-images.html">Epsiode 6</a> we took a different approach. That treeview shows customers, their orders, and the products on those orders. We added our customers, and just after adding a customer we added that customer's orders, and just after we had added the order we added the products for that order. That approach handles is capable of handling more complex data scenarios- it's necessary for hierarchical data where the number of levels is not known, such as employees who report to other employees.</p>
<p>In this episode I'll show you another approach, which many people find simpler. We'll use the same data as we used in epsiode 2 through 5: categories and products from Northwind. For this approach we'll build one query that brings in all the categores and all the products and we'll loop through all the records in that query, adding both category and product nodes in the same loop.</p>
<h4>A starting point for this epsiode's code</h4>
<p>As before, we're using data from Microsoft's Northwind Traders sample database. If you don't have a copy you can <a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=EN" target="_blank">download it here</a>. Or even better, start with the database we created in Episode 4. If you've been following along you may have it already, or <a href="http://sites.google.com/site/mymsaccessblog/MyTreeviewProjectEpisode4.zip?attredirects=0">download it here</a>.</p>
<p>If you want to build the Episode 4 database go back to <a href="http://mymsaccessblog.blogspot.com/2008/02/my-treeview-project-episode-two.html">Episode 2</a> and start with a new form there, and then add to it in <a href="http://mymsaccessblog.blogspot.com/2008/03/my-treeview-project-episode-three.html">Epsiode 3</a> and <a href="http://mymsaccessblog.blogspot.com/2008/03/my-treeview-project-episode-4-expand.html">Episdoe 4</a>.
<h4>The new query- all category and product data</h4>
<p>First we need a query that will bring the category and product data together into one list. Create a query called qryCategoryProductTreeviewData. Here's the SQL- paste this into the query's SQL view.:
<pre style="background-color: #cde6ff; overflow: auto; width: 100%;">SELECT
"C" & Categories.CategoryID AS CategoryNodeKey,
Categories.CategoryName,
"P" & [ProductID] AS ProductNodeKey,
Products.ProductName
FROM
Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
ORDER BY
Categories.CategoryName,
Products.ProductName;</pre>
It's a pretty straightforward query, but we'll talk about a few points worth noting.</p>
<p><em>"C" & Categories.CategoryID</em> and <em>"P" & [ProductID]</em> are creating text values we can use as keys for our nodes. Remember that we typically create a key for each node, that each key has to be unique, and that the key has to be text, not a number. By making the first letter of each category node key a C and the first letter of each product node key a P we will be able to determine in code whether it was a category or product node that was clicked or selected. In <a href="http://mymsaccessblog.blogspot.com/2008/09/my-teeview-project-episode-5-using.html">Episode 5</a> there's an example of code that uses the key to do something with the node that has been selected.</p>
<p>We've sorted the query results on CategoryName and Product Name. It's important when using this type of query to add nodes that the records for a category stay together- sorting on the category name will do that for us. The sort or product name will add the product nodes in alphabetical order (we could also tell the treeview to sort the nodes- in this case we'll just add them in the order we want to see them.)</p>
<H4>This Epsiode's code</h4>
<p>Here's the core of this week's code changes. Delete the subs called CreateCategoryNodes and CreateProductNodes and replace them with this new sub called AddAllNodes.
<pre style="background-color: #cde6ff; overflow: auto; width: 100%;">Private Sub AddAllNodes()
Dim rst As DAO.Recordset ' recordset for category and product data
Dim strCategoryNodeKey ' key for this category node
Dim strOldCategoryKey As String ' for detecting change in category
' open the recordset
Set rst = CurrentDb.QueryDefs!qryCategoryProductTreeviewData.OpenRecordset
' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
strCategoryNodeKey = rst!CategoryNodeKey
If strCategoryNodeKey <> strOldCategoryKey Then ' check for change in category
' change in category- add category node
Me.xProductTreeview.Nodes.Add Text:=rst!CategoryName, Key:=strCategoryNodeKey
strOldCategoryKey = strCategoryNodeKey ' remember this as the current key for detecting changes
End If
' now add product node
Me.xProductTreeview.Nodes.Add Relationship:=tvwChild, Relative:=strCategoryNodeKey, _
Text:=rst!ProductName, Key:=rst!ProductNodeKey
rst.MoveNext ' next record in qeury
Loop
End Sub</pre>
</p>
<p>Change the sub FormOpen to this new version
<pre style="background-color: #cde6ff; overflow: auto; width: 100%;">Private Sub Form_Open(Cancel As Integer)
SetupTreeview
AddAllNodes
End Sub</pre>
</p>
<p>This new sub, AddAllNodes, opens a recordset based on the query we created above,<br>
<code>Set rst = CurrentDb.QueryDefs!qryCategoryProductTreeviewData.OpenRecordset</code><br>
and reads through all the rows in the recordset<br><code>rst.MoveFirst
<br>Do Until rst.EOF
<br> ....
<br> rst.MoveNext
<br>Loop</code>
Within the loop we're going to use "control break logic". That's an old computing term for a classic program design where we keep track of a certain value in our data, and when that value changes we do something. In this case we keep track of the product category, and everytime that value changes we add a new category node to our terrview.
The variable <code>strOldCategoryKey</code> is used to remember the category key of the last category we processed. The test <code>If strCategoryNodeKey <> strOldCategoryKey</code> checks to see if we're still on the same category. Not so hard really (and I've probably over-explained the logic already.)</p>
<P>Now to the treeview-specific details. Adding the category nodes is pretty similar to the other add nodes code we've seen before:<br><code>Me.xProductTreeview.Nodes.Add Text:=rst!CategoryName, Key:=strCategoryNodeKey</code><br>A node is added with text and key coming from the query. The line that adds the product node is only a little more complex:<br><code>Me.xProductTreeview.Nodes.Add Relationship:=tvwChild, Relative:=strCategoryNodeKey, _<br>
Text:=rst!ProductName, Key:=rst!ProductNodeKey</code><br>The node is again added with the text and key from the query. The node is aaded as a child node (<code>Relationship:=tvwChild</code>) and the key of the current category node is supplied to identify the parent node this node should be added under (<code>Relative:=strCategoryNodeKey</code>). Note that you can use either the key of the parent, as we did here, or a variable that referes to the parent node itself, as we did in <a href="http://mymsaccessblog.blogspot.com/2009/03/my-treeview-project-episode-6-images.html">episode 6</a>.</p>
<p>That's all it takes! You should get a working treeview. For a simple treeview this one query approach may be attractive. My point in illustrating these diverse approaches is to show that your approach to reading your data should depend on your application and your data, as well as your preference. Once you know how to use the treeview properties and methods you'll be able to work treeviews into your application in a way that makes sense.</p>
<p>You can download the completed episode 7 database <a href="http://sites.google.com/site/mymsaccessblog/MyTreeviewProjectEpisode7A97.zip">here</a>.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com3tag:blogger.com,1999:blog-8467396942183518214.post-49781190205596800262009-11-16T06:00:00.001-05:002009-11-16T06:00:04.553-05:00Running a Command as Administrator in VBA<p>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. <a href="http://blogs.msdn.com/access/archive/2009/11/11/running-a-command-as-administrator-in-vba.aspx">Running a Command as Administrator in VBA</a> from the Microsoft Access Team Blog tells how to make this work.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-16368588598771969072009-11-15T06:00:00.001-05:002009-11-15T06:00:05.177-05:00Link Textbox Contents to Worksheet Data<p><a href="http://argeeservices.com/wordpress/2009/11/03/link-textbox-contents-to-worksheet-data/">Link Textbox Contents to Worksheet Data</a> on Glenn Lloyd's "OfficeTipsAndMethods" blog is a nice discussion of some really good techniques for working with charts in Excel.</P>
<p>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 <i>"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."</i> Hear, hear, Glenn!</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-50775855970472414732009-11-14T06:00:00.001-05:002009-11-14T06:00:03.473-05:00Gantt Chart using the Microsoft Chart Control<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/_ZSrHUkbaXhg/SvzU0ILYPWI/AAAAAAAAAFs/nPF5dQVG5xs/s1600-h/Gantt1.jpg" imageanchor="1" style="clear: right; cssfloat: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" sr="true" src="http://3.bp.blogspot.com/_ZSrHUkbaXhg/SvzU0ILYPWI/AAAAAAAAAFs/nPF5dQVG5xs/s320/Gantt1.jpg" /></a><br /></div>
<p>"How can I show a Gantt chart on my forms (or reports) using data in my database?"</p>
<p>Here's one solution that uses the built-in chart control. <a href="http://www.access-programmers.co.uk/forums/showthread.php?t=108369">Gantt Chart using the Microsoft Chart Control</a>.</p>
<p>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!</p><p>The example database shows the Gantt in a report but would work find in a form too, I think.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-64789955494444316472009-11-13T06:00:00.002-05:002009-11-13T06:00:02.079-05:00Search Criteria Form Example From Allen Browne<img style="float:right" src="http://allenbrowne.com/graphics/Search2000.gif">
<p>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.</p>
<p>You'd be hard pressed to find a better starting point than this article by Allen Browne: <a href="http://allenbrowne.com/ser-62.html">Search criteria</a>. 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.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-46174055403605016552009-11-12T10:39:00.010-05:002009-11-13T10:54:29.293-05:00Subqueries Primer: Find Employees With Salary Higher Than Their Department Average<span class="fullpost"><div style="float:right;margin-left:5px;margin-bottom:5px;padding-left:3px;padding-right:opx;padding-top:3px;border:1px solid;background-color:#d5eaff">
<a href="http://sites.google.com/site/mymsaccessblog/employees_with_salary_higher_than_department_average"><img src="http://sites.google.com/site/mymsaccessblog/DOCUMENT.gif" Title="View this post as a full page article"></a>
</div>
</span>
<p>This is a classic SQL problem- probably more a textbook exercise than a real-life business problem, but a classic nonetheless. <em>"Find all employees whose salary is higher than the average salary for their department."</em></p>
<p>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</p>
<a name='more'></a>
<p>Assume our data in tblEmployeeInfo is like this:<br />
<table border="1"><tbody>
<tr><td>EmployeeID
</td><td>DepartmentID
</td><td>Salary
</td></tr>
<tr><td>1
</td><td>1
</td><td>$30,000.00
</td></tr>
<tr><td>2
</td><td>1
</td><td>$25,000.00
</td></tr>
<tr><td>3
</td><td>1
</td><td>$40,000.00
</td></tr>
<tr><td>4
</td><td>1
</td><td>$33,000.00
</td></tr>
<tr><td>5
</td><td>2
</td><td>$25,000.00
</td></tr>
<tr><td>6
</td><td>2
</td><td>$50,000.00
</td></tr>
</tbody></table></p>
<h3>
Two Query Solution</h3>
<p>First create a query that finds average salary by department. I'll call this qryAvgSalaryByDepartment. This can be easily created in the query grid: <br />
<a href="http://4.bp.blogspot.com/_ZSrHUkbaXhg/SvwJTNpRzVI/AAAAAAAAAFM/h4rhvb9hEf4/s1600-h/qreyAverageSalaryByDepartment.jpg" imageanchor="1" style="margin-bottom: 1em; margin-right: 1em;"><img border="0" sr="true" src="http://4.bp.blogspot.com/_ZSrHUkbaXhg/SvwJTNpRzVI/AAAAAAAAAFM/h4rhvb9hEf4/s320/qreyAverageSalaryByDepartment.jpg" /></a>
<br>Or here's the SQL: <br />
<pre style="background-color: #cccccc;">SELECT
DepartmentID,
Avg(tblEmployeeInfo.Salary) AS DeptAvgSalary
FROM tblEmployeeInfo
GROUP BY DepartmentID;
</pre></p>
<p>When we run the query we see the following results:
<br />
<a href="http://3.bp.blogspot.com/_ZSrHUkbaXhg/SvwKDgeG1RI/AAAAAAAAAFU/NdEjaDO3mhA/s1600-h/qry1Results.jpg" imageanchor="1" style="margin-bottom: 1em; margin-right: 1em;"><img border="0" sr="true" src="http://3.bp.blogspot.com/_ZSrHUkbaXhg/SvwKDgeG1RI/AAAAAAAAAFU/NdEjaDO3mhA/s320/qry1Results.jpg" /></a><br />
</p>The second query simply joins the original salary data to this totals query (using DepartmentID as the common field) and uses a criteria to show only employees above the average salary for their department:<br />
<a href="http://3.bp.blogspot.com/_ZSrHUkbaXhg/SvwKsC_IdoI/AAAAAAAAAFc/sF-mZ1PReI0/s1600-h/qry2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" sr="true" src="http://3.bp.blogspot.com/_ZSrHUkbaXhg/SvwKsC_IdoI/AAAAAAAAAFc/sF-mZ1PReI0/s320/qry2.jpg" /></a><br />
or
<pre style="background-color: #cccccc;">SELECT EmployeeID, tblEmployeeInfo.DepartmentID, Salary
FROM tblEmployeeInfo
INNER JOIN qryAvgSalaryByDepartment
ON tblEmployeeInfo.DepartmentID = qryAvgSalaryByDepartment.DepartmentID
WHERE Salary > DeptAvgSalary;
</pre>
and will yield the final results we were looking for<br>
<a href="http://3.bp.blogspot.com/_ZSrHUkbaXhg/SvwLXKjInlI/AAAAAAAAAFk/njlilAkNO_Y/s1600-h/qry2results.jpg" imageanchor="1" style="margin-bottom: 1em; margin-right: 1em;"><img border="0" sr="true" src="http://3.bp.blogspot.com/_ZSrHUkbaXhg/SvwLXKjInlI/AAAAAAAAAFk/njlilAkNO_Y/s320/qry2results.jpg" /></a>
<br>
<h3>Subquery Solution</h3>
<p>You can use a query with a subquery to do the two steps of the two query solution in one query. First he's the SQL:
<br />
<pre style="background-color: #cccccc;">SELECT EmployeeID, DepartmentID, Salary
FROM tblEmployeeInfo AS T1
WHERE Salary >
( SELECT Avg(Salary)
FROM tblEmployeeInfo as T2
WHERE T1.DepartmentID=T2.DepartmentID )
;
</pre>
When we run this query we get the same results as the two query solution. Let's look at it in detail to understand how it works.</p>
<p>Inside the brackets you have a complete query- that's the subquery. It looks a lot like the first query we created. That query had a <b>GROUP BY</b> clause that told it to give us the average for each department. This query has a <b>WHERE</b> clause that tells it to give us the average for only one department at a time (more about how it knows which department to give us in a minute. The subquery's result is a single value- the average salary. That value can then be used in the main query. (Not all subqueries return a single value, some return a set of values.)</p>
<p>How does the subquery know which department to reurn an average salary for? It's <b>WHERE</b> clause tells it to select only records from the department of the employee being processed in the main query.<b>WHERE T1.DepartmentID=T2.DepartmentID</b> The main query will process each record in tblEmployeeInfo. And for each record it will run the subquery to get an average salary for their department. The <b>WHERE</b> clause tells the query to use a claue from the main query as the criteria in the subquery.</p>
<p><b>T1</b> and <b>T2</b> are aliases we give the tables being referred to in the main query and the subquery. The reason we need aliases is that they're the same table- tblEmployeeInfo. By using the <b>AS</b> keyword we can specify a name we want to use to refer to the two different instances of the table in out query. I just used T1 and T2 for table 1 and table 2. You could be more imaginative. If the subquery doesn't use the same table as the main query you don't need an alias. In SQL texbooks these aliases are sometimes called carelation IDs when they're used to match (or corelate) records in the main and subquery.</p>
<p>And just because in SQL there's always another way to do it, here's another subquery approach which uses a <b>JOIN</b> instead of a filter based on a filed in the outer query. In my mind this is less "elegant". But I'm not sure I have a specific reason for this. I haven't researched it, but I have a hunch that the solution using JOIN is not supported in all SQL implementations. Here's the SQL:
<pre style="background-color: #cccccc;">SELECT T1.EmployeeID, T1.DepartmentID, T1.Salary
FROM tblEmployeeInfo AS T1
INNER JOIN
( SELECT DepartmentID, Avg(Salary) AS AvgSalary
FROM tblEmployeeInfo
GROUP BY DepartmentID ) AS T2
ON T1.DepartmentID = T2.DepartmentID
WHERE Salary > AvgSalary
;</pre>
<p>See also: <a href="http://mymsaccessblog.blogspot.com/search/label/subqueries">all my posts about subqueries</a>.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-22284093324621059732009-11-03T06:00:00.001-05:002009-11-07T21:38:39.994-05:00Functions for Calculating and for Displaying Date/Time Values in Access<div style="margin-right:5px;float:right;background-color:#3333FF">
<a href="http://support.microsoft.com/"><img src="http://support.microsoft.com/library/images/support/cn/gsfx_brnd_ms_logo.png" border=0/></a></div>
<p><a href="http://support.microsoft.com/default.aspx?scid=kb;EN-US;210604">Functions for Calculating and for Displaying Date/Time Values in Access</a> from Microsoft covers a whole bunch of data and time topics.</p>
<p>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. <a name='more'></a>They store them in a date/time variable and then convert to numbers of days and hours for display. In my post <a href="http://mymsaccessblog.blogspot.com/2008/01/summing-times-or-datetime-data-instant.html">Summing Times, or Date/Time Data: An Instant or a Duration?</a> I recommended storing intervals as a number of minutes or seconds (or days). I stand by that recommendation. I think the example Microsoft posts is over-simplified. They read total hours from a time card table- it would be more likely to read start and end time and then to calculate an interval as a number of minutes using Datediff().</p>
<p>Here's the list of specific date functions they provide:<ul>
<li>The current month</li><li>The next month</li><li>
The last day of the current month</li><li>
The last day of the next month</li><li>
The first day of the previous month</li><li>
The last day of the previous month</li><li>
The first day of the current quarter</li><li>
The last day of the current quarter</li><li>
The first day of the current week (assuming Sunday = day 1)</li><li>
The last day of the current week</li><li>
The first day of the current week (using settings in Options dialog box)</li><li>
The last day of the current week</li>
</ul></p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-24548437218208160592009-11-02T06:00:00.000-05:002009-11-07T08:47:00.161-05:00A Utility to Help With Ensuring Uniform Control Width on Forms and Reports<p>Tony Toews has posted a neat little utility that grabs the control widths and a few other details for all the controls on your forms to help you make sure that you've sized things consistently throughtout your application. This will be especially useful when you expand a field and have to change all the controls it's used in.</p>
<p>See <a href="http://www.granite.ab.ca/access/ensuringuniformcontrolwidth.htm" target="_blank">Ensuring uniform control width on forms and reports</a></p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-22808724936174536212009-11-01T06:00:00.000-05:002009-11-07T06:27:57.192-05:00A Ribbon Class - Another approach to using the new ribbon in your applications<p>Much has been written about the new "Office Fluent User Interface" that has been introduced with Office 2007. Also known as the ribbon, it's a whole new way to work with Access, Word, Excel and Powerpoint, replacing the menus and command bars we've all become accustomed to.</p>
<p>Like them or not as a user, Access developers have to deal with the reality that the custom command bars they have been using in their applications the past don't work the way they used to.</P>
<p>In <a href="http://www.members.shaw.ca/AlbertKallal/Ribbon/ribbon.htm">this article</a> Albert Kallal presents a neat approach to solving the problem of having your application interact with the ribbon. He's created a ribbon class which uses methods similar to the old command bar methods to operate on icons within the ribbon. I haven't tried using it in an application yet, but I ran the demo and it looks pretty neat.</p>
<p>See also: <a href="http://mymsaccessblog.blogspot.com/search/label/ribbon">my other ribbon posts</a>.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-60911550074483888212009-09-08T11:27:00.000-04:002009-09-08T11:28:30.979-04:00What is the Fastest Way to Return the Record Count of a Table?<div style="padding-right:5px;padding-bottom:5px;padding-top:10px;float:left">
<a href="http://www.rogersaccesslibrary.com">
<img src="http://rogersaccesslibrary.com/welcome_files/welcome_ral.gif" width=200 border=0 />
</a>
</div>
<p>Roger Carlson has posted an interesting analysis of the various ways to get the record count of a table. Take a look <a href="http://rogersaccessblog.blogspot.com/2009/04/what-is-fastest-way-to-return-record.html">here</a>.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-43868681206795858612009-09-08T10:02:00.003-04:002009-11-07T06:28:40.877-05:00Treeview Example- Filling Recursively, Populating With Names of Folders<p><a href="http://www.utteraccess.com/forums/showflat.php?Board=11&Number=342661">Here's</a> another working Treeview example from the code archive at <a href="http://www.utteraccess.com">UtterAccess</a>.</p>
<blockquote>Quote: <i>This little database illustrates the implementation of <a href="http://support.microsoft.com/kb/209891/">Microsoft Knowledge Base Article Q20981 (How to Fill a Treeview Control Recursively)</a>. In addition, frmFolderExamples illustrates one way to fill the Treeview Control with the names of the folders located on a drive. It also shows how to return some of the properties of the treeview nodes as well as how to clear a treeview. I use some code harvested from Candace Tripp’s Browse and Open Files Database as well as ideas from Luiz Cláudio’s Treeview Control for Database Objects.</i></blockquote>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-23219599837276470382009-09-08T09:06:00.000-04:002009-09-08T09:06:41.851-04:00Roll-Your-Own “Traffic Light” Icon Set<blockquote>Quote: <i>Here’s a great tip suggested by Clint Covington. Using an expression and a Rich Text text box, you can create a traffic light indicator in an Access form or report, as in the following illustrations:
<img src="http://blogs.msdn.com/blogfiles/access/WindowsLiveWriter/Rollyourowntrafficlighticonset_A475/clip_image002%5B5%5D_thumb.jpg"></i></blockquote>
<p>I really like this use of a Rich Text text box. We used it in my shop successfully- I think it was the first Access 2007 specific feature we used. (Rich Text text boxes became part of Access in the 2007 version, I think.) Take a look at <a href="http://blogs.msdn.com/access/archive/2009/04/16/roll-your-own-traffic-light-icon-set.aspx">http://blogs.msdn.com/access/archive/2009/04/16/roll-your-own-traffic-light-icon-set.aspx</a></p>
<p>This solution gives more options for colouring controls on your forms and reports than conditional formatting does.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-48614146716022969572009-09-04T19:58:00.005-04:002009-11-07T06:41:17.809-05:00Using Right-Click With Treeview Nodes<p>
Lets say you have a treeview and you want a user to be able to right-click a node and then be able to either delete it, copy it, or view details about it.
How do you trap the right-click event? How do you show a context menu? And how do you know what node was clicked?</p>
<p>
There's no OnRightClick event for Treeviews- that would have been the easiest solution. There are MouseUp and MouseDown events that know what mouse button was pushed- maybe that would help.</p>
<p>It turns out the secret is MouseUp. <a name='more'></a>The MouseUp event fires after the node click has been processed. That means when the MouseUpevent fires the node being clicked has become the selected node. So we'll use the MouseUp event, check if it was the right mouse button that was clicked, and then do whatever we need to do to the currently selected node. (Thanks to Kirby L. Wallace in <a href="http://www.developersdex.com/gurus/articles/386.asp">TreeView Right-Click Context Menus in Access</a> for the suggestion to use the MouseUp event.)</p>
<p>Now how to display the context menu. Turns out this is really easy- just use a real context menu. Access will automatically place it according to your mouse position. Best way to create context menus (AKA pop-up menus) IMO, is in code. Sample code follows for all the necessary pieces!</p>
<p>Here's code for the subroutines that will be called from the context (popup) menu. This code goes in a module. The code has been simplified to only work with one treeview in one form for the sake of simplicity.
<pre>
Public Sub PopUpNodeDetails()
With Forms!frmTreeviewDemo.tvDetails.SelectedItem
MsgBox "Text=" & .Text & vbCrLf & _
"Key=" & .Key & vbCrLf & _
"Tag=" & .Tag
End With
End Sub
Public Sub PopUpNodeDelete()
If MsgBox("Delete node " & Forms!frmTreeviewDemo.tvDetails.SelectedItem.Text & "?", _
Buttons:=vbOKCancel) = vbOK Then
MsgBox "Node to be deleted"
' delete code goes here
' usually as well as deleting the node there's some data to be deleted from tables as well
Else
MsgBox "Delete cancelled"
End If
End Sub
</pre>
</p>
<p>
Now let's look at creating the popup menu. It's pretty easy really. Here's the code. It's clear enough I think that I won't go through it line by line. This code goes in a module and needs to be run to set up the popup menu. It doesn't need to be run every time the database is opened- the popup menu will be saved with your database.<pre>
Public Sub SetUpContextMenu()
' requires a reference to the Microsoft office object library
On Error Resume Next ' ignore error if command bar does not exist to be deleted
CommandBars("MyTreeviewContextMenu").Delete
On Error GoTo 0
With CommandBars.Add(Name:="MyTreeviewContextMenu", Position:=msoBarPopup)
With .Controls.Add(Type:=msoControlButton)
.Caption = "Node Details"
.OnAction = "PopUpNodeDetails"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Delete Node"
.OnAction = "PopUpNodeDelete"
End With
End With
End Sub
</pre>
</p>
<p>Which leave only one thing- making the popup menu pop up when the right mouse button is clicked. Here's that code<pre>
Private Sub tvDetails_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal x As Long, ByVal y As Long)
If Button = acRightButton Then
CommandBars("MyTreeviewContextMenu").ShowPopup
End If
End Sub
</pre>As mentioned above- it relies on the fact that the node being clicked has just become the selected node (the SelectedItem property of the treeview). It just tells the context menu we created above to pop up, and it does!</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com10tag:blogger.com,1999:blog-8467396942183518214.post-84420006478648568962009-04-12T06:00:00.000-04:002009-04-12T06:00:00.190-04:00Programming The VBA Editor<p><a href="http://www.cpearson.com/excel/vbe.aspx">Programming The VBA Editor</a> describes methods for programmatically interacting with the VBE editor. <i>Quote:</i>"You can use these features to write custom procedures that create, change, or delete VBA modules and code procedures." The article is written for Excel but I think it should be adaptable to Access. (I haven't tried it yet- if you try it let me know how it works!)</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com2tag:blogger.com,1999:blog-8467396942183518214.post-71831994974168640532009-04-11T06:00:00.003-04:002009-11-07T06:51:30.741-05:00Use WShell to Retreive the Path to Windows Special Folders<p>Sometimes in your program you'll need to find the path of the user's MyDocuments folder, or to the Desktop, or to the Favorites folder. Because different versions of Windows will place these folders in different places, it's important to ask Windows for the location of the file, instead of hard coding it or figuring it out from the user name.</p>
<p>This post describes a method for using the Windows Script Host to retrieve these names. The Microsoft documentation is here: <a href="http://msdn.microsoft.com/library/0ea7b5xe.aspx">SpecialFolders Property.</a></p>
<p>Here's an example of how to build a VBA function that uses this method within MS Access to find the path to the user's MyDocuments folder.
<a name='more'></a>
<pre>
Public Function GetDocumentsFolder() As String
Dim objWShell As Object
Set objWShell = CreateObject("WScript.Shell")
GetDocumentsFolder = objWShell.SpecialFolders("MyDocuments")
End Function
</pre></p>
<p>You can use the following constants instead of MyDocuments to get the path to other special folders:<pre>
AllUsersDesktop
AllUsersStartMenu
AllUsersPrograms
AllUsersStartup
Desktop
Favorites
Fonts
MyDocuments
NetHood
PrintHood
Programs
Recent
SendTo
StartMenu
Startup
Templates
</pre>
Notably absent from the list above is the user's Temp folder. See the additional reading before-I've posted a method for getting that path previously <a href="http://mymsaccessblog.blogspot.com/2007/11/getting-path-for-temp-folder.html">here.</a> There's also an post linking to an API method to get special folders <a href="http://mymsaccessblog.blogspot.com/2008/09/getting-path-to-windows-special-folders.html">here.</a></p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-16087959057804914122009-04-10T06:00:00.004-04:002009-11-07T07:04:23.924-05:00Decompiling an Access Database<p>
Sometimes your database starts to exhibit unexplained behaviour- something that used to work suddenly stops working, or seemingly inexplicable errors appear. Or sometimes the database gets larger than it used to be (even after compact/repair) or starts to load much more slowly.</p>
<p>One of the solutions often recommended is to decompile the database.
<a name='more'></a>
Decompiling is done using a command line switch, and it seems to be an undocumented option. The links below provide instructions and additional information on decompiling. Note that some of the articles talk about decompiling actually introducing problems sometimes, so it seems like it's best to only decompile when you have reason to suspect a problem and to <b>make a copy of the database before decompiling</b>.</p>
<p>Here are the links:
<ul>
<li><a href="http://rogersaccessblog.blogspot.com/2009/04/how-do-i-decompile-database.html">How Do I Decompile a Database?</a> This new post from Rogers Access Library explains how to decompile and why you might need to do this, including instructions for creating a shortcut for decompiling.</li>
<li><a href="http://www.granite.ab.ca/access/decompile.htm">Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times</a> from Tony Toews contains more details including some other database repair methods.</li>
<li><a href="http://www.mvps.org/access/bugs/bugs0008.htm">Solving Access IPFs with /Decompile</a> from The Access Web provides very concise instructions and a link to a post that attempts to explain how decompile works.</li>
</ul>
</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com3tag:blogger.com,1999:blog-8467396942183518214.post-59749549584800626602009-04-09T21:05:00.004-04:002009-04-09T21:17:15.344-04:00The world's largest collection of connection strings on the Internet<div style="float:right">
<a href="http://www.carlprothman.net/">
<img src="http://www.carlprothman.net/Portals/0/CarlProthman.NET_Logo2.jpg" width=250>
</a>
</div>
<p>
Ran across this today:
<a href="http://www.carlprothman.net/Default.aspx?tabid=81">
The world's largest collection of connection strings on the Internet.
</a>
</p>
<p>
If you want to use ADO you need connection strings. And if you want to access something outside your local Access database you may need to find the right syntax for the particular type of data you want to connect to. Carl Prothman has compiled sample connection strings for everything from Jet to AS/400 to MS Project to Oracle to.... You get the idea.
</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-80720005854171009812009-04-08T20:06:00.002-04:002009-04-10T11:21:28.511-04:00The Normal Forms in a Nutshell<div style="padding-left:5px;padding-bottom:5px;float:right">
<a href="http://www.rogersaccesslibrary.com">
<img src="http://rogersaccesslibrary.com/welcome_files/welcome_ral.gif" width=200 border=0 />
</a>
</div>
<p>Good database design is key to success with application development in Access. The principles for database design in Access are the same as they are in any relational database, and one of the core principles is normalization. Within normaliztion there's a concept called "normal forms". Roger Carlson recently posted a series of articles that explain the normal forms. <a href="http://rogersaccessblog.blogspot.com/2009/03/normal-forms-in-nutshell.html">Take a look.</a> It's a great way to get an introduction to this important concept.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-65594749620557072342009-04-07T20:00:00.000-04:002009-04-09T21:27:37.086-04:00Accessing external data using the IN clause<p><a href="http://blogs.msdn.com/access/archive/2009/03/27/accessing-external-data-using-the-in-clause.aspx">Accessing external data using the IN clause</a>.
This article on the Microsoft Access Team Blog shows some of the ways to access data from outside your database by specifying the source of the data in the "In" clause of your query.</p>
<blockquote><i>Quote:</i>The other use of the IN keyword is as part of the SELECT statement or FROM clause, and is called the IN clause. The IN clause is used to access external data, and can be used in place of linked tables. For example, let's say that you had a SQL Server database that is used as part of an application, but you don't want to maintain a DSN to connect. You could use the IN clause in a query that uses a DSN-less connection to quickly read data from the external table.</blockquote>
<p>It's one of those weird and wonderful things you can do in Access that many developers may never have tried!</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com0tag:blogger.com,1999:blog-8467396942183518214.post-87542571447131910472009-04-04T15:40:00.002-04:002009-04-04T15:50:02.638-04:00Dymanic Forms: Moving Controls Along With The Tab Control They're Part Of<div style="padding-right:5px;padding-bottom:5px;padding-top:10px;float:left">
<a href="http://www.rogersaccesslibrary.com">
<img src="http://rogersaccesslibrary.com/welcome_files/welcome_ral.gif" width=200 border=0 />
</div>
</a>
<p>Lately I've built some really neat forms where the controls are moved based on the size of the form or in response to use input. It's not so hard to change the position of a control and it can make for a really well optimized user interface.</p>
<p>One of the particular challenges when moving controls is keeping the controls on a tab control in their proper place. If you move a tab control the controls on the tabs don't move on their own. <a href="https://mvp.support.microsoft.com/profile=E2053F3A-577E-4261-AE7E-563D205C03A7">A.D. Tejpal</a> recently posted <a href="http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=448">this sample database</a> on <a href="http://rogersaccesslibrary.com/">Rogers Access Library</a> that demonstrates enumerating the controls on each page of the tab control and moving them all so that they all stay together and stay in the right place on the tab control.</p>Stephenhttp://www.blogger.com/profile/13190848153946763046noreply@blogger.com1