Welcome to the Access Forums Code Bank. This resource is here to help you add functionality to your Access applications or for you to post your own solutions to allow others to add functionality to their applications. It differs from an FAQ because many of the posts don't answer even occasionally asked questions. The posts on here are likely to provide self contained functionality you can adapt and add to your application rather than cover specific tips, techniques and gotchas. If you can't find what you are looking for here please check out the FAQ. If you want to post a question please take a few seconds to read this post.

Fistly since this is a technology forum - we love rules:
#1 - Any code\ attachments should be commented to explain what is going on (and perhaps why).
#2 - A summary should be provided at the top of the post to give an indication of what the code does, in which circumstances it might be used\ problems it might solve. Perhaps an outline of some of the techniques used that might be of interest to people even if the main purpose of the code is not.
#3 - All code must be tested and verified to work. If anything further is required (e.g. a SQL Server stored procedure) this should be made clear up front and, if at all possible, provided as part of the post. Similarly, any required library references should be detailed.
#4 - PLEASE DON'T POST QUESTIONS IN THIS THREAD - JUST CODE\ APPLICATION SAMPLES. IF YOU HAVE A QUESTION ABOUT ANY OF THE POSTS PLEASE CREATE A NEW THREAD AND LINK TO THE RELEVENT POST OR PM THE ORIGINAL POSTER. Or you could post something here. Basically it would be nice to keep this thread as concentrated with information (rather than questions) as possible.
#5 - (New rule - just thought of it ) Please ensure that your code does not infringe any copyrights or intellectual property. If you use code from anywhere else please ensure you have permission to do so and credit the source in full. You'd want the same if it were you!

So - if you have something that you think is useful to other people please share it - all members are free to do so. There are thousands of really useful code snippets and database examples on this forum that are tricky to track down. This resource is intended to keep a decent sample of these in one place.

Index
Customised Message Boxes By: Pootle Flump - including creating your own forms and using the API to adjust the inbuilt message\ input boxes.

Twin Listboxes By: Pootle Flump - demonstrating one method of moving values from one listbox to another.

Excel Automation By: Pootle Flump - lots of ways of presenting data in Excel to impress your boss\ clients.

Password Protect Form By: jwalker343 - restrict access to a form by username and password.

Error Log - V2 link to more explanations. By: jwalker343

Writing an error log to a text file By: Pootle Flump - one idea for tracking errors in an easily transported, application independent file.

Late Binding versus Early Binding By: Pootle Flump - Some of the Excel Automation code adapted to use Late Binding.

Sequential Numbers/Auto-Reference DAO/One of many Unlocking Strategies By: izyrider - & other wizardry (dynamically set a library reference, locking technique in a disconnected environment)

Search Form By: nckdryr - Techniques for a flexible search & reporting form.

Search Form 2 (updated) By: nckdryr

How to Purge Orphaned Records across Databases By: M Owen - by use of heterogeneous queries.

DB User's Computer Log By: nckdryr - using the inbuilt JET UserRoster. Never heard of it? Neither had I. Find out who's using an mdb/mde file.

Class-Providing Library: Create a self documenting class library By: izyrider - without the need for a copy of VB6.0. Encryption, XL & WSH samples thrown in for good measure.

Count Work Days in a Month By: RedNeckGeek - Find out if a date is a working day accounting for weekends holiday dates.

Find the information about References used in MSAccess By: georgev - When needing to find a GUID to be able to enforce it to be turned on for an application to work correctly on different machines.

Differentiating between MDB, MDE(1), MDE(n) By: izyrider

VB Script to Clone an mdb/mde file By: pkstormy - clone a source mdb with the user's login name and launch the cloned file so users don't utilize the same source mdb file.

GetUser Routine to Retrieve LoginID By: pkstormy - a secure way to return the current logged on user to MSAccess.

Report Form and Exporting to a csv example By: pkstormy - an example showing how to export data to a csv file via a form.

