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

Archive for the ‘Access 2010’ Category

Recently on Channel 9

At the 2015 Microsoft MVP Virtual Conference (May 14-15) Crystal Long, Brent Spaulding, and Julian Kirkness presented an information packed session on Access, Access Web Apps, and connecting Access desktop applications to Azure servers.

Channel 9 is a Microsoft community site for Microsoft customers created in 2004. It has video channels, discussions, podcasts, screencasts and interviews with Microsoft. Wikipedia

If you are interested in expanding your knowledge and understanding of the power of Access, the video of this session is a must watch. In it you will see an introduction to data management using Access, a demonstration of a working Access Web app, and a tutorial on connection an Access Desktop frontend to a cloud-based Azure backend.

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.

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.

Enhancements and Issues Office/Access 2010 SP2

Luke Chung of FMS Inc. has published this paper which should be of interest to anyone working with Access 2010 and Office 2010 generally

Create a Nested Form Access User Interface

This article demonstrates how to create a co-ordinated set of Access forms that could be used, among other things, as a Dashboard or Launching Pad in an Access User Interface. Form content depends on the needs of the application. The forms in the demonstration application are just simple examples to illustrate what can be done. They are there just to show what the code behind the scenes does.

The concept is similar to Frames in HTML Documents.

The Forms

There are six forms in the demonstration application:

Screen1r

frmShell can be thought of as an outer container or framework. It has one subform control and command buttons and a toggle button that swap actual forms in and out of the subform control. This the opening form for the application. When the application starts, this nested form displays a simple welcome message which is contained in frmShell_ContentWelcome.

frmShell

 

The other forms take turns populating either frmShell’s subform control second level subforms. The form name indicates each form’s relative position in the overall hierarchy.

Screen2r frmShell_ContentA is at the second level of the form hierarchy. If you consider frmShell to be at the top level of the form hierarchy (the patriarch) then frmShellContentA represents a child of the patriarch. frmContentA itself has two children (siblings if you don’t mind the metaphor.)

frmShell_ContentA populated with frmShellContentA

 

ContentA and Subformsr The content forms can be loaded as individual standalone forms.

frmContent and its two child forms

 
Screen3r Screen4r

frmShell_ContentTabs

frmShell_ContentTabs with Mouse pointing at the Child Tab

Screen5r  

frmShell populated with frmShell_ContentTabs (populated with frmShell_ContentA)

 

frmShell_ContentA_1 and frmShell_ContentA_2 populate two subform controls on frmChild when that form is the main child form of frmShell

frmShell_ContentTabs is an alternate child form to frmShell. This form uses a tabbed style of interface to swap its subform content in and out.

frmShell_ContentWelcome populates frmShell with startup information.

Behind the Scenes – the Code

The main code that makes it all work is quite simple as long as you get the notation right. Content screens are swapped by assigning a form to the SourceObject property of the desired subform control.

For example, if a form has a subform control named fsbMainWindow, then a content form (for example, frmContent1) can be assigned to the control with this statement:

me!fsbMainWindow.SourceObject = “frmContent1”

Once the content form has been assigned to the subform control you can then refer to any controls or public properties in one of two ways:

me!fsbMainWindow.Form.[name of property or control]

or by creating a form object and assigning the subform object to the form object. Of course you have to declare a from object.

Dim frm as Form

then you can use the object by assigning the subform control to it.

Set frm = Me!fsbmMainWindow.Form

You can then use the object to refer to the content form and its controls and properties.

frm.txtDate = Now()

assigned the current date and time to a textbox named txtDate on the content form.

If there are several ‘windows’ on the form, then the object approach makes it easier to keep track of which window content you are referring to.

A subform can talk back to the form that contains it using its own parent property.

me.parent.CurrentChild = me.name

is a trivial example. Of course you would have to declare a public variable or property, CurrentChild in the parent form.

It is possible to nest up to seven levels (Access XP and later, three in Access 2000) of subforms on a form. The notation can be confusing because you have to have the bang operator (!) in all the right places. For example,

Me!fsbMainWindow.Form

refers to the subform one level down. If you want to refer to a subform nested on the form one level down, the notation is

Me!fsbMainWindow.Form![name of subform control].Form

For a general reference on referring to sub forms and sub reports see this page on The Access Web.

If you need to set the focus to a control on a sub form, first set focus to the sub form control and then set focus to the control you want to have focus.

General Development Guidelines

If you will be swapping several forms in and out of the same subform control, it helps to first create a simple empty form that is set up with the appearance that you would like all of the forms that you will use in that particular subform. Use that form as a template for each of the others.

A naming convention also helps keep the various forms straight in you mind. For example begin the name of each related form with frm followed by a generic name for the group of forms. For each sub form, append an underscore followed by a meaningful name for the particular sub form. Using this approach, all of the related forms are listed together in the database window or navigation pane depending on your version of Access.

