Below are checklist items (which anyone is free to add to), which I hope help troubleshoot any problems with a slow mdb as well as some tips on fixing slow-running computer problems:

Before reading the troubleshooting items, I wanted to make a special note here on using unbound forms for an mdb. Unbound forms is a great technique to improve the speed of your forms and especially ideal in a large-recordset environment. Although it should not necessarily be used to avoid troubleshooting why you've suddenly encountered a slower running mdb, it is something you should consider at design time as designing unbound forms will take you a longer time in developing (as well as modifying) your application. But the benefits of speed on your forms is something that will make you (and your users) much happier.

--------------------------------------------------------------------------------

Some things to check/do for speed issues:

1. Check for a slow network card in your pc (especially if you've upgraded Operating systems.) Slow Network Card + New Operating system/Other new installations can = slow performance.
2. Check your profile file (if large, this can slow things down.)
3. Check your msconfig for any new files which are now loading up during startup due to new installations (and using RAM). -Start->Run, then type msconfig. Look at the Startup tab. Some items can be unchecked to not load in startup (i.e. Adobe, Quicktime, etc...) I found that Adobe seems to a big culprit which ALWAYS tries to configure itself to load in the startup tab (and I've never found a specific need to have Adobe do this - you can still open your pdf documents after unchecking Adobe in the startup tab, they may just load a tad slower.) You'll need to reboot after making any changes and then check the box to "Not show MSConfig changes any more" in the message box which shows after re-boot so you don't continue to see the message box until you make MSConfig changes again.
4. Index/re-index fields in your tables.
5. Check the free space on the drive with the mdb and your drive.
6. Compact/Repair the mdb. Run the JetUtil program against your mdb. Use the /decompile switch when opening the mdb. Make sure you've debug-compiled your code.
7. Create a new mdb and import all the objects (last resort type item.)
8. Minimize code in forms since newer versions of Access loads all the code in the forms.
9. Create a simple mdb and compare speed.
10. Move the mdb to your local drive or a different network drive or folder.
11. Make sure you have sufficient permissions to the networked drive.
12. Watch for multiple comboboxes/dlookup and other commands which cause forms to open slowly. A big culprit is having multiple, multiple subforms (usually in tabs) which all load in the opening of your form.
13a. Use criteria to limit opening "ALL" the records in your form and instead records based on criteria (this will help if you have multiple, multiple subforms and are loading all the records in them as well.) Instead, if using tabs for your subforms, set the sourceobject of the subform in that tab in the OnChange event of the tab control.
13b. I like to avoid using the MSAccess Filter option and instead use criteria in the sql statement. I've seen a lot of problems stem from using Filter commands versus criteria. The Filter option seems to be especially problematic when it encounters "bad" type data in your tables.
13. Make sure no backups/virus scans are happening on your computer at the time.
14. Make sure no other scheduled tasks are happening at the time.
15. Try having the network guys/gals connect you to another switch.
15a. If using daisy-chain hubs, definately switch to a switchbox. Daisy-chain hubs can be detrimental for slow data returns on a network environment.
16. Using Windows Explorer, disconnect all network drives and then reboot/connect the drive with the mdb and try it. Then connect/map the other drives.
16a. Re-Link tables.
17. Make sure you have all the references in the mdb in code view. Missing references can slow an mdb down as well as cause other problems with code.
18. Try debugging the code on your computer to see if any errors show up anywhere.
19. Make an mde file from the mdb to see if that helps performance.
20. Have someone else log into the slow computer to see if it's computer related or your profile related.
21. Try a cloning technique (http://www.dbforums.com/showpost.php...6&postcount=19) to help with speed issues (this has worked very well for me as it keeps each user in their own mdb - but you must utilize linked Access or SQL Server tables to use it.)
22. Hold down the shift key and open the mdb to bypass code. If the mdb itself still loads slow, it could be network related.
23. Defrag the harddrive (I personally recommend: PerfectDisk or Diskeeper).
24. Make sure all drivers are working correctly. Since Access is also dependant on a good printer driver, try changing the current default printer and see if that makes a difference. (Note: I've had issues where I had a bad default printer selected and the code in Access bombed out.)
25. Start->Run, then type Services.msc. Look for any new services in which Status = Running and Startup = automatic. New software loaded (ie. especially things such as anti-virus/Anti-Spyware programs, Adobe, CD-Burning software, SQL Server, Disk Management software, Network configuration changes, PunkBuster (network gaming), Steam (network gaming), etc..) often sets itself to automatically start in the services (not to be confused with the MSConfig-Startup tab.) Other than your anti-virus software (which should NEVER be changed), you can possibly change some items to Startup "Manually" (ie. double-click on the item in the listing to change the startup type.) Be careful though when doing this and research on the item you change BEFORE you make it startup manually!
26. Use application tools such as Registry Mechanic or Tune-Up Utilities Pro to find problem areas in your registry and defrag/optimize it (I've found these are very good tools which help with overall computer speed.)
27. As always, run your anti-virus and especially your anti-spyware/malware programs and fix/repair/remove problems which would tend to slow things down (especially spyware/malware as this seems to be a big culprit for slowing things down while viruses are more likely to just destroy things and make your computer or certain applications unusable.) Note here: Internet Explorer is a BIG target for viruses/spyware/malware. You may want to consider Mozilla Firefox as another possibility for connecting to the internet (I use both.) For Internet Explorer, things such as: Add-ons/activeX controls which automatically load in IE have often been the culprit. Firefox seems to handle and protect you better from bad add-ons/activeX controls.
28. Download and run HiJack and post the results on some forums which troubleshoot HiJack created files and show if your system has been infected.
29. Download and run a "Rootkit" tracing program to see if you were infected with a rootkit type program.
30. Although a lot of anti-virus programs look for keylogging programs, if you "suspect" that someone else is tracing your keyboard entries (which I've personally found is rare), this could slow things down. There are special keylogging detection software programs.

Questions: Does it run slow for other users on your computer? If so, it could be something like a missing reference or lots of stuff opening in the startup. Do other mdb's run slow on your computer or is it just this mdb? Do other programs such as Word, Excel run slow? Is there a certain time it runs slow or is it all the time? Are there certain forms loading slow and other forms load faster? Does it just run slow when you're loading the forms or does it run slow when you get behind the scenes and edit code or run a query? Determine if it's the forms by closing all forms and running a query. How many records are in the mdb? What is the size of the mdb? Are all the recordsets closing? Are there any graphics or objects being stored/loaded on the forms.

** Monitor the memory being used by opening Task Manager and see how much CPU time/memory Access uses when it is open (Processes tab). Look for other program's CPU usage. If the Access file keeps growing and growing in size, troubleshoot code for problems. Perhaps multiple forms opening and recordsets not being closed which don't seem as apparent on other computers. Although not recommended, you can right-click on Access in Task Manager and temporarily set the priority higher to see if it has an affect. While an MSAccess is open, the MSAccess mem usage size in the process tab should grow as forms/recordsets are opened. It should NOT continuously grow though (ie. beyond a 100meg for example.) You should see a point where it "steadies" and growth happens very minimally if at all. I once had an MSAccess mem size which continued to grow no matter what. After revisiting the code, I found that I wasn't closing recordsets. Once I did this, MSAccess stabalized at a certain memory usage. An interesting note here: if you re-utilize the same naming of variables (ie. rs for recordsets - Dim rs as ADODB.recordset) and other variable names, versus always using a new and different name, you'll find that MSAccess will actually have less tendency to grow.

Attached are several, several XP hidden secret trick documents in the zip files (FOR ADVANCED WINDOWS USERS), which I found on a website and I've attached to read. There are a few tricks worth reading but I BY NO MEANS have any claim to any of the techniques or accept any responsibility for using any of them (again, only for advanced users as you should know what your doing and the possibility of making your computer unstable or unusable when utilizing any of the techniques found in these documents).

Here are some additional things you can do to speed up your MSAccess forms and retrieving/updating records:

1. Unbound forms. Using unbound forms will speed up your data form entry tremendously! But keep in mind that it will also cost you more programming time. To design unbound forms, you need to write code to retrieve/update/delete data in the tables. Remember those functions you were taught in school to do this? But unbound forms are the ideal route to go especially for large recordsets and a definate for offsite slow connections to your network.

2. Don't have your Main Data form open showing all the records. Instead, design a search form to open first and once the user clicks on a specific record in the search form, open the data form with criteria (ie..."Select * from MyTable where IDAutoNumberField = " & Forms!MySearchForm!MySearchIDAutoNumberField & ""). Listboxes work great for search forms (or open the Main data form using the WHERE criteria). See examples in this code bank for ways to do this.

3. If you have a lot of Subforms on a MainForm, consider making 1 Subform (a shell to speak with no sourceobject) and then populating the sourceobject of that subform after the user clicks a button on the MainForm. If you're using a Tab control where there is a Subform in each tab, consider setting the sourceobject of the Subform in that tab on the OnChange event of the Tab Control.

4. For the recordsource of your Subforms, have it based on criteria where the ID field (that relationally joins the tables) is equal to the IDfield on the MainForm (ie. "Select * from MySubFormTable where IDField = " & Forms!MyMainFormName!IDField & ""). Also, you can avoid having to set the .LinkChildFields and .LinkMasterFields properties by having the IDField on your Subform (you can make it invisible or visible and locked/Disabled) and then set the default value of the IDField = Forms!MyMainFormName!IDField. Make sure to put in the defaultvalue of the IDField or when new records are added to your Subform, MSAccess won't know the IDField value to put in for the relational table (unless you set the .LinkChildFields and .LinkMasterFields values.)

For example, if you open your MainForm with all the records (ie. no criteria). And you have 3-4 Subforms on your MainForm. The MainForm will open much slower versus if you have your MainForm based on criteria (so that it returns only 1 record) and the sourceobject of the Subform's is populated after clicking a button. Even if you decide not to populate the sourceobject of the Subforms (and instead have a static sourceobject), you will notice a speed difference by not loading all the records on the MainForm.

One last thing I thought of quickly if I haven't mentioned it above. For your queries (which are not updatable), you can make them a Snapshot query. Snapshot queries will speed up large datasets.

I'm also going to mention indexing again as this is vital with large datasets. Don't over-index (ie. don't index every field or you defeat the purpose). I once even created a new table, indexed it first before copying data from the old table to this new one, and I actually found I got better performance (Important though - append records to this new table sorted the same way as your indexing scheme!) If you're using SQL Server, there is an option to re-build a new indexing scheme (a little different than MSAccess's indexing.) See SQL Server help on re-building indexes. This helped but I got better results creating a new table with an indexing scheme already established and then appended the records to the new table !!!Appending the records sorted the same as the indexing scheme!!! Believe it or not, this dramatically increased the return speeds on my 5 million recordset db.

This post is being added to. These were a few suggestions I've done in the past where I was working with 5-6 million records. I created this post quickly but I'm hoping there will be input by others added to this post soon.

Also see this link: http://www.granite.ab.ca/access/performancefaq.htm

Here's another link: http://www.granite.ab.ca/access/perf...ldblocking.htm

Note: There's a statement on this site which says regarding a locked *.ldb file:

"To resolve this issue we need a persistent connection to the back-end from each of the front-end workstations. This can be done using a bound form which is always open or by keeping a recordset open at all times.."

This is a BAD idea! (especially on slow/unstable networks and/or external users with slow connections!) If a user opens the mdb file and then minimizes it for 2-4 hours, it will "lock" the mdb for other users if the user has a form open bound to a table! I recommend instead that you use the vbs script on page 2 of the code bank or instead, write a bit of vba code which simply opens a recordset and closes it from time to time (or put a timer event on the form which checks for inactivity and closes the mdb.)

The following is an MSAccess application I created to cycle through an MSAccess (or linked table) calling type table and construct a URL with the phone number field in the database. The constructed URL is then passed into the web browser (for an IVR type system or anything web based) and the web response codes (along with the calling list and who it's calling) are displayed to a neat little form. It uses the timer event extensively in the process. Everything is menu/form driven.

The difficult part was getting it to cycle through the table and increase the next calling times in the table in coordination with the response from the browser. If the browser returns a "failure" response (ie. it didn't make the call because all the IP ports were all busy), that phone record is increased to call back within the next 3 minutes (or whatever time you designate) otherwise it will call that phone number back increasing the next call time with whatever minutes you set it to increase to. (Note: your IVR or other system needs to delete the record out of the dbo_CallQueSQL table if the caller completes whatever IVR code you've setup so the caller is not called back from the dbo_CallQueSQL table.) All calling activity is logged into MSAccess tables.

It's setup to cycle through the IP ports (you determine) and embed these into the URL. If for example you had 4 VOIP boxes (each on it's own IP port), the code would cycle through each of those IP ports until it found one free and use that to create the URL. It could also be used for other outbound calling type systems but if you use it for URL web posting (to make the actual call), you'll only need to change the string on the part which constructs the URL and what IP ports to use.

You can adjust many things in the tables/forms including the start/end calling date/time along with how many minutes to add to call that same phone number back again.

It took me about a month to put all this code together in vba. I had to do a lot of digging to find the right vba code. It's a complete application which does many, many things related to outbound calling. I use this in conjunction with a voxeo IVR system which initiates once the URL is successfully passed and the call is made.

I hope it's helpful for someone else (it's fully commented) or gives them insight when developing this type of system via vba. If you're looking for a good IVR application that is easy to setup, easy to develop an IVR application, comes with 2 free ports, and has an excellent 5 star support team, I recommend the Voxeo Prophecy application. Voxeo support is absolutely the BEST support team I've ever worked with and you can call them and talk directly to their support staff without going through an in-depth IVR menu system (ie. you don't need to push a thousand digits to get to a human voice.)

Well, this isn't so much "code", but it's a handy table that I often import into my databases. It's simply a list of all USPS State Abbreviations and their respective names, cleaned up for easy importation. Enjoy.

Don't they have postal codes too?

an improved demo of the unbound-form handler in #58.

izy

Date values should always be stored in a Date/Time field type. If for some reason you need to store the wording of the date, make sure you also store the date value in a date/time field in that table so you can do your searches. Keep in mind though that it's fairly easy to format the date in almost any display type on the form/report (ie. an expression in the query such as format([MyDateField],"mmm") will return values like Jan, Feb, Mar, etc... So why store the actual month in a field and have to worry about updating it on the form? Sometimes working with the formatting of the date in complex queries can lead to problems and storing the wording makes it easier for the grouping (but the criteria is usually based off of the date/time formatted field.)

I usually store my date values with =Now() which will store the date and time unless there is no reason to store the time. Storing it as Now() can lead to problems in the queries though (see below). You can also use =Date() which will store the date in a m/d/yyyy format and then you don't have to worry about specially formatting the date in the query. What's odd is that queries don't seem to sort correctly on the "default" setting of m/d/yyyy but instead sort correctly on a mm/dd/yyyy setting.

You rarely want to apply your formatting of the date field in the table design as the default setting for that field. I recall a few times where I stored the default formatting as mm/dd/yyyy to avoid formatting it as an expression in some complex queries. You can again, manipulate the formatting of the date in the query with an expression such as: Format([MyDateField],"mm/dd/yyyy") to display a date such as: 03/02/2009. An important note here: if you're having trouble sorting your date field, sort it Asc or Desc under this mm/dd/yyyy expression column in your query. Sorting a date formatted as m/d/yyyy will lead to the days sorted incorrectly.

Another important note is to make sure any search criteria you have in a date field criteria is under the correctly formatted expression/date column in your query. For example: If your query's date column criteria is based off of values on a form (ie. Between Forms!MyReportForm!StartDate and Forms!MyReportForm!EndDate), make sure this criteria is under the correct date column in your query that matches the way you've formatted the date field on the MyReportForm form. In your query, if you put the "Between Forms!MyReportForm!StartDate and Forms!MyReportForm!EndDate" criteria under an unformatted date field (ie. not an expression), you may notice a difference in your totals if your storing the Date and Time values in that date field versus just the Date value. For example, you have a date field in the query which is formatted (either as an expression or in the format property for that field in the table design or query for that column) as mm/dd/yyyy. Your criteria under the date column is >[Enter starting date]. The user enters 1/5/09 when running the query for the prompt. You may notice that it's not correctly returning records after 1/5/09 yet if the user were to enter 01/05/2009, it works.

This info is what I've found in MSAccess 2003 and prior versions. Things may have changed in MSAccess 2007.

Additional notes/comments in this post are welcomed.

Scenario (copied from a post in the MSAccess forums):

I have added this format “IGSP000000” to my AutoNumber field to automatically generate an incremental work order number. The format displays fine, but does not store the data as formatted. For example, I need the stored data for record 1 to appear as “IGSP000001”. Although the data is displayed in the field as“IGSP000001”, it is stored as “000001” without the letters “IGSP”.

How can I get the entire formatted number to store in this format “IGSP000001”?

Answer (the below answer is from healdem):

If it's an autonumbered column you can't. Autonumber columns are integer field types (well in reality they are integer sized as bigint/long datatype).

Is the IGSP a common prefix?.. ie is there any other prefix used?

If you only use IGSP then leave your design as is and always prefix the autonumber column with IGSP whenever the user sees it (ie. an expression such as IGSP: Format("IGSP" & [MyAutoNumberFieldname]), or remove the IGSP before storing the number.

The alternative is store the prefix separately as an additional column in the db. You request the whole reference, split it into its constituent parts and carry on as normal.

The third option is to create your custom numbering scheme and store the whole thing as one value. But that means you have to write your own routines to get the next available number for that prefix or whatever. If the number part has significance outside the system then you may well be best to use this technique. By significance I mean that you must have contiguous numbers with no gaps, or numbers out of sequence.

Just as an aside, you don't store formatted values in a db, you store values and then format them in the front end or the application that the user uses. Like all rules there are exceptions, but you will get off to a better start if you presume that formatting is the preserve of the front end, not the data store. You can do some formatting when you extract the data from the data store, but it should not be formatted IN the datastore. Formatting often results in textual/string representations of the data to a computer the currency value of £12,345.67p is not the same as 12345.67, attempting to store values as a text/string datatype will lead to all manner of problems, reduce the effectiveness of the query engine. Another one to watch for is dates. Store data in the base datatype dates in datetime, numbers in number and so on.

The following example shows how you can embed a query into a subform for users to easily view it. This works very well for crosstab or any type of query (where you want the query results controlled by a form display.)

It especially works well for crosstab type queries where the header "fields" are unknown.

BONUS: Also included is the "browser" shell for easily exporting any query to a filename and then opening that folder.

The nice thing I like about this method is that when the form is open and a user wants to navigate through the records/fields, it works just like Excel does (ie. you can push the down arrow key and it goes down to the next record but stays on that column!)

Neat archiving code to make backups of the mdb tables.

This was created by: Sinndho (I posted it here because I thought it appropiate for the code bank.)

Sinndho comments...
Here you are: this new version can archive records from a local table and records from an Access linked table.

Everyone: Please keep in mind that this program is just a skeleton (if I may say so!) and several important features are missing:
First, an error handling mechanism should be implemented. The structures to detect errors are in place but these are not handled properly.
Second, only a few cases among many other possible situations that could be met in a production environment are handled.

If you want to test the program using the form Archive_Form, the file ArchiveDatabase.mdb can be anywhere on the computer
however the second file Sales.mdb that contains a table that is attached in ArchiveDatabase.mdb must be located in the
C:\Data folder (or you have to change the attachment). As it is the archive database Sales2008.mdb will be created in the
C:\Archives folder but this can easily be changed.

Have a nice day!

Taken from one of the posts in the MSAccess forums:

Below was created by garethdart (I posted it here because I thought it appropiate for the code bank.)

Excel to PDF using Access VBA
Post:
http://www.dbforums.com/6399062-post13.html

HI ST (StarTrekker),

Here is a mod of Ken Puls Excel code which runs as a MS Access function;

Give this a try and see what issues you come up with - I've found that PDF Driver a little temperemental but this might just be the machine I'm on at the moment.

You should consider this a starting point only and I will help to tidy it up but I'm going to be chocca for the next few days so anyone else is more than welcome to chip in if they wish!

It could do with some error trapping and a bit of polishing up but hope it gets you started.

Anyhow - give this a try and let me know whether it's what you were after.

Option Compare Database
Option Explicit

Public Function PrintToPDF_MultiSheet_Early(sPDFPath As String, sPDFName As String, strExcelPath As String, strRecipient As String, Optional strSubject As String = "Quotation", Optional strBody As String = "Dear Sirs,")

'Author : Ken Puls (Excelguru.ca | Tips and pointers for Excel and other MS Office applications)
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from SourceForge.net: PDFCreator)
' Designed for early bind, set reference to PDFCreator

'12th May 2009 - Modifed as access function by Gareth Dart
'Creates an Outlook messsage and adds individual .PDFs of each worksheet
'Changed sPDFPath and sPDFName to function variables
'Also added strExcelPath, strRecipient
'Also strSubject and strBody as optional variables
'Dont forget to add references for Excel, pdfcreator and Outlook

'call PrintToPDF_MultiSheet_Early("c:\", "TESTpdf", "c:\xlsTest.xls", "someperson@domain.com", "Revised Quotation", "Dear Sirs,")

Dim pdfjob As PDFCreator.clsPDFCreator
Dim lSheet As Long

Dim strOriginalName As String
strOriginalName = sPDFName

'Variables for excel
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open(strExcelPath)

Dim olApp As Outlook.Application
Dim olMessage As Outlook.MailItem
Dim fsoTemp As FileSystemObject

Set olApp = New Outlook.Application
Set olMessage = olApp.CreateItem(olMailItem)

olMessage.Recipients.Add strRecipient
olMessage.Subject = strSubject
olMessage.Body = strBody

Set pdfjob = New PDFCreator.clsPDFCreator
sPDFPath = xlWb.Path & xlApp.PathSeparator

If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "PrtPDFCreator"
Exit Function
End If

For lSheet = 1 To xlWb.Sheets.Count
'Check if worksheet is empty and skip if so
If Not IsEmpty(xlWb.ActiveSheet.UsedRange) Then
With pdfjob
'/// Change the output file name here! ///
sPDFName = strOriginalName & xlWb.Sheets(lSheet).Name & ".pdf"
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

'Print the document to PDF
'Syntax changed
xlWb.Sheets(lSheet).PrintOut , , 1, 0, "PDFCreator"

'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False

'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop

End If
'Add the latest attachment to the Outlook message
olMessage.Attachments.Add (sPDFPath & sPDFName)
'Delete the temporary PDF
Kill (sPDFPath & sPDFName)

Next lSheet

pdfjob.cClose
Set pdfjob = Nothing

'Close workbook without saving changes, exit Excel then clear variables.
xlWb.Close False
xlApp.Quit
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

'Display the eMail and allow user to edit then send
olMessage.Display

'Clear variables
Set olApp = Nothing
Set olMessage = Nothing

End Function

* Loop through and populate unbound fields on a form from a table...

Public Sub FillUnboundForm()
On error resume next
Dim db As Database
Dim rst As Recordset
Dim rfld As DAO.Field
On Error Resume Next
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from mytable", dbOpenDynaset, dbSeeChanges)

With rst
If .EOF Then
Else
For Each rfld In rst.Fields
Me(rfld.Name) = rfld
Next
End If
End With

End Sub

* Loop through and save unbound fields on a form to a table...

Public Sub saveUnboundForm()
On error resume next
Dim db As Database
Dim rst As Recordset
Dim rfld As DAO.Field, myfield
On Error Resume Next
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from mytable",dbOpenDynaset, dbSeeChanges)

With rst
If .EOF Then
Else
For Each rfld In rst.Fields
rst.Edit
rfld = Me(rfld.Name)
rst(Me(rfld.Name)) = Me(rfld.Name).Value
rst.update
Next
End If
End With

End Sub

* Loop through controls on a form and construct an SQL statement...

private sub butgo_click()
dim ctl as control
dim strSQL as string
for each ctl in me.controls
if typeof ctl is checkbox then
if ctl.value = true then
strSQL = strSQL & ctl.name & ", "
end if
end if
next
strsql = "SELECT " & left$(strSQL, len(strSQL) - 2) & " FROM mytable"
end sub

I found this in my archives. I can't recall where I got it from though and there's nothing really documented but it is fairly simply coding to do drag and drop with 1 line of code in different mouse events (ie. OnMouseDown, OnMouseUp, and OnMouseMove.) I've seen a few posts on drag and drop so thought I'd post this. Hopefully it will help with any drag and drop routines you're working on.

A new method for disabling the Mouse Scroll Wheel in Access forms

Summary -

A new, free method to disable the scroll wheel in Access forms (A97 - A2003). No DLL required, also doesn't use the flakey VBA subclassing solution that Microsoft provides.

This is also an example of using a new technique that I am working on which brings the ability to run native machine code direct from VBA (in fact, full custom COM objects, without needing a DLL).

MDB download available at the above URL along with further information about the techniques used.

Also see this post: http://www.dbforums.com/6303048-post52.html

This calendar form allows you to write in text values into the date boxes themselves. The calendar is re-contstructed as you select the Month/Year.

With other typical calendars (such as using the Calendar control), you can't overlay the date fields with text boxes and show data in them. With the calendar in this example, you can.

The example also shows how you can grab data (for the calendar date boxes) from 2 different tables so if you have (for example), 2 different kinds of appointment tables, you can select which table you want to show on the calendar (note: requires modifying the queries.)

The way it works is that it breaks apart a field called: ApptDate into multiple parts to determine how the text shows. With a few easy modifications, you could incorporate this into your design.