Creating/Refreshing ODBC DSN's in MSAccess By: pkstormy - example of how to programmically create an ODBC DSN in vba.

A fun Dice, Cycle Racing, and Simon game in MSAccess By: pkstormy - some fun example games developed in MSAccess.

A sample importing routine By: pkstormy - an example of an importing routine utilizing a browse button to obtain the file.

More fun in MSAccess By: pkstormy - 3 more fun MSAccess games.

Visual Basic Games By: pkstormy - 11 strategy type games developed in Visual Basic.

Search Form Alphabetically By: pkstormy - an example of a search form with alphabetical buttons.

Calendar Control Example By: pkstormy - an example utilizing the Calendar activeX Control.

Write Conflict Example By: pkstormy - an example showing how to actually get a write-conflict error.

Updated Calendar Control Example By: pkstormy - another updated version of utilizing the Calendar activeX Control.

Remote Sharing Example By: pkstormy - how to see which users are utilizing which SQL Server tables via MSAccess.

Right-Click Menu Example By: pkstormy - a simple right-click popup menu example.

Security Demo DB WITHOUT using Access ULS By: Grafixx01 - a security demo example mdb file.

ActiveX Calender Control By: nckdryr - another example using the Calendar activeX Control.
--------
Programmatically obtain information about your project's references

Start up utility automating many deployment options dependent on the databases state (development, initial MDE & deployed). Includes text file logging - alternative to the above error log.

Validate user based on their windows login. Also a simple way to prevent users accessing inbuilt Access menu items and control box.

Utilise the windows file import dialog box in your applications.

V2 from the same author.

NOTE - this index is out of date (I am working on it!) so remember to scoot on to page 2 and have a nose around. Post #26+ still to be added to index.
NOTE - New addition to the board: Sloppy Snippets. These are code samples that do not necessarily meet the above criteria (e.g. they might not be commented, may require some jiggery to get to work in your environment etc.).

pootle flump ur codings are working excelent.

Summary - If the inbuilt Input and Messages boxes are not flexible enough for your needs (you want something more attractive, need different buttons, want users to input several values or need an Input box with Password ***** formatting) you can create your own dialog box to capture the information.

The attachment (BespokeDialog.zip) shows how to:
Use a form in Modal\ Dialog state (causing all other code execution to cease until it is closed\ hidden).
Treat a form as it actually is - a class - by defining public properties that can be read by code referencing the form.
Passing arguments using DoCmd.OpenForm
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
EDIT 2006_08_02 - Alternative to bespoke form for creating an inputbox with a password mask:
http://pub13.ezboard.com/fvisualbasi...icID=314.topic
EDIT - 2006_08_04 - And another:
http://www.tek-tips.com/faqs.cfm?fid=4617
which also links to:
http://www.danielklann.com/excel/hid...a_inputbox.htm
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
EDIT 2006_08_03 - You don't need to implement a bespoke messagebox form to differentiate between a user not entering anything in an input box and clicking OK rather than just clicking Cancel. Kudos to Wayne: http://dbforums.com/showthread.php?t=1208469.
More info: http://support.microsoft.com/kb/199824/EN-US/, http://www.google.co.uk/search?hl=en...nG=Search&meta=
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
EDIT 2006_08_04: Customise msgbox buttons. API code originally taken from http://www.trap17.com/forums/how-cus...ox-t30397.html written by TPFWebmaster for VB 6.0. Adapted for VBA. Unfortunately, after playing with it I've found that it errors (or I do) as both VBA code and the original in VB6.0 and I am not certain why or how to fix. Attached below as API_BespokeDiaolog_B.zip. (Note this is no longer the case - see EDIT 2007_07_04).
Popped a new version of BespokeDialog.zip on too.
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
EDIT 2006_08_04: Customise msgbox icon and position msgbox using co-ordinates: http://www.shrinkwrapvb.com/msgboxex.htm
This code is written by Ray Mercer for VB6.0 and is available for use and adaption within your own projects however not for redistribution without express permission. I plan to email Ray and ask if he would consent to me adapting for VBA and posting here (fully credited of course). Anyway - if you are working in VB6.0 or can adapt it yourself then you are in business.
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
EDIT 2007_07_04: Hurrah for R. Alan Diamant (azjazz) - he has fixed the error in the API bespoke dialog box. API_BespokeDialog_C.zip is (TMK) a fully functioning VBA version of the VB6.0 code referenced above and replaces the old, failing version.
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------

