Sample db using conditional formatting with a boolean (yes/no) field. (Access 2002-2003 format and '97 format)

Sample db using using a list box to build a sql statement to use as a recordsource for a subform. (Access 2002-2003 format)

Sample db using the running sum feature. (Access 2002-2003 format)

Sample db with a simple function. (Access 2002-2003 format)

Sample db takes common data and consolidates it into a single field. (Access 2002-2003 format)

Sample db using using a popup form to enter date range for a report and display this date range on the report. (Access 2002-2003 format)

This illustration shows one of the main upsides to using SQL Server (or other database sever), as a back-end to an Access front-end:

Sample db that returns focus to the original control after the secondary control executes code. (Access 2002-2003 format)

Sample db uses a combo box to filter records in a form. (Access 2002-2003 format and Access 97 format)

Here's a older version of a db I did - Was wondering if anyone had any suggestions / ideas on how to improve / add functionality to it... I know the project form needs work and and the 'tag' bit is unfinished... Any other ideas?

Sample database with a form and subform and their associated one to many tables (Access 2002-2003 format and Access 97 format).

To set the default values for controls, select the control on the tool bar and press F4. The default value properties list will appear. I like to turn off all of the 3D stuff by resetting some the values like 'Special Effect', 'Border Style', etc. This way I don't have to manually change them after the fact...

http://img2.freeimagehosting.net/uploads/ecc0816db3.gif

Simple db used to print labels. (Access 2002-2003 format)

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=6405&d=1090579677

Sample database showing one way to add 'All' as an option to a combo or list box control (Access 2002-2003 format).

:)
ken

Sample database with simple code linking to a table in another databse via code.

When I set up an MS Access database in a multi-user environment and need to have a front-end on each client I will deploy the application with 3 basic .mde files. The first is the back-end file copied to the server which contains all of the data tables. The second is the master front-end file and is also copied to the server. I refer to this file as ‘master’ front-end because it is not executed but is copied to the client’s local workstation when the database is first deployed and when a new version of the front-end needs to be distributed. The third file is what I refer to as the front-end ‘loader’. This is the file that the end user runs on their local workstation when they want to open the database. As far as they know this loader file is the actual database. I usually email them the loader file which they save to their desktop.

The first time the user executes the loader file it copies the master front-end to the local workstation, launches the local copy of the front-end and closes itself. The next time the user wants to open the database, providing there is not a newer version of the front-end available on the server, the loader file skips the step where it copies the master front-end over to the local workstation and simply opens the local front-end and closes itself.

The final scenario is where a new version of the master front-end needs to be deployed and has been copied to the server. The master front-end, and hence the local copy of the front-end, has been set up with a database variable that I refer to as the front-end version number. When a new version of the front-end is ready to be deployed I change the master front-end database version variable and copy it to the server deleting the older version.

Now when the user wants to run the database, they execute the loader file, it compares the front-end version numbers in the local and the master front-end files. When it finds they are different it replaces the older local version with the newer master version. Then again, it executes the local front-end and closes itself.

All of this is pretty standard but here's the problem; What if the user tries to open the local front-end file directly? None of the version checking routines will execute and the user will continue to run the older version of the front-end!

To overcome this I pass a token from the loader file to the front-end that must be validated in order for the front-end to open. If the user tries to run the front-end directly, the token will be absent and the front-end will not open.

In the loader file I use the command line option '/cmd' in the shell() function to pass the token to the front-end. When the front end opens, it checks the token with the complementary command() function. If it fails to receive the proper token, the front-end advises the user of the error and closes.

Code

In the loader, after the version checking routines have executed, the code that opens the front-end looks something like following:

strMSAccessAPP = "C:\Program Files\Microsoft Office\Office11\MSACCESS.exe"
strClientFEFile = "C:\LocalFiles\FE.mdb"
strFEToken = "MyTokenValue"

strCommandLine = strMSAccessAPP & " /cmd """ & strFEToken & """" & " " & """" & strClientFEFile & """"

intX = Shell(strCommandLine)


In the front-end the code that checks the token looks something like:
If IsNull(Command) Or Command <> "MyTokenValue" Then
MsgBox "This database cannot be opened directly!"
Quit
End If


Of course the front-end and loader files need to have the shift key disabled and be compiled into .mde files to protect the token.

--Note-------------------
I have included sample front-end and loader files. All that you should have to do is copy them into a folder and go into the loader file (holding the shift key down so that you get to the code), and change the string vars to your file locations. When you open the loader it should open the FE. If you try to open the FE by itself it should error and close. Or if you change the token value it should error and close...

The following database (2003), demonstrates how to pass a value to a form or report when it opens by using the forms openargs property.

Not so much a sample database but several good videos:

http://www.youtube.com/results?search_query=Microsoft+Access&search_type=

Sample database with basic custom security objects (Access 2002-2003 format).

(User name: John Doe, Password: password)

Sample db with example of how to disable the shift key when the database opens. (Access 2002-2003 format)

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

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

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.

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

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:


'Configure these==========================
'Front end file name
Const cstrFEFile = "MYDatabaseFE.mdb"

'Location of master FE file on server
Const cstrMasterFEPath = "I:\MySharedFolder\"

'Location of FE on local machine
Const cstrClientFEPath = "C:\LocalFiles\"

'Location of MS Access .exe
Const cstrMSAccessAPP = "C:\Program Files\Microsoft Office\Office11\MSACCESS.exe"
'=====================================

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:


C:\Program Files\Microsoft Office\Office11\MSACCESS.exe /cmd "MyTokenValue" "C:\LocalFiles\FE.mdb"

Then when the front end opens, the following line of code checks to see if it exists:

If IsNull(Command) Or Command <> "MyTokenValue" Then

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.

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

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