February 19, 2009

Don’t let disappearing error handling send you into a panic

  • Date: November 3rd, 2008
  • Author: Susan Harkins

Last week, I added a simple procedure to an existing Access database. While testing it, I noticed that VBA was ignoring the error-handling statement and routine. I ran a quick Compile and Repair and crossed my fingers, but VBA was still breaking at errors instead of passing control to the procedure’s error-handling routine.

I sent out a What’s going on? e-mail to several colleagues. I pulled up Microsoft’s Knowledgebase and began searching for answers. Finally, I sacrificed my favorite SQL book upon my altar to the Access gods.

I guess the gods were appeased because within minutes, friend and developer extraordinaire Jim Dettman responded to my e-mail and told me to check the Error Trapping settings in the Visual Basic Editor (VBE). I found Break On Unhandled Errors cleared and Break On All Errors checked. I checked On Unhandled Errors, and VBA executed my error-handling routines as expected.

november2008blog1fig1r.jpgcialis vs generic cialis />

I felt stupid for not thinking of it myself. How the option changed, I’ll probably never know. I certainly didn’t do it.

If you’d like to try it yourself, do the following:

  1. In the VBE, choose Options from the Tools menu.
  2. On the General tab, check Break On All Errors in the Error Trapping Section.
  3. Click OK.
  4. Run any routine that’s apt to return an error, as long as it has an error-handling routine. If necessary, create a temporary procedure that uses an Err.Raise statement.
  5. Repeat steps 1 through 3 to reset Break On Unhandled Errors.

When writing a procedure, you’ll want to add its error-handling statements. However, while developing and testing code, it’s good to turn off error trapping so you can experience all the errors. Instead of commenting out all the error-handling statements and routines, set Break On All Errors. VBA will ignore your error-handling statements and break on every error. That way, you’ll know whether you’re trapping everything appropriately. When you’re done, simply reset the option so your error handling can take over.

Just remember that the settings exist and you’ll avoid an unnecessary panic if your error trapping suddenly stops performing as expected.

Permalink • Print • Comment

Use custom field headers to create more professional Access reports

  • Date: November 4th, 2008
  • Author: Mary Ann Richardson

Query-based reports give you the opportunity to turn those unfriendly field names into better-looking headers. See how this quick tweak can polish up your reports.


When you run a report, the report field headers are based on the field names in the source table. If you base your report on a query, you have an opportunity to change the field headers. For example, say you’re basing your report on a query that includes the fields CompanyName, ContactName, and ContactTitle. Follow these steps:

  1. Open the query in Design mode.
  2. Right-click the CompanyName field and select Properties.
  3. Click in the Caption property box and type Client Company.
  4. Right-click the ContactName field and select Properties  (Figure A).

Figure A

client name

  1. Click in the Caption property box and type Name (Figure B).

Figure B

cialis usa border=”0″ alt=”name” title=”name” width=”327″ height=”311″ />

  1. Right-click the ContactTitle field and select Properties.
  2. Click in the Caption property box and type Title.
  3. Run the Query (Figure C).

Figure C

report query

Although the query results and any report based on the query use the new caption names, the field names remain unchanged in the source table.

Permalink • Print • Comment

Quickly move data to another workbook without copy and paste

  • Date: November 4th, 2008
  • Author: Mary Ann Richardson

Excel offers a fast, convenient way to transport data from one workbook to another. Mary Ann Richardson explains the technique.


Let’s say you keep all records for your clients for the Philadelphia region in one Excel workbook. The first worksheet lists the company name, address, and contact name and title. The second worksheet keeps a record of the clients’ sales transactions. You would like to use the information in the Excel workbook to send weekly reminders of weekly promotions to your clients. However, you don’t want the marketing staff to work with the same workbook as the sales staff. Using Excel’s worksheet copy/move feature, you can quickly create a workbook that contains only the information needed to address your weekly promotional mailings. Follow these steps:

  1. Open the workbook containing the customer data.
  2. Right-click the first worksheet tab.
  3. Select Move Or Copy from the shortcut menu (Figure A).

Figure A

move or copy

  1. Click the To Book drop-down list and select (new book).
  2. Select the Create A Copy check box (Figure B) and click OK.

Figure B

create a copy

Excel creates a new workbook (Book1) and copies the data from the first worksheet to Sheet 1 of the new book. Save the new workbook as Mailing Lists.

To add another list of clients from another workbook — say, one containing records on customers from Cherry Hill, NJ — follow these steps:

  1. Open the Mailing Lists workbook.
  2. Open the second workbook containing the cialis trial pack customer address listing for Cherry Hill on Sheet 1.
  3. Right-click the Sheet 1 tab.
  4. Click Move Or Copy from the shortcut menu.
  5. Click the To Book drop-down list and select Mailing Lists.
  6. From the Before Sheet list box, select (move to end).
  7. Select the Create A Copy check box (Figure C) and click OK.

