Northwind 2.0 Developer Edition: Companies

In the Northwind Starter Edition, Customer is the only company type. In the Developer Edition we have expanded that to include Customers, Shippers, and Vendors. 

We also expanded Products to include multiple vendors for a product.   

Each company can be only one type: either Customer, Shipper or Vendor. The business case for when the company type can be changed is described in the frmCompanyDetail section of this article.
'

Company types and where they are used 

• Customer for an Order

• Shipper for an Order

• Vendor for a Purchase Order

• Vendor for a Product

Additionally, Companies in the Northwind Developer version can have multiple Contacts.
'

Companies in the Ribbon

Selecting Companies from the Ribbon displays Companies in Northwind in frmCompanyList. 

Company List - frmCompanyList

In the Northwind Developer Version, frmCompanyList is a split form. A split form gives you two views of your data at the same time, Form View and Datasheet View. It also has a header and a footer.  In this implementation we will not display the form view. By not displaying the form view we will have a datasheet with a header and a footer.  

Here's how we achieve this:  

In frmCompanyList, the splitter bar between the header and the datasheet is not visible. The form property Split Form Splitter Bar is set to No, thus hiding the form view. 

Split forms and Datasheet forms are like Excel worksheets. They support filtering and sorting, and you can show, hide, or move fields or columns. Also, columns can be totaled. 

Header and footer sections of a split form allow you to use command buttons, images, text(labels) and any other access form controls you want to improve the user’s experience. To learn more about forms and split forms you can start here. Datasheets do not display the header and footer sections.

Learn more via Introduction to forms and Create a split form. 
'

The Company List form supports these five actions:

• Apply a custom, prebuilt filter, on-demand filtering, or both

• Show/Hide Fields

• Create Labels

• Show Filter

• Add a new Company

Double click anywhere in the detail row to open the Company Detail form (not just on ID as in other forms). 
'

Prebuilt Filters and on-demand filtering

The frmCompanyList form is rich with opportunities to filter the Companies in Northwind.  At the top of the screen, you can filter using these radio buttons:

• All Companies (the default)

• Customers Only

• Shippers Only

• Vendors Only

In addition, the datasheet’s built-in, on-demand, filtering options are available. Any filters applied to the Company List form (prebuilt or on-demand) are passed and applied to frmCompanyDetail when it is opened.  

To pass filters from one form to another, you must first cleanse the form’s filter string to contain only field names. Look at the code module Open_frmCompanyDetail for a more detailed explanation of how this is done and in frmCompanyDetail at the comments for Form_Load to see how it is applied.

We pass the filter information to the Company Detail form via the form’s OpenArgs property instead of opening the form with a where condition.  You can learn more about OpenArgs here.

Show/Hide Fields

Select Show/Hide Fields to open a dialog with a list of all available fields; you can check or uncheck one or more columns. The selected layout persists from one session to another until a new version of the application is deployed. See Show or hide columns in a datasheet. 

Create Labels

The Create labels button opens the Access Label Wizard. Learn more about the label wizard here: Create mailing labels in Access.  

Open Company Detail

Double Click anywhere in a detail row to open frmCompanyDetail 

Click on the ID field (set up as a hyperlink) or double-click on any other field in the detail row to open frmCompanyDetail to the selected Company and pass any form filter that was applied to the Company List to frmCompanyDetail.

Company Detail - frmCompanyDetail

At first glance the frmCompanyDetail may look relatively simple.  However, It's not.  In the code behind the form, a lot is happening!  The form highlights the following actions and features:

• Several command buttons and links

  • Add Company

  • Email Company List

  • Show Filter

  • Delete Company

  • Open a Map based on an Address

  • Open a website from a hyperlink

• Cancel or Save actions in add/edit mode

• Business rules to control when a company type can be changed

• Referential integrity checking before the delete event
  '

The Form_Load event includes the following actions:

• Use multiple parameters passed to the form via OpenArgs and the custom Northwind StringToDictionary function

