If you like my Tools, please donate so I can keep on developing.
thank you
Access Consultant

Over the years, I have written a lot of code for Access. I bring my Anywhere features into many database applications I develop. I demonstrate this as a free presentation to user groups, companies, and organizations ... in person or remotely ~ so wherever in the world there is an audience, I can be there ~ contact me for details,

Microsoft MVP
Access Basics Video Tutorials Learn VBA ListFiles Database Random Picker Error Handling Reference Database Whistles & Bells Help Examples Crystal's Analyzer Code Documenter

Anywhere Notes, Attachments, and Contact Management, Microsoft Access
by Crystal

Access Tools

Anywhere Notes, Attachments, and Contact Management, Microsoft Access (available for Access 2010/2013)

  • Anywhere Notes -- attach a memo to any field of any table. It is important to separate memo fields from the rest of your data.
  • Anywhere Attachments -- associate a File or a URL with any record of any table. If the file is an image, the image is displayed.
  • Use database properties to store and retrieve information since they don't get lost and are persistent.
  • Code to convert attachment fields to external files so Anywhere Attachments tables can be populated from existing data and attachment fields can be removed.
  • Use my Analyzer, the ultimate free Data Dictionary Tool, to create a table of tables.
Download Anywhere and Contacts by Crystal
the sample database to demonstrate the Anywhere features is my Contacts database which I freely share.

Place all 3 files: the FE, the BE, and the Attachments directory from the ZIP file in the same folder. Open the FE. It will automatically find the BE when it is needed ~ or not; there may be a bug in this code so you may need to use the Linked Table Manager for now. I will make this better when I release a version of this application that also includes Time & Billing. Since the database is split, you will be able to simply replace the Front-End ~

Cascade Delete is ON! so you can get rid of the sample contact data quickly by deleting records in c_Contact. If you decide to use the Contact Management programs for real data, you will want to take Cascade Delete off the relationships.

Anywhere Attachments Anywhere Attachments: "Attachment" files are stored externally. When a file is chosen, a copy of it is placed into the Attachments directory for the database. Currently, the code forces this path to be: CurrentProject.Path & "\Attachments\". Ideally, the Attachments directory will be under the BACK end ... just not in the code yet to look there.
Anywhere Notes Anywhere Notes: Memo fields are isolated into their own table (which can be put into a separate back-end).

The form, fc_templateAnywhere is to serve as a guide to pattern after for adding the Anywhere features to forms.
  • Three fields must be defined in the RecordSource: TID, RecordID, and RolliD.
  • Copy and paste the 2 command buttons, cmd_Attachment and cmd_Note, along with the 3 hidden controls for TID, RecordID, and RolliD to your form. If you are not using RolliD, define it as CLng(-99)
The popup features are launched by the Click Event of each command button using a function call on the Property Sheet ~ so no code to modify behind any forms. This makes the Popup tools very easy to add.

The Anywhere features provide greater flexibility, reduce chances for corruption, and keep the database file size lower.

bas_Crystal_Properties_0806_130410_0429 contains code for using database properties. Code in Custom_SetDefaultProperties can be customized for your database.

The Anywhere features use the following database properties:
  • local_TID
  • local_RecordID
  • local_RolliD
Critical to the Anywhere features working right is properly filling c_Tables, which defines TID (Table ID) and other information for each table that will have Anywhere Notes and/or Anywhere Attachments. I plan to develop administrator forms that makes setting things up easier for you.

c_Tables can be initally populated from a_Tbls, which can be imported from my Analyzer. Import a_Tbls (make sure there is only ONE Analysis in the Analyzer -- a_DBs defines DbID; Cascade Delete is On). If you used the 2010 version of my analyzer, you can run a_qApp_c_Tables without modifying. If you imported the list of tables from a previous version of my analyzer, you will have to remap some of the fields.
Download my Analyzer
(long page -- wait a minute then press F5 to refresh and it should be at the download section)

To convert data from existing fields using the attachment data type to external files, customize run_SaveAttachmentsToFiles, which converts files from one field of one table. This is in the mod_helper_Anywhere module which contains general functions and subs for the Anywhere features.

Also coming will be a 2007 version of this template ... probably when I release the Time & Billing template. I tested this download last night remotely on 2007. It couldn't link to the back-end. I had to remove some of the tables from the BE that USED TO HAVE attachment fields. I have written features for my Analyzer that create objects from the Analyzer data collected ~ just need to loop through all my databases with my code documenter and find them ;) just kidding, I know where they are; more a matter of having time to spend enhancing my Analyzer again.

I love hearing your comments and suggestions!

Get your applications in shape with private training! If you want to master Access, Excel, Word, PowerPoint, Windows Phone development, or Camtasia, contact me, I can help you. When we work together, you have the benefit of an expert sitting at your side showing you exactly what to do and correcting mistakes as you make them ... without taking any space ;) or the distraction of my being in your presence. I connect to your computer you see what I do and I see what you do, and we talk I am virtually there :) We build what you need, it is fun, and you learn!

Copyright © 2013 Crystal Long | Last updated October 2013 | Contact Webmaster for help with your web site