Access Cookbook - Ken Getz [134]
The tblFlexAutoNum table provides AutoNumber values for one table only. You may wish to extend this technique so that there is some provision for recording multiple AutoNumber values in tblFlexAutoNum. Alternately, you could create a separate AutoNumber table for each flexible AutoNumber value you need in your application. You can hide these tables in the database container either by prefixing the table names with "USys" or by checking the Hidden setting in the tables' properties sheets.
The example form concatenates the first five letters from the LastName field with the AutoNumber value. Although this convention can be helpful in sorting, it can also have a negative side effect: the AutoNumber field will have to be changed when the LastName field is changed. We included this functionality simply as an example of the kind of flexibility you have with this technique. In general, it's not good practice to combine multiple pieces of information in one field.
If you want to create AutoNumber values in two different copies of a database that could then be merged together at a later time, you could use a site-specific alphanumeric prefix to your AutoNumber field. Since each copy of the database would use a different site prefix, you wouldn't have duplicate values. However, you could also accomplish this goal by using a composite primary key comprised of two fields—the AutoNumber and the site ID.
Unlike Access AutoNumbers, the custom AutoNumbers in this solution are retrieved only when the record is about to be saved, in the BeforeUpdate event. If a user starts editing a new record and then cancels, no AutoNumber will be "wasted" on the canceled record. This technique therefore is useful in situations in which you need your numbers to be consecutive, such as for invoice or purchase-order numbers.
6.8. Back Up Selected Objects to Another Database
Problem
You use a standard backup program to save your databases, but this works only at the database level. This is fine for archival purposes, but you often want to back up individual objects. How can you get Access to display a list of objects and allow you to save selected ones to an output database you specify?
Solution
This solution shows how to create a form that selectively saves Access objects to another database. It works by using a multiselect list box and the CopyObject action.
Open frmBackup from 06-08.MDB (Figure 6-11). You can use this form to back up selected objects from the current database to another database. Select one or more objects from the list box, using the Shift or Ctrl keys to extend the selection. When you are finished selecting objects and have specified a backup database (a default database name is created for you), press the Backup button. The backup process will begin, copying objects from the current database to the backup database.
Figure 6-11. frmBackup backing up selected database objects
To add this functionality to your own database, follow these steps:
Import frmBackup from 06-08.MDB to your database.
Call the backup procedure from anywhere in your application by opening the frmBackup form. For example, you might place a command button on your main switchboard form with the following event procedure attached to the button's Click event:
DoCmd.OpenForm "frmBackup"
Discussion
To see how it works, open frmBackup in design view. The form consists of a list box, two text boxes (one of which is initially hidden), and other controls. The list box control displays the list of objects. One text box is used to gather the name of the backup database; the other is used to display the progress of the backup operation. All of the VBA code that makes frmBackup work is stored in the form's module.
The MultiSelect property