In most, if not all, of my MS Access databases, sharing data between forms is a necessity. This article explains a simple method that I like to use.

(article continues after sponsor spot)

Here is the overall plan:

• create a simple form
• populate the form with enough text boxes to hold all the data that will be shared
• open the form when the system starts, and make the form's Visible property False (eg. DoCmd.OpenForm "FormName", , , , , acHidden)

A slight twist that I actually use as my most common method, is to have a "Main Menu" form that doubles as the data holder. The Main Menu is always open, even when covered by whatever other form is open. I put the various textboxes on the Main Menu form, and set their Visible properties to False.

To fill the data in and use it, we use the following style in any VBA code.

• To set the data:
  [Forms]![frmMainMenu]![textbox_name] = somedata
• To read the data:
  local_variable = [Forms]![frmMainMenu]![textbox_name]

This method works with parameter queries as well. In the criteria cell in the query, just put [Forms]![frmMainMenu]![textbox_name].

Here is one simple example of how I have used this technique via some VBA.

The InputBox function is a quick way of getting some input from the user. It is limited though, in that it cannot do things like use a pre-filled combo box to limit answers. Let's say that in your system you frequently need to ask for some input, and you want to use a combo box. However, you do not want to add that combo box to the various forms that may need the data.

• create a simple form
• put the combo box on the form
• put one or more command buttons on the form (eg. "OK", "Cancel")
• code the command buttons with error checking code
• use the "Share Data" technique to place the resulting data on the Main Menu
• in the calling form, call the new form in dialog mode (eg. DoCmd.OpenForm "InputForm_Name",,,,,acDialog); by calling it in dialog mode the little input form will not allow the user to continue until they click one of the command buttons
• pick up the data from the Main Menu

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy