NotInList Adding a Value to a Combo Box at Runtime
The NotInList event property is triggered when you enter a value into a combo box that is not contained within the list. This event property and procedure works in conjunction with the LimitToList property, which is a Boolean property of a combo box that instructs Access to restrict selections to values in the list. This example comes from the need to add a new value to the underlying table used to populate a combo box list. Because a combo box was designed to display values from a table, it...
Altering the SQL Inside Queries
Sometimes it can be advantageous to alter the SQL inside a saved query. This is especially common when you are using pass-through queries to another database like SQL Server, but it can also come in handy when you need to nest Access queries several layers deep. Since the queries a few layers down can't be modified directly in a report or form's recordsource you may need to change them directly. Use the right syntax Remember, if you use this technique for a pass-through query, you must use the...
Backup DatabaseProject
Finally, a consistent, fast, and easy way to save your work. We can't tell you how many times we've either closed and zipped a database, or copied and renamed a database object to test some changes. Pretty soon the database window is filled with nonfunctional objects. And heaven forbid that someone forgets if Name AutoCorrect was on, which can result in the code referring to the wrong object. Again, the Access team felt the developer's pain and provided an awesome solution. You don't even have...
Properties of the Report Object
The properties of the Report object are listed in the following table. Used with the Screen object to determine the control that has the focus Returns the currently active application object Determines whether the report will be automatically centered within the Application window Determines whether the report will be automatically resized to display complete records Specifies the type of border and border elements for the report Specifies the caption in the title bar for the report Specifies...
Updating Records
The code for updating records in a recordset is almost the same as adding them, including the need to specify the dbOpenDynaset Type. You may also need to find the correct record to update using FindFirst. If you find it successfully, you can update it. .FindFirst CustomerKey amp Me CustomerKey If Not .NoMatch Then 'we found the record .Edit CustomerName ABC Construction CustomerStatus 1 .Update End If End With The With statement is purely a programming convenience. Instead of typing the name...
OnChange Late Bind a Subform on a Tab Control
The degradation of performance is a never-ending battle for the Access developer as users want more and more information, on the same form no less. But, no matter how accommodating you are to satiate their every need and desire, the smallest of performance hits causes your phone to ring and e-mail inbox to overflow with complaints. Even when you tell them ahead of time The reduction of Access performance sometimes sneaks up on you slowly, other times it's instantaneous. In case you're new, the...
Using the Macro Builder
The Macro Builder opens a new blank macro for you to work with. Before you can work with your macro, you'll need to enter the macro's name. Click OK to save your blank macro. You can now choose from a variety of actions to include in your macro. Click the first line of the Actions column to choose an action from the drop-down menu. Depending on the action you choose, you might have to enter additional information to complete your macro. For example, if you choose RunApp from the Actions combo,...
Using a SelfCertification Certificate on Another Computer
Since self-certified databases won't be trusted on another computer, you need to add your self-certification certificate to other computers that will be accessing your databases. To do this you need to create a file from your certificate, copy the file to the other computer, and add the certificate to that computer. One way to create the Certificate CER file is to view the details of the certificate from the Visual Basic Editor. To get to the details of the certificate, select Tools Digital...
Internal Certificates
An internal certificate is intended for use on a single computer or within a single organization. An internal certificate provides similar protections as the commercial certificate in that if the file or VBA project is changed, the certificate is removed, and the database will not automatically open under High or Medium security. Internal certificates can be created and managed by a certificate authority within your organization using tools such as Microsoft Certificate Server. You can create a...
OnCurrent Opening Existing Records as ReadOnly
The On Current event property triggers each and every time a record is accessed. Thus, adding a Procedure to this event will increase the load time for the user, so utilize this procedure with caution. If the user is accustomed to instantaneously browsing through multiple records, and you add a procedure that causes a noticeable delay, expect to receive a nasty e-mail. For this example, suppose that you want to always start a record in a read-only state, and when the user is ready to edit the...
Setting Macro Security Level
If you've worked with Word or Excel documents that have macros VBA code in them, you've probably seen the Security Warning message like the one showing in Figure 20-2. If you have seen that warning, you probably already have an idea about how to adjust the security settings in Access so that you can open your database. The instructions are pretty basic but have some implications that you should consider. To set up Access so that you can open your database, select Tools Macro Security. You will...
Access Wizards
As you can tell from the extensive list of enhanced wizards, Access is getting more user-friendly all the time. Adds an AutoDialer control to a form, datasheet, or toolbar. The wizard incorporates modem information and dials the number Creates a form automatically based on the selected table or query. Well, the end result is enhanced to the extent that forms themselves are enhanced Applies a predefined style and format to a form or report, and allows creation of custom styles Creates a data...
Specifying a Cursor Location
In contrast with DAO, when you create an ADO connection, you should specify a cursor, which is a database element that controls record navigation, data updateability, and the tracking of changes made by other users. There are two types of cursor client side and server side. Choose the cursor you want to use by setting the Command or Recordset object's CursorLocation property to one of the following two constants, before you open the connection or recordset rs.CursorLocation adUseClient...
Using the ADE Tools
Access Developer Extensions ADE is a set of great tools for the serious, and possibly not-so-serious, Access application developer. The ADE package includes tools that enhance your ability to design, develop, and deploy Access-based applications. The ADE package includes a tool designed to improve your application development capabilities the Property Scanner. The Property Scanner makes it easy to search collections, objects, and properties of a database for the occurrence of a particular term...
Easy Upgrading
First and foremost, developers need to know about the issues surrounding upgrading and converting. Fortunately, it is very easy and painless to upgrade from Access 2000 or Access 2002 to Access 2003. In fact, 2002 and 2003 have the same file format. The only real differences between 2002 and 2003 are the several new features. And converting from the Access 2000 file format to 2003 is as easy as selecting Convert on the Convert Enable dialog window. This is covered in detail in Appendix A. But...
Concept Review
Previous chapters introduced objects, methods, events, and properties. To recap the differences between these programming concepts, refer to the following table, as well as whenever necessary during the learning process. Understanding the differences between them will increase your ability to learn and understand VBA programming. An entity that can be manipulated with code Form, Report, Text box, Combo box, Command button, DoCmd, Debug Any intrinsic built-in functionality already assigned to an...
The Move Method
The Recordset object's Move method allows you to move the cursor to another position relative to either the current position, or that specified by a Bookmark. The Move method provides two arguments The rows argument specifies the number of rows to move, and the direction greater than zero indicates forward, less than zero means backward. The optional start argument specifies where to start the move from. When you supply a Bookmark for the start argument, DAO moves the cursor the appropriate...
Macro Security
Now more than ever, we have to concern ourselves with the security of our computer systems. One form of security addresses securing the information contained in our databases as well as the intellectual property built into our databases. This form of security was discussed in Chapter 16, Database Security. Another form of security has to do with preventing malicious attacks on our computers attacks that can delete files, spread viruses, or otherwise disrupt our work. This chapter discusses the...
Raising CustomDefined Errors
The descriptions for a great many error messages must have been written by programmers whose native language was something other than English. Some of them make for interesting reading, but there are quite a few that don't go too far toward educating you about the reason for the problem or its resolution . Raising your own errors provides the flexibility of displaying more user-friendly or user-specific error messages. The VBA Err object provides a Raise method, which lets you construct and...
Understanding ClientServer Development with VBA
Access makes it very easy to create applications that interact with other desktop database formats and enterprise-level database servers. However, the easiest methods are not always the best, and wrong choices can have serious unintended effects in the long run. A thorough understanding of how Access interacts with other databases and the various alternatives available for developers is critically necessary to make the best design choices for a specific application. In a typical business...
Create a Switchboard through the Switchboard Manager
There's another easy way to create a switchboard for your application. You can use the Switchboard Manager to create a new switchboard or edit an existing switchboard. Choose Database Utilities from the Tools menu and select Switchboard Manager from the popout menu see Figure 1-11 . To create a new switchboard, click the New button. Enter a name for your new switchboard. Once you've created your switchboard, click Edit to control the items displayed on your switchboard. For a new switchboard,...
Methods of the Recordset Object
There are a number of methods you'll use when manipulating your Record object as well. Those methods are listed in the following table. Cancels pending changes in a Recordset that uses batch optimistic updates Cancels pending changes currently being edited Creates a new reference to your Recordset that allows you to navigate independently from the original Recordset Closes the Recordset object and releases its contents Compares two bookmarks in the same Recordset Deletes the current record in...
Properties of the AccessObject
The AccessObject has its own properties and methods you can implement within your code. The following table lists the various properties of the AccessObject. Returns the current view for the specified Access object Returns the date the AccessObject was created Returns the date the AccessObject was last modified Sets or returns the full path including name of the object Boolean value that specifies whether the current object is loaded String that contains the name of the currently loaded object...
Creating Relations
The basic procedure for creating a Relation is as follows 1. Create the Relation object using the Database's CreateRelation method. 2. Set the Relation object's attributes as appropriate. 3. Create the fields that participate in the relationship, using the Relation object's CreateField method. 4. Set the Field object's attributes as appropriate. 5. Append each field to the Relation's Fields collection. 6. Append the Relation object to the Database's Relations collection. The following code...
Advantages to Using VBA over Macros
While macros are perfectly acceptable and even recommended in certain situations, there are some key advantages to using VBA instead of a macro. The following is a list of some of the advantages you'll enjoy using VBA instead of a macro. Error Handling If a macro encounters an error, it just stops. For example, a macro created to open a form will fail if the form can't be found. While Access will provide a fairly detailed error message informing you the macro failed, you can't add any error...
Signed and Unsigned Integers
The first thing to note at this point is that the C language uses something that is unknown to VBA signed numbers. VBA only uses unsigned numbers. For example, Figure 13-6 shows an 8-bit byte. Notice that having a binary 1 in the most significant bit the eighth bit signifies that the number contained within the byte is a negative number. A 0 in the same position indicates that the number is positive. Although the total number of values that a single byte can hold employing both positive and...
Error Handling with Logging
Some developers write code to insert an error log record into a table when an error occurs. The idea is to be able to analyze when and where errors have occurred by querying this table long after the errors happened. However, this technique has some issues. Access does not provide a way to determine the name of the procedure that is currently running. Since any error logging routine needs to know which procedure caused the error, you would need to manually code the name of the current procedure...
Managing Permissions 1
Setting permissions in ADO is even easier than it is in DAO. The Catalog object's Users and Groups collections manage permissions GroupOrUser.SetPermissions ObjectName, ObjectType, Action, Rights , Inherit , ObjectTypeID The ObjectTypeID parameter is an optional variant value that specifies the GUID for a provider object type that isn't defined by the OLE DB specification. This parameter is only used if you set ObjectType to adPermObjProviderSpecific. You should refer to the provider...
Windows Registry Data Types
In the same way that database table fields, variables, and API parameters require data of specific types, the kind of data the Registry can store is also defined in terms of data types. The following data types are supported under Windows 2000. This data type specifies raw binary data. Most hardware information is stored with this data type, which can be displayed and entered in binary or hexadecimal format. This data type is a 32-bit 4-byte number, which is used to store Boolean values and...
A Touch of Class
Classes have been likened to rubber stamps, cookie-cutters, and a raft of other everyday items in an attempt to make the concept more easily understandable. Since you are reading a book on software development, it is fairly safe to assume that you understand the concept of a template, like a Microsoft Word template. We like that analogy, because it succinctly describes the role of class modules and the distinction between them and class objects. Just like a class module is equivalent to a Word...
Testing for an Empty Recordset
As mentioned in the previous section, if you attempt to move beyond a recordset's boundaries, an error will occur. Similarly, if you attempt to execute any other recordset method on an empty recordset one that has not returned any records , an error occurs. Whenever you open a recordset, the first thing you need to know is, did it return any records When you open a recordset, you usually want to do something with the data it returns. If the data is there, you can confidently take whatever...
Using Access with SharePoint Services
Access 2003, when teamed with SharePoint Portal Server or SharePoint Team Services 2.0 can provide some pretty awesome data sharing capabilities. Both flavors of SharePoint Portal Server and Team Services have the ability to share and track Microsoft Office documents among multiple users. And, with built-in document check-in check-out, version tracking, subscription services, e-mail updates and alerts, Windows SharePoint Services WSS makes it easy to share and manage information. It can even be...
Creating an MDW
Your first step in creating user-level security is to select or create the MDW file that will contain the user groups and users you will define. As mentioned earlier, this can be a new file or the default file provided when Access is first used. The recommended approach is to create a new MDW file. The User-level Security Wizard will not permit you to use the default SYSTEM.MDW file. Also, if you corrupt the default MDW file, you will have to manually recover that file from another source. Many...
Adding Code to Scroll the Recordset
Although the form can now display our custom data, it does not yet have much functionality because we have no method to scroll through records or to change any data. This section will go over adding code to some of the buttons created earlier to allow for browsing records. Add the following code to the cmdMoveFirst button Private Sub cmdMoveFirst_Click rsTest.MoveFirst pPopulateFields End Sub Add the following code to the cmdPrevious button 1 Code is needed to check to see if the recordset is '...
Regedtexe
Prior to Windows XP and Windows Server 2003, Regedt32.exe was the preferred 32-bit Registry Editor for Windows NT and 2000. But, of course, nothing is perfect, and Regedt32.exe had limitations, for example, it could not import or export Registry entries .reg files. Now, under Windows XP and Windows Server 2003, Regedt32.exe is a simple wrapper program that runs Regedit.exe. On Windows NT and 2000, you should use Regedt32.exe whereas on Windows XP and Windows Server 2003, you can use either...
The Application Object
All of the Access objects you'll manipulate within code are derived from the Application object. This is the parent object for all other objects and collections within the Access object model. All objects and collections are related to the Application object, either through a direct parent child relationship or through multiple parent child relationships. Figure E-1 lists the objects and collections that are the direct children of the Application object. The Application object refers to the...
Splitting a Database
There may be a situation where multiple users actually share one database file that contains both the user interface UI and the data. Let's hope that this is a very rare situation, because it is a scenario that is prone to database corruption. But, having the data in the same file as the forms and reports becomes a significant limitation if users want to use different versions of Access. This situation has plenty of other limitations, particularly those related to performance and corruption....
Direct Access Objects
DAO is the programmatic interface between VBA and Jet databases, ODBC Open Database Connectivity data stores, and installable ISAM Indexed Sequential Access Method data sources, such as Excel, Paradox, dBase, and Lotus 1-2-3. As is described in greater detail a little later in this section, DAO supports two different data access environments, or workspaces Microsoft Jet and ODBCDirect. You use Microsoft Jet workspaces when you need to take advantage of Jet's unique features, such as the ability...
Saving a Recordset to a File
To do all this, you use the Recordset object's Save method. The following examples demonstrate how to save, reopen, modify, and then resave a recordset. Not all providers allow you to save a recordset to a file. You're safe with the Jet OLE DB provider, but to be certain with other providers, open the recordset using a client-side cursor. Dim rs As ADODB.Recordset Dim strADTGFile As String Dim strXMLFile As String 'Open the recordset rs.CursorLocation adUseClient rs.Open Customers,...
More about Errors and How to Fix Them
In addition to the warning message seen in Figure 20-14, you may run into the error message showing in Figure 20-15. The error occurs if you have not installed Service Pack 8 and attempt to use one of the wizards in Access. Some features will not work properly until you install Jet 4.0 SP 8. They include the following Switchboards created by Switchboard Manager won't function properly. You can't use the RunCode macro action to call a VBA procedure. Event handlers that call VBA procedures won't...
Common Report Events
Here are some events on reports that are commonly used. These events can run code to customize and display reports so that they are much more flexible for your users. Report On Open This fires before the recordset is evaluated for the report. Just as with forms, you can use this event to change the recordset by changing the Where or Order By clause before the report continues to load. This can be especially useful when you use a form to prompt the user for selection criteria before the report...
Creating a Parent Property
Suppose you wanted to create a relationship between a parent class and a derived class. The parent class would have code like this Option Compare Database Option Explicit 'Create a new instance of the derived class Set mobjDog New clsDog 'Create the relationship between the parent and child classes mobjDog.Parent Me End Sub Of course, you would also need Parent property procedures in the derived class. Option Compare Database Option Explicit Public Property Set Parent objKennel As clsKennel...
XML and Access
XML can be used to send, store, and display data, and even related tables, via an Internet or intranet. Think of using your browser to view Access data and even the schema or data structure. With Access 2003, you can import and export XML data and transform data to other formats using XML related files. This works with both a database .mdb and an Access project .adp . To build on familiar ground, we'll start with the similarities between XML and HTML. 5attlRad.com Access Cellphone Sample...
Obtaining a Digital Certificate
As mentioned earlier, you can obtain a certificate from a commercial authority such as Verisign, Inc. For internal certificates you can turn to your security administrator or Digital Certificate group, or you can create your own certificate using the Selfcert.exe tool. You need to be aware that if you create your own certificate, Access will still generate the macro security warning when your signed database is opened on a computer other than the one where the certificate was created High or...
VBA Error Handling
When programmers use the term error handling, they really mean graceful or planned error handling. After all, Access will take some kind of action for any error that it encounters in your code. Graceful error handling includes the following Quietly absorbing expected errors, so the user never sees them. For unexpected errors, displaying a friendly message to the user, and closing the procedure properly. Error handling in Access VBA involves adding code to every procedure both subs and functions...
The DebugPrint Statement
We've already established that the character is short for Debug.Print. It's easy to use both of these statements directly in the Immediate window however, that's not the only place you can use this statement. Consider the following code Sub FunWithStringsAndNumbers Dim strBikes As String Dim strCost As String Dim strCustomerName As String Dim intBikes As Integer Dim curCost As Currency strBikes 5 strCost 100 strCustomerName The W Hotel, New York City intBikes 5 curCost 100 Debug.Print strBikes...
Reserved Words and Special Characters
There are numerous words that should not be used to name fields, objects, and variables. For the most part, these are called reserved words. Reserved words have a specific meaning to Microsoft Access, the Microsoft Jet database engine, or in conjunction with SQL Server or ODBC drivers. It may be easy to start a list of reserved words by thinking of the list of all the properties of database objects, all Visual Basic keywords, and all third-party and user-defined names and functions. Using...
Sharing Information Is a TwoWay Street
When sharing information between multiple Microsoft Office programs, you can write code two ways. The first way is to write the code within Access to push the data into the other Office programs. The second way is to write code within those other programs to pull code from Access into the other program. This two-way street works for Word, Excel, Outlook, and PowerPoint. Because this is an Access 2003 VBA book, we'll spend more time covering the push direction, but don't worry, we'll provide...
SaveSetting
SaveSetting allows you to store a single value in the HKEY_CURRENT_USER Software VB and VBA Program Settings hive. Its syntax is as follows SaveSetting appname, section, key, setting The arguments are described in the following table The arguments are described in the following table This argument is a required string expression that contains the name of the application or project whose key is being set. This is a required string expression that contains the name of the section under which the...
Creating Other Types of Outlook Items from Access
Creating e-mail messages in Outlook isn't the only way you can use VBA and Outlook to enhance your application. You can create meetings, appointments, tasks, and journal items within Outlook using VBA. The Planning department can create an Outlook task directly from the Access application. This task will remind them to check with the receiving department on the appointed day to ensure the frame material has arrived. If the material hasn't arrived within several days, the Planning department...