Figure C

move to existing sheet

Permalink • Print • Comment

10+ ways to help your users transition to Outlook 2007

  • Date: October 29th, 2008
  • Author: Susan Harkins

Unlike the other Office 2007 apps, the Outlook 2007 interface isn’t likely to throw users into a panic. But you’ll still need to help them get up to speed with new features and show them how to take advantage of various improvements.


When you upgrade users to a new version of software, it always takes time for them to get acclimated. They can’t find their favorite features and they often resist using new features. Outlook 2007 is no exception. But the good news is, users won’t find Outlook 2007 as radically different as they might have feared. The familiar interface is still there, and old features are easy to find.

If you upgrade from a previous version, Outlook will import the existing address book, settings, and messages. You won’t lose anything, even if you uninstall the old version before installing 2007. Your system will tolerate only one version of Outlook, but that won’t be a nuisance to most users.

Here are a few Outlook 2007 tips and gotchas that will prepare you to help your users when they make the switch.

Note: This information is also available as a PDF download.

#1: The infamous Ribbon isn’t so scary

Outlook 2007’s main interface is the same, basically, as earlier versions (Figure A). In fact, the main window is so similar to 2003’s, users might be a bit confused. They’ll be expecting something wildly different, and when Outlook 2007 presents them with the familiar interface, they may think you made a mistake.

Figure A

interface

Users won’t see much difference when they launch Office 2007 for the first time.

Prepare users up front. Only Outlook 2007 item windows, e-mail, tasks, contacts, and calendars use Ribbons. (There’s a unique Ribbon for each item.)  The Ribbons are intuitive, and before long, most of your users will be comfortable with them. As you can see in Figure B, Outlook 2007’s Ribbon has little to challenge users.

Figure B

outlook ribbon

Users will be pleasantly surprised at how quickly they adjust to using Outlook 2007.

If users need help, they can use the online Microsoft Outlook 2007 Interactive Guide or download the Excel mapping workbook., which lists old and new commands.

#2: The To-Do Bar replaces the Task Pad

Previous versions of Outlook use the Task Pad to display daily tasks to the right of the calendar in Calendar view. Outlook 2007 replaces the Task Pad with the To-Do Bar (Figure C). By default, it’s turned off. To display it, choose To-Do Bar from the View menu and then choose Normal or Minimized.

Figure C

to-do bar

You can display the new To-Do Bar in normal or minimized mode.

A wide screen monitor is a plus if you want to appreciate the full effect. In E-mail view, Outlook 2007 displays the Navigation Pane, the current folder, the Reading Pane, and finally, the new To-Do Bar. You can collapse the To-Do Bar by clicking the double arrow at the top right (circled in Figure C). The list is specific to each view, which means you can open or close it in one view without changing its display in the other windows.

The To-Do Bar is more functional than the Task Pad and displays the Date Navigator (this use to be a separate view), appointments, the Task Input panel, and the task list. If appointments take up too much room, right-click the bar’s title bar and deselect Appointments, as shown in Figure D. You can also inhibit Tasks.

Figure D

turn off appointments

Show users how to customize the new To-Do Bar.

By default, the list displays a single column. Drag the list’s left column to widen the pane and display more information. There’s a lot more to the To-Do list, but a short introduction will get your users started.

#3: Old flagged mail requires a workaround

When you flag a mail item, Outlook 2007 adds it as a task to the To-Do Bar. That includes old mail items from a previous version when you upgrade. Outlook 2007 will display all older flagged mail in the To-Do Bar, even archived mail. If your users rely on flags, they’ll need to clean up as follows:

  1. Click the segmented title bar at the top of the task list in the To-Do Bar.
  2. Choose Type from the context menu.
  3. Repeat step 1 and choose Show In Groups to group the mail by type.
  4. If Posts On Top is visible in the title bar’s right edge, click it to display Messages On Top.
  5. With the flagged messages grouped, use the Shift method to select a contiguous block or hold down Ctrl and click the messages you want to clear.
  6. Right-click any flag in the selected block and choose Clear Flag/Delete Task.

That will clear flagged messages and tasks without deleting the original e-mail items. Don’t delete the items from the task list by pressing the Delete key, as that will delete the original e-mail items. Don’t clear any flagged messages you mean to keep as tasks. If you don’t want to clear the items, collapse the group (by clicking the minus sign to the left of the Message label in the title bar) to reclaim some space.

#4: A quick click deletes tasks

