Northwind Problem and Solution

Northwind Problem and Solution

If you use the Northwind database as a basis for your business application then there is a problem that you should be aware of. The sample Northwind database supplied by Microsoft provides some excellent insights in how you should program an MS Access application. However if you decide to use it at the basis for a project take the following into consideration.

What happens is if you change the name of the customer displayed on the form? Well, it’s quite a serious problem! Any orders in that Form will be transferred to the new customer you select! Not really the sort of behaviour you would want in a real life situation. You can download various versions of the northwind database here, Northwind Downloads … I also demonstrate how you can install the north-wind database…

The following videos demonstrate this problem and offer several Solutions in order of complexity.

Customer Record Error

Orders Combo Box

Video 1 (2:26)

This video demonstrates the problem and suggests a simple method of preventing it.

Video 1 (2:26)

Nifty Access YouTube Thumb Nail
Play Video

… …

A more satisfying Solution

A more satisfying Solution

Video 2 (1:48)

Adding a Combo Box and setting it up with the Wizard. The first video showed the problem and a simple solution, this video and the following ones demonstrate a much better and more useful solution. What you do is add a new combo box which is “unbound” that means it’s not attached to the underlying data, when you make a selection in it, it does not affect the data, the selection triggers some code that moves to the record you have selected.

Video 2 (1:48)

Nifty Access YouTube Thumb Nail
Play Video

… …

Pointing the combo box to the Customer Records

Pointing the combo box to the Customer Records

Video 3 (4:16)

We now have a working combo box, however it’s displaying the customer ID and not the customer name. This video shows you how to change the record source to the actual customer table using the customer table as a look up. This video also demonstrates the usage of the column widths property of combo box.

Video 3 (4:16)

Nifty Access YouTube Thumb Nail
Play Video

… …

Pointing the combo box to the Customer Records

Pointing the combo box to the Customer Records

Video 4 (3:46)

Synchronize the new Combo box.
The new combo box locates the record you select just as it should. However if you move to a new record with the navigation controls at the bottom of the form, then the customer name displayed in the combo box does not change. This can be corrected by synchronizing the contents of this combo box to the form records with some code in the “on current” event of the form. See the fourth video below for instructions on how to do this

Video 4 (3:46)

Nifty Access YouTube Thumb Nail
Play Video

… …

Tidying up the Form

Tidying up the Form

Video 5 (2:15)

First thing to do delete the dangerous control.
Demonstration of formatting multiple controls
Demonstration of the “format painter”

Video 5 (2:15)

Nifty Access YouTube Thumb Nail
Play Video

… …

Adding a Text Box to display the Customer ID

Adding a Text Box to display the Customer ID

Video 6 (4:03)

This Video demonstrates accessing the combo box column property and displaying data that is normally hidden from view. Includes a demonstration of using the expression builder.

Find more Northwind Blogs in the Menu Below:-

Video 6 (4:03)

Nifty Access YouTube Thumb Nail
Play Video

… …

More Useful Stuff HERE:-

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.