Microsoft Access General Tips

When should you use the Wizards

What are Wizards and are they all installed?

Default Values can save you time

How to display an '&' in a label

Open a combo box without the mouse

Can I set a default values for calculated control?

How to refer to a column in a multiple-column list box or combo box

Using Combos to select form records

Nine Shortcut Keys that save you time

Adding Custom Shortcut Keys

Exclamation point or period - which do I use and when?

How to copy a previous records values into a new record

"Field 'field name' can't contain a null value". What does this mean?

Recordset - what are they?

Calculating the difference between dates

When should you use the Wizards
MS Access has a variety of wizards to make things easy for you. However, we recommend that you ignore some of them as it will make life easier for you, we promise!

I will break this down into wizards for creating tables, queries, forms and reports.

There is one caveat to this. If you are designing a database with prebuilt form templates then we recommend creating a copy of the form template and then going straight to design view. We have a topic that covers this area.

What are Wizards and are they all installed?
The Access Wizards can save a great deal of time when producing standard items. Wizards also make the process of developing more complex items such as crosstab queries and combo boxes easier.

A Wizard works by taking you through the process of creating various items step-by-step.

To make sure that you have all your Access Wizards installed:

Run your MS Office setup program Setup.exe by double-clicking on it (making sure all other programs are closed).

Click the Add/Remove button, then highlight the 'Microsoft Access' option, and then click the 'Change Option'.You will see two Wizard options displayed, 'Wizards' and 'Advanced Wizards'. Make sure that both corresponding check boxes are ticked and then click 'Ok'.

If the Wizards hadn't been previously installed (either or both boxes were initially unticked) then the installation process will start. When the installation is complete you should have access to all the wizards. Use them wisely and they will pay you back in time savings and ease of use.

Default Values can save you time
Default values can save a great deal of time. When you build a table you can specify a default value for each field in the database. Often fields will only contain a limited range of information, or perhaps one particular value will be far more common than any other. In such cases, setting such a value as the default could save a great deal of input time.

For example, in an order processing database that has a field that records the 'delivery type' for each order, the vast majority of the orders are despatched via "Standard" post, rather than by "Express" or "Next Day". Setting the default as "Standard" for the 'delivery type' field would save you from having to manually type it in each time. Another example would be setting the default as =Date() in a date field for RecordCreationDate. This would put todays date in the that field.

You can set up a default value in Table Design view. Click on the relevant fields and then enter your default value in the "Default Value" property at the bottom of the design view window. Alternately, you can set your default value in form design view. Bring up the properties of the field (right mouse click on field, click properties) and then enter your value under the default value property.

How to display an '&' in a label
To make your label show: Cat & Dog, simply type in: Cat && Dog
Trust me - this works!

Open a combo box without the mouse
Move to the combo box and press ALT+DOWN ARROW

Can I set a default values for calculated control?
Access does not allow you to do this as it defeats the object of a calculated control.

How to refer to a column in a multiple-column list box or combo box
You need to create an expression that refers to the Column property of the list box or combo box control.

The syntax follows the form:

[Forms]![Form Name]![Combo Box Control Name].[Column](n)

Amend the Form Name and Combo Box Control Name and enter the column number n, where n will be one less than the actual column number because Access counts from zero for column one and so on.

Using Combos to select form records
There are several ways to get to the record you want on a form. You can flick through the records using the navigation buttons; you can use a filtering technique; or you can use the find facility.

In many instances all of these can be quite slow. Using a combo can be faster.

Let's say we have a form that contains client records, and we want to get to a specific client record quickly.

To set up a combo box to do this, go to the forms design and add a combo using the combo button found on the toolbox toolbar. The combo wizard will start up (as long as you have it installed).

You will have 3 options to choose from. Pick the 'Find a record on my form based on the value I selected in my combo box' option. You will then need to specify what information to have in the combo. If we are using a combo to select client records, we will need to include the unique ClientID field along with a descriptive field - such as client or company name - that will be displayed in the combo and helps us identify the records.

