O'Reilly Book Excerpts: Access Hacks

Hacking Access

Populate and Sort Lists with Flair

Author's note: Populating a listbox is standard Access fare. But what if you need to populate a listbox with values from more than one source, and sort the whole thing as if it were from a single source? Better yet, imagine a listbox that always displays the most popular items at the top. Users will love that! Read on, and learn how to hack this into your application.

Use these three clever techniques to populate and sort listbox controls.

Lists are integral to form design. True, not all forms need a list, but when they're applicable, selecting an item from a list is much easier than typing in the value. This also makes it easier to avoid typos.

This hack presents three ways to populate and sort listbox controls. In each example, the underlying tables and structure are key elements. The examples show how to sort alphabetically, but from two sources; how to sort based on a key value; how to sort on placement in the SQL statement; and even how to sort by tracking the popularity of the list items themselves! The SQL Union clause is a key factor to getting much of this to happen.

The Form

Figure 3-12 shows a form with three lists, aptly named List 1, List 2, and List 3.

Figure 3-12. Three list controls on a form

Behind the scenes, two tables populate the list controls: tblFruits and tblVegetables, shown in Figure 3-13. Note that they share two common fields: SortNumber and ListItem. This common structure is put to good use, as you will see soon.

Figure 3-13. Two tables used to populate the list controls

Populating a Listbox Alphabetically from Two Sources

List 1 displays the values from the two tables, sorted alphabetically as one larger list. The trick is to have the List control use the records of both tables in its Row Source property. You do this by combining the records of both tables in a Union query. Figure 3-14 shows the form in Design mode with the property sheet set to List 1.

Figure 3-14. The Row Source property for List 1

The SQL statement in the Row Source property reads like this:

	Select ListItem from tblFruits UNION Select ListItem from tblVegetables;

The Union clause allows the values from the two tables to be combined, given that the structure and datatype are the same. In other words, the ListItem field from each table is addressed with the SQL statement. Querying the same number of fields in each Select statement with the Unionquery is a requirement. The query can't run if the number of fields being accessed from each table differs.

As a result the combined records are sorted as if they really come from one source (which technically is true via the Union query). Therefore, the distinction of fruits and vegetables is purposely lost, and instead, asparagus follows apple, broccoli follows banana, and so on.

This technique is useful when you need to present items in a list that come from more than one source. As discussed in the following section, you can bring together as many sources as you need with multiple Union clauses.

Access Hacks

Related Reading

Access Hacks
Tips & Tools for Wrangling Your Data
By Ken Bluttman

Controlling the Sort in a Listbox Populated from Multiple Sources

In Figure 3-12, List 2 shows the result of sorting fruits in a certain order and vegetables in a certain order. Additionally, the fruits and vegetables aren't sorted with each other. The list also includes separators and values not found in the source tables: All, All Fruits, and All Vegetables. How did all these items get into the list?

A Union query populates the listbox. The two sources—tblFruits and tblVegetables—are used, but instead of letting the list mix and sort the items alphabetically, the SortNumber field controls the sort.

A key point here is that the range of values for SortNumber in the tblFruits table is different from the range of values for SortNumber in the tblVegetables table. The Union operation actually does combine both sources into one sort, but the SortNumber field ranges keep the two lists apart in the listbox.

Figure 3-15 shows the form in Design mode with the property sheet set to List 2. The SQL statement that serves as the Row Source property is displayed in the Zoom box.

Here is the SQL statement:

    Select "All" as a, 3-2 as SortNumber from tblFruits
    Union Select "---" as a, 3-1 as SortNumber from tblFruits
    Union Select "All Fruits" as a, 0 as SortNumber from tblFruits
    Union Select ListItem, SortNumber From tblFruits
    Union Select "---" as a, 99 as SortNumber from tblVegetables
    Union Select "All Vegetables" as a,
    100 as SortNumber from tblVegetables
    Union Select ListItem, SortNumber From tblVegetables
    Order By SortNumber

Figure 3-15. The Row Source property for List 2

Quite a bit is going on here. Overall, the SQL combines items from the source tables with items provided right within the SQL. All these tie together via the SortNumber field.

This SQL statement uses the Union clause several times, to make sure that all Select statements point to the same number of fields. In this example, that number is 2.

The SQL starts by getting the word All to the top of the list. This snippet forces the word All into the list:

     Select "All" as a, 3-2 as SortNumber

The code snippet does this by giving the word All the lowest value of SortNumber—in this case, 3-2. To be clear, neither the word All nor the value 3-2 actually comes from an underlying table. However, their placement in the SQL follows the structure of all the other Select statements in the SQL, which allows them to be combined with the other values being accessed by the SQL.

The SQL uses Union to combine values from the tables with these on-the-fly values. A number of these values are in the SQL:

    Select "All" as a, 3-2 as SortNumber from tblFruits
    Select "---" as a, 3-1 as SortNumber from tblFruits
    Select "All Fruits" as a, 0 as SortNumber from tblFruits
    Select "---" as a, 99 as SortNumber from tblVegetables
    Select "All Vegetables" as a, 100 as SortNumber from tblVegetables