Clicking a flag in the To-Do Bar’s task list or in the Inbox marks it as complete and removes it from the task list. If the task item originates with an email, Outlook 2007 won’t delete the original e-mail message, just the related task. However, Outlook 2007 won’t give you a chance to confirm or cancel the action; the task just disappears. Clicking the flag next to a flagged task will also mark it as complete and remove it from the list.

#5: Flag delayed e-mail responses

You can’t always respond to a new message right away, but if you don’t, it might fall through the cracks by scrolling off screen. Now that flagged e-mail shows up in the To-Do Bar, and you can display the To-Do Bar in all views, a flag can help you keep up with those delayed responses. Even if the actual message scrolls out of sight, the flagged task shows up in the To-Do Bar. Just pick a flag (by color) to represent delayed responses to e-mail and start flagging those messages as they arrive.

If you move the message to another folder, it still appears in the To-Do Bar (as long as it’s flagged). In addition, Outlook displays a mail icon next to the item in the task list as a subtle visual reminder, as shown in Figure E.

Figure E

flagged e-mail

Flagged e-mail shows up in the To-Do Bar.

What users might not expect is for Outlook 2007 to roll over uncompleted flagged items to the next day. That might take a bit of adjustment.

#6: Take advantage of Daily Tasks view

Calendar view displays a new daily task list in both Day and Week view. A quick peek into Calendar view, shown in Figure F, keeps you updated with the day’s deadlines, which helps you make better scheduling decisions. Week view displays task below the appropriate day.

Figure F

tasks

Tasks are visible in Calendar’s Week view.

Because this list sorts current task, previous tasks, and completed tasks by oldest tasks first, it can lose its usefulness if you have a long list of tasks — and who doesn’t? Unfortunately, you can’t change the sort order.

To delay a task a few days, simply drag it from the task list to a new day. You can drag a task to the mini calendar, but doing so turns it into an appointment, so be careful. Outlook won’t assume you want the task to remain a task.

#7: Organize items with enhanced e-mail categories

In E-mail view, Outlook displays the Categories column by default, as shown in Figure G. Categories are similar to flags, in that they mark a message. With a quick glance, you can distinguish a message by purpose, project, and so on. You can categorize calendar events and tasks as well.

Figure G

categories

Give categories meaningful names to help organize your messages, events, and tasks.

Categories aren’t new to Outlook, but now you can label them to make them more meaningful.

You decide what each categories means. Simply right-click the Categories cell and select a category. Or assign a category to a selection of multiple messages by selecting a block of messages and then right-clicking Categories.

Categories come with default names that simply repeat their color. Outlook encourages customization by prompting you to rename a category the first time you apply it. To rename a category at any other time, right-click Categories and then choose All Categories; the resulting commands are self-explanatory. You’ll notice that the Master Category list from earlier versions is gone. Everything’s in one neat list.

You can assign more than one category to a message. Simply repeat the process of assigning a category. When you do, Outlook adds the new category; it doesn’t overwrite the existing one. To remove a category, simply deselect it.

If you use one category more than any other, assign it as the Quick Click selection by right-clicking Categories and selecting Set Quick Click. In the resulting dialog box, choose the category you want to use as the default. Once you do, a single click in Categories will assign the Quick Click category.

Because Outlook saves your categories in your default data file, your categories can be available on multiple computers if you have a Microsoft Exchange Server E-mail account.

#8: Outlook’s search engine is much improved

Searching in previous versions was painfully slow and frustrating. Outlook 2007 uses Windows Desktop Search technology, which indexes data. Its search feature is easy to use and snappy. As you enter characters, Outlook 2007 immediately starts to filter items. A search of several thousand messages takes less than a couple of seconds.

In addition, you can extend searches to all mail items by choosing Search All Mail Items, as shown in Figure H. For an advanced search, click the expanding arrows to display more criteria controls, as shown in Figure I.

Figure H

search

Search all mail items with a single option.

Figure I

advanced search

Add criteria to create a more advanced search.

Vista users are already using this new technology. If you’re using Windows XP, you can download Windows Search.

#9: View attachments with a quick click

Attachments are easy to view in Outlook 2007. In the Reading Pane, click the attachment icon, shown in Figure J. If Outlook 2007 warns you about viewing files, click Preview, assuming you trust the sender. Viewing the attached file within the context of its accompanying e-mail message is helpful and efficient. However, this view allows you only to scan the file’s content. You can’t edit the file. In addition, not every file is viewable.

Figure J

attachments

View an attached file with a quick click.

#10: Minimize the Navigation Pane

Now you can minimize the Navigation Pane to a small vertical button bar to free up a bit of workspace. Simply click the left-pointing double arrow to minimize the pane. Click the right-pointing double arrow to restore it. While minimized, click any of the minimized buttons, shown in Figure K, to access that view.

