Error Handling in VBA
Every function or sub should contain error handling. Without it, a user may be left viewing the faulty code in a full version of Access, while a run-time version just crashes.

---

Handle Run-Time Errors in VBA
When you are programming an application, you need to consider what happens when an error occurs. An error can occur in your application for one of two of reasons. First, some condition at the time the application is running makes otherwise valid code fail. For example, if your code attempts to open a table that the user has deleted, an error occurs. Second, your code may contain improper logic that prevents it from doing what you intended. For example, an error occurs if your code attempts to divide a value by zero. Learn how to trap run-time errors.

---

Run-time error on form
I have set up a database for monitoring users of our financial system. with the user ID being numeric, and everything has worked fine. Recently the administrators of the database have asked me to alter the database to accept alphanumeric ID's. After changing the data type to text, certain of my forms that use a filter are producing the following error message. "Run-time error '2501' The ApplyFilter action was cancelled. You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box." I know that the error message is due to the change in data types, because when I delete the alphanumeric data and change the type back, the forms work again.

---

How to: Detect the Limits of a DAO Recordset
In a Recordset object, if you try to move beyond the beginning or ending record, a run-time error occurs. Learn how to use the BOF and EOF properties to avoid run-time erros when moving through a recordset.

---

Error Trapping
You can use the On Error GoTo statement to trap errors and direct procedure flow to the location of error-handling statements within a procedure.

---

Error Handling
Probably no matter how careful and meticulous you are, some time to time, there will be problems with your code or your application. Some problems will come from you. Some problems will be caused by users. And some problems will be caused by neither you nor your users. This means that there are things you can fix. Those you can avoid as much as possible. And there are situations beyond your control. Still, as much as you can, try anticipating any type of problem you imagine may occur when a user is using your application, and take action as much as possible to avoid bad situations.

---

Test a macro by using Single Step mode
By single stepping (single stepping: A debugging technique that enables you to run a macro one action at a time or run Visual Basic code one line at a time.) through a macro (macro: An action or set of actions that you can use to automate tasks.), you can observe the flow of the macro and the results of each action (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.), and isolate any action that causes an error or produces unwanted results. (Applies to Microsoft Office Access 2007)

---

I get the error message "Could not delete from the specified tables"
When you run a delete query (delete query: A query (SQL statement) that removes rows matching the criteria that you specify from one or more tables.), Microsoft Office Access 2007 may display the error message Could not delete from the specified tables. This article lists cases in which you can use a delete query, explains why the error message appears, and provides steps for correcting the error.

---

Run a query
A query is a set of instructions that you can use for working with data. You run a query to perform these instructions. In addition to returning results which can be sorted, grouped, or filtered a query can also create, copy, delete, or change data. This article explains how to run queries and provides only brief overviews of the various types of queries. The article also discusses error messages you might encounter when you run different types of queries, and provides steps you can take to work around or correct those errors.

---

Customizing your application's environment with user profiles and run-time options
Benefits of using an Access user profile, Benefits of using run-time options, Using methods to verify user profiles and to dynamically modify Jet registry values.

---

Access 2007 Error - when I try to import a text file
I am having some issues with Access 2007. When I try to import a text file into a table using a predefined spec, the routine will run for a while and then error out with the message "the search key was not found in an record". Any ideas on what this is and how I can fix it??

---

Scheduling Macros to Run at a Certain Time
This MS-Access tutorial will teaches you Scheduling Macros to Run at a Certain Time. (video tutorial)

---

MS Access Date/Time ODBC issue
When you run a query based on a linked (attached) Microsoft SQL Server table that contains a Date/Time field, and the criteria for the Date/Time field contains a literal time value, Microsoft Access returns an empty result set.

---

Set Access run-time options
A registry tweak to set Access run time options.

---

Add, remove, or change error bars in PivotChart view
Error bars graphically express potential error amounts relative to each data marker in a data series. You can add one set of error bars per series.

---

Create a Parameter Query
A parameter query prompts you to provide data or information before the query is run. For example, you could query your database to show you the sales you have generated in the month of your choice. Each time you run the query, you will be prompted to identify the month that interests you. These instructions apply to Access 97.

---

Select Statement Help- works in MS SQL but not Access
Th following sql select statement works in mssql server but gives an error in Access. Says that there is a "syntax error in FROM clause". Can anyone translate this into access.
SELECT L1.* from log as L1 JOIN (SELECT [vehicle number],Max(Date+' '+time) as maxdate FROM log GROUP BY [vehicle number]) AS L2 ON L1.[vehicle number] = L2.[vehicle number] and L2.maxdate = L1.date+' '+L1.time

---

Resolving report errors
How do I avoid #Error in my reports? I want to use Sum([InvoiceAmt]). InvoiceAmt is a number field. However, in some cases there is no data and the Sum function does not work. The report prints a #Error and the total is "#Error". How can I get around this?

---

Avoid #Error in form/report with no records
Calculated expressions show #Error when a form or report has no records. This sort-of makes sense: if the controls don't exist, you cannot sum them. How to suppress the #Error that Microsoft Access displays in forms and reports that have no records.

---

Customize Not In List error message
My users get a generic error when they type something in a combo box control that is not in the list. I still want to force them to select a choice from the list, but how do I customize this error message to make it more friendly?

---

Access XP Crashes at Query Run
I have Microsoft Office XP SP3 installed on an Windows 2000 Pro PC. It works great except for when I'm attempting to run one query in Access XP. This is a query that is based on imported data from Excel 2000. The query was also built from Access 2000 (Office XP is a new upgrade for this PC). The query works fine from PCs running Access 2000, but causes Access XP to crash with no warning. (the application just closes completely and gives no error message) Can anyone provide information on this problem? Is there an application log I can check? Again, other queries work fine but this query crashes the whole app. Thanks in advance for your help!

---

Run a saved import or export operation
When you run an import wizard or export wizard in Microsoft Office Access 2007, you can save the settings you used as a specification so that you can repeat the import or export operation at any time. This article describes how to run a saved operation from within Office Access 2007 or Microsoft Office Outlook 2007. What do you want to do? * Save an import or export operation * Run a saved operation from Access * Run a saved operation from Outlook

---

Lost Links of External Tables
We have already learned several methods to work with external data sources. By linking them to MS-Access Database or directly opening them in Queries by setting SourceDatabase and SourceConnectStr Properties. In either case the Source Data must be present in their original location all the time. But, there is a possibility that the links to some of these tables can be lost either by deleting or renaming the source table by mistake. We will come to know about the error only when we attempt to work with the external tables and chances are that the error pops up in the middle of some process steps.

---

Avoiding divide by zero errors in Access
If you try to divide a number by zero in Access, you receive a #DIV/0 error if you are in a form or report, or a #ERROR error if you are in a query. To avoid a divide by zero error, you can use the IIF function to set the results field to a zero-length string ("") if the divisor in a calculation is zero. You can then search the field for any values that contain a zero-length string and flag the results. The following steps demonstrate this technique by creating a table and a bound form, inserting three text boxes, and entering an expression.

---

How to fix a Compile error in query expression Error
In Access 2003/XP/2000/97, I tried using the Left function in a query and I get the following error: ("Compile error.in query expression") I've seen this function work in other Access databases. How come it does not work in mine?

---

Dynamic Report
Report designing in MS-Access is very easy as we already know. Once we setup a procedure to run the macros/programs to prepare the data for a standard Report, all we need to do is to design the Report and add it to the Report List. Every time the User runs the report it is ready to Preview or Print. The Source data may change, based on the report period setting, but there will not be any change in the structure of the Report or source data or need any change on the Report design itself as all the standard labels, like main heading, the period indicator, Report prepared date, Page Numbers etc. are already taken care as part of the designing task. We are going to discuss a Report that is not so easy to plan on the above rules or based on a fixed source data structure. The structure of the source data is not predictable or shall we say field names of the source data can change every time we run this Report. If the data structure changes every time then you cannot put the field names permanently on the report design as we normally do. Adding to the difficulty the number of fields for the report also may change.

---

Prevent a query from timing out
In Access 2003, I keep getting an ODBC timeout error when I run my query. How can I prevent the query from timing out?

---

Syntax error - missing operator
I am trying to compile a code for emailing individuals from my database but come up with a syntax error from ther where statement.

---

Date Stamp on a form
I have been teaching myself access, so I don't know the jargon that well, so I hope this makes sense. In my database, I have to run a delete and append query to update information each day before users can access the information. I've designed a macro that will run the update, but want to add a date stamp on the form to show when the update was done each day. (So, on click of the button, the "last updated" field on my form will be date/time stamped). Can you help?

---

Problem opening access form
I am getting an error message that says "There isn't enough memory to perform this operation. Close unneeded programs and try the operation again". I have shut everything down and still the error occurs.

---

dsum
I am trying to show on a report the total sales for an order I am showing the information from the header The OrderItems have the details =DSum(" [Quantity] * [PricePerYard]","tOrderItem","[OurOrder=]" & [OrderID]) I get an error all the time

---

Change format from text to date in Access table
I'm having a little trouble with my Access database. The previous database manager has a table with fields for month as text format and not time or date. Unfortunately, I need to calculate the length of an event occurring (the length of flowering time from when it starts to when it ends in terms of the number of months) and I can't do that with the field in the text format. Is there a way that I can convert the data from text format to time format (only as the month, not as a full date like 1/1/2007)? I've tried to figure this out a number of ways, including update queries with new fields, but I always get an error message.

---

Date Functions in Microsoft Access
MS Access is a very versatile data handling package. It has numerous inbuilt function which enable formulating complex queries. The date functions of Microsoft Access include Date, DateAdd, DateDiff, and DatePart. They are very helpful in data handling.

---

No Data and Report Error
Report Source Query or Table can end up with no output records. In that case some of the controls with formula on the Report will show #Error on them.

---

Create a Macro
Microsoft Access provides the ability for you to create macros. A "macro" refers to a set of actions that can be run automatically, and on demand. You could make a macro run when a user presses a certain key, or you could make a macro run everytime your database is opened.

---

Simple Queries
Queries are saved sets of instructions that allow users to view selective parts of data contained in one or more tables or other queries. These instructions are written in a language called Structured Query Language (SQL). Each time a query is "run" it selects for display from the data currently in the database so that if data is added and then a query is run, the new data will be displayed according to the instructions contained in the query. Tow major functions of queries are ordering and filtering.

---

Database variable not defined error
I'm running an Access 2003/XP/2000/97 database with some VBA code in it. Within the VBA code, a database object is declared as follows: (Dim db As Database) When this code is encountered, Access highlights the line and I receive the following error message: (Compile error: User-defined type not defined) I've seen this code work in other Access databases. How come it does not work in mine?

---

Outgrowing Access, Having problems with Access, time to switch?
I work at a small business, where we keep a lot of our records in a Microsoft Access database with quite a bit of custom coded forms running on top. We generally have one to three users accessing it at the same time. Our database size is already past 800 MB, and we are starting to have crashes. I would like to either migrate to another database, or optimize our existing setup, so that we can run smoothly without crashes. My only worry is safely moving the forms and reports. As I'm not much of a programmer, the less coding required, the better.

---

Access Error, runtime error 2424
Hello there All, has anyone seem this runtime error 2424?

---

When I go to open .mdb it gives and error then installs
I have Office XP installed and all apps within it work just fine when I open them except Access. When I try to open Access with a shortcut on my desktop, I get and error that says "Windows cannot find the file...". Behind that message, another opens that says "Preparing to Install... please wait while Windows configures Office XP Professional" If I go to the folder called C:Program FilesMicrosoft OfficeOffice10 and click on the MSACCESS.EXE file itself, there is no error, but it does the install part. Word and Excel start perfectly without this issue. I tried using the cd to repair the installation, but this had no effect.

---