Frequently Asked Questions for MS Access and Access VBA
Last updated 17/04/11
This page holds questions that students have asked, plus answers to problems that Project and Placement students have come across. You might find it useful to use Edit-->Find to locate a question.
Click link here for an Excel spreadsheet that lists some common errors (compilation, run-time and logic) with possible causes and solutions when coding with VBA. This spreadsheet has been created for a year 2 module to accompany a 'finding and fixing errors' tutorial. It is intended that new entries will be added from time to time. This version posted March 2011.
Useful links:
http://support.microsoft.com/kb/146864 Error trapping with VBA
http://msdn.microsoft.com/en-us/library/aa264975(VS.60).aspx core (trappable) VBA Errors
http://msdn.microsoft.com/en-us/library/aa338172(VS.60).aspx miscellaneous VBA errors
http://msdn.microsoft.com/en-us/library/aa338178(VS.60).aspx additional VBA information
http://www.mdbrecovery.com/mdb-corruption.html some steps to avoid a corrupt database
Also use Google advanced search (type/paste in the 'exact wording or phrase' box) to look for entries for a particular error message.
Students wishing to use Access VBA may like to use the VBA Trainers.
If you think of any useful items that could be included here, please email the site owner.
You may also like to look at some example databases or Links to other websites
This link looks useful - lots of FAQs http://allenbrowne.com/tips.html
|
Make sure that you have enough space on your
disk! Access needs to use extra space to perform certain tasks, and all changes/additions you make to your database will also require more space. If there is not enough space available then your database could be corrupted. This corruption could cause all sorts of weird error messages and failures. See also Access FAQ 27. This error used to occur with earlier versions of Access, so may not apply to Access 2003 or 2007, but the advice does still apply. Access FAQ 35 has occurred with students using Access 2007 and 2010. |
The information here is split into those FAQs that have a standard 'point-and-click' solution (Access Q&A), and those that require the use of VBA (VBA Q&A). With hindsight, it might have been better to have just one list...
How do I add (or subtract) time differences to (or from) a date?
Why do Yes/No fields show as a square box rather than the words?
Why don't my table relationships show as '1' and '∞'? All I have is the line.
Why has a relationship shown as 1:1 when I was expecting 1:∞?
I've chosen referential integrity when setting up a relationship, but get an error message - why?
How do I convert an Access database to/from another version?
What Documenter options should I choose when printing out my table details?
What does "Application-defined or object-defined error" mean?
How do I make a calculated field in a query, form or report?
Why is the data in my report not shown in the same order as in the query?
How can I combine column values to form just one column in a query?
How do I select the top/bottom rows of a query dynaset? amended April 2010
How do I reconnect a mail merge Word document when I have moved the Access database?
What does the message "Undefined function '<name>' in expression" mean?
What does the message "Table 'TempMSysAccessObjects' already exists" mean?
Why don’t my query criteria work when I use an input mask to attach a prefix character to a field?
Why is my event code ignored? (e.g. click on button but nothing happens)
What does the message "The expression <event name> you entered..." mean?
Why doesn’t a formatted prefix for an AutoNumber ID show in a combo box?
What do the different RowSourceTypes for list and combo boxes mean?
Why doesn't Access 2010 give me the option to select a Style for new forms or reports? October 2010.
What does the message "Missing or broken reference to the file 'MSCAL.OCX' " mean? November 2010.
Why do I get a "Type mismatch" error trying to use Recordsets in Access 2000+?
How do I create my own primary keys? For example, membership key = M123456.
What does the message "Compile Error: Expected variable or procedure, not module" mean?
What does the message "Run Time Error - You cancelled the previous operation" mean?
Why does a calculated date give the wrong result in an SQL statement?
What does “Run-time error ‘3061’: Too few parameters. Expected 1” mean?
What does "The current field must match the join key '?' in the table.." mean?
What does "Run-time error '3251': Operation is not supported by this type of object" mean?
What does "Runtime error 3027 - cannot update. Database or object is read-only" mean?
Why does DoCmd.SendObject to send emails cause Access 2010 to crash? August 2010
How can I change a report or chart RowSource SQL at run-time (dynamically)? April 2011
Access has many date manipulation functions - see Help for a full list.
Example
- add 1 year to the system date:
=Date()
uses the Date function and will give today's date (the system date).
=DateAdd ("yyyy",1, Date()) uses the DateAdd function to add one year to today's date.
You cannot reference another field in an expression like this in a table definition. But you could do so in VBA code for a data entry form, or in a calculated field in a query, for example.
=Date() + 365 will give a date in a year's time, but will not work for leap years!
There are some more examples in Access FAQ10 example 2 and Access FAQ 12
2. Why do Yes/No fields show as a square box rather than the words?
You have probably got the field defined as a 'check box' in the table definition. If this field is actually destined for a form, then a check box may be appropriate. However, it can look odd in the table layout or on a report. If you want to change the display, simply look at the property box for the field on the table, form or report and change the display control from 'check box' to 'text'.
You may also like to think whether a Yes/No box really is the most appropriate data type to use. The user has to enter either 'Yes' or 'No' for the field. A 'Yes' (or tick in a check box) on a form for 'Smoker' may make sense, but seems (to me, at least) to be decidedly odd for the 'Sex' or 'member/class' fields for the Chelmer leisure Scenario (though it does save space). You may like to experiment with using a Look-Up Wizard for the data type here, and setting just the two values 'Male' and 'Female' or 'Member' and 'Class' for the list box that the Wizard will create. 'M' or 'F' etc would save space.
3. Why don't my table relationships show as '1' and '∞'? All I have is the line.
You have probably not chosen to have referential integrity checked when you created the relationship. Delete the relationship (click on the line then choose delete) and try again, this time choosing to have referential integrity checked.
See also Access FAQ 5 I've chosen referential integrity when setting up a relationship, but get an error message - why?
Referential integrity is important. For example, in the Chelmer Leisure database, for the 1:m relationship between the Category and the Membership Tables, referential integrity will ensure that all member records have a membership type that is already listed in the Category table.
It is best to set up all relationships before entering data.
4. Why has a relationship shown as 1:1 when I was expecting 1:∞?
This can occur if...:
...you are joining two primary keys. A 1:∞ relationship requires that one key only is a primary key. Check to see that you are joining the correct fields.
...you are
joining a primary and a foreign key and the foreign key is specified as 'no
duplicates' in the Index property for that field in the table.
(a) If the foreign key can have duplicate values in the table (this is the
normal situation) then change the Index property to show this.
(b) If the foreign key really is a unique value in the table then check to see
if you need to use a separate primary key for this table; perhaps you can use
the primary key from the main table in both tables (as in point 1 above).
You should also check your design carefully. Sometimes a 1:1 relationship is correct (e.g. to save space in the database for information that that doesn't apply to all records, or to put sensitive data in a separate table in order to restrict access). But you need to be able to justify each 1:1 relationship that you have, so that you know that it is correct.
5. I've chosen referential integrity when setting up a relationship, but get an error message - why?
There are two common reasons for this:
1 - Existing data violates integrity (error message: "Microsoft Access can't create this relationship and enforce referential integrity" - this message is then followed by a nice clear example of what the problem might be).
The most likely reason that either or both of your tables already have data in them but that the data violates the integrity rule.
Example A: You have data in your membership table but not yet in your category table. The tables will be joined via the category number. As referential integrity requires that all category numbers in the membership table are also in the category table, you must now set data in your category table before creating the relationship.
Example B: You have data in both the membership and the category tables. The likely reason is that you have a category number in your membership table that is not in the category table. Correct your data and try again.
It is safest to set up all validations and rules before entering any data.
2 - Key data types do not match (error message: "relationship should be on the same number of fields with the same data type")
The relationship joins two keys - one in each table. These keys should be of the same data type and size. If the primary key of one table is an Autonumber field (LongInteger), then the corresponding foreign key in the other table should be a Number data type, also LongInteger. If one key is a text field, then so should the other one (and of the same size).
6. How do I convert an Access database to/from another version?
When you open an Access database in a newer version of the software, Access will ask you if you want to convert it and then, if you reply 'yes' will do the conversion. When converting from Access 97 to Access 2000, it is recommended that you first compile all the code (see Debug-->Compile all Modules), then repair and compact it (see Tools-->Database Utilities). If you don't do this, you may get a message saying that the database is corrupt. This not does seem to be necessary when converting to XP.
If you reply 'No' to the conversion, then the database can be opened and data amended (I think), but no design changes can be made.
There is also information regarding conversions to/from different versions in Access Help. Use the keywords
convert, access
to find the relevant pages.
(Tip. You may find it easier to use Help if you switch the 'Assistant' off and then use Help-->Index and type the relevant keywords in. This will show you all matching references and you can search for the one that fits what you want)
7. I'm setting up a validation rule and get the message "Error evaluating CHECK constraint in the validation rule". What does this mean? With thanks to the student who had this error, sorted it out, and told me the answer.
This has occurred where students have written a validation rule enclosed in quotation marks, or possibly with quotation marks instead of square brackets around a field name.
Quotation marks are used in programming (also in Access SQL and expressions) to denote a string literal. The actual contents of these literals are not looked at by the software but are simply used as required. For example Town = "Chelmer" where Town is taken to be a data name and "Chelmer" is a string literal.
June 2003 (and updated January 2009). This
error has also occurred with Recordset DAO code, where a student was writing a
record to a table. The table had a field validation rule. This appeared to
conflict with the Recordset DAO code and was the cause of the error. When the
field validation rule was removed and replaced by validation VBA code then all
was well. So, do not use field (or table) validation rules with Recordset DAO
code; code all validations using VBA code. If you are coding everything via Recordsets then it
would seem more logical to validate data via code anyway.
This error
could also occur with embedded SQL with invalid data.
8. What Documenter options should I choose when printing out my table details?
The Documenter gives you several options to choose from:
The options chosen above will give a concise listing with just one line for each field, but showing record (table) validations only.
If you want to show all the field validations as well, choose the '...and Properties' option for Fields. Note, though, that this gives a much larger listing, with a load of extra stuff you may not want. You could cut the listing down by publishing it in Word then deleting the stuff you don't want.
If you have used the Table Planning Sheet to plan out your table before creating it, then the sheet and the shorter listing could provide a concise method of documenting the table details and field validations.
9. What does "Application-defined or object-defined error" mean?
This error has occurred in some labs at DMU Leicester, when students are trying to use the Documenter.
It probably means that the Access installation is incorrect. Please report the problem to the lab technicians or the Student Support Centre <csesupport@dmu.ac.uk> then go and try another lab.
10. How do I make a calculated field in a query, form or report?
Query
Example 1
You should all be familiar with the concept of adding table fields to a query in the Query Design window. A calculated field in a query is a Query Design column, which is not a simple reference to a table field, but is the result of a specified calculation.
For example, suppose you have a stock table with entries for stock number, description, unit price and quantity in stock (there will probably be other fields in the table as well). The following will query the stock details plus the total value:
The SQL is:
SELECT stock.StockNo, stock.Description, stock.UnitPrice, stock.QtyInStock, [UnitPrice]*[QtyInStock] AS TotalValue
FROM stock;
The result of running the query is:
|
StockNo |
Description |
UnitPrice |
QtyInStock |
TotalValue |
|
1 |
Socks |
£5.99 |
6 |
£35.94 |
|
2 |
Handtowel |
£7.95 |
43 |
£341.85 |
NOTE: You do not have to have query columns for the individual table fields used in the calculation. For example, the above query will still work if the Query Design only uses StockNo, Description and TotalValue.
Example 2 New 8 March 2005
Sometimes the calculated value may vary according to the value in another field. For example, a database has a field called ReceivedDate for a date when an enquiry was received. The business has set a target date of 2 working days (i.e. excluding weekends) for each enquiry to be actioned, and requires a query to list enquiries and their target dates. (This example is based on a real-life situation).
The picture below shows a very simple query which uses the built-in Weekday and IIf functions to calculate such a target date.
The SQL is:
SELECT tblDates.ReceivedDate, IIf(Weekday([receiveddate])=5 Or Weekday([receiveddate])=6,[receiveddate]+4,[receiveddate]+2) AS TargetDate
FROM tblDates;
The result of running the query is shown below. 28th February 2005 was a Monday.
|
qryDates using IIF |
|
|
ReceivedDate |
TargetDate |
|
28/02/2005 |
02/03/2005 |
|
01/03/2005 |
03/03/2005 |
|
02/03/2005 |
04/03/2005 |
|
03/03/2005 |
07/03/2005 |
|
04/03/2005 |
08/03/2005 |
The Weekday function returns 1 for Sunday to 7 for Saturday. For further examples and information about date functions, see FAQ 12 on queries using dates.
The IIf function is very useful in cases like this. The basic description of the function is:
IIf(expression, truepart, falsepart)
The logic of how it works can be expressed as:
If expression is true then
Result = value in truepart
Else
Result = value in falsepart
End if
Thus, in this example,
Expression is Weekday([receiveddate])=5 Or Weekday([receiveddate])=6
It is checking to see if the received date field is for a Thursday or a Friday
Truepart is [receiveddate]+4
4 days are added to the Thursday or Friday date for the required working date after the weekend
Falsepart is [receiveddate]+2
2 days are added to the (Monday to Wednesday) date for the required working date this week
Form or Report
One method is to base the form or report on a query such as the one above.
Alternatively, add a text box to the form or report then:
Put the appropriate formula in the Control_Source property for the text box (see McBride Unit 24), or
Use VBA code to do the calculation and put the value in the new text box.
These alternative methods are discussed in more detail in section 3.2 of the Getting Started VBA Trainer.v5.
See also:
Access FAQ 26 What does the message Undefined function '<name>' in expression" mean?
VBA FAQ 5 How do I update a table value via a form?
11. How do I make an autonumber field start from 1 again?
When you are doing initial testing or if you make mistakes entering data and delete a row, the AutoNumber field keeps incrementing from the last number used. You may wish to start from 1 again (perhaps with existing data or perhaps after deleting initial test data).
This is very simply done as follows:
Make a copy of the table (just in case…)
Remove all relationships between this table and any others
If the AutoNumber field is a primary key, click on the primary key icon to undesignate it as a key
Delete the row for this field
Insert the row again and recreate the field as an AutoNumber field
Redesignate it as a primary key, if appropriate
Look at the table in design view - it should now be renumbering from 1 again.
Reinstate the table relationships.
WARNING 1 - if you have a table joined to this one, with the second table using the AutoNumber field as a foreign key, then renumbering the first table will upset all the relationships, as the data will no longer correspond.
WARNING 2 - if you are using the AutoNumber key as a base for a second key field key of the form M000001, where you have VBA code on the form that affixes "M" to the front of the AutoNumber key, then the second key field will NOT be changed automatically by this method. You would need to do an Update Query to reset the second key field, for example:
UPDATE Member SET Member.[Membership No] = "M" & Format([Memb No Auto],"000000");
Where [Memb No Auto] = the AutoNumber key
and [Membership No] = the second key field
and the calculation is that already used on the form.
Consider the 'date of joining' field in the Membership table of the Chelmer Leisure database. The management may want to know answers to questions to such as:
- How many people joined in 1992?
- How many people joined in each month of 1992?
- How many people joined each year?
You specify the criteria as normal for the column in the Query Design screen, or the WHERE / HAVING section of SQL. The table below shows some examples. Note that some of the details have calculated columns that make use of the date functions, Date, Year and Month.
Access has many of these date manipulation functions.
For an example showing calculated columns and the functions, see after the table.
|
Criteria |
Column(s) |
Result |
|
>=#01/01/92# And <=#31/12/92# |
Date of joining |
Selects data between the two dates shown If used for a query requiring totals, will group by all the separate dates, not by the year. |
|
Between #01/01/92# And #31/12/92# |
Date of joining |
As above. |
|
Year(Date())-1 |
Date of joining |
Selects data for last year. |
|
Year(Date())-Year([date of joining])<4
|
Date of joining |
Selects data where the year of the date of joining is less than 4 years ago. |
|
<DateAdd("yyyy", -4, Date()) New - 8 March 2005 |
Date of joining | Selects data where the date of joining is less than 4 years ago. The DateAdd function is used to subtract 4 years from the system date. |
|
1992 |
Year([date of joining]) |
Selects dates of the form dd/mm/1992 Use this to group for a count of members joining in 1992. |
|
[please enter required date] |
Year([date of joining]) |
Similar to above, but uses a run-time parameter for the year. This will cause a parameter box with the text "[please enter required date]" to popup. |
|
=forms!frmReports!txtYear New - 8 March 2005 |
Year([date of joining]) | Similar to above, but checks the value in a textbox called txtYear on a form called frmReports. This uses the Forms Collection. |
|
none |
Year([date of joining]) |
Use this to group for a count of members joining by year. |
|
1992 and none |
Year([date of joining]) and Month([date of joining]) |
This uses two calculated columns to select by month for 1992 - see example below. |
Query Design screen for the last example, counting membership joins by month for 1992:
Experiment with some of the other examples above and see results.
SQL for the above example:
SELECT Month([date of joining]) AS Month, Count(Membership.[Membership No]) AS Total
FROM Membership
GROUP BY Month([date of joining]), Year([date of joining])
HAVING (((Year([date of joining]))=1992));
Result of running the query:
(the year column is suppressed here as it will be the same for all rows).
|
Month |
Total |
|
2 |
1 |
|
4 |
2 |
|
6 |
3 |
|
7 |
1 |
|
8 |
1 |
But what if the management wanted the query to show all months (1-12), even when there were no new members joining that month? See Example 3 in FAQ 13 How can I list details not in a database? and FAQ 18 What is an Outer Join?
The Forms Collection. New - 8 March 2005
The Forms Collection is invaluable for enabling queries (and VBA code – see VBA Getting Started Trainer Appendix I) to reference values on an open form. See example in table above, where frmReports is assumed to be a form where the user enters parameter values and then can open reports based on queries that use the parameters. The field for the parameter used in the example shown is called txtYear.
There is some limited Help available from the Access database window; search using the keywords Form Object, then select the item ‘About referring to an object or its properties in expressions’. This item does not mention queries (I don’t know why) but does show how to make a reference to a field on an open form.
There is more information (using the same keywords) via VBA Help.
Date manipulation functions. New - 8 March 2005
There are many date manipulation functions available in MS Access. The Help files for these functions are only available from VBA Help (which is not very helpful) even though the functions can be used in Access queries, etc. Two such functions are Date [gets the system date] and DateAdd [add days, months, years etc to a given date], demonstrated in the table above. Another useful function is Weekday, an example of which in use can be seen in FAQ 10.
There is some limited help via the Access database window. Search with the word ‘expressions’ and then look at the item titled ‘examples of expressions’. This has various examples of calculated fields in queries, forms and reports, and includes some examples using date functions.
In VBA Help, use the keywords Function; Date; Time; Date and Time. There is some more information in the VBA Getting Started Trainer, with a summary in Appendix H.2.
VBA Help New - 8 March 2005
To see VBA help, you need to be in an open code window. (If you don’t know how to do this, a simple method is to (a) click on the Modules object in the database window, then (b) click on New. Then you will see the code window open. This is a separate window to the Access database window, so you can safely close it when you have finished looking at Help).
13. How can I list details not in a database? For example, members who have not made bookings, or cars that are not on hire?
This is a very common requirement in booking systems, for example listing hire cars, hotel rooms, etc that are free for particular dates. Another use is for for financial data for accounting periods.
Listing free slots for bookings (for example, the bookings each hour for a day for the Chelmer Leisure Centre) can be done by several methods. Some of these are illustrated using worked examples in the VBA Trainers.
The clue to the answer is in the words 'not in' in the question. There is a simple solution, based on two queries:
Query1 - list the unique identifiers (membership, car, or room numbers, for example) for the bookings that have been made.
Query2 - select the required details, where the unique identifiers are NOT IN the list from Query1. This uses a NOT IN subquery, selecting just the one column from the subquery.
There follows below three examples of instances of use of a NOT IN subquery.
Note that Access allows you to refer directly to sub queries within the SQL, and this can make things easier to follow. Other RDBMSs may allow similar references to Views, but if they don’t, follow the examples below to set up the full SQL.
Example 1, the Chelmer Leisure management want to know which members did not make any bookings last year.
Query1 - select the membership numbers of the members who did make a booking last year:
SELECT DISTINCT Membership.[Membership No]
FROM Membership INNER JOIN Bookings ON Membership.[Membership No] = Bookings.[Membership No]
WHERE (((Year([Date]))=Year(Date())-1));
You may need to add the word DISTINCT if creating the query via Query Design. It is not essential, but does reduce the size of the result of query1 and thus may (?) speed up query2.
Query2 - create a query to select the required fields, and in the criteria for the Membership No column do:
- enter : NOT IN (
- paste the SQL for query1
- replace the semi-colon (;) at the end of the SQL for query1 with a closing round bracket
- your SQL will now be of the form:
SELECT [Membership No] <,any other field(s) required>
FROM Membership <,any other tables required>
WHERE [Membership No] NOT IN (SQL for Query1);
The second select here is known as a subquery. Access may add loads of extra brackets here, which can be a bit confusing.
Actual SQL, selecting the three fields shown is:
SELECT Membership.[Membership No], Membership.Lastname, Membership.Firstname
FROM Membership
WHERE Membership.[Membership No] NOT IN (SELECT DISTINCT Membership.[Membership No]
FROM Membership INNER JOIN Bookings ON Membership.[Membership No] = Bookings.[Membership No]
WHERE (((Year([Date]))=Year(Date())-1)));
Alternative method, referencing intermediate queries directly, rather than using the full SQL:
SELECT Membership.[Membership No], Membership.Lastname, Membership.Firstname
FROM Membership
WHERE Membership.[Membership No] NOT IN (SELECT [Membership No] FROM [Query1]);
this might be a simpler method to use.
Example 2 - click here for an example car booking database (Access 2000). This has just 2 tables, Car and Booking, with a form in which to enter the two dates for the hire period. There are two queries; Booked (the 1st query) and Free (the 2nd query, which uses the SQL from the 1st query for the NOT IN subquery). Have a play with it and see how it works. I think it covers all conditions, but it is up to you to test it thoroughly in your application! There are two versions of the Free query (called Free and Free2; one with the full SQL, and one referencing the Booked subquery by name).
Click here for a Word document with further explanation.
There is just a small amount of VBA code behind the form, for the two command buttons. The Booked button code is generated by the 'run query' form wizard. The Free button code is my own, to operate a list box which shows the details of the cars that are free.
Example 3 - The Chelmer Leisure management want a graph showing numbers of members who joined in each month in 1992.
See Access FAQ 12 How do I query on all records by date or parts of a date? This FAQ shows how to list totals for all months in which at least one member joined. What is needed now is to list all the months NOT IN this list, and then to join the two lists together.
Stage 1 - create the query 'Totals by months in a year' from the above-mentioned FAQ.
Stage 2 - create a new table ("Month"), with just one column ("Month"), and add numbers 1 through 12 as data to the table.
Stage 3 - create a new query ("months with no bookings") which uses a NOT IN subquery:
SELECT DISTINCT Month.Month, 0 AS Total
FROM Month
WHERE (((Month.Month) Not In (SELECT Month([Date of Joining]) AS Month
FROM Membership
GROUP BY Month([Date of Joining]), Year([Date of Joining])
HAVING (((Year([Date of Joining]))=1992)))));
The result of this new query is then
|
Month |
Total |
|
1 |
0 |
|
3 |
0 |
|
5 |
0 |
|
9 |
0 |
|
10 |
0 |
|
11 |
0 |
|
12 |
0 |
Note how to create a column with a fixed value in it. Look to see what the Query Design screen looks like.
Note also that the subquery SQL is the same as that for the 'totals by month in a year' query, but omitting the 'Total' column, as the subquery must have only one column.
Stage 4 - open a new query, do not choose any tables, go to the SQL view and do the following:
(a) paste in the SQL for the 'totals by month in a year' query - deleting the semi-colon (;) at the end
(b) add the word UNION
(c) paste in the SQL for the 'months with no bookings' query (do not delete the semi-colon at the end).
You now have a UNION query, with SQL like:
SELECT Month([Date of Joining]) AS Month, Count(Membership.[Membership No]) AS Total
FROM Membership
GROUP BY Month([Date of Joining]), Year([Date of Joining])
HAVING (((Year([Date of Joining]))=1992))
UNION
SELECT DISTINCT Month.Month, 0 AS Total
FROM Month, Membership
WHERE (((Month.Month) Not In (SELECT DISTINCT Month([Date of Joining]) AS Month
FROM Membership
GROUP BY Month([Date of Joining]), Year([Date of Joining])
HAVING (((Year([Date of Joining]))=1992)))));
Note that for UNION queries, the results of each query must be of the same format (i.e. same number of columns with the same data types in the same order).
Run this and see what you get! You can then use this new query result as the basis for a graph.
See also What is an Outer Join?
Alternative method, referring directly to query, instead of copying the SQL.
SELECT DISTINCT Month, 0 AS Total
FROM [Month]
WHERE Month Not In (SELECT Month from [Totals by months in a year]);
Note that must only select one column for the sub query.
Alternative method for UNION query, referring directly to both queries:
SELECT * FROM [Totals by months in a year]
UNION
SELECT * FROM [months with no bookings]
ORDER BY Month;
14. Why is the data in my report not shown in the same order as in the query?
I don't know why this is, but you can specify the order of the report using 'Sorting and Grouping' (see the View menu; there may also be an icon on the toolbar) and this appears to work OK.
You can also add/remove group headers/footers using 'Sorting and Grouping'. This can be useful if you want to change an existing report.
15. How can I force a page break in a report?
Look up 'page break, report' in Help - this is easy to do and clearly explained in Access 97, so simply experiment until you get what you want. However, I can't find it in Access 2000/2002 Help, so copy below the text from Access 97 Help.
Page breaks are useful if you want a new group to start on a new page. The group value can then be put in the group header and this header used to replace the page header.
Page or report headers/footers can be added/removed via the View menu.
Other group headers/footers are controlled via View--> Sorting and Grouping.
Page breaks in reports
All this is extracted from Access 97 Help, by typing ‘page break’ in Contents and Index. Access 2000/2002 help does not appear to have this (or if it is there it not so easy to locate). However, you can find something if you type “how can I force a page break in a report?” into the Help answer wizard.
Add a page break to a report
In reports, you use a page break control to mark where you want to start a new page within a section. For example, if you want a report's title page and introductory message printed on separate pages, place a page break in the report header after the controls that you want to appear on the title page and before the controls for the second page.
1 Open the report in Design view.
2 Click the Page Break tool in the toolbox.
3 Click the report where you want to place the page break. Place the page break above or below a control to avoid splitting data in that control.
Microsoft Access marks the page break with a short dotted line on the left edge of the report.
If you want each group or record in a report to start on a new page, set the ForceNewPage property of the group header, group footer, or detail section.
Print each record, group, or section on a separate page on a form or report
1 Open the form in Design view or the report in Design view.
2 Double-click the section selector to open the property sheet.
3 Set the ForceNewPage property of the section to one of the following settings:
· None. (Default) Prints the section on the current page.
· Before Section. Prints the section at the top of a new page.
· After Section. Prints the following section at the top of a new page.
· Before & After. Prints the section at the top of a new page, and prints the following section at the top of a new page.
Notes
· The ForceNewPage property is available for all sections except page headers and footers.
· To print each record on a separate page, set the ForceNewPage property of the detail section to After Section. To print each group on a separate page, set the ForceNewPage property of the group header to Before Section or the ForceNewPage property of the group footer to After Section.
Use Visual Basic to force a page break in a report if a condition is met
1 Open the report in Design view.
2 In the toolbox, click Page Break , then click in the report section where you want a conditional page break.
3 Open the report's PageHeader_Format event procedure.
4 In the event procedure, add an assignment statement that sets the Visible property of the page break control to No. For example, if the name of the control is CondPgBreak, add the following assignment statement:
Me![CondPgBreak].Visible = False
This hides the page break control when the report starts formatting each page, so the page doesn't break.
5 In the Format event procedure of the section where you placed the page break, add Visual Basic code that sets the Visible property to Yes when a condition is met. For example, suppose you want a page break to occur in the detail section of the report if the value of the Counter control is 10, so that the first 10 records will print on the first page. Add the following code to the Detail_Format event procedure:
If Me![Counter] = 10 Then
Me![CondPgBreak].Visible = True
End If
When the condition is met, the page breaks. After the page is broken, the event procedure attached to the page header hides the page break control until the condition is met again.
16. How can I combine column values to form just one column in a query? For example, join forename and surname, or show first initial and surname?
In order to join fields you use the concatenation character
& (ampersand).
Remember to allow for space characters between fields where necessary.
Query column showing the full name.
Enter the following for the Field row in the Query:
Fullname: [Firstname] & " " & [Lastname]
The Fullname field now consists of three parts, joined together: the Firstname, a space character, the Lastname.
Note that there is no need to specify the Membership table in the Table row.
Query column showing the first initial.
Enter the following for the Field row in the Query:
Init and name: Left([Firstname],1) & " " & [Lastname]
As above, the field consists of three parts, but there the first part if just the first character of the Firstname field. The Len function is just one of several useful String Manipulation functions available - to see details, look at Help.
Here you must not specify the table name in the Table row. If you do so, Access will say that there is a syntax error in the expression, as it will look for the field Left([Firstname],1) in the Membership table, and object to the presence of the comma.
Be careful if your query uses more than one table and the tables have the same field names for the fields that you wish to combine!
Look at Access FAQ 21 How can I work out initials from a name field? for screen prints and results for these examples.
17. What is a CROSSTAB query? (also known as a PIVOT TABLE query)
"A crosstab query displays summarised values (sums, counts, and averages) from one field in a table and groups them by one set of facts listed down the left side of the datasheet and another set of facts listed across the top of the datasheet." (Explanation from Access 97 Help). These queries can be extremely useful for providing summarised information.
You may find that using the crosstab query as the source for a report proves problematic (various error messages, etc). A simple way round this is to create a second query, which is merely a simple select query that selects all the fields from the crosstab query. Then base your report on this second query. This seems to work...
Example using Crosstab Query Wizard (example with only one table)
As an example, suppose you wanted to see totals of bookings by Room/Hall/Court. Do the following:
(a) Create a new query and choose crosstab query wizard
(b) Choose the Booking table, then the [Date] field for the row headings.
(c) Choose the [Room/Hall/Court] field for the column headings.
(d) Choose the Count Function.
(e) Save and run the query. The result will look like:
|
Date |
Total Of Booking No |
Court 1 |
Court 2 |
Court 3 |
Fitness Suite |
Sports Hall 1 |
Sports Hall 2 |
|
13/05/1996 |
5 |
1 |
|
|
2 |
1 |
1 |
|
14/05/1996 |
4 |
|
|
1 |
3 |
|
|
|
15/05/1996 |
5 |
1 |
|
|
1 |
|
3 |
|
16/05/1996 |
5 |
|
1 |
|
1 |
|
3 |
|
17/05/1996 |
3 |
|
|
|
1 |
|
2 |
Date functions could be used in the date column to select date ranges, show values by year, etc. (See Access FAQ 12 How do I query on dates or part of a date?)
Look at the result in the Query Design Window and at the SQL. Note the TRANSFORM and PIVOT SQL statements.
Example using SQL (example with more than one table)
Suppose that the Chelmer Leisure management wants to check that correct category numbers have been allocated to members. One way would be to have the row header for the year of birth and the column headers for the category description (the description being more of more use than the category number).
Create a query with the following SQL:
TRANSFORM Count(Membership.[Category No]) AS [CountOfCategory No]
SELECT Year([Date of Birth]) AS [Year of Birth]
FROM [Membership Category] INNER JOIN Membership ON [Membership Category].[Category No] = Membership.[Category No]
GROUP BY Year([Date of Birth])
PIVOT [Membership Category].[Category Type];
Compare this SQL with that for the first example above, and look at the query design window. Run the query and see what you get.
A better query would show the actual ages rather than just the year of birth. The Getting Started VBA Trainer shows how to create and use a CalculateAge function. Using this, the SELECT statement could be changed to : SELECT CalculateAge([Date of Birth]) AS Age
So - experiment with the examples above and try creating a crosstab query of your own. For example, create a query to show the bookings each day by membership type. You will need to use the Booking, Membership and Membership Category tables. Select the booking date for the row header, the category description for the column header, and count the category descriptions for the value column. Go back to the first example above and see how the Crosstab Query Wizard has created a Total column, then try doing the same for your new query.
See also VBA FAQ 14 How can I add a criterion to a Crosstab query?
See section 8.4 of the Getting Started VBA Trainer for a further example of the use of Crosstab query.
Inner Joins (the normal joins with which you should be familiar) show rows in a query where there are matching records in both tables.
"Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table." (Description here taken directly from Access 97 Help).
Right outer joins are similar to left outer joins, with the tables in the other order.
As an example, consider the Query Totals by Month in a Year from all records by date or part of date? and example 3 in How can I list details not in a database? An outer join can be used to query the result of the query joined with the month table, to show all months from the Month table, and values where they exist from the query. However, outer joins cannot be used with queries, only with tables, but queries can be used to create tables. So do the following:
Stage 1 - Create Totals by Month table by changing the query SQL (and saving it as a new query) so that it looks as follows:
SELECT Month([Date of Joining]) AS Month, Count(Membership.[Membership No]) AS Total
INTO [Totals by Month]
FROM Membership
GROUP BY Month([Date of Joining]), Year([Date of Joining])
HAVING (((Year([Date of Joining]))=1992));
Save and run the query - look at the tables where you should now see this new one.
Stage 2 - Create a query using the Month table (see stage 2 of example 3 of FAQ 13 How can I list details not in a database?) and the Totals by Month query as shown below.
To create the join between the tables, click on Month in the Month table and drag to Month in the Totals by Month table. Then right click on the line to see the Join properties box and choose option 2: "Include ALL records from 'Month' and only those records from 'Totals by Month' where the joined fields are equal".
Now, if you run this second query, the result you get is:
|
Month |
Total |
|
1 |
|
|
2 |
1 |
|
3 |
|
|
4 |
2 |
|
5 |
|
|
6 |
3 |
|
7 |
1 |
|
8 |
1 |
|
9 |
|
|
10 |
|
|
11 |
|
|
12 |
|
This shows all months, and values where they apply. Note that the Create Totals by Month query must always be run before running the Totals by Month - Left Join query. You will be prompted to delete the existing Totals by Month table. If running the queries via VBA, you should code a DROP TABLE command first.
Look at the SQL to see how the outer join is coded.
Compare this method with the NOT IN method shown in example 3 in Access FAQ 13 How can I list details not in a database?
See Part 8 of the Getting Started VBA Trainer v 5.1 for further examples of the use of Outer Join queries.
19. Can I compact my database automatically?
Yes - this is a new feature for Access 2000. Use Tools-->Options and then choose the General tab. Click in the Compact on Close box. See Compact on Close.
For fuller information (which includes when you can not use this feature) use Help with 'Close' as the keyword, and choose 'Compact and Repair an Access File'.
However, using this can be dangerous if the process crashes or if you are short of disk space. See Access FAQ 27 What does the message "Table 'TempMSysyAccessObjects' already exists" mean? . It may be safest to make a backup first, then compact manually via Tools-->Database Utilities. See also section 7.4 of the Getting Started VBA Trainer.
Yes - this is a new feature for Access 2000. Use Tools-->Options and then choose the General tab. Click as required on the entries in the Name AutoCorrect box. See Name AutoCorrect.
For fuller information use Help with 'Name' and 'AutoCorrect' as keywords, and choose 'About using Name AutoCorrect to fix errors caused by renaming'.
21. How can I work out initials from a name field?
See later on this section for logic for a possible own VBA function to do this.
The example here demonstrates several useful features:
Calculated field - a new field that displays the results of a calculation defined with an expression.
LEFT function, here used to select just the first character to the left of the field. See also RIGHT and MID functions.
Using the '&' character to concatenate (join, combine) elements in an expression.
If you want to use initials of all names it's probably easiest to have middle names in separate fields.
The example uses the Membership table from the Chelmer Leisure database.
The two fields Initial and FullName are calculated fields - they do not exist in the table, but are created via the query.
Initial uses the left function to select just the leftmost single character of the Firstname field from the table.
FullName concatenates the Title, Initial and last name into one field. Note how the & character is used to join each bit and how spaces (" ") and full stops (". ") are put in the result. The FullName here uses the Initial field, but it could also have used the left function instead.
Query to create Initial and Fullname:
Result of query:
|
Title |
Initial |
Lastname |
FullName |
|
Mr |
A |
Walker |
Mr A. Walker |
|
Mrs |
D |
Cartwright |
Mrs D. Cartwright |
|
Mr |
J |
Perry |
Mr J. Perry |
|
Miss |
A |
Forsythe |
Miss A. Forsythe |
|
Miss |
D |
Jameson |
Miss D. Jameson |
|
Miss |
P |
Robinson |
Miss P. Robinson |
|
Mr |
D |
Harris |
Mr D. Harris |
|
Mr |
I |
Shangali |
Mr I. Shangali |
|
Mrs |
M |
Barrett |
Mrs M. Barrett |
|
Mr |
G |
Weiner |
Mr G. Weiner |
|
Mr |
D |
Ali |
Mr D. Ali |
|
Ms |
A |
Young |
Ms A. Young |
|
Mr |
I |
Gray |
Mr I. Gray |
|
Miss |
F |
Swift |
Miss F. Swift |
|
Mrs |
S |
Davies |
Mrs S. Davies |
|
Mrs |
R |
Robinson |
Mrs R. Robinson |
|
Mr |
A |
Everett |
Mr A. Everett |
|
Mr |
L |
Locker |
Mr L. Locker |
|
Miss |
A |
Locker |
Miss A. Locker |
|
Mr |
E |
Jones |
Mr E. Jones |
You can also create your own function, using VBA, to do this.
(a solution is not given here as it is set as labwork, with the logic shown below, on a module)
A suitable function header could be:
Public Function myInitials(prmString As String) As String
When testing in the Debugger, ?myinitials(“John Joe Fred”) should return “JJF”.
Logic examples below assume the first character is not a space. Could use Trim/LTrim at the start to trim off any starting spaces.
Suggested logic 1:
get first initial (hint – use Left)
get length of string (hint – use Len)
For each char in the given string
If the char = space then (hint – use Mid)
position to next char (hint – add 1 to loop
counter)
if position is outside end of string then
exit for
else
add char after the space to the initials
so far (hint – use &)
end If
end If
Next 'go round again
return upper case initials
Suggested logic 2:
get first initial (hint – use Left)
get length of string (hint – use Len)
set a counter to 1 for start of string
Do
find position of space in string (hint – use InStr)
If position = 0 Then ‘char not found
exit do 'no (more) occurrences of char
else
position to next character (hint – add 1 to counter)
If position > length Then
Exit Do
Else
add to list of initials (hint – use &)
End If
End If
Loop
return upper case initials
22. How do you use the Calendar Control?
This feature has been removed from Access 2010. See VBA FAQ 43.
The calendar control is an Active-X data object that shows a calendar on a form and allows a user to choose a specified date.
It can be used as an alternative to requiring the user to type a date directly into a text box. You need to think carefully which control (textbox or calendar) is appropriate for the given context.
Putting the control on a form.
This is simple, via Insert à ActiveX Control, and then choose Calendar Control from the list presented.
The name for the control will be something like Calendar0. You can change this via the property box to something like ocxCalendar (using the usual prefix for this object).
By default, the months may show as Jan, Feb… and the days as Mon, Tue…. You can change these via the property box; look at the Other tab for the entries for DayLength and Month respectively. Look at the property box to see the various other items here that can be changed. As for other form controls, these items can be changed using VBA code (see below for an example of how to change the Value property).
Showing today’s date
The date shown will probably be the date that you created the control on the form. To make it change to the system date each time the form is opened, code the following line in the Form_Load event:
ocxCalendar.Value = Date
This uses the Date Function to assign the system date to the Value property.
(Note – this code has no effect in the Form_Open event!).
Using the Calendar
You can refer to the control by name in VBA code just as with any other form control. You can also use it with a forms collection reference (for example, as a query parameter): [forms]![form1]![ocxCalendar]
If you look at the property box you will see that there are five events for which you can provide VBA code.
There are examples of use of a Calendar control in the VBA Trainers.
"A union query combines fields from two or more tables or queries into one field in the query's results. You might use a union query to combine data from two tables. For example, you might combine company name and city data for all companies in Brazil that are listed in the Suppliers and Customers tables." Access 2000/2002 Help.
Note that both SELECT statements must return a dynaset that has the same number of columns with the same datatypes in corresponding columns. The UNION clause will combine the two dynasets into one dynaset. You cannot create a UNION query via the query design window, but you can create each separate SELECT query this way, then copy/paste the SQL into a new UNION query into the query SQL window.
See VBA FAQ 4 How can I create a multi-purpose query/report? (which actually uses very little VBA) and Example 3 in Access FAQ 13 How can I list details not in a database? for some examples.
An example from Help is:
SELECT [CompanyName], [City]
FROM [Suppliers]
UNION SELECT [CompanyName], [City]
FROM [Customers]
ORDER BY [City];
24. How do I select the top/bottom rows of a query dynaset?
This is very easy to do with MS Access. Look at Access 2000/2002 Help, keyword ‘maximum’, and select the item “Show only high or low values in a query”.
In Access 2007 the same keyword will get you an item "Find the records with the top or bottom values in a group or field" (on the first page of the list on my machine!).
This Help information looks very useful, and indeed, helpful.
The following screen shows a query to list all members of the Chelmer Leisure Centre by ascending date of birth.
By using the little drop-down box shown above, you can select the top/bottom rows that you require. You can specify either an absolute number of rows, or a percentage. There are suggestions in the drop-down box, but you can over-ride these by entering your own figure.
In the example above, the top values (in this case, for those members whose dates of birth are earliest) will be selected, as the list is sorted in ascending order; i.e. the first several rows are selected. If you wanted to see those at the other end of the list, simply sort in descending order.
Look at the SQL – note the new item, 'TOP 5':
SELECT TOP 5 Membership.[Membership No], Membership.Firstname, Membership.Lastname, Membership.Title, Membership.[Date of Birth]
FROM Membership
ORDER BY Membership.[Date of Birth];
new 3 April 2010
(If you wanted the report to list by descending Age, then add a calculated column to the query to calculate the age. There is a suitable function in section 3.2.3 of the Getting Started VBA Trainer.)
Most likely you will want to add up something, for example total bookings by member in the Chelmer Leisure scenario, or total products sold for a sales system. You may also require further parameters such as bookings/sales between two user-specified dates. You will need an aggregate query for this, with an appropriate sort order, then use Top on the totals column.
However, there is no very easy way to allow the user to select this Top value (5 in the example above) at run-time. Two suggestions are shown below; both need VBA code:
One way I have done this, for the 'top n' records where n is determined by the user, using VBA is:
Use a combo box on a form, and allow the user to select a value for the 'top n'.
Create the report as usual using your query.
Group the report on the field being used for the top value.
Set the required sort order (even though this has been specified on the query it
may need to be done again here).
Note that Access Top will take account automatically of equal value rows (joint
positions).
Delete the RecordSource property from the report property box.
In the Report_Open event set up the query SQL in a String
variable.
Use the value from the form combo box for the 'Top' value in the SQL.
Then assign the value in this String variable to the report RecordSource
property.
The report will now open using the data retrieved by the SQL.
In the Report_Open event set an integer variable to the value
1 (one).
The first record listed will be number 1 in the 'Top n' list.
In the Detail_Print event, add 1 to the integer variable for each line printed.
In the GroupHeader_Print event show the integer variable value in the header.
An alternative (possibly more challenging) method is:
Read the aggregate query (without the Top clause) into a DAO Recordset.
Read the Recordset row by row from the first record (this means coding a loop), taking account of duplicate values, writing the records to a temporary table (perhaps adding the ‘position’ in the list as a new field, or you can work this out using report event code as for points 6 and 7 above).
Use a combo box on a form for the user to specify the 'top n' value, and stop the loop when the appropriate number of rows have been read and written to the temporary table.
Work out your logic for this before you start to code it!
Then use this temporary table as the basis of the report.
And don't forget to add suitable improvements to your report. Some of these may require VBA; see section 5 of the Getting Started VBA Trainer. See also advice for report design. Improvements to your report should include:
Showing the 'top n' value, and any other query parameters used, in the report or page header.
Catering for an empty report.
Group and report totals.
25. How do I reconnect a mail merge Word document when I have moved the Access database?
When you create a mail merge Word document linked to an Access database, the full connection path of the database is stored with the document. If you move the database (or install the database and document on another machine) you will need to change the path.
This is relatively simple (if a little long-winded) to do. Click here for an explanation.
26. What does the message "Undefined function '<name>' in expression" mean?
This can occur when creating calculated fields on a query (and possibly in other situations). Some possibilities are:
You are using a built-in function, or one of your own, but have mis-spelled the name.
You are using a function of your own, but have not coded it yet.
The function is not declared as Public so cannot be accessed from another module.
You have a module name the same name as the function (so Access gets confused, understandably!). Change the module name.
27. What does the message "Table 'TempMSysAccessObjects' already exists" mean?
This message occurs when you try to close your database with 'compact on close' or when you use the menu Tools-->Database Utilities to compact the database.
When you make changes to data, objects or code in an Access database, new space is allocated for the new information. Deleted or old information is merely made 'unavailable', but it still occupies space. Thus, your database can grow very large during development but contain a lot of wasted space. During live use, changes and additions to data can slow down the database as access times increase. By compacting the database, you release the wasted space and tidy up (reorganise) the database.
Access compacts your database by creating a new version, copying across the current information and ignoring the old stuff, then deletes the old version and gives the new (compacted) version the name of your original database. But it needs space available on your disk to be able to do this. Access Help has some useful information; use the Answer Wizard with the keywords 'compact and repair'.
The message above appears (though this is guesswork) to be caused when there is not enough space to complete the compacting, and seems to happen when students are very close to their limit on University or College networks. (This, of course, happens nicely at the end of a semester/year, when project and assignment work is reaching deadlines!). Access does not give a message to say that the compact process has been cancelled, but just appears to stop without any indication that something was wrong. Next time you try it, the process is unable to continue as a temporary table (named in the message) was not deleted in the previous compaction.
I have been unable to find out how to locate the table and delete it.
If you are happy with the database being large (and getting ever larger) then simply don't attempt to compact it again.
If you need to overcome the problem (due to lack of space on your machine, or because it is a project for a real client) then the only solution that I can think of seems to be to:
Create a new (empty) database.
Copy across all objects (tables, queries, forms, reports, modules, etc) in your current database to the new database. [The associated code modules for forms and reports will be copied automatically with the object].
Recreate the relationships in the new database.
This method is long-winded, but works.
You should then be able to use the new database in place of your old one, and carry on with compacting it as required.
Later - the link at http://support.microsoft.com/kb/818099 gives further information, including how to delete the table.
28. Why can’t I enter data into my form?
With a form based on a table, there is usually no problem entering data, unless the AllowEdits and/or AllowAdditions property has been set to False.
If the form is based on a certain type of query, then data entry and insertion of new records is not allowed on the form (or via the underlying query). You can see if this applies to a query by looking at the navigation bar at the bottom of the query; if the ‘new record’ icon on the bar is greyed-out then this query will not allow data entry or addition. The query types include:
SELECT queries without a join (Cartesian product queries).
SELECT queries using DISTINCT.
SELECT queries using WHERE for the SQL join, rather than the INNER/LEFT/RIGHT join as used in Access.
Aggregate Queries (those using COUNT, SUM, MAX etc).
Self-join queries.
Queries with complex joins, perhaps based on other queries.
I would guess that the reason is that Access cannot easily identify the table row(s) that would need to be edited, so no editing or additions are allowed.
The problem can also occur if your ERD/FD is incorrect. For example, the joins between tables are incorrect or you are using cross-postings of foreign keys.
Queries such as the above are often required for reports, charts, list boxes, combo boxes etc, where data is displayed rather than updated, so the lack of editing does not cause a problem.
They can also be used for forms where information is simply displayed. Click events can be activated on some of these forms. Where the form is based on a Crosstab query, a click on a field which has a value in it will trigger the click event (if coded) for that field; a click on a Null field does not appear to do this. With some other query types, field click events may be activated for Null values as well – you will need to experiment with the query for your particular application.
29. Why can’t I use the combo box wizard to find a record on a form? The third option that should be there isn’t listed.
This wizard is available via the form design toolbox and normally gives the following three options:
1 I want the combo box to look up the values in a table or query.
o Use this for a combo box that provides a list from which the user can choose from a list of values taken from a table or query.
o The RowSourceType property is set to Table/Query and the RowSource property is the SQL that selects the required fields from the table/query.
o Set the LimitToList property to Yes if you want the user to be limited to the list shown.
o You can use VBA code to allow the user to update rows in the table with new list items; see the ‘Getting Started with VBA’ Trainer version 5 section 6.3).
o No VBA code is generated.
2 I will type in the values that I want.
o Use this for a combo box that provides a list from which the user can choose from a list of values.
o For this method you type the list in yourself, and it is stored in the RowSource property of the combo box. The RowSourceType property is set to Value List.
o Set the LimitToList property to Yes if you want the user to be limited to the list shown.
o This method is not as flexible as the method (above) that uses a table or query, as the list is fixed and can only be changed by the programmer/designer.
o No VBA code is generated.
3 Find a record on my form based on the value I selected in my combo box.
o This third option creates a combo box with items selected from the table/query on which the form is based. When the user chooses an item from the list, the associated record becomes the current record on the form.
o The RowSourceType property is set to Table/Query and the RowSource property is the SQL that selects the required fields from the table/query.
o See also the ‘Getting Started with VBA’ Trainer version 5 section 3.4.2.
o VBA code for the combo box AfterUpdate event is generated to find and move to the required record.
However, the third option will only show for a form that is based directly on a table or query. If the form was created via the form wizard, and fields were selected from two or more queries, then the third option for the combo box is not available.
Solution:
Look at the RecordSource property for the form (Data tab). This should show the SQL that selects the data for the form.
Create a new query, not based on any table/query and open the SQL window for the query.
Copy the form RecordSource SQL and paste it into the query SQL window.
Save the query with an appropriate name.
Change the form RecordSource property so that it now uses the new query (delete the form RecordSource SQL and choose the query from the drop-down box to the right of the property row).
You should now be able to use the combo box wizard and have all three options available.
Note: it is more flexible to base forms (and, indeed, reports) directly on tables/queries. Queries can be tested before the form/report is created, and any change to a query is then automatically reflected in the form/report. New fields added to a table/query can be added to a form/report using the Field List.
(Note – the third option above is also not available for unbound forms, as it is not applicable to such forms).
30. Why don’t my query criteria work when I use an input mask to attach a prefix character to a field?
Some students use an input mask of (for example) the type “M”0000 in order to generate key values of “M0001”, “M0002” etc, requiring the user only to enter 0001 and the mask will add the “M”.
However, this prefix character (or it could be more than one character) does not exist in the database table, but only in the mask for the user to see for data entry.
Therefore, when you have a criterion for a field like this you must enter only the numeric characters for your criterion. If the criterion uses a parameter field on a form then you could make this a combo/list box for the user to select from, or (for a textbox) set an input mask that automatically adds the prefix so that the user simply enters the numeric part.
See also the Custom Keys example database.
31. Why is my event code ignored? (e.g. click on button but nothing happens)
Check the property box for the control.
This should have [Event Procedure] against the appropriate event (e.g. Click event for a button, AfterUpdate event for a combo box) in order to provide the link between the control and the code (the code could be wizard code or code that you have written, or wizard code subsequently amended - it does not matter how the code was generated).
If the row is blank, choose to create the event (click ... at the end of the event row) and you should then be positioned in your event code. If this code consists just of a procedure header and footer, then one of the following may apply:
You have changed the name of the control (this usually breaks the link). If the control was created by a wizard, then the name you gave the control at that time will be used for the event code behind the control (e.g. cmdClose_Click for a wizard Close Form command button you called cmdClose). If you then change the control name the event name will no longer match. So - find the code for the original name and change the old name to your new one (or change the name of your control back to what it was).
You are using the wrong event. Check through the code for the name of the control and choose the correct event for the property box link and try again.
If you now look at the property box, the event link should be present and the control should work when activated.
32. How can I lock part of a form?
This is easily done (though can be time-consuming), as each field can be individually locked:
(a) in the form design view [see Crash Course in Access Basics section 5.3, Exercise Frm2(c)],
or
(b) via VBA code in an appropriate event (for example, the Form_Load event if wanting to set these properties for the form when it first opens).
Some useful properties for non-data-entry fields are:
set Locked property (Data tab on field property box) to Yes
set SpecialEffect (Format tab on etc) to Flat
set BackStyle (Format tab on etc) to Transparent
It can also be useful to move these fields to the end of the tab order
(View-->Tab Order).
If using VBA code, then code as follows (where FieldName is the name of a form
field):
FieldName.Locked = True
'note - use True, not Yes
FieldName.BackStyle = 0 'transparent - colour of form
will show through
FieldName.SpecialEffect = 0 'flat
Use VBA Help to explore the settings further.
You may also like to look at the 'Getting
Started' VBA Trainer sections 2.1-2.3, as this discusses using the form
AllowEdits property.
33. How can I order records in logical day of week order?
If you have a table or query with a field for the weekday name, and sort (ascending) on it, you will get the results in alphabetic order; Fri, Mon, Sat, Sun, Tue, Thu, Wed.
If your table contains dates, you have to create a query with the weekday names first.
The document linked here shows how you can get the data sorted in logical weekday order; Sun, Mon, Tue, Wed, Thu, Fri, Sat.
The Access database linked here contains the examples in the document.
34. Should I embed or link images into my database?
There is a lot of useful information this in Access 2003 at http://office.microsoft.com/en-us/access/HA011472041033.aspx in an article called “Use images in your Access forms, reports, and controls”.
The article also has two videos showing:
Demo: How to add a background image to a form or report
Demo: How to add a static image to a form or report
The article recommends using linked images for Access 2003, for various reasons concerned with:
The number of images to be stored.
Application size (“Access databases have a two-gigabyte size limit.”).
Performance.
Ability to handle more image file-types.
The necessity of having MS Photo Editor on each machine using the application (MS Photo Editor appears to have been removed from MS Office 2003, I think).
Linked images require the use of a little VBA, though it's not difficult; look at Access Help with “Add a picture or object“. They also mean that you need to refresh the image path if the image folder is moved. But these seem to be the only disadvantages of linking.
One way to avoid having to refresh the image paths with linked images could be:
Have all images in the same folder.
Store the path in a separate table in the database. This is best as linked table, separate from the application front-end.
Store just the filename in the link.
Concatenate the path and the filename when opening the form/report that shows the image.
If the images are moved, you simply have to change the pathname in the new database table. The application needs no changes.
This method is also very useful when testing the application on several different machines; simply change the path to match the location of the files. February 2008 - example database available.
Another advantage of linked images is that, if you have a logo that appears on all forms, reports, letters etc produced by the application, then you simply replace the old logo file with a new one (but with the same name) and the application itself needs no changes.
June 2008 - see comments in the Access 2007 What's New document and information about logos and attachments.
See also VBA FAQ 21 How can I search for files outside my database?
35. What does the message "The expression <event name> you entered..." mean?
This message will probably be part of a longer message similar to:
"The expression <event name> you entered as the event property setting produced the following error: Object or class does not support the set of events".
The causes for this include:
A compilation error. You should always clear all compilation errors before attempting to run any code. Even better, clear all errors as they arise (code a bit, compile it, test it, repeat...).
Your database is corrupted. This can happen if there is insufficient room on the disk for your database. So Compact your database (save a copy somewhere else first) to remove deleted items and keep the size to a minimum (see also the notice at the top of this page). I have managed to clear the error from a student database by creating a new database and copying each object across from the error database to the new one. I compacted the error database first.
Incompatibility between Access and Office. This error has also occurred with me when running Access 2002 on a machine that had Office 2007 (but not Access 2007) installed. So - ensure that your version of Access and your version of Office are the same.
It appears that the Access 2007 command button wizard generates the following code for the Close button:
Private Sub cmdClose_Form_Click()
On Error GoTo Err_cmdClose_Form_Click
If Me.Dirty Then Me.Dirty = False
DoCmd.Close
Exit_cmdClose_Form_Click:
Exit Sub
Err_cmdClose_Form_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Form_Click
End Sub
The line shown above in bold was not generated by the command button wizard in Access 2003 or earlier and now sometimes causes the above run time error if you are using a database with an Access 2000-2003 file format (a .mdb file).
According to http://support.microsoft.com/kb/121677 this error occurs with unbound forms (which makes sense, when you think about it). Their solution is to bind the form (using the RecordSource property) to a table, query or SQL statement. But I can't see why you need to do this - if your form is unbound then that's what you want - a menu, for example, is a common version of an unbound form.
My solution is to remove the line shown above in bold.
37. Why doesn’t a formatted prefix for an AutoNumber ID show in a combo box?
It is important to realise that using a format does not change the data itself, only how it is shown. Access Help states: “format: specifies how data is displayed and printed”.
A format used to add a prefix to an AutoNumber key therefore does not store the prefix with the key value in the data table. The prefix is only added when the value is displayed - but not always, as when the value is the bound field in a combo box. Unless you realise this it can be difficult working out what is going on. An AutoNumber value is numeric, and is entirely under the control of Access, so cannot include any other characters when it is stored - logical when you think about it.
When you want to use the value in a query, you must use only the numeric part, not the version with the prefix. This might be one of the reasons why the combo box value does not show the prefix, so that you can use a Forms Collection reference for the value (see point 3 below).
The database linked here contains the Chelmer Leisure Member and Category tables.
1. The two tables are joined by [Category No] with referential integrity enforced. The [Category No] in the Category table has had a prefix format added to the property for the AutoNumber Primary Key, but the Member table [Category No] Foreign Key has not got such a format. But the two tables still join correctly with referential integrity enforced, showing that the prefix is not part of the data, but just used for display.
2. Form frmMember1 is a simple wizard form, based on some of the fields in the Member table. The Category field has been changed to a combo box based on the Category table, with LimitToList = Yes and ColumnHeadings = Yes. The dropdown list for the combo box shows the category number with a prefix, but the category number in the [Category No] combo box displays as a number, without a prefix. You could display it with a prefix by entering the same format (\C00) for the combo box as is used for the [Category No] in the Category table, but this then displays "C00" for a new record. Removing the default value of zero for this field on the Member form before creating the form control may remove this (but I've not tried it).
3. Form frmMember2 is a copy of frmMember1 with the [Category No] combo box changed to hide the category number value (the ColumnWidth property has been set to zero for this column) so that the second column (the [Category Type] description shows in the combo box field. There is also a new unbound textbox txtCategoryNo and VBA code is used to show the category number value with a prefix added to it. The command button on the form will open a query qryMembersForCategory that selects all members in the Member table with the same [Category No]. If you look at the query you will see that there is a Forms Collection criterion referencing the combo box not the new text box, this picks up the numeric value in the bound (and hidden) column of the combo box.
4. Form frmMember3 is a copy of frmMember2 without the command button and with no VBA code. This uses the IIF Access built-in function to put the formatted [Category No] in txtCategoryNo so is an alternative version for those who don’t like using VBA. The code is in the RowSource property for the text box txtCategoryNo. Look up the IIF function in VBA Help – it’s a very useful function.
Finally, if you want to store the prefix with the AutoNumber ID in the table, then see the Creating custom primary keys example database.
38. How can I prevent a report field from being truncated?
If the field is for something like 'Comments' or 'Details' then you might like to consider using a Memo datatype. This will allow you to enter a lot of detail in the field and on the report it will automatically be sized to match the data over as many lines as are required. (See also the example database Opening Word (and other) Documents - this shows an alternative method from keeping large details within the database).
If the field is for something like 'Description' or 'Sporting interests' (the latter is an example from the Chelmer Leisure database), then do one of the following:
Set an appropriate field size in the table/form for the field, and ensure that the report field is large enough to show all details. This may not be possible in all cases if the report shows a lot of other fields as well.
Change the report field 'CanGrow' property to Yes (look at the field property box Format tab; the property setting is usually No by default). The contents of the field will now wrap round and be displayed in the same field width but over as many lines as are required, in a similar fashion to Memo fields.
39. Can I save a report as a PDF file?
Yes. See the document attached here. I think this is a new feature for Access 2007 (or else I missed it before...).
40. What do the different RowSourceTypes for list and combo boxes mean?
There are three possible values ('Table/Query'. 'Value List', 'Field List') that can be used for this property. The document linked here gives an overview.
The document here is from MS Access 2007 Help and gives some more detailed information.
The database attached here shows how to use these properties with some VBA code.
See Access FAQ #4, the Multi-purpose query database, for a further example.
41. Why do I get a dialog box “Enter Parameter Value”?
This dialog box is usually Access's way of saying that you are referring to the name of something that it does not recognise.
The attached document and database may help you to understand this and to spot why this problem is occurring with your database and help you to fix things.
42. Why doesn't Access 2010 give me the option to select a Style for new forms or reports?
See information on the Access 2010 page of this site.
43. What does the message "Missing or broken reference to the file 'MSCAL.OCX' " mean?
The reference is to the library for the Calendar Control that was available in previous versions of Access.
It has been removed from Access 2010.
See information on my Access 2010 page.
***********************End of Access Q&A**********************************
1. What does a "type mismatch" error mean?
It means that you are trying to compare apples with oranges!
You may have a statement such as:
If member-ID = user-request-ID THEN...
where you are trying to compare two data items.
The error means, quite simply, that the two values are not the same data type. Possibly one is text and the other is numeric.
The same error will occur for assignment statements where you are assigning a value to an item of a different data type, or if you are calling a procedure which is expecting a certain datatype but the value passed is not of the expected type (e.g. a form textbox value that is null or not date/numeric, and which has not been validated first). There will be many other situations when this can occur! Use the debugger to check the values in the variable(s) concerned.
The error can also occur when using Recordsets. See VBA FAQ 2 Why do I get a "Type mismatch" error trying to use Recordsets in Access 2000?
2. Why do I get a "Type mismatch" error trying to use Recordsets in Access 2000?
The "Further VBA" Trainer was developed using Access 97, which used Data Access Objects when accessing tables in VBA code. In section 6, for example, the code in Figure 6.1 contains the line
Dim BookingTableRecords as Recordset.
This is fine in Access 97 but will cause the "Type Mismatch" error in Access 2000, as Access 2000 uses ActiveX Data Objects (with, confusingly, very similar initials).
To adjust code so that it works in Access 2000, do:
Add
the letters DAO so that the code reads like:
Dim <Recordset name>
as DAO.Recordset
(replacing <Recordset name> with the correct name)
Still in the code window, choose Tools-->References then ensure that the item 'MS DAO 3.6 Object library' is selected in the list presented; this is not referenced by default.
The code shown in the "Further VBA" Trainer should now work in Access 2000.
See also:
Access VBA Help, keywords 'Converting DAO Code to ADO'. This is the source for the information above.
Explanation in section 3.1.4 of the 'Further VBA' Trainer v5.0.
3. Why does my list box click event code no longer work?
If you have created a form object such as a command button, then delete the button, the code is left in the form module. If you create another button with the same name, the new button click event will be linked to the code, as the names are the same. This can be handy if you want to redo a form object, as you do not have to redo code.
However, if you delete and recreate a list box, the old code may not always be picked up. So, to re-link it to the list box, go to the object property box and simply recreate the relevant event; this should then link to the code.
4. How can I create a multi-purpose query/report which allows the user to enter a variable number of values each time?
Note - the suggestions below mainly use Query Design and the Forms Collection - VBA only really comes into play for checking date ranges or for setting the sort order at run-time for the report.
Click here for an Example database - this is the result of following the instructions below, plus some extra bells and whistles.
Click here for version 2 of Example database - 30/3/2010, showing how to use a Field List combo box (see Access FAQ #40) and how to search through controls on a report page.
Click here for explanatory notes - this explains about the extra bells and whistles.
The Query
When a system is up and running, the user may want to do various one-off queries. The information from these queries may be wanted quickly, and the user will not want to go through negotiations with the system developer to produce the report and arrange a price. It is useful to provide a general-purpose query if possible. For example, the Chelmer Leisure Management (see scenario in Access Further Skills textbook) may want queries such as:
Details of all members with a particular category, with certain dates of birth, with certain sporting interests, by surname, by gender, etc.
Rather than having lots of separate queries, it is possible to create a query that picks up the required values from a form via parameter values in text and combo boxes, etc.
30/3/2006. See the Getting Started VBA Trainer section 7.3.5 for how to save this query dynaset to an Excel spreadsheet.
Try the following:
Create a form with unbound text boxes that are to contain the user's criteria for a selection of fields from the membership table. Make sure that the text boxes are the same data type as the corresponding field on the membership table.
Create a query to select all the fields from the membership table.
Add a command button to the form to run the query. Clicking on the button will bring up a list of all the members.
Querying on a numeric field:
The example database shows a method using a drop-down box
based on a Union query. Querying
on the starting characters of a text field:
Enter the following for the criterion for the last name:
Like [forms]![Memb query]![Lastname] & "*"
Now, entering a category of 1 and a lastname of J will list all members with a
category of 1 and lastname beginning with J.
This is also useful for postcode analysis, e.g. listing all members in the CH1
area of Chelmer.
Note that if the user leaves the parameter box blank
then this criterion will not select rows where this field is Null as "LIKE '*'
" does not select Null values. There must be a non-Null entry in every row for
this field. It would therefore be useful to set a default value for the field
and to set the Required property to Yes.
This comment amended 30/3/2006. Querying
on characters within a text field:
Enter the following for the criterion
for the sporting interests field:
Like "*" & [forms]![memb query]![sporting
interests] & "*"
and try this out with, say, aerobics (or even 'aer').
Note that if the user leaves the parameter box blank then
this criterion will not select rows where this field is Null as "LIKE '*' "
does not select Null values. There must be a non-Null entry in every row for
this field. It would therefore be useful to set a default value for the field
and to set the Required property to Yes.
This comment amended 30/3/2006. Querying
on a yes/no field:
Enter the following for the criterion for the sex field:
Like [forms]![memb query]![sex] & "*"
This field is (confusingly!) specified as a yes/no field in the book, so the
entry in the form field has also to be yes/no here. It would be better if this
was simply M or F in a single-character field, and the user would then select
via a combo box.
The example database shows a method using a drop-down box
based on a table. Querying
on a specific value in a date field:
Enter the following for the criterion
for the date of birth field:
Like [forms]![memb query]![date of birth] & "*"
Now you can specify a particular date or all dates.
Querying on a range of dates.
If Not [Date of Birth1] Then ' put the user's value in the
dob1 = [Date of Birth1] ' query field
Else ' or set a default early date
dob1 = #1/1/1001#
End If
If Not [date of birth2] Then ' as above, but set a
dob2 = [date of birth2] ' default late date
Else
dob2 = #12/31/9999#
End If
Now run the query and experiment - watch the values that get set in the dob1 and dob2 fields.
Finally - change the 'visible' property for dob1 and dob2 to 'No', so that the fields are now 'hidden'.
Querying
on a mixture of parameters or none.
All the above has been based on just one table, but it is also applicable to a form based on a query joining two or more tables.
The Report
As a report can be based on a query, then, instead of having the form command button run a variable parameter query, as done above, choose to run a report instead.
The report header should list the parameter values that the user entered in the form fields. This is very straightforward. Create unbound text box field controls in the report header and enter Forms![form name]![field name] in the control to pick up the value from the form (with the appropriate form and field names, of course!). If there is no value in the form, then this will show as blank.
The example database demonstrates all this, with the parameters shown in the report header and with an option for the user to specify the sort order at run time.
5. How do I update a table value via a form? For example, subtracting quantity sold from the stock total.
The form fields for the table fields are locked (by setting the appropriate field property to No) to prevent the user changing them. As these form fields are bound to the table, any change to the value on the form will also change the underlying table row.
The AmtSold field is an unbound text box - i.e. it is not bound to any table.
The ConfirmSale button is a non-Wizard command button (use non-Wizard buttons for purposes that the Wizards do not cover).
The VBA code is as follows:
Form Load event
Before Update event for AmtSold field
Click event for the ConfirmSale button
So, when the form is opened the sale button is disabled. When a sale is recorded, the button is enabled so that the user can click on it to confirm the sale. This activates code that updates the quantity in stock; as the form field is bound to the table row, this will automatically update the table. The button is then disabled to stop the user from clicking on it again; alternatively, the form could be closed.
It is also normal to have the unit price on the form plus a calculated field to show the value of the sale (quantity x unit price). See Access FAQ 10 How do I make a calculated field in a query, form or report?
In a properly designed system, the stock code number would be typed in (simulating a scanner) and the form opened showing just that record. There would also be checks (in the ConfirmSale click event) such as:
AmtSold > 0
AmtSold not null
AmtSold <= QtyInStock
The 'Getting Started' VBA Trainer covers validation of data entered into fields on a form.
6. How do I create my own keys? For example, membership key = M123456.
See the creating custom primary keys example database.
7. How can I change the size of a form at run-time?
Example - suppose you want a form to open so that it is always maximised:
In
the Form_Load event, code: DoCmd.Maximise
This will maximise the size of the form - and of all other windows (apart
from pop-up windows).
In
the Form_Close event, code: DoCmd.Restore
This will restore the form (and all other windows) to their previous size.
Look up Maximise and Restore in Help.
Also look up Minimise and SelectObject.
Look also at section 4.3.8 in the Getting Started VBA Trainer; this uses the InsideWidth property to set the width of a form at run-time. This bit added 15/1/2007.
8. How can I cater for production of (mailing) letters? changed 15/12/2003
Using a fixed letter set up as a report is a method often used by students. The report is designed with the letter text in a text box on the report, and with the variable data picked up from a bound table or query. This is very inflexible, as the user will have to go back to the developer to get the letter changed. This method may or not require VBA, depending on the complexity of the report.
The Mailing Letter example database demonstrates how to:
create individual and bulk letters by typing the text in a form, picking items from drop-down boxes, etc.
use Mail Merge (with a simple query and one that picks up a parameter value from a form)
All except the simple query Mail Merge use VBA.
9. I'm using the debugger for VBA code and get the message "The value in the field or record violates the validation rule for the record or field". What does this mean?
This has happened when one decides to stop the debugger, after replying 'Yes' to 'This command will stop the debugger'.
If you then run the code without the debugger everything is OK.
So it would appear to be caused by the stopping of the debugger, not an error in your code (I think...).
10. How do I pass an array as a parameter?
The two methods below show how to specify and use an array as a parameter in a private or public sub procedure or function. The example given is of a Public procedure, but this method also applies to Private procedures and/or Functions.
Method 1:
Public Sub myProc(prmArray() As <datatype>)
Where <datatype> = the datatype as in the declaration of the array, or Variant.
Note that the actual bounds of the array are not specified; the open and close brackets () indicate that this is an array.
The parameter as passed to the procedure must be an array or else the calling code will not compile: "Type mismatch: array or user-defined type expected".
Method 2:
Public Sub myProc(prmArray as Variant)
A Variant variable can cope with any datatype, including an array. Note that there are no () after the array name.
The calling code will compile if the parameter passed to the procedure is not an array (compare with Method 1).
The procedure
must therefore check at run-time that the parameter is an array:
If [Not] IsArray(prmArray)
then ...
Both Methods:
The array definition in the procedure header does not specify the bounds of the array, but Access provides two useful functions LBound and UBound which determine these at run-time:
For IntCount = LBound(prmArray) to UBound(prmArray) ...
11. What does the message "Compile Error: Expected variable or procedure, not module" mean?
This can occur when you have a module name the same name as a function (so Access gets confused, understandably!). Change the module name.
See also Access FAQ 26 What does the message Undefined function '<name>' in expression" mean? .
12. What does the message "Run Time Error 2001 - You cancelled the previous operation" mean?
This message is far from helpful, as it bears no obvious relation to the problem!
It can occur in VBA when Access cannot make sense of part of an embedded SQL statement, or when elements within a Domain Aggregate Function are incorrectly specified.
Example:
Look at the Staff Holiday Booking example database. The Holiday form uses a DSum statement to add up the total holiday days booked:
Forms![Staff]!txtSumOfDays = DSum("days", "qryHolDates", "[staffId] = " & Me!staffId)
If you change the "days" to "day" for the first parameter to the DSum statement then you will duly get the error message, as there is no field called "day" on qryHolDates.
The solution is to look carefully at each element of the SQL or Function, and check that they are entirely correct. Put all SQL in string variables, then you can check the contents at run (i.e. failure) time in the Debugger. It could also be useful to put the 3rd parameter (the SQL criterion) for a Domain Aggregate Function in a variable for checking at run/failure time.
Tip. it can be useful to try the function without the optional 3rd parameter (the SQL criterion). If it does not fail, then the fault is in the criterion, so check that carefully. If it still fails, then the fault lies with one (or both!) of the first two parameters.
See the end of section 6.6 of the 'Getting Started' VBA Trainer for a list of things that cause errors in embedded SQL - many of these also apply to Domain Aggregate Function code.
You may also get this error message when you hit OK on an unexpected parameter box, without actually entering a value. The parameter boxes (as you should know) pop up when Access cannot find the item specified - the usual cause is a misspelled name, or an incorrect name for a form or field.
13. Why doesn't the Round function round up numbers all ending in 5? Sometimes it rounds up and sometimes it rounds down.
Examples: Round(4.5,0) returns 4
Round(3.5,0) returns 4
The answer can be found at http://www.tek-tips.com/faqs.cfm?fid=3734 . The text at this site as at 11:13 6/12/2004 is copied below (as links can sometimes be removed).
(My thanks to Matthew Dean for pointing this site out to me, when I asked him why Round was apparently inconsistent/wrong).
http://www.techonthenet.com/access/functions/numeric/round.htm has a really neat solution! 8/3/2005.
"If you wanted to round 12.65 to 1 decimal place in the traditional sense (where 12.65 rounded to 1 decimal place is 12.7, instead of 12.6), try adding 0.000001 to your number before applying the Round function:
Round(12.45+0.000001,1)
By adding the 0.000001, the expression that you are rounding will end in 1, instead of 5...causing the Round function to round in the traditional way.
And the 0.000001 does not significantly affect the value of your expression so you shouldn't introduce any calculation errors."
| Start of text from
tek-tips site.................................... The Round() function rounds 2.45 to 2.4 and is correct. Why? |
Before you use the Round() function in Access, please read this FAQ or you may introduce calculation errors into your application: Syntax: Round(expression[, numdecimalplaces]) The Access Round() function appears to be a handy way of rounding decimal values to a given number of decimal places. For example, Round(2.341, 2) returns 2.34. However, it does not perform rounding the way you might expect. Take this example: Round(2.385,2) You would expect this to return 2.39, but if you open an immediate window and type ?Round(2.385,2) you will get the following output: 2.38 (not 2.39) Even though the last digit was 5, it rounded DOWN. If this is not the behaviour you expected, then here’s the explanation: The Round() function does a round-to-even, not the more well-known round-to-larger. If the argument ends in 5, the number may be rounded up or down to achieve an even rightmost figure. So in our example above: 2.385 could be rounded to either 2.38 or 2.39. The former has an even rightmost figure, so this is the result. The reasoning behind this is to eliminate cumulative errors over a series of calculations. Put another way, 100 half pennies should round to be equal 50 cents, not 100 cents. A RoundToLarger() function: Public Function RoundToLarger(dblInput As Double, intDecimals As Integer) As Double 'Implements a variant of the Round() function, that rounds-to-larger 'rather than rounds-to-even, like in Excel: Dim strFormatString As String 'Format string Dim strResult As String 'If input is zero, just return zero. Else format as appropriate: If dblInput <> 0 Then strFormatString = "#." & String(intDecimals, "#") If Right(strFormatString, 1) = "." Then strResult = Format(dblInput, "#") Else strResult = Format(dblInput, strFormatString) End If Else strResult = "0" End If 'If the result is zero, Format() will return "." - change this to "0": If strResult = "." Then strResult = "0" End If RoundToLarger = CDbl(strResult) End Function Acknowledgement: Thanks to “hnawebmaster” for help in compiling this FAQ. Dave Mc Donald www.mcdonaldit.com End of text from site.................................... |
31/1/2005. I think there is an error in this function if the result is zero. The lines:
'If the result is zero, Format()
will return "." - change this to "0":
If strResult = "." Or strResult = ""
Then
need changing as shown above in red, or the run time error 13 'Type Mismatch' may occur.
I have also had a go at my own function, but it's dependant on the maximum values allowed by the data types, so will not work for numbers with more than 15 digits in total. The code is shown below, for those who are interested.
Public Function myRound(prmNumber
As Double, prmPlaces As Integer) As Double
'own function to round 5-9 up and 0-4 down
'VBA Round function rounds-to-even for .5.
'IMPORTANT - if prmNumber has more than 15 digits (regardless of the where the
dec. pt is)
' then the Double datatype would appear to truncate the least significant digits
'try testing with ?myround(12345678901.12345,2) and look at the value passed in
prmNumber
Dim dblIntegerPart As Double 'to
store the required digits
Dim dblDecimalPart As Double 'to store the
surplus decimal places
Dim dblTemp As Double
'for intermediate calc only
'move the decimal point by the required number of digits
'example, 4.45 required to 1 decimal place gives 45.5
dblTemp = prmNumber * (10 ^ prmPlaces)
'note: 10^0 = 1
'truncate the value to get rid of the unwanted decimal places
dblIntegerPart = Fix(dblTemp)
'subtract truncated value from temporary number to find out
what the unwanted digits are
dblDecimalPart = dblTemp - dblIntegerPart
'are the unwanted digits +0.5 to +0.99999etc or -0.5 to
-0.99999?
If Abs(dblDecimalPart) >= 0.5 Then
If prmNumber < 0 Then
dblIntegerPart = dblIntegerPart - 1 'subtract 1 for -ve number
Else
dblIntegerPart = dblIntegerPart + 1 'add 1 for +ve number
End If
End If
'put decimal point back in original position and return the
rounded value
myRound = dblIntegerPart / (10 ^ prmPlaces)
End Function
14. How can I add a criterion to a Crosstab query?
See also Access FAQ 17 What is a CROSSTAB query?.
NOTE added 12/4/2005 - this item also applies to 3-D charts. If you look at the SQL generated by the Chart Wizard you will see that it is a TRANSFORM query. If you wanted to add a criterion, then see the information below.
Simple literal criterion.
To add a criterion with a literal value specified in the query is fairly simple. Add the column to the query, set the grouping to ‘Where’ and add the value in the criteria cell. The SQL shown in Access FAQ 17 with a criterion of [Category No] = 1 will then be (Where clause shown in bold):
TRANSFORM Count(Membership.[Category No]) AS [CountOfCategory No]
SELECT Year([Date of Birth]) AS [Year of Birth]
FROM [Membership Category] INNER JOIN Membership ON [Membership Category].[Category No] = Membership.[Category No]
WHERE (((Membership.[Category No])=1))
GROUP BY Year([Date of Birth])
PIVOT [Membership Category].[Category Type];
Parameter criterion.
However, if you used a criterion that used a parameter such as [Please enter Category No] or a Forms Collection reference such as forms!frmcategory![Category No], your query would fail with a message similar to:
The Microsoft Jet database engine does not recognise “[Please enter Category No]” as a valid field name or expression.
or
The Microsoft Jet database engine does not recognise “forms!frmcategory![Category No]” as a valid field name or expression.
A way round this is to put the criteria value(s) in a table and use IN for the criterion expression.
Example (using the query from Access FAQ 17 as above):
1. Create a new table called TempCat, with a numeric field called Category No.
2. Create a form with an unbound textbox called txtCategoryNo.
2.1 In the txtCategoryNo_AfterUpdate
event code:
DoCmd.RunSQL "DELETE * FROM TempCat"
DoCmd.RunSQL "INSERT INTO TempCat VALUES (" & txtCategoryNo & ")"
This will delete any previous rows from the table, and add the new value from
the textbox to the table. See Part 6 of the
Getting Started VBA Trainer for information about the DoCmd.RunSQL
statement (which includes information about suppressing the various information
messages that you will get when deleting/inserting table rows)..
3. Alter the Crosstab query criterion to IN (SELECT [Category No] from TempCat), so that the SQL now looks like:
TRANSFORM Count(Membership.[Category No]) AS [CountOfCategory No]
SELECT Year([Date of Birth]) AS [Year of Birth]
FROM [Membership Category] INNER JOIN Membership ON [Membership Category].[Category No] = Membership.[Category No]
WHERE (((Membership.[Category No]) IN (SELECT [Category No] from TempCat)))
GROUP BY Year([Date of Birth]), Membership.[Category No]
PIVOT [Membership Category].[Category Type];
The query will now pick up the required parameter value, which can be varied by changing the value in the textbox.
Tables used to store parameter values do not have to have just one row (though the SELECT SQL in the IN clause must select only one column). The SQL statements can generate several rows in a table. For example, if you wish to select values between a pair of dates, and if the date parameters on a form are called txtStartDate and txtEndDate, then the code to generate rows in a table called TempDate which has just one (date datatype) column could be as shown below. Note that dates in SQL statements must be in USA format!
Dim dtDate As Date
Dim strDate As String
'delete previous rows from table
DoCmd.RunSQL "DELETE * FROM TempDate"
dtDate = CDate(txtStartDate) 'set to start date on form
Do Until dtDate > CDate(txtEndDate) 'loop until end date has been passed
'dates in SQL statements must be in USA format
'so must reverse day and month before putting in SQL statement
strDate = Month(dtDate) & "/" & Day(dtDate) & "/" & Year(dtDate)
'write date to temp table
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & strDate & "#)"
dtDate = DateAdd("d", 1, dtDate) 'add one day
Loop
Access Help.
Look up “Create a parameter query” in Access Help.
Additional note - 1st March 2005
A possible public function to do this date-reversal (and to be used wherever needed) is:
Public Function myUSADate(prmUKDate As Date) As Date
'Convert date from dd/mm/yyyy (UK) format to mm/dd/yyyy (USA) format
Dim strDate As String
strDate = Month(prmUKDate) & "/" & Day(prmUKDate) & "/" & Year(prmUKDate)
myUSADate = CDate(strDate)
End Function
And it would be used in an embedded SQL statement as follows:
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & myUSADate(dtDate) & "#)"
Use the Debugger Immediate Window to test the function, and specify dates as dd mmm yyyy to avoid confusion, as Access also dates dates specified as dd/mm/yy in this window to be USA format. For example:
?myUSADate(#1 mar 2005#)
Further note - 6th April 2006
If you create a wizard combo box to find records using a date field, then Access generates the following code to convert the dates to USA format:
"#" & Format(Me![cboFindDate], "mm\/dd\/yyyy") & "#"
(where cboFindDate is the name of the combo box)
15. Why does a calculated date give the wrong result in an SQL statement?
Access 2002 Help says:
“You must use English (United States) date formats in SQL statements in Visual Basic. However, you can use international date formats in the query design grid.”
(Search with ‘dates in SQL’)
If you have a form with a date field called txtStartdate, a variable called dtDate of Date datatype, and a table called TempDate with just one column for a date, then the following statements will all insert the correct date in the destination table:
dtDate = txtStartDate
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & dtDate & "#)" ‘from variable
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & txtStartDate & "#)" ‘from form
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & Date & "#)" ‘today’s date
However, if you want to do a calculation on a date (calculate a date a library book is due back, for example) statements such as
dtDate = DateAdd("ww", 2, Date) ‘add 2 weeks to today
'dtDate = DateAdd("d", 14, Date) ‘alternative method
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & dtDate & "#)"
will treat the date returned from the DateAdd statement as being in mm/dd/yyyy format (I.e. USA format). So you will need to reverse the day and month elements of the date, as demonstrated in VBA FAQ 14 above. Putting the date in a textbox on a form and then using the value from that textbox does not work, even though the textbox displays in correct UK format!
If the day number value of the date is greater than 12 (20/2/2005 for example), Access seems to realise that the date is in UK format and will make adjustments itself (20th February 2005). But if not (3/5/2005 for example), it will assume USA format (5th March 2005 rather than 3rd May 2005).
16. Why don't the Date and Time functions work in my module?
Some students have experienced problems where a call in VBA code to the Date() or Time() function returns Null or an invalid/incorrect value (and this can cause a run-time failure 'invalid use of Null' in some cases).
The problem seems to occur in a form or report module where there is a table, report or form field called Date or Time. If you code something like:
Dim dtDate as Date
dtDate = Date() 'get system date
then Access appears to refer to the field called Date, not to its own function. (It would be handy if this situation gave rise to a compilation error or a specific run-time error, then at least you know what was going on).
Solution 1.
Don't use the names Date and Time for table, form or report fields.
(The Chelmer Leisure database from McBride does use these names, unfortunately).
Solution 2.
Create your own functions in a separate Access module. As the module is not bound to a table or query, there is no conflict between names, and Access will use its own functions correctly. Code the following two procedures:
Public Function myDate() As Date
myDate = Date
End Function
'-----------------------------------------------------------
Public Function myTime() As Date
myTime = Time()
End Function
Then code as follows to get the system date and time:
Dim dtDate as Date
Dim dtTime as Date
dtDate = myDate() 'get system date
dtTime = myTime() 'get system time
17. Why doesn't my 'On Error' code trap the error?
Check that you are using the same version of MS Access as MS Office.
For example, if you are using MS Access 2003 with MS Office 2002, then the 'On Error' code does not appear to be invoked; any error condition is intercepted by Access with the usual Access error message and the code will fail.
However, if you use an Access 2002 MDE file created with Access 2002, and then run it on a machine that has Access 2003 with Office 2002, all appears to work OK.
18. How can I get totals on a form?
You may have something like an Order form with a separate (or sub) form for each Order Line, and want to show the order totals (from counting/adding information from the order lines) on the Order form.
Look at the 'Crash Course' in Access Basics document.
The relevant bit is section 5.5, Exercise Frm4, 'put
totals on a form', part (b). The example here counts up a total number of
attendees at a class (using the Chelmer Leisure scenario), using DCount.
If you want to add up order totals (i.e. total cost of the order) you will
need to use DSum to sum up the appropriate value.
Look also at the VBA Trainers and the Example Databases for examples of use of these functions.
19. How can I copy details from one form to another?
This is very easily done using the Forms Collection.
There are examples of use in sections 4.3.8 and 8.4.6.2 of the 'Getting Started' VBA Trainer (available from the Student Advice Centre) and Appendix I discusses this Collection briefly.
Suppose (for example) you have a customer form which opens an Order form, and you want to copy customer details to the Order form automatically. You would code the assignment statements in the Form_Load event for the Order form.
Look also at the Example Databases for examples of use of the Forms Collection.
20. What does “Run-time error ‘3061’: Too few parameters. Expected 1” mean?
I have had this error when using the OpenRecordset method (DAO code) when I have misspelled a table field name in the SQL. After experimenting a bit, I found that it also occurs with some SQL syntax errors. So - the error probably means that the SQL is incorrect and cannot be run.
If you misspell the table name with SQL in DAO code then you may get run-time error 3078 instead; this has a very clear error message explaining the likely cause of the error.
Look
at the end of section 6.6 of the Getting
Started VBA Trainer for a list of common mistakes when writing embedded SQL
using the DoCmd.RunSQL method; this list also applies to SQL embedded in DAO
code.
21. How can I search for files outside my database?
This is very easily done using the Search Folders Collection. Look in Access VB Help using ‘Search Folders’ and at the article at http://msdn2.microsoft.com/en-us/library/aa164017(office.10).aspx
The FileSearch item apparently is deprecated in Access 2007. I haven't tried it in 2007 yet, so don't know what this means in practice. See http://allenbrowne.com/Access2007.html .
In order to use the various methods and properties you first need to install the MS Office 10.0 Object Library (via Tools-->References from a code window).
Access VB Help has example code that shows you how to use the Collection, and display the found filenames in message boxes (but it omits to show you that you need to declare some variables first).
The code shown below has been used by me to locate linked image files. The path of the images folder is in a table called Path which has just one field called Path (and just one row). The code reads the filenames (excluding the full path) and puts them into a table called ImageName which has one field called ImageFileName. This code is called when the application starts, so the ImageName table is refreshed to show the list of images currently available.
Public Sub myFindImages()
'must install Microsoft Office 10.0 Object Library to use this
'install via Tools-References from a code window
'code adapted from VBA Help for SearchSubFolders Property
Dim i As Integer 'loop counter for the files found
Dim fs As FileSearch 'FileSearch Object
Dim strLookIn As String 'path to search in
Dim intLen As Integer 'length of folder path
Dim intLen2 As Integer 'length of path + filename
Dim strSQL As String 'SQL to add filename to table
Dim strFilename As String 'filename (no path)
'get path of folder from path table
strLookIn = DLookup("Path", "Path") 'get path of images folder
intLen = Len(strLookIn)
strLookIn = Left(strLookIn, intLen - 1) 'remove backslash at the end
'delete previous entries from image list table
strSQL = "DELETE * FROM ImageName"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
'find each file in images folder and add filename to image list table
Set fs = Application.FileSearch 'assign this application's file search property to the FileSearch Object
With fs
.LookIn = strLookIn 'specify the folder to be searched
.SearchSubFolders = True 'if want to look in subfolders as well
.Filename = "*" 'look for all folders - won't find anything if this line missing
If .Execute() > 0 Then 'do the search - returns 0 if no files found
For i = 1 To .FoundFiles.Count 'for each file found...
intLen2 = Len(.FoundFiles(i)) 'full path for file
strFilename = Right(.FoundFiles(i), intLen2 - intLen) 'strip off just the filename
strSQL = "INSERT INTO ImageName VALUES('" & strFilename & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL 'put filename into table
DoCmd.SetWarnings True
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
22. How can I capitalise the words that make up a name?
See below for a function
to capitalise the initial letters of words.
This takes into account the
following characters:
‘ (apostrophe) – as
in O’Hara
- (hyphen) as in
Parker-Bowles
(space) as in
Burton On Trent
To use the function code something like: Name = myWordCase(Name)
in the After_Update event for the field Name.
Access 2003 (and after) has a constant vbProperCase which can be used to convert names with spaces in them:
Town = StrConv(Town,vbProperCase)
But this doesn’t appear to take account of names with apostrophes or hyphens.
Public Function myWordCase(prmString As String) As String
'takes a given string and changes the start letter of each word to uppercase & the rest to lower case.
'looks for space, apostrophe and hyphen to denote start of a new word.
Dim intLen As Integer 'stores num of chars in prmString
Dim intCounter As Integer 'counter for position within the string
Dim strString As String 'variable for working on the given string
strString = LCase(prmString) 'start by converting all chars to lower case
'Left(strString, 1) = UCase(Left(strString, 1)) 'can't use this - get rt-error 424 'object required'
Mid(strString, 1, 1) = UCase(Mid(strString, 1, 1))
intLen = Len(prmString)
For intCounter = 1 To intLen 'check each char in turn for space, apostrophe or hyphen
If Mid(strString, intCounter, 1) = " " _
Or Mid(strString, intCounter, 1) = "'" _
Or Mid(strString, intCounter, 1) = "-" Then
If intCounter + 1 > intLen Then 'is next position outside end of string?
Exit For 'yes - exit loop
Else
'convert char after found char to upper case
Mid(strString, intCounter + 1, 1) = UCase(Mid(strString, intCounter + 1, 1))
End If
End If
Next 'go round again
myWordCase = strString 'return the converted string
End Function
23. What does "The current field must match the join key '?' in the table..." mean?
The full text of the message is: "The current field must match the join key '?' in the table that serves as the 'one' side of the one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table".
(It really would help if the message identified the field and the table...)
This occurred with a student final year project and took me several hours to sort out. Details are below:
Background
The project had an order form based on a query joining the orders and customer tables:
tblCustomer (CustID, Forename, Surname, CustomerType,...)
tblOrder (OrderID, CustID, OrderDate, ...)
qryOrder (SELECT tblOrder.OrderID, tblOrder.CustID, tblCustomer.Forename, ...)
The idea was that the CustID field on the order form be a combo box based on customer details, the user would select the customer who is placing the order, and the customer detail fields would then be populated on the form.
The error message above occurred when attempting to place a new order and selecting a customer from the combo box.
The student database was a .mdb database, and the error occurred when running under Access 2002, 2003 and 2007.
Cause of the error
The form used an option group for the CustomerType, bound to the field on the Customer table, but the Orders table was (correctly) the table with the primary key in the query qryOrder. It appears that this binding causes the error - I'm guessing this is because of the way Access handles option groups and tries to match values in the field with values specified in the option group.
When the binding was removed, the error no longer occurred.
If a combo box, or a simple text box, was used for the field, even if bound, the error did not occur.
Solution
The option group should be unbound.
In the Form_Current event write code to move the CompanyType value (perhaps from a hidden field on the form, or use something like DLookup) to the frame for the option group:
IF Not IsNull(OrderID) Then 'existing record
Frame99 = CompanyType
End If
Write the same line in the CustID_AfterUpdate event:
Frame99 = CompanyType
This worked in the project mentioned above.
24. How can I send emails from my database?
Three possible methods of doing this are listed below:
(a) Hyperlink datatype
It is possible to use a hyperlink datatype for an email address in a table (and thus for the associated field on a bound form). Clicking on the address in the form field will open your default email client with the email address inserted in the To row ready for you to complete the email.
If the user wishes to edit the field then he/she must click and hold in order to be able to do this.
(b) Hyperlink button
Create your email address field as a standard text field.
Create a non-wizard command button on the form. Set this to be a hyperlink by typing anything (doesn't have to be a valid email or anything sensible) in the hyperlink property.
Then, in the button click event type something like:
cmdSendEmail.HyperlinkAddress = "mailto:" & txtMemberEmail
When the user clicks on the button, the button hyperlink address will be set to the appropriate value for the value in the email field text box, and the default email client will open as before.
(c) DoCmd.SendObject method
This needs a bit more VBA code but can be more flexible as you can set up various parts of the email (address[es], subject, message, etc) and can choose to have the email open in the default email client or be sent automatically. The example database Sending Emails uses this method.
Feb 2009. The example database attached here shows how to send an attachment with an email. This document shows Access 2002 Help for DoCmd.Send Object (this gives a bit more information than the Help in Access 2007).
You might also like to look at http://www.utteraccess.com/forums/showflat.php?Board=84&Number=1597715
25. What does "Run-time error '3251': Operation is not supported by this type of object" mean?
You are using a property or method that is not available at this time (though the line that fails has compiled).
This occurred for two students who were attempting to use the FindFirst method for a Recordset. When declaring the Recordset the second parameter had been omitted. In Access 2003 (or earlier) this did not matter as the default was (or appeared to be) dbOpenDynaset. However, these databases were created with Access 2007. It may be that the Access 2007 default is now something else.
So code the open command as:
Set rstName = dbName.OpenRecordset("tblName",
dbOpenDynaset)
not
Set rstName = dbName.OpenRecordset("tblName")
26. Why does my loop code cause Access to ‘hang’?
I recently (March 2009) hit this problem with a couple of loops using DAO Recordsets. The loop processing was quite complex, with a lot going on for each record, involving further loops (to process further Recordsets and strings) and DLookup statements..
When I tested the loops initially using breakpoints and MsgBox statements to trace execution all was well. As soon as I removed the breakpoints and MsgBox statements the code just ‘hung’ at the start of the main loop.
My colleague Matthew Dean supplied the answer; he thought that this could be a disk cache problem. Having the MsgBox statements allows the cache to clear any writes to take place. What could be happening is that the loop is hogging the system and the code is failing as a result. He suggested that I try coding the command called DoEvents which tells the system to do what it needs to do. This worked a treat under both Access 2002 and Access 2007.
All I did was code just DoEvents at the point in the code where previously I was using the MsgBox statements (thus ignoring the value retuned by the function) so that it executed every time through the loop.
Access 2002 and 2007 VBA Help have the information shown below about the DoEvents Function:
(Access 2007 also has an example which stores the value returned by the function, and executes the command every 1000 times around the loop)
|
Yields execution so that the operating system can process other events. Syntax DoEvents( ) Remarks The DoEvents function returns an Integer representing the number of open forms in stand-alone versions of Visual Basic, such as Visual Basic, Professional Edition. DoEvents returns zero in all other applications. DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent. DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing. Caution Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control. |
27. What does "Runtime error 3027 - cannot update. Database or object is read-only" mean?
This error has occurred with students using DAO code where the Recordset SQL has involved a standard SQL join as a WHERE clause. Queries with this type of join cannot be updated by Access (I don't know why!) - Access can only update queries where the table join is coded using its own version of INNER/LEFT/RIGHT JOIN (an Access join).
See also Access FAQ 28 Why can’t I enter data into my form? - the problem is the same.
A way round the problem in DAO code is as follows:
Create a query to select the records for the recordset.
If necessary use the Forms Collection for criteria to pick up the required records (the criteria values can be put in hidden fields on a form if they are not on a form already).
Make sure the table join is an Access join (remove your WHERE criterion and draw the usual line to join the tables in the query design window).
The SQL for the recordset will now be of the form: "SELECT * FROM Query1".
This method also has the advantage that you can test out your query before using the recordset.
28. Why does DoCmd.SendObject to send emails cause Access 2010 to crash?
I don't know why this occurs but I found a fix that worked in my database.
I was checking the library references (via Tools-->References) in a code window and noticed that the ActiveX Library was above the DAO library. I only use DAO code and usually have these library references the other way round (so that DAO takes precedence). So I swapped the references and sending the emails now worked OK. My default email client is Outlook Express.
I found some items on the web from people who also had this problem but none of the first few I checked seemed to suggest why this was happening or how to fix it.
http://www.utteraccess.com/forum/DoCmdSendObject-t682420.html looks a useful alternative method. I paste the code below in case the link changes or is removed:
29. How can I change a report or chart RowSource SQL at run-time (dynamically)?
If you have a report based on form parameters, and/or one that uses the TOP clause, then you can set the RowSource property SQL in the Report_Open event. See Access FAQ 24 How do I select the top/bottom rows of a query dynaset?, the extra bit added April 2010.
Me.RecordSource = strSQL
'where strSQL = a string variable in which you have set up the required SQL
However, setting the SQL in the RowSource property for a chart isn't as simple. I tried using various events to set this property, but without any luck. then I came across the following page which provides a simple solution that does the trick: http://www.pcreview.co.uk/forums/error-setting-rowsource-using-vba-chart-t3417699.html
Create a query for the chart - it doesn't really matter what it does as it will be changed later (see point 3 in this list).
Go to the property box for the chart and change the RowSource to use the new query.
In the Report_Open event code the following:
CurrentDb.QueryDefs("qryForChart").SQL = strSQL
If the chart is based on the data for the report then you can use the same strSQL variable
************************************End of VBA Q&A*************************************