Let's take a look at a query that cannot be used to update data in the database (a non-updatable query). Once again we're using a summer camp database and we have a query that is detecting activities that have fewer than three campers registered. These activities will be consolidated with other activities or cancelled due to low enrollment. The query below answers this question:

Figure 1

It is important to realize that the reason a query is non-updatable is often because the query relies on another query that is non-updatable. This is the case in this example shown in Figure 1. This query (qryNoUpdate3) uses another query (qsumActivityCount), a query which counts the number of activities campers have signed up for. It's this summation or Totals query that makes the query above non-updatable, because it, in itself, is a non- updatable query – see the January 2005 Access Wizard for more information about summation queries.

When the query in Figure 1 is run it returns the following:

This report shows the activities that have less than three campers enrolled. Let's say that you decide you want to change the Activity Name Ping Pong to Table Tennis. You can certainly do that in the Activity table, or maybe from a form, but you can't do it from this query because the data it returns is non-updatable. For demonstration purposes, let's say that we really do want to change the name from Ping Pong to Table Tennis everywhere in the database through this query interface. How can we do that? Well there are at least two solutions. This month, we'll take a look at the simpler of the two – replacing the non-updatable summation query used in this query with the function Dsum.

Before we move to the solution, we really should understand the non-updatable summation query used to count the number of campers signed up for each activity. The figure below presents the query in the design view:

As I mentioned earlier, this is a summation (or summary) query. When you are using or creating a summary query, you'll see the ∑ symbol at the top of the screen in the menu bar. It uses the table trelCamperPrefActivity (a trel table is an intermediate table for many-to-many relationships, see September 2004) that tracks campers who have signed up for various activities.

Look back at our original query in Figure 1 above. If we can remove the query qsumActivityCount (a non- updateable summation query) from our query with an expression using the Dcount function, we'll be able to update data in the database when the query runs.

Here's our revised query:

The changes to the original query are as follows:

1. We have added an expression that utilizes the DCount function to the query. Because of space restrictions, you can’t see the entire expression, so here it is in its entirety:
   
   Expr1: DCount ("ActivityKey","trelCamperPrefActivity","ActivityKey=" & [tblActivities].[ActivityKey])
   
   The DCount function essentially counts the number of instances that a certain criteria is met. I won't try to explain all the intricacies here. It's part of the much larger subject of domain functions that will be featured in a future article. In the meantime, in order to understand how to use it, search Access Help for "DCount Function."

2. We've replaced the summation query qsumActivityCount with the table trelCamperPrefActivity – this was the original basis for the summation query. The table trelCamperPrefActivity is called by the DCount function.

The results obtained with this revised query look exactly the same as before; however there is one very important difference. If we want to change Ping Pong to Table Tennis we can do it directly in the query when it runs and it will ripple throughout the application.

It's the removal of the summation query that changes the status of the query to updatable. Since we still care about the count, we get to that through explicit counting using the DCount function.

Last month we looked at when you can update data in the database through a query (an updatable query) versus when you cannot (a non-updatable query). This month, we discussed a solution that can turn a non-updatable query into an updatable query in certain circumstances. We replaced a summation query with a DCount function calling a many-to-many table, thereby converting a non-updatable query to an updatable query. The DCount function is resource intensive, and if used with a large query that returns a lot records, you may find that performance plummets.

There are other ways to turn non-updatable queries into queries in which the data can be changed, including something called Sub queries. Be on the lookout for this solution to our problem in an upcoming issue.

Frequently when editing data in a form, you’ll find that when your cursor is in certain fields the form will jump to the next record when you prefer to stay on the current record. This can be really annoying since it requires you to hit the prior record button, or even worse you start entering data on the wrong record.

Preventing this record jumping behavior is simple with a small change to the form design:

Open the form in design view and go to the form properties (View | Properties from the menu at the top of the screen). On the tab labeled "Other" there is a property called "Cycle". This property controls what happens when you reach the last control on your form. The default is "All Records." By changing this to "Current Record," you'll no longer find yourself jumping to the next record when you don't intend to.