Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Microsoft Announces MVP Virtual Conference

MVP15_MicrosoftMVP_VC_EmailBanner

I wanted to let you know about a great free event that Microsoft and the MVPs are putting on, May 14th & 15th.  Join Microsoft MVPs from the Americas’ region as they share their knowledge and real-world expertise during a free event, the MVP Virtual Conference.

The MVP Virtual Conference will showcase 95 sessions of content for IT Pros, Developers and Consumer experts designed to help you navigate life in a mobile-first, cloud-first world.  Microsoft’s Corporate Vice President of Developer Platform, Steve Guggenheimer, will be on hand to deliver the opening Key Note Address.

Why attend MVP V-Conf? The conference will have 5 tracks, IT Pro English, Dev English, Consumer English, Portuguese mixed sessions & Spanish mixed sessions, there is something for everyone! Learn from the best and brightest MVPs in the tech world today and develop some great skills!

Be sure to register quickly to hold your spot and tell your friends & colleagues.

The conference will be widely covered on social media, you can join the conversation by following @MVPAward and using the hashtag #MVPvConf.

Register now and feel the power of community!

clip_image001

· World-class free online conference that features technical content presented by Americas’ region MVPs that is open to the public

· More technical content (Level 200, 300, 400), less marketing

· 5 tracks: IT Pro English, Dev English, Consumer English, Mixed Spanish, Mixed Portuguese

· Event will be broadcast via Lync using L+ which enhances the conferencing capabilities of Lync

· Two full days of sessions with simultaneous webcasts running across all 5 tracks

· Thursday May 14th and Friday, May 15th

· Start at 8am PT and running until 6pm PT (Pacific)

· Day 1: 45 sessions + Keynote, Day 2: 50 sessions

· Keynote on Day 1 to be delivered by Steve ‘Guggs’ Guggenheimer, Corporate VP of DX

· On Demand content available via Channel9

· This event is not just for MVPs, it’s for everyone!

Protecting Custom Templates

 

Between now and May 31st, check out these offers from Microsoft:

Azure 30-day Trial

MSDN Subscription

CANITPRO At The Movies (English)

CANITPRO At The Movies (French)

 

 

A crash can impact your computing life in ways that are mere inconveniences to major disasters. In this article I am going to discuss ways of limiting inconvenient data loss.

First a definition: an inconvenient data loss (as opposed to a catastrophic loss) is the loss of data which can be rebuilt relatively easily, requiring only a moderate amount of time and inconvenience to accomplish the re-build.

Catastrophic loss, on the other hand, refers to the loss of critical data that would be very difficult, perhaps even impossible, to rebuild. Some examples of critical data are business or personal financial records, family photos where a print doesn’t exist, original artistic creations, any non-trivial original work that you have created on the computer.

 

Data loss is a question of when, not
if it will happen; it will happen.


Catastrophic Data Loss

The only way to prevent catastrophic data loss is to regularly and frequently back the data up to a second location. How often should you back up? The more important the data, the more frequently you should back it up. A web search will turn up links to many backup applications that will assist you in backing up. Once you have decided on an application, you then need to decide what (individual files and folders) to backup, where to place the backup, and when the backup should happen.

  • Choose what to backup carefully
  • Choose a location that is not on the same
    disk or media as the actual data
  • Choose a backup frequency that will
    minimize the amount of critical data
    that will be lost when your computer crashes

Inconvenient Data Loss

The specific inconvenient data loss that prompted this article was a recent computer failure. In short, Windows crashed, making my computer unusable until I re-installed Windows. At the time, Office 2010 applications were my main tools. Roughly 75% of everything I do using my computer involves one or more Office applications (Word, Access, Excel, Publisher, PowerPoint.) I thought I had a reliable backup strategy in places with backup software copying my documents, spreadsheets, etc. to a backup folder that was in turned synced to a cloud location. That way, even if my computer experienced a total failure, I would still have all my files, with little or no loss.

Over the years, custom templates have become an essential part of my electronic tool box. For example I have Word templates that help me quickly create several different documents that are an essential part of my training practice. These include class lists, class evaluations, training quotations, and course outline formats. In short, whenever I recognized that I was creating documents that repeated standard information, I created a template that would include the information common to each of these types of documents.

Of course it takes time to create good templates but the invested time is quickly repaid because having a template eliminated re-inventing the wheel to create routine documents.

imageAnd so it seems, templates were the Achilles’ heel of my  backup strategy. I had overlooked that fact that custom templates and page parts in Office 2010 and earlier, were not stored in a location that is readily accessible to backup software. In other words, backing up document does not back up templates.

When windows went down for the count, the crash took out of circulation the folders where my custom templates were stored. Recovery, while not difficult, has been time consuming. Because I didn’t lose my data, I have been able to open files that I had originally created from each template and delete any of the content that was not part of the generic template. The difficult part has been remembering exactly what templates I had been using.

Avoiding the Problem in Office 2010 and Prior

Obviously this is an experience I would like to avoid in the future so I have modified my backup strategy. Ironically, the solution I came up with resembles the Office 2013 approach to custom template storage, something I became aware of only after I had worked out my new strategy.

First, I created a folder, MyTemplates,  in my Documents folder. Each time I create a new template, I save it to the the default templates folder and then save a new copy to MyTemplates. My backup strategy already included the Documents folder so MyTemplates is automatically backed up with every scheduled backup.

This approach isn’t ideal because it does require manual intervention whenever I create or modify a template but this minor inconvenience is well worth the bit of time it takes because it minimizes the risk of having to recreate templates in the event of a catastrophe.

How Office 2013 Handles Custom Templates

Office 2010 and prior Office versions buried custom templates in subfolders managed by Windows. The exact folder location depended on the particular Office version. In Office 2013, custom templates can become more accessible to the user. The default is a folder, Custom Office Template in the user’s Documents folders.

Given the experience I outlined above, I strongly recommend going with the default and then ensuring that the Custom Office Templates folder is included in the list of locations that you regularly backup.

 

Related articles:

The article Finding Template From Previous Office Versions suggests pointing your custom templates link to the templates folder that Office 2010 used. This approach is not a solution to the lost templates issue discussed above.

This article: Office 2013 Custom Templates Location the Custom Office Templates folder in Office 2013.

New OneNote Videos from Microsoft

Clutter

I’ve long been a fan of OneNote as a simple to use yet effective business and personal organization tool.

Check out this article to see Microsoft’s tongue in cheek depiction of the sticky note jungle that OneNote can help you solve.

Web Enabling your Application

Access 2010 now allows you to easily publish your application to the Web for sharing with other users. This process has been simplified by using Microsoft’s SharePoint Online, part of the Office 365 suite, as no local placement of a SharePoint server is needed. When you share the app with others, all they need is a Web browser to work in your Database Application.

Publishing your Application

One of the common uses of Data Macros is to validate data being entered into a record. The following example will stop the creation of a record in the Asset table if the user is trying to add a warranty value if they have not entered an Aquired Date.

Procedure: Publishing your Application

1. Open the Database Application to be published.

2. Select the Backstage View ribbon tab.

3. Click the Save & Publish option and then Publish to Access Server.

image

4. Enter the full address of your online SharePoint server. Enter the credentials to the SharePoint site, and click the Publish to Access Services button.

image

If successful and Access can publish to the given site, the Web Browser will open and display your published site – distribute the URL to users who need to access the Application via the Web.

This article was written by Nick Williams. Nick is one of the Access course tutors at Acuity Training, a hands-on IT training company with offices in central London & Guildford UK.

Data Macros

Use Data Macros to add logic to events that happen in Tables, this could be adding records, updating a record or deleting data. Data macros are created and managed from the Table ribbon tab while you are working in the table view. There are two main types of data macros

  • Event Driven, which are those triggered by table events.
  • Named, which run in response to being called by name.

Creating a Data Macro

One of the common uses of Data Macros is to validate data being entered into a record. The following example will stop the creation of a record in the Asset table if the user is trying to add a warranty value if they have not entered an Aquired Date.

Procedure: Create a Data Macro

1. Open into Data Sheet view the Table you are adding the Data Macro to.

2. Select the Table ribbon view tab.

image

3. Click in this instance the Before Change button.

