Do you reuse expressions, forms and reports?

We have developed our forms which works as pop up multi-lingual combo box, Query Form we also have developed a Transaction engine which do processing, reporting module, Authorization module etc. These are all table/data driven and developed since Access 2000. Which we reuse them in all our custom applications and are ported to Access 2007.

I maintain a folder with 'sample' apps - often single function that I use when testing out code snippers for specific tasks. When I need to repeat the same process the I will import the Form/Report/Query/Module etc. I do however have one particular Model that I use as a base for nearly all apps - this is the backbone of most apps and then the customers mods are added as needed. It would be good to be able to have a specific template that could be developed and then re-used (with all the controls/logic intact) I am now in the habit of transfering my DB Objects to to Text (TransferText Method) and then building a text based template...

I still keep the old Windows Cardfile.exe around as a quick file index. I have it full of often used expressions I can reference when that "How did I do that before?" moment comes up.

Jeff,

Do you mind sharing your expressions with me? If you don't, use the email link on the page to send them to me. I'm trying to wrap my head around how many there might be and what patterns are most useful. Thanks

ClintC: I like this Blog. Could you add in MS ACCESS 14 an automatic front end updater utility that automatically updates the Access front end (FE) to client workstation(s). That would be very good for developer and power users. Users would always have the latest version of FE in the workstation without need of third party utilities. Thanks. Sam Caro.

I have a template database that I use as the starting point for all new systems. This contains the following:

A splash screen and the logic to check for linked data.

A main menu switchboard

An application class object that I use to setup and store global variables

Various modules containing standard string, math and other useful functions (linking tables, file dialog)

A list view form and sub-form

A single-record form (with Find) and subform

An A4 landscape report (with margins and fonts setup)

An A4 portrait report (with margins and fonts setup) I also use the free MZ Tools VBA utility. I particularly like the multiple clipboards for code snippets and the enhanced search that allows you to see all matches. For 2003 versions I also use FMS Total Access Analayzer to double-check my code. On the visual side if Access 14 were to implement Styles it would make formatting and updating controls on forms and reports for consistency much easier. Each contol would be linked to a style (either built-in or custom)and a WYSIWYG Style Editor would allow you to create and update these. A community code exchange that allowed users to share/exchange code would be great, but the most commonly used functions should be available out-of-the-box. Peter

most of the time i do like Peter Schmidt. A template database used as the starting point for all new systems. This contains the following: A splash screen and the logic to check for linked data. A main menu NOT SWITCH BOARD An application class object that I use to setup and store global variables Various modules containing standard string, math and other useful functions (linking tables, file dialog) All applications have a customized main form that is, a form with a data flowdiagram for the main menu, so users are identified with how the data flows throught the database and how are arws related. We have the FMS speel checker thats is very important before deploying our apps. We are in need of a new conditional formatting for forms and reports like the excel conditional formatting. And of course, better linking to SQL, will be great.

I develop in access 2003. My MAIN application is designed for companies that uses for billing purposes, products, costumers ... moreover i develop other applications for individual costumers... The MAIN application (mde actually 9,5mb) uses three libraries:

1-Code library:

All my applications share a 'mde' library (actually 2,1Mb. compiled) that contains tables, code and forms. The code: Generic error handler, text functions, api functions, table relink, Dao and date funtcions, menus functions ... the forms are: About form, Calendar form, Euro conversion tools, Help form, Piracy form ... and others, the table contains errors messages and actions to take in every error ... I reference this 'mde' in my applications and call the functions and forms as usual.

2-Report Library:

I create a report library (mde, actually 2,7 compiled) where i have all the common reports and also the personalized reports (every costumer has his own invoice format and is not allways possible reuse the report). I call the reports from the main application using an 'IN' in the select stament in the reportsource.... I create this library

because the main program grow so much with the reports in it...

3- An independent code-form library where a rent programer make me some tasks like create txt files from accounting data to send to other programs ...

he create clases that i call from the main mde file... For the other applications I have a 'Generic'-template mdb (acttually 1,6mb) that i use to begin an application (this one call too the code library). I usually copy and paste reports and forms but it will by useful use templates, now i am working in reusing the same form (with the new clausule ...).

I also use FMS utils Analyzer, CodeTolls and sourcebook. The main problem with libraries is if you make a change and recompile the library you have to recompile the application too and distribute all to your costumers, also i have problems calling forms from a library because the called form uses the library menu bar not the application menu bar ...

1) We have a standard blank report that we use as a template. It has a bunch of preconfigured properties like GridX/GridY, margins, header, footer, page numbers, etc... 2) We have a module for all our databases with functions and procedures used in many of our forms and reports. It contains things like:

- functions for postal code and phone number formatting

- an age calculation function

- a function for saving and retrieving database settings from a table used for that purpose only

- a function used in a textbox for creating a comma delimited list from a SQL statement (Names of Children: Joe, Sally, Dave). Useful when a subreport is not appropriate. 3) We have a module for all of our database with procedures we use during development. Often these functions are hooked up to a keyboard shortcut through the autokeys macro. Examples:

