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 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.

Customer Record Error
Video 1  (2:26)
Orders Combo Box
This video demonstrates the problem and suggests a simple method of preventing it.
Video 2  (1:48)
A more satisfying Solution
Adding a Combo Box and setting it up with the Wizard. T
he 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 3  (4:16)
Pointing the combo box to the Customer Records
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 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 5  (2:15)
Tidying up the Form
First thing to do delete the dangerous control.
Demonstration of formatting multiple controls
Demonstration of the “format painter”
Video 6  (4:03)
Adding a Text Box to display the Customer ID
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.
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn