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

Posts Tagged ‘Access Applications’

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–The Right Tool for the Job

Guest Blogger Jack Leach is a colleague of mine at UtterAccess where his pet project is the Access Wiki. Recently, in a mailing list that we both subscribe to, Jack posted his experience in building an Access application for his business. You can find his website at http://www.dymeng.com and his blog at http://dymeng.com/blog. Jack has graciously given his permission to OTM to publish his success story.

I’ve used Access to create a shop management software for my manufacturing business.  Besides the average FE type stuff: customers, orders, purchasing, quickbooks integration, the software also handles all the backend stuff: visual scheduling, what-if scenarios, integrated quality control, everything right down to tooling tracking (of which there’s thousands).  Min/Max for both customer pull-supply as well as shop supply, so on and so forth.  The whole nine yards.

Even more so, I was able to integrate basic CAM (Computer Aided Machining – like CAD that outputs machine tool code for producing the parts) into the routing structure for the components we make.  AutoCAD automation for reading customer drawings and auto-generating tolerance sets for quality reporting, etc.

The very first piece of the application – my first Access project – saved the company an estimated $20k/year by providing different machine code formats for different machine tool platforms.

We work with medical, military, aerospace and other demanding industries, which has all been fully supported by this for quite a few years.

Create an Interleaved Report Part 3 – The Report

The database development process is straightforward but can onCartoonly be efficiently carried out if the developer follows a set sequence of steps. The order of the steps is important because each step depends on the step before it. This series of articles has illustrated this sequence. Part 1 described the results of the data analysis step where we need to gain a full understanding of the data available to meet the client’s requirement. Of course, the design process illustrated here itself depend on a complete and accurate understanding of the client’s needs.     

Even the best design in the world sometimes needs some revisions and tweaking when we start to develop queries and reports. Feedback from the Output phase of the process sometimes sends us back to revise and refine some aspect of the design.

 

Design Process Diagram

 

The query at the end of part 2 is a good example of this. As is, that query can only provide the id of a representative, not the representative’s actual name. So we need to add a another table, tblHumanResources  to the query.  This change enables the query to provide representative names for the report.

Final Query Diagray Rather than including the three representative name fields in the query grid, the first column of the query is defined by a calculation: Representative: [hrlname] & ", " & [HrFName] & " " & [hrmname].

All that remains now is to design the actual report.

Report Design Graphic

Building the detail section of the report is simply a matter of dragging and dropping fields form the field list in the correct position. The naming convention described in part 2 helps ensure that the fields are situated in correct product column and weekday Sales or Quotation  row. While the field names obscure the actual product name.

Because there are multiple controls for each product in two different categories, individual addition expressions must be created in order to display weekly totals for both Sales and Quotations. For example the expression for the weekly sales total for Widgets is:

=Int([SAPr1_MO])+Int([SAPr1_TU])+Int([SAPr1_WE])+Int([SAPr1_TH])+Int([SAPr1_FR])

Here again, the naming convention make it easy to create the expressions for each product without a lot of typing. After creating the first control (for product 1 – widgets) and typing the expression as its data source, simply make five copies. Edit the data source for each product total in turn and replace the ‘1’ with ‘2’, ‘3’, ‘4’, ‘5’, or ‘6’, depending on the product. This technique will also work for the Quotations totals.

After adding all the controls, adding appropriate labels then formatting and aligning them, and setting up report grouping on Representative, this is what the final report looks like:

Report Preview Graphic

You should keep in mind that, because queries are limited to a maximum of 255 fields, this approach will only work if 255 or fewer ‘cells’ required in the report details. This report has 60 ‘cell’ (6 products x 5 days x 2 categories) so it is will within the limit. 

Create an Interleaved Report Part 2 – The Data

In this article I will focus on the data structure behind the interleaved report. The data  illustrated is only a small subset of what you would find in a typical full-scale application. In order to produce the report described in  part 1, we need to know:

  • who made the sale or quotation CaveExec cartoon
  • what product was involved
  • was the action a sale or a quotation?
  • when did it happened

