Video How-To: Make a Query Ask For Input.

Take a look at this Video How-To: Make a query ask for input. It's a great first place to start if you want to learn how to have queries prompt for input, or use a form to provide the criteria for a query.

Quote:
Want to make your query ask for input when it runs? Watch these demos to see how you can add parameters to a query, handle input that is the wrong kind of data, and use a form to collect parameters and then use them with multiple queries.

Help Access run faster

The Microsoft Office Online team has posted a new rundown on performance considerations for Access here. Some of the tips are also in other articles I've linked previously, but this looks like a good list.

Coding Techniques and Programming Practices

Here's a Microsoft article with some leading practices for developers.

Coding Techniques and Programming Practices

The table of contents:

• Coding Standards and Code Reviews
• Coding Techniques
• Best Practices
• Conclusion
• Suggested Reading

Access/Outlook Automation

Here's a good looking article from Microsoft on automation between Access and Outlook: Using Automation in Microsoft Office Access 2003 to Work with Microsoft Office Outlook 2003

My Treeview Project | Episode Two: Northwind Categories and Products

In Episode 1: The Hello World! Treeview we built the simplest of treeviews, just to introduce some of the concepts. In this episode we'll build a treeview to display products in categories.

We'll use data from Microsoft's Northwind Traders sample database. If you don't have a copy you can download it here .

Start by creating a blank form in the Northwind Traders database and add a Treeview control, the same way we did in episode 1.

Read more »

MS Project- Use the TaskTable to Get the Fields Project Displays



If you're building an application that uses data about tasks in an MS Project project plan, you may simply need all the properties of all the tasks, or you may wish to use the data as it's currently displayed in MS Project. A task has dozens of pieces of data associated with it; only some are displayed in the current view.

Read more »

MS Project Automation Links

MS Project is part of Microsoft's Office family and so behaves like the other Office tools in terms of providing a rich object model for VBA development. I've built integration between Project and Access and between Project and Excel. Here are some links you might find useful if you're considering Project automation:

Read more »

Microsoft Controls Reference

Here's a link to keep handy: ActiveX Controls Reference on MSDN. It's written for VB and Visual Studio, rather than for VBA and Access, but the differences between the two are not important for this purpose. At least in my environment there's no help loaded for ActiveX controls such as Treeview, Listview, and DateTimePicker, so I use this reference to the properties, methods and events of these controls.

How do I let my user pick a date from a calendar?

There are tons of ways to do this- some use ActiveX controls, some do not. Some have more functionality than others.
For a simple approach, the DateTimePicker ActiveX control is probably your best bet. The only disadvantage is that it's an ActiveX control, which can be difficult to manage if you have to install in a variety of environments. Using the DateTimePicker is really just like using a combo box. Use it bound or unbound. Just like a combo box, when the user clicks the arrow the control shows the list of choices- in this case a calendar where the user can click on a date.

Some Microsoft links:

• A walkthough of using the Access Calendar Control
• A reference for the DateTimePicker ActiveX control
• A reference for the ActiveX MonthView control

Some non-Microsoft web links:

• A nice round-up on UtterAccess.com of several good solutions
• A Pop-up Calendar for your Access Forms (Tutorial using the Access Calendar Control)

Demo: Bring your business data together in an Access database

This is really an introductory overview of Access. It would be a good place for a brand new user to start. If you're think you might need to use Access, or you're wondering if you've outgrown Excel as a data store, this would be a good place to start. Demo: Bring your business data together in an Access database.

How do I resize my text box to fit my text?

You can set the Can Grow property of your text box to Yes all you like, it won't grow on your form when it's displayed. Can Grow only applies when a form or report is printed. So how do you make your text box adjust its size to exactly match the amount of text you're displaying?

Take a look at TextHeightWidth.zip on Stephen Lebans' site. It's actually pretty easy to implement and allows you to find out how big your text really is and to adjust the text box (or combo box or list box) accordingly.

Excel: Using Custom Functions in Dynamic Ranges

This is neat. Define ranges in Excel not as a static group of cells but using a function which will dynamically determine which cells to include.

Another Treeview Reference from Microsoft

I stumbled across this post on MSDN this morning. It gives a great overview of the treeview control that I hadn't seen before.

See also: other treeview posts on my blog.