4. Within the Macro screen, add the necessary actions you require. This example shows an If program flow

image

Add the necessary values

image

 

 

 

 

 

 

 

 

 

 

 

5. When you have completed the Macro, Save and Close the Data Macro.

image

The next time a new or edited record does not meet the validation set, the following message will appear and stop the update of the Record.

image

Procedure: Edit a Data Macro

1. Open into Data Sheet view the Table you wish to edit the Data Macro for.

2. Select the Table ribbon view tab. You will notice the Event button will appear yellow if there is already a Data Macro created.

3. Select the relevant Event button and make the necessary changes.

image

4. When you have completed the Macro, Save and Close the Data Macro.

Procedure: Delete a Data Macro

1. Open into Data Sheet view the Table you wish to Delete the Data Macro for.

2. Select the Table ribbon view tab.

3. Select the relevant Event button and make Delete the changes for.

4. Within the Data Macro screen, remove each command by selecting the at the top right corner of the command.

image

5. When you have completed the Macro, Save and Close the Data Macro.

image

This article was written by Nick Williams. Nick is one of the Access course tutors at Acuity Training, a hands-on IT training company with offices in central London & Guildford UK.

Finalizing Your Application

Once your database design is complete, it can be very complex with many Tables, Forms, Queries and Reports which in most cases the user will only need to use directly a small number of. To enhance the user experience and usage, we generally use switchboard menus (a collection of specially created forms) for the user to navigate around the Database.

As they will have the relevant tools available via these forms/buttons, the Ribbon, QAT and Navigation Pane are really redundant to them, and hiding them will tidy up the appearance of the database and also ensure no unexpected access features are selected.

Hiding the Ribbon

Using your Current Database options, you will generally set a Form to open when the Database is opened by the user. This Form we will create some code which will hide the Ribbon when the form is opened and again Show the ribbon when the Form is close.

Procedure: Hiding the Ribbon

1. Open within Design view the Form that is displayed when the Database is loaded.

2. Select the Design ribbon tab and click on Property Sheet to display.

image

3. Within the Property Sheet window, select the Event tab and click the three dots button next to OnOpen event.

image

4. Select the Code Builder option from the presented list and click OK.

image

5. The Visual Basic Editor window will now display, ensure the following red text is entered as shown below.

DoCmd.ShowToolbar “Ribbon”, acToolbarNo

image

6. Select the save button and close the Visual Basic Editor window.

Now you have the code set to hide the Ribbon while this specific form is opened, you will need to complete the next task to show the Ribbon again when the form is close.

Showing the Ribbon Again…

Once you have entered the code to hide the Ribbon, when you close your database the Ribbon will always be hidden. We resolve this problem by adding the code to the OnClose event of the Form to show the Ribbon again. .

Procedure: Showing the Ribbon

1. Open within Design view the Form that is displayed when the Database is loaded.

2. Select the Design ribbon tab and click on Property Sheet to display.

3. Within the Property Sheet window, select the Event tab and click the three dots button next to OnClose event.

4. Select the Code Builder option from the presented list and click OK.

4. The Visual Basic Editor window will now display, ensure the following red text is entered as shown below.

DoCmd.ShowToolbar “Ribbon”, acToolbarYes

5. Select the save button and close the Visual Basic Editor window.

Once the database is closed, each time it is opened the form will launch and the Ribbon will be hidden and then shown once the form is closed.

Hide the Navigation Pane

To ensure your general user audience cannot gain access to all of the database objects – only the ones you give them access to via a switchboard, hiding the Navigation Pane is the answer. This feature is set by database, even though you have hidden the Navigation Pane from one Database Application, it will re-appear for others.

Procedure: Hiding the Navigation Pane

1. Open the database you wish to hide the Navigation Pane for.

2. Select the File Backstage View button and click on Options.

3. Within the Options dialog box, select the Current Database section.

image

4. Locate the Navigation section and de-select the Display Navigation Pane check box.

image

5. Click OK to save the option change.

6. You will be prompted with the following dialog box, you must close the database down and re-open it for the change to take effect.

image

7. To display the Navigation Pane in this Database again, repeat the above steps, selecting the Display Navigation Pane check box.