These descriptions suggest the need for certain tables:

  • salespeople
  • product lines
  • sales activity
  • sales activity types

Here’s how the tables are organized in the database:

Structure

Obviously this is a minimal structure designed to illustrate what’s needed for the reports. The Human Resource table has only three name fields whereas in a real application much more information about people would be required.

The data is normalized to the third normal form. The problem here is to create a report from a flattened version of the data. The client’s requirements in part one specified that the report require one column for each product group sales on one row followed by a similar row or product group quotations. Keeping in mind also that the desired report should have some vertical space between each pair or rows and that there be one pair of rows for each day of the week, it should be apparent that a simple datasheet or even a continuous report will not meet these requirements.

Before designing the actual report it is first necessary to flatten the data. Crosstab queries are specifically designed to do exactly that. The data we will need in the query includes the sale representative, the date, the type of action, and the number of times the action happened on each day. So the basic crosstab design will look like this:

Basic CrossTab

Notice that the query has included a field to restrict the results to a particular time frame (July 26 – 30, 2010). In this example the criterion has been hard-coded. In a working database the query would most likely refer to a user-specified time frame from a form or memory value.

What’s missing from this query, however, is any sort of specification of the activity type (sale or quotation) and the relevant day of the week. To further refine the specification, it is necessary to add two additional Where fields to the query.