All these parts of the SQL force the list to present a value: All, All Fruits, All Vegetables, or ---. None of these values comes from the tables. However, all of them are paired with a sort number, and this is what places them in their sequential place in the listbox.

Consider the sort numbers associated with these on-the-fly items, while considering the sort numbers of the items in the tables (see Figure 3-13). Sort numbers for the vegetables start at 101. Therefore, the All Vegetables item has been associated with the number 100. This forces it to appear in the list directly above the actual vegetables.

Keep in mind that a listbox such as this, with several possible items a user can select, also requires a related level of functionality to handle the user's selection. If a user selects a single fruit or vegetable, chances are the application will continue processing. However, what if a user selects All Fruits? Your processing will need to handle all the values in the tblFruits table.

Also note that you enter the separator characters (---) into the list for the sake of segregating parts of the lengthy list of items. This is rather pleasing for someone scrolling through a long list; however, a user can select the separators! Therefore, you need to ensure that user validation and feedback are in place in case this happens. Typically, if a user selects the separator characters, a message should appear alerting him to make another selection.

Sorting List Items by Popularity

It's not always easy to know ahead of time which items users will select most often from a list. You can use a Sort Number field to arrange list items in a way that seems best, but there is an even better way to do this.

Why not let user actions drive the way the list is sorted? Keeping in mind that it is easy to sort a list by a numerical field, logic dictates that the values in the numerical field should reflect the popularity of the list items.

This is easy to do by updating a list's Sort field each time it is selected. Figure 3-16 shows the form in Design mode with the property sheet set to List 3.

Here's the Row Source SQL statement for List 3:

     SELECT Occurrence, ListItem FROM tblFruits ORDER BY Occurrence DESC;

This listbox uses the tblFruits table exclusively. This table has the additional Occurrence field, which drives the way items are sorted in the listbox. Note from the Row Source property that items are listed based on the Occurrence field values being in descending order.

Figure 3-16. The Row Source property for List 3

To make sense of this, it is necessary to somehow update the values in the Occurrence field. This update occurs when you process the selected list value—in whatever way your processing works. For the purpose of this demonstration, a button has been placed on the form. Here's the Click event for the button:

	Private Sub cmdUpdateCount_Click()
      'get the current count for this item
	  Dim selected_item_count As Integer
	  If Not IsNull(Me.List3) Then
        selected_item_count = _
           DLookup("Occurrence", "tblFruits", "ListItem='" & Me.List3 & "'")
		'increase the count and update the table
		selected_item_count = selected_item_count + 1
		DoCmd.SetWarnings False
		DoCmd.RunSQL ("Update tblFruits Set Occurrence=" & _
		   selected_item_count & " Where ListItem='" & Me.List3 & "'")
	  End If
	End Sub	

In a nutshell, the DLookup function finds the current value of the Occurrence field for the selected item and stores it in the selected_item_count variable. The value is incremented by 1, and a SQL Update statement writes the value back into the table, for the given item. Finally, the list is refreshed so that on the form the list will resort.

As a result, when items in the list are selected and processed, they float to the top of the list. You can see this by comparing the placement of items in List 3 in Figure 3-12 with the values of the Occurrence field in the tblFruits table in Figure 3-13. For example, raspberry is the first item in List 3 because it has the highest value in the Occurrence field.

Protect Intellectual Property

Author's note: Here's a hack that shows you how to put a watermark on your reports. By using a semitransparent graphic and setting a few properties, you can make sure your report displays and prints with an obvious "Confidential" message sprawled across the page.

Prevent misuse of confidential and copyrighted material by printing watermarks on reports.

If someone is bent on taking your intellectual property, you have no fool-proof way to prevent it. However, common sense dictates that we do our best to protect our assets. Often, putting some wording on the page or in the report header or footer saying the material is confidential serves this need. However, this method doesn't necessarily make the message stick out like a sore thumb.

An additional measure is to put watermarks on reports. A watermark sits right within the body of a report, page after page. It ends up underneath the actual text and is somewhat transparent; that way, it doesn't obscure the text, but it is evident enough to get the message across in a big way.

Figure 4-16 shows a report in which a watermark sits mixed in with the data. The word "Confidential" stretches diagonally from the lower left to the upper right. The text appears to sit on top of the watermark.

Figure 4-16. Using a watermark to get the message across

Making the Watermark