pootle flump ur codings are working excelent.

Summary - You can allow users to select multiple items from a list to create a new, bespoke list.

The attachment shows how to:
Use a form in Modal\ Dialog state (causing all other code execution to cease until it is closed\ hidden).
An alternative to using properties to return values from a form - instead creating a function method.
A few techniques for reading listboxes.
Some code to make an attractive button effect. NOTE - THIS PARTICULAR CODE IS NOT MINE - IT WAS POSTED BY ANOTHER MEMBER OF DBFORUMS ALTHOUGH I AM NOT CERTAIN WHO IT WAS. PLEASE COULD YOU LET ME KNOW IF THIS IS YOUR CODE SO I CAN EITHER CREDIT YOU OR REMOVE IT AS REQUIRED.

Requires a reference to DAO 3.6.
Izy has a much more friendly technique for achieving the same effect which he has posted on the board previously.

pootle flump ur codings are working excelent.

Summary - The below code automates excel and populates the spreadsheet with data from Access. You can use this as an alternative to DoCmd.TransferSpreadsheet as it allows you greater flexibility in particular for formatting your worksheets.

The attachment shows how to:
Copy data from Access to Excel in one fell swoop (rather than a cell at a time)
Automate another application (in this case Excel)

Requires a -
(EDIT 2006_08_03 - removed requirements list. These are now detailed with the code)

I consider myself told
Code removed, zipped mdb added.
------------------------------------------------------------------------
------------------------------------------------------------------------
EDIT 2006_08_03 - replaced zip file with new one - Added Intermediate excel automation code (more formatting features, applying autofilters, subtotals and creating a bar chart).
Template Excel file added and more descriptive comments in the code.
------------------------------------------------------------------------
------------------------------------------------------------------------
EDIT 2006_08_03 - replaced zip file with new one - Added more Intermediate excel automation code (pivot tables).
I am pretty clearly an Access developer automating Excel. I realised this when I remembered once reading that Excel has builtin functions to get (for example) the last populated cell. As such - if you spot any dumb or inelegant code in the attachment please let me know.
------------------------------------------------------------------------
------------------------------------------------------------------------
EDIT 2006_08_06 - RoyVidar sent me on a path that took me to some nice MS links re automation in general and also Excel specifically. There are a couple of nice functions in this code too (the resize method of the Range object in the create chart link looks especially nice).
General Automation:
Office Object Model Documentation
Excel Automation:
Automate Excel
Navigating Excel Object Model (applies to other object models too)
Create Excel Chart
Word Automation:
Automating Word (not read link)
Word Mail Merges (not read link)
------------------------------------------------------------------------
------------------------------------------------------------------------
EDIT 2006_08_06 - Have a look here for some more specific automtion information regarding Late and Early Binding and also the difference between New and CreateObject.
------------------------------------------------------------------------
------------------------------------------------------------------------
EDIT 2007_01_06 - Posted V4. For this I tidied up some of the code & comments. Added a simple GUI. Adjusted the code so it is less particular about the template not being available & also will not overwrite previous exports. Added a fourth module splitting data out onto seperate sheets.
------------------------------------------------------------------------
------------------------------------------------------------------------

pootle flump ur codings are working excelent.

Summary:
This code adds MINOR form protection, it prompts for a username and then password and refrences it to the code.


this attatchment shows:
a basic VB code that, using MSA, prompts the user for a username, and then a password, and checks BOTH to the code. if they are correct it allows entry, if incorrect it shows "incorrect password" and then closes the form.

