Microsoft Access Database 2013: Building A Navigation Control Form

Of course, one assumes you have also pre-prepared and built either the forms or reports that will be linked and associated to each tab added which will require using the wizard or templates available.

I always suggest a plan before building these forms and reports and have suggested many times from my previous blog posts in the past of the approach to the above. In order to keep your Microsoft access tutorial forms optimised and perform well, some useful tips may help when build a form.

Microsoft Access Database

I like the way this video uses both the property sheet and ribbon bar to easily set and apply various properties which also uses the right-mouse click action to quickly gain access between views (via the tab).

The music may not be to your taste but I find having music on in the background really helps me when I design Microsoft access tutorial forms. It can even be some heavy rock!

34 Edgwarebury Lane,Edgware,Middlesex,HA88LW,UK

Advertisements

MICROSOFT ACCESS DATABASE FUNCTIONS

This is something I have touched on briefly in the past with an article called Microsoft access tutorial Functions which introduced you to the popular functions and the tool to generally browse for other not so popular function calls.

So as a revisit to this topic, there’s no point describing each and everyone that’s at your disposal – that would be silly as we all have different uses for our Access databases but instead you can find a full list (by category) by going to Microsoft’s Office Access Functions (by category)page instead.

Microsoft Access

Microsoft Access Database Functions

It clearly states that this page contains links to articles that provide details about common functions used in expressions in Microsoft Access.

They are arranged first by group and then are arranged alphabetically at the end of this article.

TIP   Beginning with Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires.

Each function shown has a clickable link drilling into more detail with some examples too and will prove a useful resource over and above the standard Microsoft Access help.

Microsoft access tutorial can be used directly in a query, form, report and macros but can have a slightly different syntax when using the VBA programming code as a function by definition returns a value and there sits to the right side of another object or variable.

34 Edgwarebury Lane,Edgware,Middlesex,HA88LW,UK

MICROSOFT ACCESS TUTORIAL – HOW TO USE INPUT MASKS

Microsoft Access databases provide a wealth of tools and utilities to help control design layouts and data integrity. Working with the latter, you can reduce and even dismiss any programming at all to control data entry to a field in a table or via a form using the Access Input Mask feature.

Take a quick look at this useful microsoft access tutorial (using version 2010) on how to set up an input mask.

 

https://www.youtube.com/watch?v=7XstSSyG8fw

 

Very easy to create indeed and the video covers the pre-set options which to be honest is based on the US style conventions for storing telephone numbers and zip codes!

Microsoft Access Tutorial: How to use Input Masks

From one of my earlier posts, I’ve already mentioned about the key to knowing the input mask feature is to understand the code that sits behind this tool.

This is particularly more important to know and master if you are going to not just build custom code solutions but clearly for the non US based systems, makes it more relevant.

So you can now avoid and forget zip codes and build your own post code or scrap social security numbers for national insurance numbers.

The easiest way to start is to use the input mask wizard tool for the selected field in the properties section of the design view for a table (or form) and then you can modify the code it generates or even build expressions via the wizard tool.

Remember to save changes should you need to add more than one field’s properties (other way, you will be prompted to in any case) and that we are only talking about text and date/time data type fields as numeric values do not apply.

As I’ve already said, the default input masks options does not always meet your needs and you may need to edit them. In the wizard, simply click the Edit List button provided on the first screen of the Input Mask Wizard and start editing.

Here are the valid characters that you may include in an input mask:

0: User must enter a numeric digit (0 through 9)

9: User may enter a numeric digit (0 through 9)

#: User may enter a numeric digit, space, +, or –

L: User must enter a letter

?: User may enter a letter

A: User must enter a letter or numeric digit

a: User may enter a letter or numeric digit

&: User must enter a character or a space

C: User may enter characters or spaces

In the list above, note the use of the words; “must” and “may“. Some Microsoft Access database input mask characters are optional, allowing the user to enter data in a field but also allowing them to leave it blank.

