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
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:
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|