Microsoft Office 2000/Visual Basic Programmer's Guide

Quote:
The Microsoft Office 2000/Visual Basic Programmer's Guide describes how to use the applications and components in Microsoft Office 2000 to build custom solutions. This guide is the definitive resource for developers who are creating custom solutions based on Office applications. From the overall design of your solution to the nitty-gritty details of implementing it, this book tells you what you need to know to get things done quickly and productively.

This is a great resource for Office VBA developers. Among other valuable chapters it includes one of the only references I have been able to find for custom classes and objects.

Use a Pop-Up Form To Filter Your Report

How do I use a form to specify criteria for my report using a form? There are a number of ways to do this. This post in the code archive at UtterAccess.com is really neat. It demonstrates a report popping up a form to collect criteria from the user.

Read more »

Subquery: Add Missing Master Records

Let's say you have some data to add to your detail table, but some of the master records are missing. Here's a example query that uses a subquery to add "missing records" based on matching an ID field between two tables.

INSERT INTO tblMasterRecords ( MyID, MyText )
  SELECT MyID , "New Master record"
  FROM tblNewData
  WHERE NOT EXISTS
      (SELECT * FROM tblMasterRecords WHERE MyID=tblNewData.MyID);

Some things to note here:

• The query in the brackets is called a subquery.
• The EXISTS condition resolves to true when the subquery returns any records. By using this in the WHERE NOT EXISTS syntax, the main query returns records from tblNewData only when the subquery doesn't return any records.
• Inside the subquery, the ID of current record in the main query is referenced as tblNewdata.MyID.

My Treeview Project | Episode One: The Hello World! Treeview

The hello world program is a common first exercise when learning to work in a new programming environment: a program that simply displays or prints "Hello World!". In the first episode of this new series on treeviews we'll build the hello world treeview in an Access form.

Read more »

If My Filter Doesn't Find Any Records, Show Me All the Records Instead!

Here's a neat little SQL subquery. The request was: If no records match the specified criteria show me all records instead. I think it was to be used in a form where the user would be able to browse the data. They'd use keyword to subset the data, but the design point was to never not show any records.

This can be done using the Exists keyword. Here's an example:

SELECT Clients.*
FROM Clients
WHERE
  (Clients.ClientCode Between "BC" And "BZ")   
  OR   
  (NOT EXISTS
    (SELECT * FROM Clients WHERE Clients.ClientCode Between "BC" And "BZ")
   ) ;

The EXISTS condition resolves to true when the subquery (inside the brackets starting with SELECT) returns any records. In this case I use NOT to reverse that. So if the condition WHERE Clients.ClientCode Between "BC" And "BZ" doesn't find any records, the criteria staring with NOT EXISTS is true, and so all records match the complete criteria (because of the OR) and the query returns all the rows in the table.

A Working Treeview Example with Drag and Drop

Here's another working treeview sample from UtterAccess.com. This one's pretty sophisticated, with a drag and drop function used to change who staff report to and another to drag unassigned orders from a listview into the treeview where you drop them on the staff that will work on them. A nice implementation indeed.
The author, Brent Spaulding, is one the the treeview experts at UtterAccess.
See also: other treeview posts on my blog.

Union Query Basics

Union queries are an important part of your SQL toolset that you may be overlooking. I ran across a two part overview of Union queries that would be a good starting point for anyone trying to understand how to use them, or even why they might be useful. Take a look at:

Microsoft Access Union Queries (Part 1)
Microsoft Access Union Queries (Part 2)

Quote:
Union Queries are used to bring together two recordsets of data to merge into one recordset of data. For instance, let’s say you have two tables, one for sales going to individuals, and one for sales going to companies. A union query can bring all of the records from both tables (providing you are querying the same number of fields) into one giant recordset so you can view all of your records at once.
This is useful because although you may want to keep tables separate because they may pertain to different departments, bringing them together into one big query will allow you to run different statistical numbers across all of your sales. You would easily be able to compare the percentage difference in corporate clients to individuals in any region, or perhaps see where your greatest individual sales base is in order to target corporations in the same area.
Union Queries can also be used to create a single source for a mailing list. Union Queries eliminate the need to create a make-table query in order to bring in some records, then an append query to add others on top which bloats the size of your database as you’re storing all this data twice – once in their own tables, and once merged in a new table – which means you’ll have to deal with deleting specific data or updating only certain data and creating new object after new object in your database.
Union Queries are just like other queries, they don’t take up the space of a table, and the query is always updated to reflect new table data. Just like other queries, Union Queries can be used for report record-sources as well! Great stuff!