• Use the OpenArgs form property to allow a user to add a new Company while taking an Order

• Use the GoToRecord technique to add a new Company

• Use the SearchForRecord technique to move to the Company selected on frmCompanyList

• Branching/Conditional Execution options (See Form_Load Event)

  • Option 1 - Case Statement

  • Option 2 - Nested If Else

  • Option 3 – ElseIf

  • Option 4 - Nested If Else and ElseIf
    '

In the Form_Current event, the following occurs: 

• Change a sub form caption and source object at runtime by calling the sub ManageFormOptions(). This subroutine is called from multiple places in this form. 
  '

Cancel or Save Actions when in add or edit mode. 

Our Access forms are bound.  What does that mean?  From Microsoft: 
“A ’bound’ form is one that is directly connected to a data source such as a table or query, and can be used to enter, edit, or display data from that data source.”

With bound forms, Access does not require you to explicitly “save” your data.  When you move from one record to the next Access automatically saves your data.  Often that works just the way you want it to.  But what if a user must take an action to indicate they are ready to save? 

For example, form frmCompanyDetail has a sub form for Contacts (sfrmCompanyDetail_Contacts).  So, what happens if you start to add or edit the company data — let’s say phone number — and pause to fix a typo you notice in a Contact’s information?  By default, Access will save any changes you made in the company record when you click on the contact record, which is not what you want. 

In this form, we show one way to accomplish this with a single record for a Company.

What is the reason for having the Cancel button? The escape key cancels any entered or changed (but un-saved) data in a control on the form. In this state, the pencil icon appears in the record selector (a grey vertical bar on the far-left side of the row).

However, after you click the Add Company button, you will notice that there is no pencil icon until you begin entering data in the new company record. What if you change your mind on adding a new company at this point? If you press the escape key, nothing happens because you haven’t entered any data yet. That is why we have the cancel button.

Clicking the Cancel button always works; the escape key works only when you have entered or changed data.  

If you start to add or change data and then close the form without clicking save or cancel, by default Access will save the data.  

Business Rule:  
As a Northwind business rule for Companies, we want you to explicitly indicate you want to “Save”.  To ensure that, we must check if the user clicked save in the Form_BeforeUpdate event and if they did not click save, prompt the user to indicate if they want to save or cancel their changes.
'

Company Type Change

Business Rule: If a Company has any existing Orders, Purchase Orders, or is a Vendor for a Product, the user cannot change the Company Type. We have created a function, CompanyIsActive(), to determine if any of the above conditions exist. If they do, the user cannot change the company type until these references are cleared up (deleted). 

This rule is only enforced in this form.  In a production ready application, you would not allow users to change data by opening the table directly.  In Northwind, we allow you to open the tables and do whatever you want.  This is a great way to learn, but it also leaves you free to make changes that might violate the rules. 

You should always program defensively. Plan for the unexpected. Even if the Company is currently a Shipper, that doesn’t mean they were not at some time a vendor or customer. Their ID might be found in older records in the ProductVendor or the PurchaseOrder tables.
'

Custom Dialog Form.

To display its findings, cboCompanyTypeID_BeforeUpdate uses a dialog form, frmGenericDialog. This can be a great alternative to the standard message box when you have a lot of information to display. It is also used with the employee form for a similar purpose.
'
 
Delete Referential Integrity Check

In the Northwind Starter Edition we let the Access default behavior notify you that a company cannot be deleted when it has related records.  

In the Northwind Developer Edition we notify you before the delete, instead of accepting Access default behavior.  We do that using the same function as we did the Change Company Type; function CompanyIsActive().

The difference is that we only care about Orders or Purchase Orders. If there are no Orders or Purchase Orders, we confirm your intention to delete, and we will delete the Product Vendor and Contacts if they exist for you. 

• Defining table relationships in an Access database 

• Northwind 2.0 Developer Edition: Things you should know

• Northwind 2.0 Developer Edition: All topics