Some useful new features for Access 2000
Date last changed: 26/03/11
Instead of having to remember to repair/compact your database, you can now specify that you want Access to do this automatically whenever you close the database.
Click the 'compact on close' box on Toolsà Options: General tab.
See also What does the message "Table 'TempMSysAccessObjects' already exists" mean?
The Name AutoCorrect option allows you to propagate field name changes in tables in fields to queries, forms and reports. Previously you would have had to make the changes separately in each area affected.
Click
the required boxes in the 'Name AutoCorrect' option on Toolsà
Options: General tab.
Subdatasheets in table datasheet view now let you see information from related tables.
Use the + sign by the ID field. See also Formatà Subdatasheet and try the options.
Show 4-digit years for Short Date
Provides the ability to show 4-digit years for Short Date, rather than the default 2 digits, via Toolsà Options: General tab.
See also Help for 'Use four-digit year formatting'.
Now that we are just past the start of a new century it is important to show many dates in a 4-digit-year format.
4-digit year formats should always be used for dates of birth.
See below to see the change in the property box for date fields when this option is set.
This feature gives you the ability to define conditional formatting of form and report controls based on values in the current row, without writing VBA code or macros. It is very similar to that in Excel, and as easy to use.
Some examples of situations where this can be used are:
Highlighting overdue membership on Membership form
Open the form in design view, click on the required control (in this example it is the [date of last renewal] field), choose Format-->Conditional Formatting, and set the condition and formatting as required. In the example below, the condition is that the last renewal date is before (less than) a year ago (today's date minus 365 days). The formatting chosen has been to use bold font with a red background.
This is an example of comparing the field value with a numeric expression, here using the built-in function Date(). You could also use a function of your own; see Highlighting overdue membership renewal on a report - this method uses a function that will also allow for leap years.
And this is what the form looks like:
Highlighting free slots on BookingGrid form
In the BookingGrid form used in Part 6 of the Further VBA Trainer, it is now possible to show booked slots as grey so that the free slots stand out better. It was not possible to do this in Access 97, even with VBA code.
Open the form in design view, click on the required control (in this example it is the [FSuite] field), choose Format-->Conditional Formatting, and set the condition and formatting as required.
It is not possible to use 'Null' or '=Null' here as a direct comparison with the field, as what you enter will then be taken as a string text for comparison. You need to use an expression with Null.
And this is what the BookingGrid form now looks like, with the booked fields showing with a grey background. If you wanted to remove the class and membership numbers as well, a simple method would be to set the font to the same colour as the background.
Highlighting overdue membership renewal on a report
This example uses the 'member for n years' report from Section 2.11 of the Getting Started VBA Trainer. That report used VBA code to set the formatting to highlight members listed whose last renewal date was a year or more ago.
The example below sets the font to blue if the renewal is overdue. The expression is that from the VBA code shown in Fig 2.11.3 of the Getting Started VBA Trainer, and shows that it is possible to use a user-written function (in this case myCalculateAge) in a conditional formatting expression.
And the resultant report looks like:
Highlighting negative amounts on a financial statement
The example here is a very simple 'bank statement' report. There are three places in the report where a negative balance can show, the brought/carried forward balances, and an intermediate balance during the statement.
The screen below shows how to use conditional formatting to test the bound report field [last balance] for a value less than zero:
And the result of this is:
The running balance and the carried-forward value are both unbound text fields, with the values in them calculated using VBA code. Applying conditional formatting here does not seem to have any effect, possibly because the field is a textbox (although defined as currency) and is being compared with a numeric value? This therefore has to be coded using VBA:
If txtBalance < 0 Then
txtBalance.ForeColor = vbRed
txtBalance.FontBold = True
Else
txtBalance.ForeColor = vbBlack
txtBalance.FontBold = False
End If
You can now create Data Access Pages (objects that include an HTML file) for users to view, browse and edit database information via the Internet or over a company's local Intranet. This feature requires that the user has MS Internet Explorer 5 or higher on their machine plus an Office 2000 licence, so the pages will not be suitable for full use over the Internet with all browsers.
This replaces previous publishing facilities in Access 97.
It provides the ability to create dynamic HTML data access pages for publishing. See database window - data access pages are created/edited via the usual tables/queries/forms/reports/modules list.
See also Data Access Pages.