WheresActually, in order to report on each product for each day of the week, a total of ten queries will be needed (5 days x 2 action types.) Before creating these queries, however, it will speed things up greatly if you specify column headings in the  queries properties. (Remember that cross-tab data has to have pre-defined column headings if the data is to be used in a report.

Crosstab PropertiesThis basic query is only needed as a model for the queries that will ultimately be used in the report. By setting the query up with as much generic detail as possible, you can quickly create the ten queries simply by modifying the product and day of week criteria and using save as repeatedly for each set of criteria.  Subsequent work with the queries will be much easier if you use a consistent naming style for these day/product queries.

The names used in the example database are:

Sales queries: Monday to Friday

  • qxtbActivity_SA_MO
  • qxtbActivity_SA_TU
  • qxtbActivity_SA_WE
  • qxtbActivity_SA_TH
  • qxtbActivity_SA_FR

Quotation Queries: Monday to Friday

 

  • qxtbActivity_QU_MO
  • qxtbActivity_QU_TU
  • qxtbActivity_QU_WE

  • qxtbActivity_QU_TH

  • qxtbActivity_QU_FR

Notice how these names follow a consistent pattern. The names all begin with qxtbActivity, indicating that the query is a crosstab of the Activity tab. The next two characters after the first underscore indicates whether the query is retrieving sales or quotation data. The final two indicators indicate the weekday that data represents. Using consistent naming patterns like this simplifies both the creation and identification of the queries. For example, you can use a text editor to search and replace the distinguishing characters to create the other queries in the set.

These queries will return the sales and quotation date for each sales rep for each day on which each rep, respectively, had activity. However, in the unlikely event that a rep had no sales or no quotation activity, that rep will be missing from the days sales or quotation data or both. What we really need is for the rep to be included in the query output with zeros for each product column.

What we need is a list of sales reps who were active in the reporting period. A simple query of representatives in the activity table grouped by rep with a criteria limiting the date range will do the trick. The SQL of the query is:

SELECT tblActivity.Rep
FROM tblActivity
WHERE (((tblActivity.ActDate) Between #7/26/2010# And #7/30/2010#))
GROUP BY tblActivity.Rep;

 

Now this query can be joined to each of the crosstabs to produce a set of ten selection queries. These queries all follow a similar pattern and use an outer join to ensure that all representatives will be included whether or not they have sales.

Select QueryIn these queries, the product names have been “aliased” so that they can be distinguished as to sales or quotations on a day by day. Again a consistent naming pattern helps speed up the creation process. For example, QUPr1_MO is the alias for Monday’s Widget quotations and SAPr1_MO is the alias for Monday’s Widget sales. The Pr1 simply indicates that this is the first field of products which is widget data. The queries themselves are names “qsel” followed by the part of the crosstab name after “qxtb”

For example, the query in the diagram above is named qselActivity_QU_MO, indicating that it will provide data for the Monday column of the report.

We need one more query to give us the data that the report will display. Think of this as an umbrella query that will combine the results of the ten queries just created into a single query:

Final Query

The order of the sub queries doesn’t matter much but it will be easier to work with query when designing the report if the field list are arranged left to right, Monday to Friday. Each table should be joined to the table on its right by the Rep field. In this diagram the field list are arranged in two rows simply to make it easier to display in this article. In the actual query, qselActivity_QU_MO is to the right of qselActivity_SA_FR.

The final query has 61 columns – rep plus one column for each of the SAPr… and QUPr… fields.

In the final part of this series we will see how to display this query data in a grid format grouped by representative.

Create an Inter-leaved Report From Normalized Data Part 1

The client’s requirements were quite specific. They wanted a report that would show data from two data sources on alternating lines. Each pair of lines had to be separated from the next pair by a small vertical space. The first row of each pair was to report product sales, the second row, product quotations, for each of six distinct product lines. Simple but time-consuming to do in a spreadsheet. Somewhat complicated to setup but simple to produce as an Access report. Here’s a mock-up of the report done in Excel. Report Prototype

Excel or Access?

This question comes up quite regularly. Which would be better for the job I have to do? Truth be known the question is actually miss-stated. You will get a more accurate answer if you ask, “Is this a spreadsheet or a data management task?” Regardless, the question deserves much more in depth treatment than is possible here but the short answer can be found by clearly identifying the job you have to do. Do you simply need to ‘crunch numbers,’ (analyze data) or do you need to collect and manage data on an ongoing basis? Data management in Excel, or any other spreadsheet application, for that matter, quickly becomes cumbersome and error prone. Excel is a tool for analyzing numerical data. Access, on the other hand is a Database Manager.

 

In the organization requesting the report, sales people generate quotations and sales for each of the company’s six main product lines. (In case you haven’t guessed, the product lines here have fictitious names.) The client wanted a weekly activity report that showed the number of quotations and sale for each salesperson for each product. What clinched the decision that this report needed a data management solution and not a spreadsheet solution was that client wanted other, similar reports, that summarized data over time. In order to produce such on-going reports, sales and quotation histories had to be maintained. Choosing the best data manager for the job is a whole other decision process. Suffice it to say that, in this case, Access was determined to be adequate for the client’s business volume. As you may or may not know, relational database management systems (RDMS) work best if the data is properly normalized. The mock-up above shows the data laid out in what some refer to as a flat-file format. The layout seems natural and easy to understand and that is how most people envision their data should be set up in a database. Sadly, therein lies a trap however. Flat file organization is very inefficient and error prone, in part because there is a lot of repetition of data values. One of the goals or normalized tables, on the other hand, is the elimination of data repetition. In short, there is a simple rule of thumb that applies here, To get the report right, first get the data right.

In part 2, I will illustrate the normalized structure for the data used in the report.

Access 2010 Navigation

Check out this Access Team Blog article in which Wouter Steenbergen describes new navigation features in Access 2010 in detail

John R. Durant’s WebLog : Why VBA Still Makes Sense

This article, John R. Durant’s WebLog : Why VBA Still Makes Sense , presents a solid case  for why VBA is the ‘next level’ when it comes to application development in Access and other members of the Office suite. John provides an excellent of VBA since its early days to the present and on into Office 2010 including his perspective on the ‘fit’ between .NET and VBA. John R. Durant’s WebLog : Why VBA Still Makes Sense is definitely a worthwhile read.