Office Tips and Methods

- Canadian IT Professionals, Check out the pre-release of System Center 2012 and get a coffee gift card.

How would you like a DoubleDouble ? – Canadian IT Professionals – Site Home – TechNet Blogs

This article demonstrates how to create a co-ordinated set of Access forms that could be used, among other things, as a Dashboard or Launching Pad in an Access User Interface. Form content depends on the needs of the application. The forms in the demonstration application are just simple examples to illustrate what can be done. They are there just to show what the code behind the scenes does.

The concept is similar to Frames in HTML Documents.

The Forms

There are six forms in the demonstration application:

	frmShell can be thought of as an outer container or framework. It has one subform control and command buttons and a toggle button that swap actual forms in and out of the subform control. This the opening form for the application. When the application starts, this nested form displays a simple welcome message which is contained in frmShell_ContentWelcome.
frmShell

The other forms take turns populating either frmShell’s subform control second level subforms. The form name indicates each form’s relative position in the overall hierarchy.

	frmShell_ContentA is at the second level of the form hierarchy. If you consider frmShell to be at the top level of the form hierarchy (the patriarch) then frmShellContentA represents a child of the patriarch. frmContentA itself has two children (siblings if you don’t mind the metaphor.)
frmShell_ContentA populated with frmShellContentA

	The content forms can be loaded as individual standalone forms.
frmContent and its two child forms

frmShell_ContentTabs	frmShell_ContentTabs with Mouse pointing at the Child Tab
frmShell populated with frmShell_ContentTabs (populated with frmShell_ContentA)

frmShell_ContentA_1 and frmShell_ContentA_2 populate two subform controls on frmChild when that form is the main child form of frmShell

frmShell_ContentTabs is an alternate child form to frmShell. This form uses a tabbed style of interface to swap its subform content in and out.

frmShell_ContentWelcome populates frmShell with startup information.

Behind the Scenes – the Code

The main code that makes it all work is quite simple as long as you get the notation right. Content screens are swapped by assigning a form to the SourceObject property of the desired subform control.

For example, if a form has a subform control named fsbMainWindow, then a content form (for example, frmContent1) can be assigned to the control with this statement:

me!fsbMainWindow.SourceObject = “frmContent1”

Once the content form has been assigned to the subform control you can then refer to any controls or public properties in one of two ways:

me!fsbMainWindow.Form.[name of property or control]

or by creating a form object and assigning the subform object to the form object. Of course you have to declare a from object.

Dim frm as Form

then you can use the object by assigning the subform control to it.

Set frm = Me!fsbmMainWindow.Form

You can then use the object to refer to the content form and its controls and properties.

frm.txtDate = Now()

assigned the current date and time to a textbox named txtDate on the content form.

If there are several ‘windows’ on the form, then the object approach makes it easier to keep track of which window content you are referring to.

A subform can talk back to the form that contains it using its own parent property.

me.parent.CurrentChild = me.name

is a trivial example. Of course you would have to declare a public variable or property, CurrentChild in the parent form.

It is possible to nest up to seven levels (Access XP and later, three in Access 2000) of subforms on a form. The notation can be confusing because you have to have the bang operator (!) in all the right places. For example,

Me!fsbMainWindow.Form

refers to the subform one level down. If you want to refer to a subform nested on the form one level down, the notation is

Me!fsbMainWindow.Form![name of subform control].Form

For a general reference on referring to sub forms and sub reports see this page on The Access Web.

If you need to set the focus to a control on a sub form, first set focus to the sub form control and then set focus to the control you want to have focus.

General Development Guidelines

If you will be swapping several forms in and out of the same subform control, it helps to first create a simple empty form that is set up with the appearance that you would like all of the forms that you will use in that particular subform. Use that form as a template for each of the others.

A naming convention also helps keep the various forms straight in you mind. For example begin the name of each related form with frm followed by a generic name for the group of forms. For each sub form, append an underscore followed by a meaningful name for the particular sub form. Using this approach, all of the related forms are listed together in the database window or navigation pane depending on your version of Access.

Summary

Using a shell form to ‘house’ the main forms of a user interface makes it easier to create and maintain a consistent ‘look’ and feel for the application.

Demonstration File

The demonsration file can be downloaded from:

I’d like to know for an article I am writing, just how difficult you think it is to learn one of more of the office applications:

• Word
• Excel
• PowerPoint
• Access
• Publisher
• Visio

I have posted a small survey here if you would like to participate.

Glenn

Mouse and keyboard essentials can become second nature quite quickly, once you learn them. However, if you are new to Windows computing or have simply learned by doing, you may be missing some of the basics.