Summary

Using a shell form to ‘house’ the main forms of a user interface makes it easier to create and maintain a consistent ‘look’ and feel for the application.

Demonstration File

The demonsration file can be downloaded from:

Action Queries-Delete Queries

QueryDelete

Delete queries delete all records from the specified table that meet criteria specified in the query.

 

 

Scenario: The table of country names that you have inherited contains some invalid names. You have already updated the records that used to point to these names so they now point to the correct form of the relevant country name. Now you want to remove the invalid names from the table.

 

Before and After

With just a few entries, you could just open the table and delete the bad records. However, if you have many records to delete, a delete query can take care of all of them at once.

IMPORTANT: Before running a query that will modify your data, it is very important to first back up the data. You can do this either by copying the table’s structure and data or by backing up the entire database.

Setting up the query is quite straight forward. Use the table you want to clean up as the query’s record source and include the field that will determine whether or not a record should be deleted.

qdelDeleteCountriesTypos

 

This query will delete records that contain “United States” in the Country field. All other records will be untouched.

If you include additional criteria on the rows below the first criteria, then the  query will delete records that match any one of the criteria.

qdelDeleteCountriesTyposAll

 

 

 

 

 

Just to be safe, however you should not run a delete query before you view it. Notice the distinction between viewing and running the query. ViewDatasheet

 

Viewing a query will display the selected records in the query’s datasheet view.

QueryRun

Running the query, on the other hand, actually deletes the selected records.

 

Note also that double-clicking a query icon in the database window or Navigation Pane, runs the query. If you are not absolutely sure you should be running a query, open it in design view and view it in datasheet view.

Sample files for Action Queries (Access 2000 and 2007/2010 format) can be downloaded from:

 

Action Queries-Update Queries

QueryUpdate

Update queries are the means to modify record data in one or more fields in all the records that match the criteria you specify. For this article, I have modified the sample data so that it reflects an all too possible scenario.

Scenario: You have inherited a customer database where customer data was stored in a single table. You have normalized the table. When you created and populated the Countries table, you didn’t recognize that the country data inserted into the original table was inconsistent. Some country names were mis-spelled (Mxecio, United Staes, for example) and several forms of the name were used for others (US, USA, and United States, for example). The problem came to light with the Regional Manager for Mexico reported that one of more of his customers appeared to be missing when he search for customers in Mexico.

Solution: When you looked at the Countries table you recognized that the data had spelling variations for some countries. You have produced a new table with the correct spelling for each country’s name and there are no variations in the form of the name.

imageNow you want to clean up the data so that your normalized customers table will refer only to the new Countries table. You can’t simply delete the bad spellings because some of the records in your new customers table probably refer to these imagemisspelled country name records.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The solution requires first identifying which customer records refer to bad country name and then updating those records so that they refer to the correct spelling. The query results at the right is a comparative list showing the current list of country names together with a list of only valid names. In design view the query looks like this:

image 

Notice that the tables are joined by the country names, not by keys. An empty cell in the valid country column indicates that the name in the cell in the left column is invalid. This query gives us the information we need to know which records to update and what CountryID should be used for the update.

In the Customers table, these changes need to be made to the CountryID field

  • Mxeico 13 update to 12
  • United Staes 21 update to 24
  • United States 22 update to 24
  • US 23 update to 24

Because each of these updates has different criteria, four update queries will be required. (As you will see, you use a single query and simply modify the criteria after each update.) After we execute these updates, the customer table will correctly refer to Mexico and USA, respectively in the Countries table. Then the invalid names can be deleted from the Countries table.

A simple unmatched records query will help identify just the customer records that need attention. It is similar to the last query but this is what the results will look like:

image

The Is Null Criteria limits the output to just the unmatched records:

image

Now we are ready to create the actual update queries. This query will need the valid country id with which we want to update the affected Customer records:

image

This query includes the Company name field simply for “comfort.” The table requiring update is joined in this case to its related Countriesimage table so that we can restrict the record by the actual. Before running an action query it is usually a good idea to view the record selection you have specified. However, the datasheet view of an update query only shows you the field to be updated with its current value. To see more about the records that will be updated, you can temporarily convert the query to a select query and the view its datasheet.

image

Again you are only seeing the current data but you can verify that you have in fact set the criteria correctly. Now all that remains for the update is to convert the query back to its update form and then run it.

After you run the query for the first incorrect country name, you can change the query to the next misspelling, verify and run it. You simply repeat this process until you have updated the Country Id for all of the misspelled country names, one at a time.

It’s a good idea at this point to create a query to verify that the invalid country names are no longer referenced from the Customers table.

Remember: never run any data manipulation queries before backup up the affected tables and or the entire database.

Final cleanup involves deleting the records with the bad spelling. The next article in this series will discuss Delete Queries