To create a watermark, you need to create a graphic that you will set to the Picture property on a report. You will need a graphics program to create a decent watermark. Several good graphics programs are available. I use an excellent, affordable program called Paint Shop Pro by Corel Corp. (http://www.jasc.com). Whichever graphics program you use, it must be able to do the following:

Work with text as a graphic

You want to be able to stretch and orient the data.

Apply transparency settings

The final graphic should be in the ballpark of 75% transparent. It's best to determine the actual setting via trial and error.

Specify the size of the graphic

When creating the graphic, it's important to make it almost as large as the paper you are using. Whatever size you make the graphic is the size it appears on the report (based on a property setting described later in this hack).

Save the graphic as a .jpg, .bmp, and so on

You can use any file format that can be used when tapping the Picture property on the report.

Creating such a graphic is beyond the scope of this hack, but for your information, the graphic in Figure 4-16 is 70% transparent and was saved as a .jpg file. The graphic is about 4 x 7 inches. Figure 4-17 shows how the graphics file appears on its own.

Figure 4-17. The watermark as a graphics file

Using the Watermark

Once you save the watermark as a file, go into the report's Design mode. In the property sheet, click the Picture property, and browse to select the graphics file, as shown in Figure 4-18.

Figure 4-18. Setting the report's Picture property

A few other relevant settings work with the Picture property:

Picture Type

You have a choice of Embedded or Linked. Embedded is the correct choice here. Linked will attempt to open the graphic separately, which isn't the point of using a watermark.

Picture Size Mode

The choices are Clip, Stretch, and Zoom. Each treats how the graphic is placed on the report in a different manner. Experimenting with these settings is the best way to understand how they work. However, as mentioned earlier, if you sized the graphic correctly when you created it, use the Clip setting here. Following this approach helps to avoid having to guess your way through the picture's placement on the report.

Picture Alignment

You have five choices: Top Left, Top Right, Center, Bottom Left, and Bottom Right. Most people choose Center, but you might find a different setting serves your needs.

Picture Tiling

The settings are Yes and No. The No setting places the graphic once on the page. The Yes setting tiles the graphic, which creates a repeating pattern. Try both of them to see which is better for you. The Yes setting makes a busier-looking watermark, but perhaps that is what you want.

Picture Pages

This lets you designate on which pages the watermark will appear. The choices are All Pages, First Page, and No Pages. I hope you don't choose No Pages, or you won't see your watermark!

You also might have to change the Back Style property on the report. You might have to do this because the watermark appears under the text, and text boxes can take up more room than the actual text they display. Figure 4-19 demonstrates this dilemma. In the report, the rectangular shape of the text box covers up part of the watermark. You don't actually see the text box, but the rectangular shape becomes apparent when it's contrasted with the watermark underneath.

Figure 4-19. Text boxes covering up the watermark

To avoid this behavior, go into the design of the report. For any text boxes that sit over the watermark, change the Back Style property from Normal to Transparent. This forces the text boxes to display just the text, which is exactly the effect you want. Figure 4-20 shows how the report appears when the text boxes are transparent.

Figure 4-20. The watermark, appearing through the text boxes

Use Word to Compare Data in Two Access Tables

Author's note: Here's a great way to look for differences in the data in two apparently identical tables. This hack shows you how to use Word's Compare and Merge Documents feature to easily identify the discrepancies.

Look for discrepancies the easy way, using Word's Document Compare utility.

Sometimes, you have to compare data in two Access tables. Usually you do this when you have one table that derives from two different copies of the database. The data might differ between the tables; for example, some data has been updated in one table, and now you need to uncover the discrepancies.

You can do this in a couple of ways. You can use some queries, but if there are many fields, query design could be difficult. Another option is to write code to read through both tables and identify the differences. This works but it also takes a bit of time to get the code working correctly.

Here's a great alternative: Word has a built-in feature that compares two documents and highlights the differences.

The first thing you need to do is export the Access tables as text files. Word then uses these to run a comparison. Figure 7-15 shows the two tables already saved as text. As you can see, they appear identical.

Figure 7-15. Two tables saved as text files

In Word, open one of the text files. Then, use the Tools → Compare and Merge Documents menu item to browse to the second text file. As shown in Figure 7-16, you have options for how to compare and merge the documents. I always choose "Merge into new document." That way, I know the original files aren't altered.

A new document is created, but you immediately run into a problem. Word's spellchecker and grammar checker will flag nearly everything as incorrect because the export from Access creates records with no space breaks. This is correct for the data, but not as far as Word is concerned. So, the next thing to do is turn off the spellchecker and grammar checker in Word's Options dialog, as shown in Figure 7-17. By the way, the first opened text file didn't flag any errors because it was still a text file. The new merged document, on the other hand, is a proper Word document.

Figure 7-16. Setting up the document comparison

Figure 7-17. Turning off spellchecker and grammar checker in Word

Once you can see the document for what it is, you can see places where the data doesn't match because the data is formatted with strikethroughs, as shown in Figure 7-18.

Figure 7-18. Identifying unmatched data

Scrolling through this data is a breeze. You can quickly see where the data is different and decide what to do about it.

—Andrea Moss

Editor's note: Check back to this space again next week for two more excerpts from Access Hacks. We'll cover how to automate the process of checking and updating your database tables against those in a master database, as well as how to use a browser right on an Access form.

View catalog information for Access Hacks

Return to Windows DevCenter.

Copyright © 2017 O'Reilly Media, Inc.