*off topic* i think everyone should post their code as an attatchment like i did, saves room on each page. other than that great idea!

Summary - Unhandled errors on remote client machines can be very hard to diagnose especially if you are not certain what the precipitating factors are or you can't replicate the error in the lab. In this case more information is required. The attached database contains code to log errors to a local file (you can't log to tables as the error might be network\ connecton problems and you don't want to overwrite your FE if it contains valuable information). If the error log file does not exist then it is created the first time the code is run.

The attachment demonstrates how to:
Use the Open statement for file IO operations.
Use public enums
Raise errors.

I wrote this totally blind - I had no idea (and still don't!) how other people have approached this problem and so don't know if my solution stands up or is pretty weak. Comments welcome (though ideally in a PM, new thread or with a post of something that is better). It is fast though. I tested it by "generating" 1 million errors (simulating my standard app performance ) and it completed the logging in a barely perceptible time. I think I didn't even bother timing it in the end.

pootle flump ur codings are working excelent.

Breakin the rules, I know...

I'm stickying this thread for now. I had some ideas about format and the like which basically revolves around providing an index and a standard format as you have proposed. Unfortunately I had a little life explosion here and had to back off my ancillory projects for a minute.

In the mean time, have at it!

Summary:
Automate Excel using Late Binding. One of the modules here converted to Late Binding.

Late Binding Vs Early Binding:
Early Binding is when you tell the application up front where you are going to get your objects from. In late binding the application only finds out as it creates the objects at runtime.

Early Binding Pros:
Faster execution of code (the application uses it's preruntime knowledge of the objects to speed up method and proerty calls)
NB - I have read a little that this applies to inprocess calls (to DLLs like ADO and DAO) but execution is slower for out of process calls (calls to EXEs like Excel, Word etc).
Intellisense.
Object model available for viewing in Object Browser.
Integer parameters for the methods and properties are conveniently titled as constants.
Many code errors are picked up at compiletime.
There is no late binding in .NET if you plan to port your skills.
Dynamic help.

Early Binding Cons:
DLL Hell - this is the biggie - this is where the application was informed that it would be using a version of objects that is not avaialble on the client machine. This may be because they are not installed (in which case there can be no binding, early or late) or they are of an earlier version than the application expected. NOTE - it should not be a problem if the client has a later version of the objects. A simple example is if you develop an application that references the 11.0 (2003) Excel library and distribute to clients running XP - the application will crash. Remember though that the converse (XP Ap, 2003 clients) is fine.
Difficult to have confidence in your application if you cannot be sure about your client's workstation set ups.
Different syntax to what some (e.g. ASP developers) developers might be used to.
Run the master copy of your app on a workstation with a later version of your references and Access will upgrade them for you without warning.

Late Binding (pos and Cons):
Generally the opposite of Early Binding.
The only other pro is that the syntax more closely resembles vbscript (as used in ASP).

EDIT - 2006_08_07 - added note re writing Late Bound code.
If you look at the Pros of Early Binding you will notice that many of the advantages are when you are writing code. As such it is a good idea to write your full procedure Early Bound to make use of intellisense, picking up compile time errors, the object browser, help and debugging constants and then convert your (working) code to Late Bound. If you review the LateBinding.zip file you will see that I have commented all the sections that I changed and retained the Early Bound code as comments. As a side note, I would recommend the same principle for when you start writing vbScript (just be aware that there are some further syntax changes beyond the late bound changes).

NB. This is why writing this sort of stuff is so valuable! I might have read a book on COM and numerous articles over the years but I still learned something new researching this post. Relevent links below:
http://word.mvps.org/FAQs/InterDev/E...ateBinding.htm
http://blogs.msdn.com/cambecc/archiv...01/145309.aspx
http://support.microsoft.com/kb/245115/
http://www.codecomments.com/archive2...-6-523102.html
http://support.microsoft.com/kb/247579/EN-US/
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
EDIT - 2006_08_06 - Decided to test the idea that late binding to Excel is quicker than early binding (LateBindingVsEarlyBinding_V1.zip). Rather frustratingly the factor that seems to have the most affect is the order that you call the early binding and late binding procedures. However, the late binding method does seem to be faster.
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
EDIT - 2006_08_06 - inspired by another of RoyVidar's links I started looking at the difference of New and CreateObject. Must admit I initially thought they were the same. They are not. This is not strictly an Early\ Late Binding issue (simply that you don't get the choice in Late Binding) but I thought it interesting:
MS info on CreateObject Vs New - Link supplied by Roy (Must confess - I am going to have a fresh look at an error that has blighted about 0.5% of the client machines in our organisation armed with this info)
An FAQ page from VB2TheMax (love that name - unashamably geeky)
CreateObject Vs New from ITToolbox
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
EDIT - 2006_08_07 - Some more comments from Roy - here is a nice link for those of you that are getting a little Bamboozled by the tech heavy links above:
Early & Late Binding
I added a note towards the top on recommended technique to write Late Binding code (again thanks Roy).
I am also adding an internationalisation change to the SQL in LateBindingVsEarlyBinding_Vx.zip (guess who's to thank?).
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
EDIT - 2006_08_07 - Added LateBindingVsEarlyBinding_V2.zip. This tests DAO (an inprocess DLL) for late and early binding speed. Again, the order the tests are run is the main difference and can make either of the methods run faster. I think I need to rethink my testing procedure....
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------

pootle flump ur codings are working excelent.

DemoSerial002 is a tidied-up version of an old demo posted here.
Format is Access-2000

items of potential interest inside:
-obtain sequential numbers (not autonumbers!) - inspired by Mike Owen.
-reference DAO automatically - inspired by Wayne Phillips.
-optimistic concurrency locking - generic.

module utilitySerials is extremely verbosely commented.
form frmDemo is uncommented (it is trivial, and only there to exercise the utilitySerials module).
routine doDAO() in frmDemo is uncommented! it's a mixture of uncommentable magic and the trivially obvious. if you are curious, you should be able to review it's gestation over many months in old posts by Wayne, Pootle and others. bottom line is that it is small, it is fast, it works, and it has cured one of my longest-lasting headaches.

izy


LATER: i found the source thread for the totally magical 0,0

another protection form by me, includes explination of code and how to access, for beginners in VBA and access:

http://jwwdesigns.net/other/pass.zip

In response to Pootle's prompt, here's one for the more basic level programmer and those new to the whole access/vb thing...

Ever wanted to add a search form to your database. Here's a quick way to do it . This may not be the most elequent way to do it, but it works. It will at least allow the not-so-SQL-inclinded user to perform a (semi) custom search.

After due consideration, I'll add this query which demonstrates using the Find Unmatched Records Wizard to delete "orphaned" records in tables split across separate database files.

DELETE * From OrphanedRecordsTable WHERE (OrphanedRecordsTable.DesiredColumn IN
(SELECT OrphanedRecordsTable.DesiredColumn
FROM OrphanedRecordsTable LEFT JOIN [C:\WhateverFolder\WhatEver.mdb].MatchingRecordsTable ON
OrphanedRecordsTable.DesiredColumn = MatchingRecordsTable.DesiredColumn
WHERE (((MatchingRecordsTable.DesiredColumn) Is Null))));

And as a corollary: You have a built in SELECT query to pull data from tables across separate database files ...

SELECT * FROM OrphanedRecordsTable INNER JOIN [C:\WhateverFolder\WhatEver.mdb].MatchingRecordsTable ON
OrphanedRecordsTable.DesiredColumn = MatchingRecordsTable.DesiredColumn
WHERE (MatchingRecordsTable.DesiredColumn=XXXXX);

Summary: Allows a user (or just the admin if set up correctly) to view who is using the database. I find it particularly useful when I need to do some maintenance.

This is based off of Microsoft's Code with some alterations to allow it to be used in a textbox (instead of having to use the Immediate window).

**Note: This can have troubles if someone is inside the database after using the shift-bypass.

prompted by pootle's XLS extravaganza further up this sticky, i decided that this afternoon would be a good time for me to update my export-to-XLS stuff. i HATE to write this sort of code twice and i LOATHE copy/paste of old code into a new application and then attempting to understand my thinking from three years back as i try to inter-twine the old code with the new app.

since i owe pootle a post to this sticky - a little demo Class library featuring pootle's XLS stuff seems reasonable.

the ZIP contains a test/demo app: CPLtest.MDB, and a small library: iLib.MDB
iLib is a Class-Provider that feeds late-bind Classes to applications.
CPLtest exercises iLib and illustrates the calling convention.

the major 'facility' in iLib is iExcelClass which is a late-bind Class implementation of some of pootle's XLS stuff plus a couple of my own little twists. i hope iExcelClass doesn't break, but it is my first-draft of the code so there might be some holes: please PM me if you find anything bizarre (XLS Exists/Not is not brilliant - suggestions welcome).

a library with only one book is not that exciting, so i added a couple of old-faithfulls from my production library: iHashClass; iUserClass (need CAPICOM, WSH registered on the machine, but no A-reference required).

for the demo to have half a chance of working:
A2K or later
CAPICOM (iHashClass late-binds so won't error unless you call iHash)
WSH (iUserClass late-binds so won't error unless you call iUser)
save iLib as c:\temp\iLib.MDB
save CPLtest.MDB anywhere you like
ensure CPLtest.MDB has a functioning reference to iLib.MDB (should show up in object browser etc etc)

don't be disappointed with the load-speed. move to MDE and it is fine
MDE process is: convert iLib to your A-version; make iLib.MDE; delete reference in CPLtest.MDB to iLib.MDB; close; add reference to iLib.MDE in CPLtest.MDB; convert CPLtest.MDB to vour A-version; run /decompile on a copy of CPLtest.MDB; make CPLtest.MDE

even though the following warning appears in the code several times, i must repeat it here:

' /// CRITICALLY IMPORTANT
' /// in 'real life' iLib should be 'compiled' to .MDE
' ///
' /// if you are experimenting, you can use iLib as MDB (as CPLtest does), but
' /// BEFORE you run CPLtest.frmMain with iLib in MDB format do the following:
' /// Open CPLtest.MDB
' /// Alt-F11 (opens the Code window)
' /// menu: Tools/Options/General - select BreakOnUnhandledErrors
the reason is that at least one exit routine in iLib can generate runtime errors (handled with Resume Next) as it tidies up stuff like hidden non-interactive Excel instances and .mousepointer --- in MDB any setting other than BreakOnUnhandledErrors will break into the code leaving the instance and the eggtimer stranded if you click the wrong button. in MDE format this does not happen of course.

STRONGLY RECOMMEND that you do not have any open XLS with unsaved data if you are using iLib in MDB form.
INSIST that you read through all the code before you run CPLtest.frmMain
FIX for most crashes is a reboot, so don't have unsaved data sitting on your machine when you run this demo.

all the usual caveats: this is demo code and includes my first draft of a medium complexity class module developed on a sunday afternoon with a beer in my left hand - no guarantees ! !

izy

PS: if you have other Classes that should be added to iLib, PM me

This module contains 3 functions.

The first counts the total weekdays for a month, excluding holidays.

The second determines whether or not the current date in the loop is a holiday. It's preloaded with typical American holidays.

The third counts the number of weekdays that have already passed in the
current month, excluding holidays.

These functions come in handy on reports that are run daily, where
you want to have a header that says
"Day 7 of 20 Shipping Days This Month"

I long ago stopped using these, as my reports are no longer in Access,
so I can't promise they're bug free.