Home  aadconsulting.com_ 

Access Extra No 18 April 2003_

  Access Tools and Databases AAD CONSULTING Sydney Australia_ | _Advanced Access Development
     
 What's New

Office 2003 News

  • The Jet database engine will get a new service pack, Jet 4 SP7.
  • Access 2003 will have a new Object Dependencies Task Pane, graphically representing object dependencies in a tree view.

Access KB Browser Article References Updated

A further 94 articles references, mostly for Access 2000, have been added, for a total of 2,260 articles. Download the updated database from http://www.aadconsulting.com/kbbrswer.html

The new articles are also listed in the New KB Articles section of the newsletter.

 Tips
Compact & Repair Trick

In a database with a StartUp Form, the form will open after a Compact/Repair operation on the open .mdb. To avoid this annoyance during development, hold down the <Shift> key before clicking Compact and Repair (Access 2000 and Access 2002). In Access97, this only works for Compact Database.

Get more tips, samples, articles and code from the

FreeAccess Access Utilities CD

Get the guid of a library reference from code

Sub AddReference()

___Dim refItem As Reference

___Set refItem = Access.References.AddFromFile _
___("D:\Program Files\Common Files\Microsoft _
___Shared\DAO\dao360.dll")

End Sub

Since the guid is unique for every object library, for example, the guid of "Microsoft DAO 3.6 Object Library" is always
{00025E01-0000-0000-C000-000000000046}, and you can obtain the guid of any object library using the above code. You can then use the .AddFromGUID method to add a reference to the object library without knowing the file path.

Formatted MessageBox in Access 2000/2002

Function FormattedMsgBox( Prompt As String, _
Optional Buttons As VbMsgBoxStyle = vbOKOnly, _
Optional Title As String = vbNullString, _
Optional HelpFile As Variant, _
Optional Context As Variant) _
As VbMsgBoxResult

___If IsMissing(HelpFile) Or IsMissing(Context) Then
______FormattedMsgBox = Eval("MsgBox(""" & Prompt & _
______""", " & Buttons & ", """ & Title & """)")
___Else
______FormattedMsgBox = Eval("MsgBox(""" & Prompt & _
______""", " & Buttons & ", """ & Title & """, """ & _
______HelpFile & """, " & Context & ")")
___End If

End Function

Prevent users from tabbing to the next record

Prevent users from tabbing to the next record in a form by setting the form's Cycle property to 'Current Record'.

Windows System Colors

Color ID No... Color Description

-2147483648 Scroll bar
-2147483647 Desktop
-2147483646 Active window title bar
-2147483645 Inactive window title bar
-2147483644 Menu bar
-2147483643 Window
-2147483642 Window frame
-2147483641 Menu Text
-2147483640 Window Text
-2147483639 Title bar text
-2147483638 Active window border
-2147483637 Inactive window border
-2147483636 Application background
-2147483635 Highlight
-2147483634 Highlight Text
-2147483633 3-D face
-2147483632 3-D shadow
-2147483631 Dimmed (disabled) text
-2147483630 Button Text
-2147483629 Inactive window title bar text
-2147483628 3-D highlight
-2147483627 3-D dark shadow
-2147483626 3-D light
-2147483625 ToolTip Text
-2147483624 ToolTip background
-2147483621 Active window title bar color2

Using Access 2000/2002 Help

Another tip. Highlight a keyword in a module and Hit <F>1 to open the Help file entry for that keyword.

Subform Shortcuts

<Tab> to move from the control immediately before the subform to the subform

<Ctrl>+<Tab> to move to the control on the main form that follows the subform *

<Ctrl>+<Shift>+<Tab> to return to the control that precedes the subform *

* If there isn't a control on the main form to move to the main form will move to the next or preceding record.

 Downloads
Daigle.Net MS Access Page
 Code Samples
imdList all CommandBars in a Database

Private Sub ListMenuBars()
'Need a reference to Office Object Library

Dim cbar As CommandBar
Dim cbctl As CommandBarControl

___For Each cbar In CommandBars
______Debug.Print cbar.Name, cbar.NameLocal, cbar.Visible, cbar.Index
___Next

End Sub

Download a .bas module I developed to create a Custom Toolbar in Word, and view VBA code for creating Menu Items and Toolbars in Office applications on the fly.
 Useful links

MS Smart Solutions

 Coming soon...
  • A new version of the SmartForm Wizard that will create a lightweight form with zero code behind it, intelligent navigation buttons, and a standardised global code module.
  • Ezy SQL Editor and Builder Add-In that significantly improves on the Access Edit SQL Window.
  • XML Explorer Add-In Explore XML documents with this Access Add-In. As XML documents are structured as trees of data, with this tool you can dig down into XML data using a Windows Explorer like interface.
  • Sticky Toolbar - an Access form-based Toolbar that moves with a form.
   New Access KB Articles
     

Article No

Access Version

Article Title

Q209757

ACC2000

Choosing a New Printer Resets Printer Settings

Q268159

ACC2000

Parameter Arrays Do Not Work with NPV Function

Q263650

ACC2000

How to Use the UniqueTable Property of a Data Access Page

Q297020

ACC2000

AutoCorrect Does Not Capitalize the First Word of the First Sentence

Q209091

ACC2000

How to Optimize Microsoft Access When Using ODBC Data Sources

Q288751

ACC2000

Unable to Create Dynamic Active Server Page (ASP) in Access 2000

Q207679

ACC2000

ActiveX Controls List Does Not Show All ActiveX Controls

Q209787

ACC2000

Network Users Can Open Their Own System.mdw Exclusively

Q228641

ACC2000

Access Help for the ApplyFilter Action or Method Is Incorrect About the Third Argument

Q208247

ACC2000

KeyDown Event Doesn't Fire for Custom ActiveX Controls

Q269444

ACC2000

#Deleted Entries Appear in Alternate Rows in Linked SQL Server Tables

Q208226

ACC2000

TransferText Can't Import Data into Enabled 2.0 Database

Q208900

ACC2000

Linked SQL Server Binary Data Displayed Incorrectly

Q209011

ACC2000

How to Start Macros, Functions, and Applications from a Combo or List Box

Q207857

ACC2000

Excel ISAM Rounding Errors

Q246953

ACC2000

BUG Access 2000 Quit Method Leaves Access Running

Q239886 ACC2000 How to Simulate AutoLookup with a Stored Procedure in Access Client/Server

Q248738

ACC2000

Microsoft Jet 4.0 Reserved Words

Q207829

ACC2000

How to Manipulate ZIP Codes in Microsoft Access

Q209207

ACC2000

How to Use Command-Line Switches in Microsoft Access

Q207769

ACC2000

How Microsoft Access Saves the Sort Order of a Form Depends on the Version

Q209811

ACC2000

Tips on How to Troubleshoot Microsoft Access Macros

Q299973

ACC2000

Using Microsoft Jet with IIS

Q207761

ACC2000

Changes in Use of DISTINCTROW in Microsoft Access 2000

Q247488

ACC2000

Column Properties in Query May Disappear After You Run DAO Code or a Wizard

Q209120

ACC2000

No Password Prompt Appears When Using Microsoft Access .MDB with ODBC

Q206641

ACC2000

Articles on Enhancing Window and Screen Display

Q209723

ACC2000

Microsoft Access Appears to Hang During a Database Compact Operation

Q208159

ACC2000

Paste Link to Microsoft Excel Displays First Field Name Only

Q209805

ACC2000

"Could Not Find Installable ISAM" Error Message

Q225939

ACC2000

Cannot Use ADOX Views Collection with SQL Server or MSDE

Q231802

ACC2000

How to Create a Top 10 Report

Q209919

ACC2000

How to Use Animation ActiveX Control in Access

Q209587

ACC2000

Microsoft Graph Changes May Not Be Retained

Q815285

ACC2000

"Can't Open Registry Database" Error Message Occurs When You Run the Workgroup Administrator Utility

Q210037

ACC2000

How to Test a User-Defined Filter Using the Filter By Form Feature

Q815033

ACC2000

Update Query Still Modifies Records After You Cancel the Query

Q815277

ACC2000

"Numeric Field Overflow" Error Message Occurs When You Query a Table That Is Linked to Excel Spreadsheet

Q209215

ACC2000

How to Reset the Page Number on Group Level in a Report

Q209128

ACC2000

Compile Error Occurs Using Derived Math Functions in Help

Q278099

ACC2000

"????????" Is Displayed as the Value of a SQL Server GUID Field

Q210035

ACC2000

How to Create a Collection of Collections

Q210019

ACC2000

How to Use Automation to Rotate a Three-Dimensional Chart

Q210017

ACC2000

How to Use Automation to Change Border Properties for a Chart

Q209066

ACC2000

How to Create and Use Subqueries

Q209745

ACC2000

Default Values for AutoLabel and AddColon Properties

Q242889

ACC2000

MsgBox Function Ignores At Sign (@) Formatting

Q207586

ACC2000

"Too Few Parameters" Error Occurs on ASP with Parameter in Subquery

Q814811

ACC2002

"Disk or Network Error" Error Message When You Open Access 2.0 Database Files

Q299974

ACC2002

Using Microsoft Jet with IIS

Q295222

ACC2002

Access Unexpectedly Quits When You Click in Filtered Group of a Data Access Page

Q295277

ACC2002

Access Quits When Running a SQL Query

Q814858

ACC2002

Access May Stop Responding When You Try to Create an MDE File or an ADE File

Q331989

ACC2002

Memory Is Not Released When You Close Forms and Reports

Q295270

ACC2002

Access Quits When Previewing Report Created from Form

Q295230

ACC2002

Filter Toolbar Disappears When You Use the Advanced Filter/Sort Feature

Q295228

ACC2002

Cannot Add Control While Code Is Running Behind Form

Q295217

ACC2002

Access Quits When You Switch from Print Preview to Design View and then to Datasheet View

Q295232

ACC2002

Access Quits When You Sort a Table While Renaming a Column

Q290081

ACC2002

Cannot Create a Data Access Page Connected to SQL Server 6.5

Q235960

ACC97

"ORDER BY Clause Is Not Valid..." Error with Linked Table

Q168438

ACC97

Changes with DISTINCTROW in Microsoft Access 97

Q182762

ACC97

Single with Currency Format Displays as Double When Edited

Q222135

ACC97

Using Microsoft Jet with IIS

Q269580

ACC97

#Deleted Entries Appear in Alternate Rows in Linked SQL Server Tables

Q245685

ACC97

Errors Linking SQL Server 7.0 Table That Has Spaces in Table Name

Q171193

ACC97

Sample Procedure to Find the Location(s) of a File

Q248186

ACC97

#Deleted Appears in Linked Oracle Tables in Access Service Release 2 (SR-2)

Q176614

ACC97

Microsoft Access 97 Upsizing Tools Available

Q191619

ACC97

"Too Few Parameters" Error on ASP with Parameter in Subquery

Q176568

ACC97

How To Use Visual FoxPro DBC Files in Microsoft Access

Q168302

ACC97

Time Cards Subform Not Filtered with New Records

Q815281

ACCESS

"File sharing lock count exceeded." Error During Large Transaction Processing

Q167928

ACCESS

How Microsoft Access Saves Sort Order of Form Depends on Version

Q329824

ACCESS

Synchronization of an Access Table May Fail with "The Synchronization Failed Because a Design Change Could Not Be Applied to One of the Replicas." Error Message

Q171850

ACCESS

"Too few parameters" When Using Microsoft Access Data in Microsoft Query

Q812719

ACCESS

HOW TO Programmatically Create a New Column in an Access Report

Q170142

ACCESS

Issues When Using the BuildCriteria Method

Q169153

ACCESS

Sample Query to Print One Label for Two People in a List

Q173933

ACCESS

OLE Object Error When Setting the RowSourceType Property

Q174005

ACCESS

Error Updating Field When Adding New Record to Form

Q179899

ACCESS

How To Create a Custom Paper Size in Windows NT 4.0

Q90100

ACCESS

Remote ODBC Tables Are Read-Only Without a Unique Index

Q180167

ACCESS

No Warning Closing a Form with a Required Field Left Null

Q812718

ACCESS

HOW TO Reset an AutoNumber Field Value in Access

Q328828

ACCESS

Troubleshooting Errors That May Occur When You Update Data in Queries and in Forms

Q180458

ACCESS

Problems Outputting Report with Datasheet Form as Subreport

Q155666 ACCESS Troubleshoot ""Couldn't Find Installable ISAM"" Error
Q182823 ACCESS "Continued" Header with Groups That Span Multiple Pages (95/97)
Q187685 ACCESS How to Display an AutoLookup Field in Word Mail Merge
Q181497 ACCESS How to Create a Chart That Does Not Summarize Data
Q128809 ACCESS How to Create a Chart That Does Not Summarize Data

Q817575

OFF2000

Brazilian: Text Is Incorrectly Flagged as Incorrect Spelling

Q816916

OFF2000

New Naming Schema for Microsoft Office Hotfix Packages

     
- Back Issues


No 17 March 2003
No 16
February 2003
No 15
December 2002 / January 2003
No 14
November 2002
No 13
October 2002

Download a Searchable Compendium of Issues 1-12  

Visit our Link Partner:
Superior Software for Windows
Access, SQLServer and .NET Downloads

http://www.ssw.com.au
Top

Top

Editor: Tony D'Ambra
CONTENTS:
What's New
Tips
Downloads
Code Samples
Useful links
Coming soon...
New AccessKB Articles
Back Issues

Subscription to this newsletter is free.

This newsletter may contain links to sites or files on the Internet which are owned and operated by third parties. AAD CONSULTING is not responsible for the content of or files from any such third-party site.

Any files are provided "as is", without warranty of any kind. AAD CONSULTING does not assume any liability for use or the consequences of use.

To subscribe or unsubscribe from this mailing list, select a choice from combo-box below, enter your email address and click OK:

Have questions or comments?
Send them to us.

Access is a trademark of Microsoft Corporation.

©2001-2003
Anthony D'Ambra.
All rights reserved.

Top