The characters “.  ,” “:” “;” “” and “/” may be included as placeholders and separators where required. In addition to these, you may also include some special directives in your input masks which can include:

!: Input provided by the user is entered from left to right instead of the normal right to left format.

>: All characters after this symbol are converted to uppercase regardless of how the user inputs them.

<: All characters after this symbol are converted to lowercase regardless of how the user inputs them.

\: The character immediately following the slash will be displayed in the mask as written.

“”: Characters included in quotation marks will be displayed in the mask as written.

Need to learn more about Microsoft Access? Check out my eBook bundle offer.

MICROSOFT ACCESS VBA VS MACRO

A common question often asked: Would it be a good idea for me to use Microsoft access macros or would it be advisable to use Microsoft Access VBA code instead?

Microsoft Access VBA vs MACRO

You should base your choice to use either Microsoft access macros or MS access VBA code with two initial concerns:

  • Security
  • Usefulness that you require.

Macros are considered to be easier to learn than VBA and in most cases it’s probably faster  to create a macro than it is to write VBA code.

People tend to consider Macros because VBA code is perceived to be more programmatic, offering a variety of options that appear confusing and time consuming to understand. It’s generally a steeper learning curve!

To help guarantee the security of your database, you should attempt to utilise MS access macros as the first conceivable opportunity and only turn to VBA programming  just for operations that can’t be proficiently used by utilising a macro action call.

Besides, you should attempt to utilise MS access macro actions that doesn’t require or allow permission calls to the database so it will run. Implementing and utilising Microsoft access macros along these lines enables your clients to be sure that the database does not contain any programming code that could damage information or different documents on their PCs.

To encompass the more extensive utilisation of macros, Microsoft Access 2016 contains numerous new MS access macros that enable you to manufacture more effective macros than you can work by utilising better compared to the prior versions of MS Access VBA that can be used to perform any operation that a macro can perform. VBA also allows you to perform a multitude of more advanced operations to include the following:

  • Incorporate error-handling modules to assist in the running of your applications.
  • Integrate Word and Excel features in your database
  • Present users with professional forms-based layouts to interface with your database
  • Process data in the background
  • Create multi-purpose forms
  • Perform conditional looping

When you add programming automation to an object or control, you ought to consider a specific order of approach:

  • A MS access macro containing just activities that don’t require giving some level of trust to the database in order to run
  • MS access macro containing activities that do require a level of trust in status to the database in order to run
  • VBA methodologies -consideration

People new to Access are tempted to use macros to automate their Access Database Applications in the notion that learning and using Access Visual Basic for Applications (VBA) would be much more difficult and time-consuming.

Microsoft Access Tutorial: Creating A Many-To-Many Relationship

Microsoft access tutorial In a relational database, a many-to-many relationship exists when a record in one table can be referenced by one or more records in another table and vice versa.

You need to have two or more tables to create relationships. To link these tables, you will have to create another table (a third) which will be your interceding or junction table.

Here are the steps again in creating a third table:

1. Go to the Create tab.

2. Click on Table Design. A new table will be opened.

3. Create two fields (plus any additional supporting fields).

NOTE: The two key fields are going to be the primary keys from the two tables that you have created which are in effect known as secondary or foreign keys (duplicates OK).

  • To identify the primary key from a table:
  • Click on the existing table and select View on the upper left corner of MS Access.
  • You can identify the primary key when you click on one of the fields and see the Primary Key button, right next to the View button, being highlighted in orange.
  • Once you find the primary key, take note of three things: Field Name, Data Type, and Field Size (located at the bottom).
  • Type in the Field Name in the first field of your junction table. Indicate the Data Type and the Field Size.
  • Be sure to include the primary key from each of the tables.

4. Designate all fields as the table’s primary key.

  • Hover your mouse over the selection box next to the first field.
  • Click, hold and drag your mouse down to the last field to select all fields.
  • Click on Primary Key button on the upper left corner.

5. Save the new third table. Label it accordingly for easy identification.

Microsoft Access Tutorial

