Ken's Sample db's

Re-usable Password Form

Sample db with example of how to create a multi-use password form. (Access 97 & 2002-2003 formats)

Mouse scroll

Sample db with example of how to prevent the mouse wheel from scrolling to a new record in a data entry form. (2003 Version)

LookUp Values Table & Form

This sample database shows how to manage basic lookup values for an infinite number of combo boxes and / or list boxes with a one table and one form.

Sort values

Sample database shows how to dynamically manage a field used to sort or prioritize records.

Front End Version Control

Overview
The following describes the methods the associated files use to deploy a front end to local workstations. There are two files; the sample front end (MyDataBaseFE.mdb) and the 'loader' file (MyDataBase.mdb). A back end is not considered in these samples. Any method used to link to a back end from the front end should work.

The use of the two files are simple. You make some minor changes to the loader so that it knows what the file names are and where they are located. Then send it to the end user to copy to their desk top. When they want to run your application this is the file they execute. The front end is copied to the server. This server copy is now referred to as the master front end. When you have a new version of the front end, you delete the old one on the server, put the new one in its place and go into it and change the version number. The next time the user runs your application, if there is a new version, it gets copied to their local workstation and is opened.

The Database Property
The master front end really needs only one element to make it work: a database property called 'DatabaseVersion'. By default an MS Access database file does not contain this property so it must be created. The form 'zfrmVersionControl' will create it automatically when it is opened if it does not already exist and assign it a value of '01'. This default value of '01' can be changed in the code'. In addition to creating and assigning the initial value, zfrmVersionControl is used to update it. It can take any format desired as it is simply a text data type. So the version format you use can be '1' or '1.1' or even 'V1.1'.

The Loader File
The loader file has one code module called 'basVersionControl'. In it a function called 'fncVersionControlStartup' is called from the autoexec macro when it is opened. When executed it checks to see if the front end exists on the local workstation. If not it copies it over, opens it and closes itself. If the front end does exist then it compares the 'DatabaseVersion' values between the local copy of the front end and the master front end on the server. If they are different it deletes the front end on the local workstation, copies over the master front end from the server, opens it and closes itself. If a copy of the front end does exist on the local workstation and the 'DatabaseVersion' values are the same then it simply opens it and closes itself.

To set up the loader file go into the module 'basVersionControl' and change the following constants:

The code comments are self explanatory. The destination of the front end should already exist on each workstation.

The remaining functions and procedures in the module 'basVersionControl' are called from within the module.

The Front End File
The sample front end file has three objects; The forms 'zfrmVersionControl' (described above) and 'frmStartUp' and a code module called 'abasStartUpRoutines'.

The form 'frmStart' is simply a sample start up form which happens to have a text box control that displays the current 'DatabaseVersion' property value.

The code module 'abasStartUpRoutines' does two things, neither are required for the front end loader to work. It contains one function called 'fncStartUpRoutine' which is called by the autoexec macro when the database is opened. The first thing it does is check to see if the database has been opened by the loader to prevent a user from opening the front end directly. This is done by checking to see if the correct command line parameter has been passed to the database when it was opened. To do this the front end the loader file builds a command line string similar to the one below. The command line switch '/cmd "MyTokenValue"' passes the command line parameter to the front end:

Then when the front end opens, the following line of code checks to see if it exists:
If this evaluates to true then the code simply proceeds to the next step which is to open the startup form. If it is false the it advises the user of the condition and exits the database.

Again, this is secondary functionality in these sample files and not required. The only thing that is required in the front end is the database property called 'DatabaseVersion'.

The shift key should be disabled to prevent the user from by passing this functionality. Since this functionality does exist in the sample front end file you will have to hold down the shift key to get to the objects.

Summary
Modify the loader file so that it knows what the file names are and where they are located and send it to the end user. Copy the form zfrmVersionControl into your front end and set up the database version number. Put the front end on the server and you are done. When you have a new front end, change the version number in it and copy it to the server deleting the old one.

Modify File Created date

Sample database used to modify a file's creation date:

Object Manager V1

Overview

Object Manager is used to hide or display database objects using the objects 'Description' property. This is useful when the database object windows become cluttered and difficult to navigate. For example if you are working on objects related to customers you can display only objects that have been placed in the 'Customers' group.

Set up

Object Manager uses three forms. The main form is zfrmObjectSelectionMain. The other two forms are subforms, zsfrmFormObjects and zsfrmObjectGroups. The only other objects are two tables, ztblObjectGroups and ztblObjects. To use Object Manger simply copy these forms and tables over into your .mdb file. and open the main form.

How to use Object Manger

On the main form there are two tabs. The Groups tab is the list of groups you have for objects. In addition to the name and group field there is a View indicator check box. Select these check boxes to see objects in the associated group when you click the View Selected button on the left side of the main form.

The second tab contains the list of objects in your database. This list is a static copy. To view the objects currently in the database click the Get Objects button. The first column is the object type, the second is the object name. The third column is used to indicate to the save routines that a change has been made to the objects group. When you make a change in the fourth column, the Group, it automatically turns the Update check box on. This prevents the save routine from having to change all of the objects in the database. The Group column combo box is driven from the groups listed on the Group tab.

The basic usage is as follows: Add objects to the database as needed, leaving the description property blank. Then open the main Object Manager form and set up your Groups. This is usually done once the first time you use Object Manager and then only as needed the change objects groups. Then go to the Objects tab and click on the get objects button. When all of the objects appear you select which group you want them in. Then click the Save Objects button. At this time when you go back and look at the objects in the database window they will have the Objects group in the description column. This is how you put objects in groups.

To hide or display groups open the main form and on the Groups tab select which objects you want to display and click the View Selected button on the left side of the form. And close the main form. To view all objects or hide all objects click on the associated button.

Comments

The sample included uses the Northwind database to illustrate Object Managers functionality. To use it click past the splash screen and click on the 'Display Database Window' button.

This is version 1. In Version 2 the plan is to add a check box at the bottom of the form that automatically gets the objects when the form opens so that you don't have to click the Get Objects button. Also in version 2 I plan on setting it up where you can add descriptive text in the description in addition to the objects group.

There are no means to manipulate Page objects as I rarely use them.

If you do a Get Objects and the current description has a comma in the text it will error.

Comments on this version and suggestions for version 2 are solicited.

VBA Graph Help file

Good collection of samples from MS. The link to the original source is included in the db...

Sample database illustrating one method of creating audit trail:

Sample DB with code module to control MS Access application window.

Change these numbers to change the window size:


Change the following formulas to change the window placement on the screen:

Sample Office Pool application (2003)

Thankyou Ken, these examples have really helped me with Access basics.

Sample db showing simple use of the before update event (Access 2003)