Microsoft Access Team Blog

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.

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

Some of my "common" functions (forms/reports/modules) get updated as time passes by. That's why I copy objects (that I need) to new project from the latest development step.

#include (from an existing library, eg. MDB, ASCII-files, etc.) would be fine in Access 14. In development mode #include should create temporary objects. It should import the objects during translation to MDE (ACCDE).

I also use simple a template to create new reports plus automatic format for forms.

I miss expression builder in A2002 VBA code window. Is it available in A2007?

Clint C: I'll to extract some objects & code to implement incremental search for forms, and objects & code to create automatic reports based on form's RecordsetClone... and e-mail all that stuff to you.

Oops: I'll to extract => I'll try to extract

There were few times where I wanted to have a templates but ended up simply doing it from scratch, with copying bits & parts from other applications, simply because I found it too problematic to abstract away the functionality into black boxes that was also easy to use.

I see a lot of patterns and think that this would be more useful as a class module, but quickly discover that it just adds another layer of complexity.

One example:

I can't quite create a instance of form then bind it to a subform control. If I could, this would be very useful as I could then create single form to handle common kind of data (say a form for Address entry) then implement it everywhere with modifications to its properties set by the parent form, so we can enjoy different validations in different parent forms without rebuilding the whole functionality.

To take this further, consider cascading comboboxes. This would be perfect thing to create a object out; implement a class module of cascading combobox, add it to the list of available control for future drag'n'drop to which I only need to supply the controlsource and rowsource at build time.

So basically, I want to reuse forms & controls, but am hampered by the limitations and the fact that it's not fully object-oriented.

HTH.

I'd like to see the following developer abilities added:

Scan/Replace of Fields so that all references to the old field name are changed.  All references include inside queries, including queries inside controls, inside forms, inside code, etc.  

I have situations whereby I need to rename fields and have it rename all occurrences.  This must be done before we can even migrate to use SQL Server.

We have legacy code created over 10 years ago, and the original design used "spaces" in field names which was a very bad practice, but we must keep the code running.

We would like to migrate to use SQL Server which doesn't allow spaces in field names, as backend database, but cannot do so with fear of breaking the application by missing a location where a field with a "space" in the name is referenced.  Scan and replace is only possible in the VB Editor.

Going along with my previous post, the same would actually hold true of all access objects.  We have Table Names with spaces, as well as Form Names, and Query Names and Report Names with "spaces" in them.  So we really need a better way to rename an object and have all references to the object renamed, including references inside program code such a docmd.open acform "My Form with a Space" to docmd.open acform "MyFormWithASpace" (as an example)

From my previous post - I have received a few  questions by email - I'll answer them here if that's OK

Why use unbound forms and controls ? Doesn't that take away from the RAD mentality ?

Speed is the main reason for Goldsoft using  unbound forms and controls - if a user clicks on a button to load the Product Form and you have 200,000  products listed in a Combo box on the form then the form takes sometime to appear on screen - by loading an unbound form with an unbound control the form appears almost instantly - then you supply the RowSource(s) to the unbound controls and it becomes a background function to populate them

The RAD mentality is replaced with an automated VBA code writer in Goldsoft

How does the HCS locking method work ?

The HCS method turns record locking into a "edit-save-write" process - a single record is locked for the duration of the process - once the save (or cancel_edit) button is clicked text files are used to control the "write queue"

This method allows the developer to use a single front end on the server without stress (I cap it at 50 concurrent users)

Can beginner programmers use Goldsoft ?

Not a good idea - Goldsoft is specifically designed for Access developers wanting to break into the multi-user market with confidence or clients wanting to reduce the support time on their databases

M. David Matney: Maybe you could try V-Tools:

http://www.skrol29.com/dev/en_vtools.htm

With V-Tools you can even handle Import/Export specifications!

Clint C: V-Tools should be implemented in Access.

Can beginner programmers use Goldsoft ?

Not a good idea - Goldsoft is specifically designed for Access developers wanting to break into the multi-user market with confidence or clients wanting to reduce the support time on their databases

Yes (for beginners), because if they will start or use goldsoft, they will be mislead and will not become a true software developer in the future.

For a true msa developer, they will surely shave their head if they will use your Goldsoft.

its not really a good example for both novice and true msa developer.

Present your Goldsoft to Kallal,tejpal,ken getz, g robinson, a browne, gunderloy and other msa developers and lets see if what are there comments.

I'm very sure, they will shave their heads too!

ClintC:

Once again, do you plan to add an automatic front end updater utility in MS ACCESS that automatically updates the front end in client workstations? I am not a developer and I believe that would help implement ACCESS databases in workgroups.  Thanks. Sam Caro.

