Five Quick Tips for Working in Microsoft Access

Need some help with your Microsoft Access databases? Here are 5 quick tips to help you achieve great results!

1. How to create an autonumber field that begins with a number greater than 1

In the Microsoft Access tables the AutoNumber field type allows you to assign a unique sequential number for each row in the table. The AutoNumber fields make the Primary Key and they are unique and permanent.

In new tables Microsoft Access AutoNumbering begins with 1 and the increment is 1 with each new row. However, you might need to start the AutoNumber at a number higher than 1.

Just create an Append query to insert the value of the ID one less than the desired starting number (or start value). Let’s consider that you want a start value of 1000.

Run this query

INSERT INTO tblName ( ID )
SELECT 999 AS Expr1

Where tb1Name is the table name and ID is the name of the AutoNumber field

Run a second query to delete the row that your inserted

DELETE
FROM tblName
WHERE ID=999;

Note: There’s an alternative option; open the table and manually delete the row that you have inserted

Now add the next record value to the table and assign it the value 1000

This method works with a new table, or with a table whose AutoNumber has not yet reached the start value. The table ID field for existing rows will not be changed, but the subsequent rows will with 1000.

2. How to change the default behaviour of controls

While designing a report or form and placing a Text Box, you have a Label control attached to the Text Box. At times you might not find what you require so you have to delete the label. There may be many Text Boxes that do not need the labels. It might require a lot of clicking and deleting.

In order to avoid this you must change the default behavior of the control. Just follow the steps

  • Step 1: Open the Form or Report Designer.
  • Step 2: Select the control on the Ribbon Control tab. In the Tools tab open a Property Sheet.
  • Step 3: Change the Auto Label property from Yes to No and close the Property Sheet.

You are done, now you can place a Text Box on the form or report, no label will be attached.

Note: Do not forget to record default settings before making any changes, in case you might need to roll back to the default behavior.

4. How to create a switchboard form in Microsoft Access

Creating a Switchboard form in Microsoft Access is simple. You can create the Switchboard form even without using the Switchboard Manager. You can create the menu structure with the help of Navigational Control as well.

In Microsoft Access you can launch the Switchboard Manager in different ways

Add command to your Quick Access Toolbar

  • Step 1: Click the arrow on the QAT and click More Commands to open the Customize area
    Step 2: Select Commands Not
    Step 3: On the Ribbons and find the Switchboard Manager option. Add the command to your QAT and then launch it

Add the command to your Ribbon

  • Step 1: Right-click anywhere on the Ribbon and click Customize the Ribbon to open the Customize area
    Step 2: Select Commands Not On Ribbons and find the Switchboard Manager option.
    Step 3: Add that command to one of the existing Ribbon groups or create your own Ribbon group.
    Step 4: Now launch it from there

Note: In case you want it in the immediate windows

Press CTRL+G to open the Immediate Window. Now type the following command and then press Enter:
DoCmd.RunCommand acCmdSwitchboardManager

5. Deleting records using the delete query

Delete queries are a fundamental part of a Microsoft Access relational database with which you can remove multiple records at a time. Users can specify the the records to delete by linking to data in another table. With delete queries you can

  • Empty a table (delete all its records)
  • Delete all records with a particular value in a field
  • Delete all records with a range of values in a field
  • Delete records based on criteria in multiple fields
  • Delete records with matching values field(s) in a joined table

Find out more on our Microsoft Access Training Courses:

Microsoft Access Introduction    Glasgow  |  Edinburgh
Microsoft Access Intermediate    Glasgow  |  Edinburgh
Microsoft Access Advanced    Glasgow  |  Edinburgh

Leave a Reply

Your email address will not be published. Required fields are marked *