This article will focus on basic keyboard skills which all seem simple once you know how to use them but which some people find very difficult to use in the first place. The article includes some examples of shortcuts that are possible with the mouse or keyboard actions but our main interest is how to use the mouse and keyboard keys alone or in various combinations. Many times when you ask for help the article or forum post suggests that you use mouse or keyboard keys is certain ways. This article is about what these suggestions mean.

How familiar are you with your mouse? Here are some basic points to be aware of:

• Mouse

  • Right or Left

    A basic windows mouse has two ‘keys,’ left and right. When you rest your right hand, palm down on the mouse, your index finger rests naturally on the left key and you second finger rests on the right. The left key is used to select or choose the item you have used the mouse to point to on the screen. The right mouse key is typically used to open a shortcut menu of choices that are appropriate to whatever it is you are doing at the moment.

  • Click or Drag

    A Click is a single quick down and up motion of the specified mouse key. A Drag on the other hand, means that you should press and hold down the specified mouse key while you move the mouse in the desired direction.

  • Scroll Wheel

    Your mouse may have a wheel between the left and right keys. You can spin this wheel in either direction. The action spinning the wheel causes depends, in part on where you are pointing when you spin it. For example, in Word 2007, or 2010 (or any of the Office applications with the ribbon interface), spinning the wheel which change the active ribbon tab.

  • Single Click

    Different actions require different kinds of clicking. A Single Click means quickly pressing and releasing the mouse key once only.

  • Double Click

    A Double click, on the other hand, means rapidly pressing the mouse key twice. The setting for just how quickly it is necessary to repeat the click for Windows to interpret your mouse action as a double click is adjustable (see below.)

  • Control Panel Adjustments

  

You can use the Mouse options in the Windows Control panel to adjust double click speed and to switch the Left and Right mouse key if you prefer to use the mouse in your left hand

• Adjust double click speed

  If you have difficulty double-clicking you can use the mouse properties to adjust the time between the first and second click.

• Switch Left and Right

  If you are left-handed you can use the mouse properties so that the mouse keys are reversed.

A computer application is nothing more or less than a tool, a device used to do a job. Personal computer applications rely on the skills of the person using the application to get the job done. Some of these skills are application-specific, some relate to a general understanding of computer use, and some relate to the user’s general knowledge level and understanding of how to solve the problem at hand.

Some applications, like some tools, are easy to use and require only basic personal skills. Other applications, like some other tools, are more difficult to use and require higher personal skill level.

Take such a simple task as driving a nail. That is probably something you learned to do as a child. Most likely, though, when you first started trying to use a hammer, you held it, perhaps in two hands and close to the head. You had to learn that the tool works best when you hold it in one hand, close to the end of the handle, so that the hammer became like an extension of your arm. Most likely that skill is now so basic to you that you don’t even recall learning it.

The point is, whether you realize it or not, every tool, virtual or real, requires at least some skills in order to be useful in helping you to do the job at hand. When it comes to looking at the Microsoft Office Suite in this light, there are some common skillsets that apply to the whole suite. Additional skills specific to each application build on this foundation.

Here, in no particular order, are essentials for the office suite that people suggested when I asked or gleaned from my own experience.

• Know the correct way of starting and shutting down your computer.
• Know the difference between shortcuts and application icons.

I once had a client who deleted an application to make space, because “there was a much smaller version on the desktop”!

• Know what version of Windows you are using.
• Know what version of Office you have available.

Newer versions of Office and Windows have more features and do things differently than earlier versions. You can help others help you (especially in on-line forums) if you can indicate your version of Office.

• Understand the work and task at hand that you think you can use an Office application for.

Just as it is difficult to pound a nail with a backhoe, each Office application is better suited to some purposes than others.

• Mousing basics
  • the difference between click and drag
  • mouse pointer shapes
  • how to manage Windows
• Keyboard shortcuts.

There are literally pages and pages of keyboard shortcuts. Learn the shortcuts that are most useful to you, even if you prefer to use you mouse. There are time that you can’t use the mouse but you can use a keyboard shortcut to do what you need. Keep in mind also that some keyboard shortcuts depend on the language and keyboard version you are using. A good grasp of basic keyboard shortcuts will help you with all of the Office applications. Some keyboard shortcuts are useful in or apply to just some of the applications.

• How to organize and manage your files.

Develop a standard naming and filing system for your work. You will spend far less time looking for that file that you created and saved a couple of days ago. Don’t mimic the M*A*S*H episode where Klinger filed all correspondence under C. That might work if you have only a few files but with the huge capacity of today’s storage media, it’s easy to lose track of your work and have to waste valuable minutes tracking it down.