- a procedure that finds and selects report based on some or all of the name. Imagine a list of several hundred reports. You hit F3, an input box prompts you to enter all or part of the report name, (with wildcards if you want), and when you hit enter it will select the first match. Hit F3 again to jump to the next match.

- a procedure that centers sizes a control - usually a line or a label - to the width of the report and center aligns it

- a procedure to that will take any number of selected textboxes on a report and make corresponding labels that can be copied into the page header or group header

- a procedure to take a vertical stack of textboxes on a report and transpose them into a horizontal line of textboxes (useful immediately after dragging a list of fields from the field list)

- a keyboard shortcut for turning a control source like "PaymentAmount" into "=SUM([PaymentAmount])" 4) We have developed a utility that searches through all databases on our server and compiles a list of the reports in those databases. We then use that to see if there is a report that we can reuse instead of creating from scratch.

I tend to simply write all my formulas from scratch. (does that make me "super smart?") The only one I seem to have trouble remembering is the row-specific aggregate where the aggregate itself is filtered or otherwise dependent on data from that particular row. Having to use the enclosed quotes can get a little tedious and the address convention changes per argument. That to say: cleaning up the expression writing to make it easier to write espressions in the first place would be very helpful. The "build expression" window is particularly opaque to intuition as there is little to no help immediately available on how to use the functions. Increased user interface in this area would be most helpful. On the subject, the "build expression" window is particularly small. I believe it only shows three lines. When writing complicated formulas, I often have to resort to working in a text editor just to be able to see the whole formula in a "tabbed" format (as in the pattern of standard code). I'm often nesting at least several levels deep, so a tabbed format with color coding in a bigger window would make me more comfortable. I think a library for user created expressions would be very handy. I currently keep my expression snipets in OneNote. Regarding forms, a "screen width" indicator directly on the background in design view would be helpful. I regularly use maximized forms and have to flip back and forth to see if I've gone over the edge of the screen. And I must admit, even though 2007 encourages navigation via the navbar, I still prefer to build my own navigation macros activated by button. I think it's more intuitive for more complicated databases where a slough of possibilities overwhelms the user. I use this so much that it would be nice to have an auto "create-button-that-runs-a-macro-that-closes-this-form-and-opens-another-form-and-can-be-edited-later" option. I often run processes during navigation from one screen to another, so the ability to simply add a sub-macro to a particular navigation macro is important. Back on the subject of functions, I find myself missing the more versatile Excel functions when I'm in Access. Automated analysis of data is important in my work, especially with SOX in place. A larger library of functions and more help on how they're useful would be much appreciated.

I have built applications that are all over the map in terms of depth of features and overall purpose. I gave up trying to maintain a repository of code bits, because the code bits often evolve, and some apps using function fnXYZ() wouldn't appreciate the new version...it gets comlicated. I always create an application starting from some other application that has the most commen scope and navigation style. I then toss out most of the content leaving the core of my framework and some example forms, reports, modules etc. I use Find and Replace from Rick Fisher as well as MZ Tools VBA utility. I have a well developed naming convention structure which allows me to rename objects safely, as needed. Sure it'd be good to have a shared code repository. But there are already many such out there, I use them once in a while. I'm not positive that one hosted by Microsoft (I think that's your questions implication?) would be needed. What we 'need' is our toolbars back (as an option) and fully up to date Access - SQL Server goodness. That would make many of us 'more productive' in a very big way.

I think a code sharing repository is a great idea. The more we use the same code and design the easier it will be for clients to find a developer to support their systems. Clients want to employ someone who can hit the ground running. I use verbose VBA code in unbound forms since I specialize in multi-user systems. My system writes the code according to the design of a table. Record locking is achieved via the HCS method. Have a look at the free downloads on www.hockley.com.au - all the code is open source so that other developers can tweak it to their liking (without changing the purpose or outcome) - they can also change the look and feel to suit their style or a particular industry A library where developers could pull down fully usable pieces of code as well as form and report designs would help beginner programmers no end.

I use a template App to start with, Toss out what I don't want. Also I use MZ Tools VBA utility A true god send!The code snippets is good the Error Handler feature is amazing

Also Speed Ferret for find and replace and a very strong naming convention (Leszynski/Reddick Guidelines for Access) A must for good coding and being able to do find and replace safely (Nothwind etc could use a update in that regard!!!!!) I also agree with michaels

"What we 'need' is our toolbars back (as an option) and fully up to date Access - SQL Server goodness. That would make many of us 'more productive' in a very big way."

I'd like to be capable of using VB.NET besides VBA because of the following advantages: a. managed code

b. capable of using the whole whopping .NET functionality

c. better maintainable code: 'ON ERROR GOTO' sucks big time and 'TRY ... CATCH' is KING.

d. even better security thankt to compiling to .DLL.

e. VBA streams might nog be that stable. I've had bad experiences with Access 2000 under Windows 98 of corrupted VBA streams. Only the non-documented /Decompile switch could save my project, other wise my complete project would have fatally crashed.

I noticed that Access12(2007) was already presented and discussed in this blog back in 2005, which is more then 2 years ahead of time (see for example the blog post from 21 November 05 ). According to this time frame, if Access14 is due in 2009, it would have already been presented to the developer community.

Do you have an estimation of when we will be hearing the details of Access2009?

Thanks