Figure K

views

Views are still easily assessable from the minimized Navigation Pane.

#11: Word is the only e-mail editor

Word is the only e-mail editor you can use with Outlook 2007. Specifically, Outlook 2007 uses Word’s HTML engine, which works well — most of the time. You might see a few HTML messages behave oddly, but for the most part, users won’t notice much difference. They just need to be aware that they no longer have a choice in the matter.

#12: View multiple calendars

To view multiple calendars, highlight Calendar in My Calendars. Outlook 2007 displays your calendars in tabbed windows, as shown in Figure L. (More than one calendar must be checked for this view to appear different from the other calendar views.) Using this view, you can overlap and color-code calendars to find competing appointments and tasks. To overlap (or remove the overlap), click the arrow in the tab. To add a calendar to this view, select it in cialis to buy My Calendars.

Figure L

multiple calendars

View multiple calendars in a single view.

#13: Share calendars

Exchange accounts have been able to share calendars for a while. Now, Outlook 2007 lets non-Exchange accounts share calendars using Microsoft Office Online (a free service) as follows:

  1. In Calendar view, right-click the calendar you want to share (publish).
  2. From the resulting shortcut menu, choose Publish To Internet.
  3. Click Publish To Office Online. The first time you publish a calendar, Outlook 2007 will ask you to register using your Windows Live ID account. If you don’t have an account, you can create one by following the provided instructions.
  4. Select the number of days you want to share the calendar.
  5. Click the Detail arrow to determine the amount of detail to share.
  6. Choose whether to limit those who can view your calendar (Permissions).
  7. By default, Outlook 2007 will update the calendar during the next manual or automatic Send/Receive. You can also click Advanced and then click Single Upload. Updates will not be uploaded if you want to upload the calendar without subsequent updates.
  8. Click OK.

You can also e-mail a calendar, as follows:

  1. Right-click the calendar you want to mail and choose Send Via E-mail.
  2. From the Calendar list, choose a calendar. By default, Outlook 2007 sends your default calendar.
  3. From the Date Range list, choose the time period you want to include. Or click Specify Dates and enter a custom date range. (The more you include, the larger the e-mail message will be.)
  4. Choose the details you want to share from the Detail list. Outlook 2007 won’t share private items unless you change this setting in the Advanced options.
  5. Click OK.
  6. In the To control, enter the name of the person to whom you’re sending the calendar.
  7. Click Send and then click OK to confirm the action.
Permalink • Print • Comment

Easy lookup formulas in Excel

  • Date: October 4th, 2008
  • Author: Susan Harkins

Creating the right formula often seems like rocket science, but Excel offers many tools to help. One of those tools is the Lookup Wizard. You’ll need a lookup formula to return a value from a table (the intersection of two values). Lookups cialis tadalafil side effects can be difficult to write even if you know what you’re doing. Don’t struggle with the problem; launch Excel’s Lookup Wizard.

If you don’t find this helpful wizard on the Tools menu, install it as follows:

  1. Choose Add-Ins from the Tools menu.
  2. In the resulting dialog box, check Lookup Wizard and click OK.

october2008officeblog1figar.jpg

In the worksheet, label two cells for entering two parameters, which represent the column and row headings. (You don’t have to do this, but the labels make the process easier.) Then, you’re ready to launch the wizard and create a lookup formula as follows:

  1. From the Tools menu, choose Lookup.
  2. Identify the table that contains the lookup values and then click Next.

october2008officeblog1figbr.jpg

  1. The wizard does a good job of identifying the appropriate column and row headings (the parameters). Most likely, you won’t have to do a thing other than accept the wizard’s choices and click Next.

october2008officeblog1figcr.jpg

  1. In the next window, decide whether you want to display parameters and the formula’s results or just the results. If this is a one-time calculation, you don’t need the parameters. If you want a more dynamic solution, include the parameters as shown. Then, click Next to continue.

october2008officeblog1figdr.jpg

  1. Specify a cell for the first parameter and click Next.

october2008officeblog1figer.jpg

  1. Specify a cell for the second parameter and click Next.

october2008officeblog1figfr.jpg

  1. In the final window, select a cell for the formula and click Finish.

october2008officeblog1figgr.jpg

Everything’s in place now. Simply update the parameters to find the value you need.

october2008officeblogfighr.jpg

Pay close attention to the formula in the Formula Bar. How long would you spend writing and testing such a complex formula? I don’t even bother. Excel is more efficient than I’ll ever be!

Permalink • Print • Comment
« Previous PageNext Page »
Made with WordPress and a healthy dose of Semiologic • Sky Gold skin by Denis de Bernardy