Using a TreeView Control as a Record Selector

Treeview continues to be one of the most common of the search keywords that bring folk to MyMSAccessBlog. I love developing with treeviews and if I can narrow down the specific questions folk are searching for answers to I'll post some detailed tips or solutions. For now, here's here's a zip file from Helen Feddema's Access Archon page with a working example:

• original version circa 2002 with an article describing how it works
• new version from Feb 2008 with increased functionality including a neat "copy record" function

The example uses a treeview to select books or authors to display information about.

Quote:
A TreeView control makes a better record selector than the standard combo box for situations where you are working with hierarchical data, and want to allow selection of an item from a branch of the data hierarchy.

For all my treeview posts click here.

How Should I Structure My Tables for Questionnaires or Surveys

Scenario: casual database developer has built a database for surveys or questionnaires and can't figure out why the queries are becoming so cumbersome.

Turns out there's a right way to design a database for this kind of work, and it's nothing like you'd do it in Excel. Here's an article from Microsoft that describes the right way to manage data like this.

Calculating elapsed time is more than just numbers

Here's a neat post on the Access Team Blog. It's a function you can use in your application to display elapsed time in a more "friendly" way. The units used and the precision applied are determined by the magnitude of the interval- take a look!

Quote:
I didn’t just want to show the hours or even days elapsed, but something more in sync with the way I want the information given to me- specifically that when dates are closer to the current time they are represented more precisely and dates that are farther away are shown generically.

***Update 2009-04-04: This article has also been posted on MSDN as: Constructing Modern Time Elapsed Strings in Access 2007.

Restore a Deleted Microsoft Access Table with VBA

I ran across this post: Restore a Deleted Microsoft Access Table with VBA which uses simple code to un-delete a table. I tried it in Access 2007 and it seemed to work fine. It looks like it relies on the fact that the table will still be in the tables collection until you close the database. It builds and executes a simple SQL query to copy from the (now invisible) table to a new table. Sounds like a good reference to keep handy!

Fixing AutoNumbers when Access assigns negatives or duplicates

I ran across this post from Allen Browne on resolving problems with autonumbers. It seems like a useful post to keep handy for when I need it someday.

In Access 2000 and later, an AutoNumber field may begin assigning extreme values, negatives, and even duplicates. The attempt to assign a duplicate fails if the field is primary key, so it may not be possible to save new records in an affected table.

Filtering Crosstab Queries

I was cruising UtterAccess this morning and came across a thread talking about filtering crosstab queries using parameters. I remember having trouble with this once but had not really needed it lately so hadn't looked into it. The solution in the thread pointed back to two ohter UA posts with the answer:

• I have a report pulling data from a crosstab query. I want to add a date range criteria and also display the start and end date of the result.
• Need to transpose data...crosstab?

It seems it's as simple as specifying the parameter explicity in the query's parameter list. I did a little more reading and found two other good articles:

• ACC2000: Error When Running Crosstab Query with a Parameter (from Microsoft)
• Crosstab query techniques | Handle Paramaters (from Allen Browne)

The problem is summed up tersely in the Microsoft article as:

If you create a crosstab query that uses an implicit parameter [XXX] or a form reference in a WHERE clause (criteria), you may receive the following error message:

The Microsoft Jet database engine does not recognize '[XXX]' as a valid field name or expression.

And including your parameter or a reference to the form control in the query's parameter list solves it quickly and easily. Allen Browne's article has a step-by-step description of doing that.
Buried within one of the Access posts listed above is the phrase:

"You can use the Parameter names in the Report just like a Field in the RecordSource of the Report."

What's that all about? I tried it and it's as simple as that. It's redundant when you're filtering on a form control, but when you let the query prompt for the parameter you can use the parameter in your report just like a column in your recordsource. Really neat for listing the selction criteria in a report's header.
Nice- it's 7:40 on Saturday morning and I've learned two things already today!

**Some links above corrected 2011-09-05**