I think sharing code with the community is a good idea, but building a bunch of new UI for that purpose would be less usefull than other developer needs.

I do a mix of rewriting code (because it's quicker than finding where I used it last), importing from another app, and maintaining a common code library.  The common code has the standard stuff like string functions, isloaded (this should be built into access vba), relink functions, username and AddReference code.

I agree with Toni Maura, the fact that you can't change a library mde without recompiling the app is a hastle.  I tend to add references on the fly for this reason.  Another drag is you can't compile an app that calls a function in a library mde if you're adding references at runtime.  I use eval() and correctdb(a function I wrote that determines whether to call currentdb or codedb) to get around this...what a hastle to debug!

On the subject of library mdes, they are really hard to debug.  You have to keep closing the app and opening the library mdb then make changes then build mde then open app and test.  It would be nice if you could set reference to the mdb and make changes to library code and have them save.  When you do this, the changes are lost.  I can't say how many hours of rework I've done because I forgot which project contained the code I was editing.  If you modify code in a library mdb from within the app it does not get saved!

I too think it would be nice to have the expression builder available in vba again.

Another nice thing would be the ability to open a query in design from code.  Like the 'definition' menu option that brings you to a function when you click on it's name in code.  The definition of a query would open when you click on the docmd.openquery "qry_name" in code.  I spend a lot of time pulling up action queries in db window by name from code.

It's also a drag that the query design is modal when accessed from the recordsource properties list of a form/report design.  I am forever opening a recordsource to find that the query is based on another query so I have to close and go find it in the db window.  On that note, how about the ability to drill down to a query design from another query design?  Sort of like how you can open table design from relationships windows.

PS...ClintC, Any news on 'Access Web Services' in v14?

HTH,

Josh

Oh yeah...back to your question about form templates...  I agree with someone who says styles for forms would be nice.

As for the current way for setting default control properties, is hard to edit the defaults.  It would be nice if there was a way to save the default properties of a control and import them from one db to another.  Like the border, font, colors etc for a textbox.  You can edit these defaults by selecting the control from the toolbox and editing the properties window before you drag in onto the design.

I think that's how it's done, I rarely do it because you have to do it all over again for each mdb.

Can you make it stick with the access options, or make it importable from one mdb to another like import specs can be?

Thanks,

Josh

First, I love this forum... I develop in Access on an 'as needed' basis.  Some years, it's all I do, other years I don't touch it at all... so just reading this page has gotten me charged up again about designing solutions in Access for my employer!  

My standard elements that I copy over as a template are:

1. Sentinel : a form and some code to basically force users to exit the application via my coded exit button.   Helps to ensure my 'application quit' routines run.

2. Bypasser : a module that gives me a way to Enable or Disable the ability to hold the shift key down to bypass startup code.

For expressions, I use very few if any.  So yeah, I usually will employ ones I've used before, so I go back and recall where I have it in a development copy of another .mdb file.  If that's not an option, I google it and see what comes up.  

In regards to the 'wish' that's been posted in other comments for a 'built-in way to automatically update FE clients'... I do this too, but it's not automatic.  It's more of a convoluted recipe that involves VERSION tables on the FE and BE sides, some startup code that compares version #'s, dates, and a CRITICAL UPDATE bit... and finally if needed, a call to DoCmd.Execute to call a script (.BAT) that does the copy magic.   It's not perfect, but it works in my situation.

To all who posted to this article so far, Thank you!

Troy

I tried to use library databases in my early days with Access and found them too difficult to debug and constently having to switch databases was seriously annoying so I gave up.  I copy code from one app to another.  The code is rarely used without some modification so this method works fine.  There are a few things I use frequently:

1. subform to relink the back end

2. subform to browse to a file

3. subform to brows to a directory

4. code module to export objects as text.  I seem to be plagued by corruption issues so this is a life-saver since many corrupted forms can be exported as text and saved.

5. my own version of the standard switchboard form.  I have made some modifications that correct the close function and add functions such as the ability to open a form in datasheet view.  I also have a "long" version that has 12 items instead of 8.  I change its look in each application so that it fits in with the theme of the other forms.

6. I have forms/reports/tables/queries that I import as a set when I have the need to manage a lot of small lookup tables.

7. I have forms/reports/tables/queries that I import as a set when I have an application that needs to run groups of reports which eliminate the need for the user to run them one at a time.

I have my own database of date calculation functions that I copy as needed.  These are designed so they don't need to change from use to use and could exist in a common code library.  I turned this into a sample that I have posted in Access forums and demonstrated to my local Access users group.

And finally, I use the FMS sourcebook tool.

Ya tenemos, como un pastel en un escaparate, sin tocar, sin oler, sin probar, sólo ver, las primeras