Follow the prompts, choosing the field that uniquely identifies the record (probably the ID).

If you choose an ID field you will probably want to hide it so that just the client/company name is displayed.

When asked what field to store the selected value in, choose the 'Remember value for later use option', as we don't want to store the selected value.

When your combo box is complete you can use it to go straight to the record you want, either by using the drop down and selecting, or alternatively, but typing directly into the combo. As you type the combo will move to the record that matches you input. When it reaches the one you want hit the enter key and the form will move to that record.

Nine Shortcut Keys that save you time
Moving through and entering data can be speeded up significantly by using shortcut keys.

Here’s a list of some of the more useful shortcuts:

To undo typing CTRL+Z or ALT+BACKSPACE

To Undo changes Hit ESC once for current field, ESC twice for current record

To insert the current date CTRL+SEMICOLON (;)

To insert the current time CTRL+COLON (:)

To insert the default value for a field CTRL+ALT+SPACEBAR

To insert the value from the same CTRL+APOSTROPHE (') field in the previous record

To add a new record CTRL+PLUS SIGN (+)

To delete the current record CTRL+MINUS SIGN (-)

To recalculate the fields in the window F9

Adding Custom Shortcut Keys
Custom shortcut keys can make navigating through your database far easier. If you want to be able to quickly switch to a particular field on a form you can do so with a shortcut key.

To assign a shortcut key to a field, edit the caption property of the label of the particular control you want to jump to, adding an '&' before the letter you want to act as the shortcut key.

For example, if you wish to be able to jump to a 'Name' field you could edit the 'Name' label accordingly:

N&ame

In Form View the label will be displayed with the 'a' in name underlined:

Name

Pressing ALT-a will switch the focus to the 'Name' field. This technique can be used on any object that has a caption property.

Exclamation point or period - which do I use and when?

There is a basic rule to follow here; if you name something yourself then use the exclamation point, if Access has named something then use the period. For example, form names and control names need an exclamation point. Properties need a period.

If you use the Expression Builder, the punctuation is filled in for you when you select a control or property, together with the names of the objects and controls.

How to copy a previous records values into a new record
If you find that you often enter the same value in one field of your table you can use two methods to save re-typing the data.
1. Using CTRL+' (apostrophe) will repeat the value input in the previous record.
2. Amend the field's DefaultValue property in Design View to the most commonly used value.

"Field 'field name' can't contain a null value". What does this mean?
When you set the Required property of a field to Yes, you must have an entry for that field or Access will not let you save the record. Either input a record into that field or set the Required property to No.

Recordset - what are they?
Access has three main types of Recordsets which are sets of records that behave like objects. Recordsets represent data either from a table or from a query.

A Table-type Recordset is a representation of a base table that you can use to add, change, or delete records from a single database table.

A Dynaset-type Recordset is the result of a query that can have updatable records.

A Snapshot-type Recordset is a static copy of a set of records that you can use to find data or generate reports.

Calculating the difference between dates
If you have two date fields you may wish to calculate the time between them. This can be done in a number of ways, either as a calculated field in a query, as a calculated control in a for or report, or as a calculation in a VBA procedure.

The basic syntax to get the number of days between two dates is:

=[One date field] - [Another date field]

You can also use the following functions:

=Month([One date field] - [Another date field])

which will calculate the number of months between the two fields, or:

=Year([One date field] - [Another date field])

to return the number of years between the two fields.

There is another function, the DateDiff() function. This uses an argument to determine the how the time interval is measured. For instance,

=DateDiff("q",[One date field] - [Another date field])

will return the number of quarters between the two fields. The other intervals that can be used in this expression are as follows: "yyyy" - Years, "q" - Quarters, "m" - Months, "y" - Days, "d" - Days, "w" - Weekdays, "ww" - Weeks, "h" - Hours, "n" - Minutes, "s" - Seconds.

Click the link if you need to Repair Access database. Fast service, no obligation, free quote!