Code Samples The code samples here are ones that I find myself using in almost every project. Common as they are to me, I get questions asking how to do these things. I hope you find them useful and pass them along to others in need.
| Note: To copy a code sample, place your cursor in the code area and press Ctrl+A, then Ctrl+C|
Mailing More Than One Attachment Using Outlook
This code takes advantage of late binding so you do not have to add a reference to the Outlook library which may vary from machine to machine. If you want to use early binding, add the Outlook library to your references and substitute the Object data type for the one in the comments.
Just remember not everyone will have the same version of Outlook that you do. That's why I use late binding. With late binding, the VBA compiler will select the "Outlook.Application" that is available on the machine. With early binding, the version is hard-coded. If it does not match your version, it will fail.
Count the Number of Workdays Between Two Dates
I'm often asked how to determine the number of workdays between 2 dates. Assuming the workweek is Monday through Friday, this code will do it.
It can be edited to accept a different work week. Use a Holidays table to discount those days.
Note: I used ADO in this example only because, at the time it was written, Microsoft said DAO was dead. Silly me, I believed them.
Turn Off AllowDesignView
For some reason not known to me, Access 2000 - 2003 defaults all forms to show the Properties box in form view. While that might help in touching up a form's design, it can be embarrassing it you forget to set the "Allow Design Changes" property to Design View Only. It's time to quit going through each form to manually make that change. This code will iterate through all forms and change the setting for you.
Turn Off Subdatasheets
Starting back in Access 2000, the tables had a new property called Subdatasheet. This remarkable feature gave you the abiltiy to drill down to related records when viewing a table in datasheet view. There is a little plus sign (+) to the left of the first field in each record. Clicking on that plus sign would open a "sub" view of child records for that particular record.
Unfortunately, that feature can really slow down the performance of not only the database as a whole, but also the loading of forms. I generally turn it off because the databases I create usually don't allow the users direct access to the tables. Forms and reports are all they see so the users don't have a need for the Subdatasheets. Why sacrifice performance unnecessarily?
If you open a table in design view and right-click on on the table, you will see Properties as a choice. Select it. By setting the Subdatasheet property to [None], you turn off the feature for that table. But this must be done for each table. There is no global setting. By using DAO TableDefs you can loop through all the tables and set the property.
Be sure to turn off the Name AutoCorrect Tracking under Options>Current database before running this code. If you don't the Subdatasheets will be set up again when you compact the database.
Here is how it is done (Note: If you are using Access 2000, be sure to add the DAO library to your references).
Close All Forms/Reports But The Current One
When designing a database I might have several forms and/or reports open at once. Using the Find/Replace feature in the code window can also open more objects if they contain the string you are searching for.
So there I am with 20 to 30 objects open, and I want to close all but the one I selected. Clicking on Window/Cascade will organize the objects so that the Close icons are easily seen, thus letting you click on each one to close the objects, but that takes forever.
I wrote this code to do all that "clicking" for me. There is an option for saving without a prompt that comes in handy when you want to save them all or pick and choose which ones to save.
Convert Decimals to Fractions
This one is from Clive Williams. I've seen a few examples of how to do this conversion, but they have all been rather kludgy. Clive's elegant function does it perfectly with very few lines of code. Definately one to keep in your code vault.
Give A Caption To Each Form
One of the of the things I can count on every time I develop a new application is that I will forget to set at least one form's caption. If you do not fill in a caption the user sees the form name in the title bar (or tab in 2007 and later). Because I name my forms with a prefix of "frm" and a name that makes sense to me, using that as the form's title is not going to be the best choice. Something like "frmStartup" should be "Main Title".
In order to make sure all my forms have a proper title as the caption, I run this routine to loop through all the forms and use an InputBox to set the caption.
Note that I gave myself the option to leave the caption blank. Subforms don't need captions. The user never sees those.