• Know how to backup your work and preserve working versions (drafts) of work in progress.

Sure your IT department backs up your files regularly (they do, don’t they?) but if you have your own system of backing up individual files that are important to you, you will be able to recover from a disaster much more quickly than if you don’t.

• Know how to proof-read your work. Don’t pass anything along to anyone else until you have proof-read it two or three times.

Remember that it is easy to overlook typos and spelling errors if you proofread immediately after you type. Take a break from the task and go back to proofread your work several hours later or even the next day. Develop a work buddy system for proofreading someone else’s work in exchange for their proofreading yours.

• Know how to use Office’s Review tools (Spell Checker and Grammar Checker) and be aware of their limitations. Be sure to add local spellings to the dictionary if appropriate.

Automated grammar checking is still far from perfect. In English, for example, the word “place” can be used as either a noun (‘Let’s go to John’s place.’) or as a verb (‘Place the parcel on the table.’) When you do use it as a verb, the grammar checker reports that you have an incomplete sentence.

• Understand templates and how to use them to save yourself work.
• Be willing to reinforce new skills by using them frequently until they become second nature.

No doubt there are other Office essentials that could be added to this list. However, if you are just starting to learn about using Office applications, keeping these points in mind (and raising them with your trainer/instructor) will help you focus learning experience.

The basic notion for this article came from my classroom experience with people who had come to an Introduction to Excel class. Many of these people were unable to answer the question, “What type of work will you be doing in Excel?” In coming articles, I will discuss application specific essentials. I also frequently ran into (again with Excel newcomers) what seemed to be an almost mystical belief that Excel users could use the application to handle all of their math problems; these people (and perhaps their bosses) seemed to think they did not need any math skills. All they wanted to do, they said, was to learn formulas. More about that in the Essentials – Excel article.

 

Acknowledgements:

Recently I asked a number of applications experts and users what they thought were essential skills for using Microsoft Office applications. Thanks to the UtterAccess members, and members of the Microsoft Excel Users, Microsoft MVP Professional Group, and MVP@Work groups at Linked-In who responded.

Congratulations 2011 Microsoft MVP!

When I opened my inbox this morning, one message jumped out at me. Microsoft has honoured me with the Most Valuable Professional award for 2011. I am just as thrilled today as I was when I received my first MVP in 2007. Thank you Microsoft!

See a sneak preview of the next version of Windows. Microsoft have published the first of a series of videos demonstrating what is coming in the next version of Windows. Interesting!

Delete queries delete all records from the specified table that meet criteria specified in the query.

 

 

Scenario: The table of country names that you have inherited contains some invalid names. You have already updated the records that used to point to these names so they now point to the correct form of the relevant country name. Now you want to remove the invalid names from the table.

 

With just a few entries, you could just open the table and delete the bad records. However, if you have many records to delete, a delete query can take care of all of them at once.

IMPORTANT: Before running a query that will modify your data, it is very important to first back up the data. You can do this either by copying the table’s structure and data or by backing up the entire database.

Setting up the query is quite straight forward. Use the table you want to clean up as the query’s record source and include the field that will determine whether or not a record should be deleted.

 

This query will delete records that contain “United States” in the Country field. All other records will be untouched.

If you include additional criteria on the rows below the first criteria, then the  query will delete records that match any one of the criteria.

 

 

 

 

 

Just to be safe, however you should not run a delete query before you view it. Notice the distinction between viewing and running the query.

 

Viewing a query will display the selected records in the query’s datasheet view.

Running the query, on the other hand, actually deletes the selected records.

 

Note also that double-clicking a query icon in the database window or Navigation Pane, runs the query. If you are not absolutely sure you should be running a query, open it in design view and view it in datasheet view.

Sample files for Action Queries (Access 2000 and 2007/2010 format) can be downloaded from:

 

Update queries are the means to modify record data in one or more fields in all the records that match the criteria you specify. For this article, I have modified the sample data so that it reflects an all too possible scenario.

Scenario: You have inherited a customer database where customer data was stored in a single table. You have normalized the table. When you created and populated the Countries table, you didn’t recognize that the country data inserted into the original table was inconsistent. Some country names were mis-spelled (Mxecio, United Staes, for example) and several forms of the name were used for others (US, USA, and United States, for example). The problem came to light with the Regional Manager for Mexico reported that one of more of his customers appeared to be missing when he search for customers in Mexico.

Solution: When you looked at the Countries table you recognized that the data had spelling variations for some countries. You have produced a new table with the correct spelling for each country’s name and there are no variations in the form of the name.

Now you want to clean up the data so that your normalized customers table will refer only to the new Countries table. You can’t simply delete the bad spellings because some of the records in your new customers table probably refer to these misspelled country name records.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The solution requires first identifying which customer records refer to bad country name and then updating those records so that they refer to the correct spelling. The query results at the right is a comparative list showing the current list of country names together with a list of only valid names. In design view the query looks like this:

 

Notice that the tables are joined by the country names, not by keys. An empty cell in the valid country column indicates that the name in the cell in the left column is invalid. This query gives us the information we need to know which records to update and what CountryID should be used for the update.

In the Customers table, these changes need to be made to the CountryID field

• Mxeico 13 update to 12
• United Staes 21 update to 24
• United States 22 update to 24
• US 23 update to 24

Because each of these updates has different criteria, four update queries will be required. (As you will see, you use a single query and simply modify the criteria after each update.) After we execute these updates, the customer table will correctly refer to Mexico and USA, respectively in the Countries table. Then the invalid names can be deleted from the Countries table.

A simple unmatched records query will help identify just the customer records that need attention. It is similar to the last query but this is what the results will look like:

The Is Null Criteria limits the output to just the unmatched records:

Now we are ready to create the actual update queries. This query will need the valid country id with which we want to update the affected Customer records:

This query includes the Company name field simply for “comfort.” The table requiring update is joined in this case to its related Countries table so that we can restrict the record by the actual. Before running an action query it is usually a good idea to view the record selection you have specified. However, the datasheet view of an update query only shows you the field to be updated with its current value. To see more about the records that will be updated, you can temporarily convert the query to a select query and the view its datasheet.

Again you are only seeing the current data but you can verify that you have in fact set the criteria correctly. Now all that remains for the update is to convert the query back to its update form and then run it.

After you run the query for the first incorrect country name, you can change the query to the next misspelling, verify and run it. You simply repeat this process until you have updated the Country Id for all of the misspelled country names, one at a time.

It’s a good idea at this point to create a query to verify that the invalid country names are no longer referenced from the Customers table.

Remember: never run any data manipulation queries before backup up the affected tables and or the entire database.

Final cleanup involves deleting the records with the bad spelling. The next article in this series will discuss Delete Queries

Creating the new customer table is just a little different from the three make table queries in part one.

The query includes the original customers table and the three new tables. Note that the customers table is joined to each of the other tables by the field that contains the data pasted into the new tables by the respective make table queries. Some of the customer records may not have data in one or more of the Address, State/Prov and Country fields. The join types have each been modified so that all customer records will be included.

If we did not change the join type from the default equal join, then the only records the query would produce would be those that had data in the Address, State/Prov and Country fields in the original table. With the join type changed, the query will produce nulls whenever there is no corresponding value to the customer’s address, state or province, or country.

We can use the query grid to modify the field names and data that will be inserted into the new table.

In the query grid, each field cell begins with the name we would like to use for the field in the new table. Since not all customers are not companies and the table is the Customers table, CustomerName is probably a better field name for this data. So the expression

CustomerName:Company

is in the field cell of the first column. So the data that is in the company field in tblCustomers will be pasted into the CustomerName field in the new table.

For the Address, State/Prov, and Country fields, we want the new table’s data to be the Primary Key field of the respective tables that now contain list of unique values for those fields. We can’t use and current names in the source tables for these fields in the query and for each of them a calculation is required to deal with possible null values. So we will use the names Address, State, and Country. After the table is created we will manually edit it so that these field names indicate that they are key values.

The three calculations are

Address:IIF(IsNull([AddressID]),Null,clng([AddressID]))

State:IIF(IsNull([State_ProvID]),Null,clng([State_ProvID]))

Country:IIF(IsNull([CountryID]),Null,clng([CountryID]))

Each of these calculations serves the same purposes

• Name the corresponding field in the new table appropriately
• If there is a null value in the query results populate the corresponding field in the new record with null.
• Convert non-null values to the long datatype  because the three source fields are autonumbers

Set the Make Table up using the name tblCustomers_. Once the new table is created, we will change the name of tblCustomers to ztblCustomers and the name of the new table to tblCustomers so that we can retain the original table as a backup and continue to use the name tblCustomers for the ‘real’ data.

Executing (running) the query produces a new table in the database:

All that remains to be done is to modify the table’s design so that it has a primary key and appropriate field name and then create the relationships.

The modified and renamed new table looks like this:

Creating the relationships finishes the process:

Using a series of Make Table queries in this tutorial, we have taken the data from an unnormalized table are produced a normalized table of customers with three related tables.