This article was written by Nick Williams. Nick is one of the Access course tutors at Acuity Training, a hands-on IT training company with offices in central London & Guildford UK.

Access Image Gallery

image

Use the Image Gallery to provide an easy way to add, reuse, and update images on forms and reports within your Database application.

Once an image is inserted onto a form or report, it is automatically added to the Image Gallery, and becomes part of the database. From that point on you can quickly add the image to any other forms or reports in that database.

When you update the image in the Image Gallery, it will automatically update any other occurrences within your database.

Adding to the Image Gallery

When an image is added to a Form or Report, the image will automatically be added to the Image Gallery. However, during your scoping and design of the Database, you will highlight logos and other images that will be repetitively used within the design objects, these can be added ready to be used at a later stage.

Procedure: Add a Gallery Image

1. Open any Form or Report into Design View.

2. Select the Design Ribbon tab and click on Insert Image.

image3. Select the Browse option, and locate your image within the Insert Picture dialog box

image

4. Select the image and click OK.

Repeat the above procedure until all your images have been added to the Image Gallery.

Amending an entry in the Image Gallery

Once in the Image Gallery, your image can be Renamed, Updated or even Deleted. If you Update an Image, all occurrences of the image that have been used in the Database application, will automatically be updated. If the image is Deleted, every occurrence of the image will be replaced with a blank image control, you will need to then manually update the control to fill with the new image.

Procedure: Add a Gallery Image

1. Open any Form or Report into Design View.

2. Select the Design Ribbon tab and click on Insert Image.

3. Right mouse click on the image you wish to amend.

4. Select the required option from the shortcut menu.

This article was written by Nick Williams. Nick is one of the Access course tutors at Acuity Training, a hands-on IT training company with offices in central London & Guildford UK.

Looking for Beta Testers

 

 

Chris Long, the developer of SSE Setup is looking for beta testers of the Access deployment functionality. SSE Setup is a "Simple Smart Easy!" application installation creator for Windows. Free for personal use and low cost for commercial use, SSE Setup is intended to be, in Chris’ words, "an installer to fill in the low-cost/free gap since SageKey is so expensive."

Download SSE Setup from www.ssesetup.com. Make sure you check for updates after installing it and install the latest updates. Then open the help file and go to the "Access Deployment" section for further info.

Sharing Reports

We’d like to welcome guest blogger Nick Williams. Over the next few weeks, Nick will be publishing a series of Access-related tutorials. Nick is an Access tutor based in the U.K.

If a group of users do not have access to your Database Application, share data with them by exporting Reports. Select the file format you want to save in—Excel, PDF, HTML or other format.

This process can be performed on a regular basis, you are able to save the export steps and run them again easily. Additionally you are able to create an Outlook Task while saving the export steps, this will not only remind you when it’s time to export the report, but also a Run Export button will be added to the Task automatically, so you can run it from within Outlook.

Procedure: Setting up an Export Reportimage

1. Right click on the Report to be exported within the Navigation panel.

2. Select Export, and choose the format to be exported to.

3. Within the Publish dialog box, select the location and enter the filename to be used.

4. Point and click Publish.

 

 

image[11]image

5. You will now be prompted to save the Export Steps. Once the checkbox has been selected, enter a Save As name and Description.

If you would like to have a task prompt you when the export is due to run again, select the Create Outlook Task tick box. Click Save Export to continue.

6. If the Create Outlook Task has been selected, ensure the correct Date/Time and Reminder are set within the Task.

Procedure: Running a Saved Export

1. Select the External Data ribbon tab and choose Saved Exports.

image

2. Within the Manage Data Tasks dialog box, select the Saved Export and click Run.

image

Also, if you have a Task setup in Outlook, you are able to open the Task and click the Run Export button within the Task ribbon tab.

Click OK to confirm the running of the Export Report.image

 

 

 

 

 

 

 

This article was written by Nick Williams. Nick is one of the Access course tutors at Acuity Training, a hands-on IT training company with offices in central London & Guildford UK.

See How One Note Can Enhance Your Productivity

 

This Blog article contains a laundry list of how one person make the most of OneNote in his business and personal life.