Next, Creating a many-to-many relationship:

1. Go to the Database Tools tab.

2. Click on the Relationships button. You will see the tables.

3. Hover over the primary key of the first table.

4. Left click the mouse, hold, drag and drop it over the top of its counterpart in the junction table.

5. Once you release your mouse, a dialogue box where you can edit relationships will pop up.

  • Click on Enforce Referential Integrity.
  • Click the Create button.

6. Repeat steps 3, 4 and 5 for the other tables.

 

An Access database many-to-many relationship is made of two one-to-many relationships. Look at the diagram below.

Microsoft Access Tutorial

To learn more about Microsoft Access database relationships and the concepts behind RDBMS (Relation database management systems), why not take a look at my eBook on Understanding Access Database Relationships (RDBMS) – Joining Tables which comes with a guaranteed 30 day money back policy and email support.

Microsoft access tutorial

34 Edgwarebury Lane,Edgware,Middlesex,HA88LW,UK

MICROSOFT ACCESS 2016 MACROS: SHOW ALL MACRO COMMANDS

The great debate of whether to use Microsoft Access VBA programming macros or the higher level of VBA code will linger on until the end of time (or realistically, Access is discontinued)!

In the meantime, most Access developers will continue to lean towards Access VBA but the majority of power users, self-taught Access designers will still embrace the ease of g using macros to automate their databases.

Microsoft Access 2016 Macros Show All Macro Commands

With the later versions of MS Access (post 2010), macros have become more powerful and flexible and with the added introduction of better web integration and the fact VBA is prohibited when publishing your database objects online, there’s now an even stronger case for macros becoming the first choice tool.

Microsoft Access 2016 Macros: Show All Macro Commands

However, not all macros are deemed safe and certain keywords have also been prohibited which by default are not visible when working in the macro design view mode.

In the design view mode for a new or existing macro (I’m using version 2016) which can be created either via the Navigation pane which makes these macros public or via a typical Access object like a form or report which will make these macros private (note the scope between the two ways macros can be created!), You will see under the ‘Design’ tab in the ‘Show/Hide‘ section, two icons with the first revealing the Action Catalog pane view and the second icon to reveal the non-safe keyword macro commands.

As I already mentioned, by default the second icon is switched off leaving only the safe macro keyword commands knowing full well any published objects will work online. When you switch this to reveal the non-safe keywords, they are very obvious with a little exclamation icon indicator appearing to the left of each no-safe macro keyword.

It confused me when I first used the newer versions as a common macro keyword called ‘SetValue‘ has been demoted to unsafe and thought this was actually been dropped and superseded by another newer keyword – but no, it was hidden!

So, normal service continues for the seasoned macro user but you may want to check out another blog post on Microsoft Access macros which leads to my ever popular eBook on How To Use Microsoft Access Macros & Automate Your Application.  Microsoft Access VBA programming

34 Edgwarebury Lane,Edgware,Middlesex,HA88LW,UK

Microsoft Access Database 2016: Printing Reports

Microsoft Access VBA programming A report is normally based on that all important query and understating the database fields, data types and structures.

Back to the video tutorial (above) and the print preview layout mode should be intuitive to use as it’s part of the familiar look and feel from Microsoft Office application’s preview modes. However, there’s an important consideration of how much functionality you want to present to users and not to confuse then with too much choice. Also, the frequency of how often a report is run, printed and even exported may lean towards automating this with either a macro or even the higher level; VBA code procedure.

Sometimes, having a customised MS access forms  with a command button to run, print, preview and/or export a report into something like the popular PDF format can be the extra finishing touches for that polished and professional look and feel.

Microsoft Access database

Building customised Microsoft Access database reports requires time and some skill as the standard templates do not provide the rich layouts this powerful system needs.

To learn more about building reports, why not take a look at my eBook offer on How to Build Access Database Reports – Creating that all professional look! which comes with a 30 day email support and money back guarantee.

34 Edgwarebury Lane,Edgware,Middlesex,HA88LW,UK