Info Nyu
The Procedure menu item appears unavailable disabled if the code window in The Add Procedure dialog box in Figure 6.1 allows you to name your procedure and select a procedure type and scope. Adding a procedure with the Add Procedure dialog box. Adding a procedure with the Add Procedure dialog box. If you select All Local Variables as Statics, your procedure-level variables maintain their values through your program's execution. After creating your procedure, the Add Procedure dialog box tells...
Info Nui
All of the code required to implement the Choose My Adventure Main form follows. Option Compare Database Option Explicit 'Declare form-level Connection object variable. Dim localConnection As ADODB.Connection Private Sub cmdAbout_Click 'Show the About form. DoCmd.OpenForm About End Sub ' Use the assigned option button value to get ' the next page in the book. GetPage Me.fraQuestionAndOutcomes.Value End Sub Private Sub cmdQuit_Click 'Quit the application. DoCmd.Quit End Sub MsgBox Welcome to The...
INSERT INTO Statement
You can use SQL to insert rows into a table with the INSERT INTO statement. The INSERT INTO statement inserts new records into a table using the VALUES clause. VALUES '1234abc456edf', 'Beginning SQL', 'Vine', Though not required, matching column names can be used in the INSERT INTO statement to clarify the fields with which you're working. INSERT INTO Books ISBN, Title, LastName, FirstName, Publisher VALUES '1234abc456edf', 'Beginning SQL', 'Vine', Using matching column names is necessary, not...
A Ono
Wow The information in Table 2.2 should trigger an interesting question in your head, which goes something like this. If binary codes can represent both characters and numbers how do I know what type of data I'm working with The notion and application of variables help to answer this question. Variables provide a storage mechanism that accurately manage the binary representations for us. For example, if I store data in an Integer variable, I can feel pretty good VBA will give me back an Integer...
Do While
The Do While loop uses a condition at the top of the loop to determine how many times statements inside the loop will execute. Because the loop's condition is checked first, it is possible that the statements inside the loop never execute. In this loop, it's possible that x is less than y, preventing the statement inside the loop from ever executing. In the Do While loop, the statements are executed so long as the condition evaluates to true. In other words, the loop stops when the condition is...
Assistant Object
Because of its professional graphics and animation, the Assistant object is a popular Microsoft Office Object to work and learn with. More than likely, you have already seen the Assistant object with Microsoft applications such as Microsoft Word, Microsoft Excel, and Microsoft Access. In a nutshell, the Assistant object exposes the animated Microsoft assistant. Depending on the version of Microsoft Office you have, there are up to eight Office assistant characters you can install. To manually...
IsNumeric
Sometimes preventing input errors can be as easy as determining whether a user has entered a number or a string. There are times when you may wish the user to enter his or her name, or maybe you are looking for a number such as age. Either way, Visual Basic provides the IsNumeric function for testing such scenarios. The IsNumeric function takes a variable or expression as a parameter and returns a Boolean value of True if the variable or expression is a number, False if it is not. Private Sub...
StringBased Functions
Someone famous once asked, What's in a name Someone less famous yours truly once asked, What's in a string So what is in a string Well, lots. Strings are key building blocks in any high-level programming language. More specifically, they are data structures that contain one or more characters. Note that it is also possible for strings to be Null undefined . Groupings of characters and numbers comprise strings. These groupings of characters can mean different things depending on their use. Many...
Toggle Buttons
When used in an option group, toggle buttons serve the same purpose as option buttons and check boxes, which allow a user to select one item at a time. In the next example seen below , I use an option group of three toggle buttons to change label properties Using toggle buttons in an option group. Using toggle buttons in an option group. Option Compare Database Option Explicit Private Sub tglRed_GotFocus IblOutput.ForeColor vbRed lblOutput.Caption Red End Sub Private Sub tglWhite_GotFocus...
Simple SELECT Statements
To retrieve information from a relational database, SQL provides the simple SELECT statement. A simple SELECT statement takes the following form. SELECT ColumnName, ColumnName FROM TableName The SELECT clause identifies one or more column names in a database table s . After identifying the columns in the SELECT clause, you must tell the database which table s the columns live in using the FROM clause. It is customary in SQL to append a semicolon after the SQL statement to indicate the...
Rockyacs
VBA generates an error if you try to access an office assistant using the Application object's FileName property for an assistant that has not been installed. To install an office assistant, select it using the Choose Assistant menu item as described earlier in this section. If the assistant is not installed, Microsoft Office prompts you to install it. To display the Office assistant using VBA, you need to work with the Application object's Assistant property. The Assistant property returns an...
J Bzh
To successfully use the Buttons parameter of the MsgBox function, you work with variables and conditions. Specifically, you need to create a variable that holds the user's response when the user selects a button on the dialog box. The variable gets its value from the result of the MsgBox function. That's right the MsgBox function not only creates a dialog box, but also returns a value. This is how VBA programmers determine which button on the dialog box was clicked. The possible return values...
J Nad
The Datasheet window shown in Figure 8.4 displays the result set returned from the SQL query. t A result set is a common phrase used to describe the result or records returned by a SQL query. Sometimes it is not necessary to retrieve all columns in a query. To streamline your query, supply specific column names separated by commas in the SELECT clause. SELECT LastName, FirstName, Title FROM Employees In the preceding query I ask the database to retrieve only the last names, first names, and...
Info Pvs
After you've added all label names and clicked next, the wizard asks you to choose a default control if one is desired. In Figure 3.6, I've asked the wizard to make my item, called Red, the default control. Figure 3.6 depicts the next step in the wizard, where you set values for each option in the group. Option values allow VBA programmers to tell which option the user has selected in the group. The wizard's default values for each option are acceptable. The next wizard screen, displayed in...
UPDATE Statement
You can use the UPDATE statement to change field values in one or more tables. The UPDATE statement works in conjunction with the SET keyword SET UnitsInStock UnitsInStock 5 You supply the table name to the UPDATE statement and use the SET keyword to update any number of fields that belong to the table in the UPDATE statement. In my example, I'm updating every record's UnitsInStock field by adding the number 5. Notice that I said every record. Because I didn't use a WHERE clause, the UPDATE...
Command Bars
In VBA, command bars represent a number of user interface entities such as toolbars, menu bars, and shortcut menus via right-click . You can declare variables in VBA as a CommandBar object type. To demonstrate, the next program code declares a CommandBar object variable and uses a For Each loop to iterate through each CommandBar object found in the CommandBars collection. Each command bar's name is printed to the Immediate window. Output is seen in Figure 11.6. Private Sub...
Chapter Program Math Quiz
The Math Quiz program in Figure 4.7 is a fun way of learning how to incorporate chapter-based concepts such as loops, random numbers, and list boxes into your VBA applications. The program prompts a user for the number of math questions she would like to answer. Then, Math Quiz prompts the user with a predetermined number of addition questions using random numbers between 1 and 100. A list box with columns stores each question, the user's response, and the result. Controls and properties to...
Vj Hvv
The Filename attribute can contain paths in addition to filenames. For example, if you want to create employee records in a file named employee.dat on a floppy diskette, you could use the following syntax. Open a employee.dat For Output As 1 The result of the Open function varies depending on the initial action chosen. If the Input parameter is chosen, the Open function searches for the file and creates a buffer in memory. If the file is not found, VBA generates an error. T c,t A buffer s an...
CommandBarControl Object
The CommandBarControl object represents controls found in menus and toolbars. Command bar controls can be buttons, boxes, and pop-up controls. Using the CommandBars collection, you can identify each command bar control found in a command bar. This concept is revealed in the next program code, which outputs the Caption property for each control found in the Database command bar. Private Sub cmdFindCommandBarControls_Click For Each myControl In Debug.Print myControl.Caption The CommandBarControl...
Common Controls Continued
As you've already seen, there are a number of common controls available to you in the Access Toolbox. In this chapter you learn about a few more that can require the use of conditions. Specifically, I discuss the following common controls and you can view them in Figure 3.3
In the Real World 1
Truth tables are commonly used in mathematic and logic circles such as quantitative analysis, discrete mathematics, and Boolean algebra. Using logical operators, truth tables allow one to evaluate all possible results to prove an outcome. Table 3.2 demonstrates the truth table for the And operator. The And operator uses two inputs to determine the result for the entire expression. Table 3.2 Truth Table for the And Operator
CommandBarButton Object
The CommandBarButton object is a specialized command bar control that represents a button on a toolbar or menu that the user can click. You can use the CommandBarButton object in conjunction with the CommandBar object to build your own custom toolbars, which is demonstrated in the next procedure. Private Sub cmdAddCustomCommandBar_Click Set customBar CommandBars.Add Sheila Set newButton customBar.Controls _ .Add msoControlButton, CommandBars Insert _ .Controls Table .ID Set newButton...
DELETE Statement
The DELETE statement removes one or more rows from a table. It's possible to delete all rows from a table using the DELETE statement and a wildcard. More often than not, conditions are placed on DELETE statements using the WHERE clause. Once again, pay close attention to Access's informational dialog boxes when performing any inserts, updates, or deletes on tables. The DELETE statement can perform cascade deletes on tables with one-to-many relationships if the Cascade Delete Related Records...
Variable Scope
Variable scope is a fancy way of describing how long a variable will hold its data, or in other words its lifetime. VBA supports three types of variable scope. To create a variable with procedure-level scope, simply declare a variable inside of a procedure. Dim dProfit As Double dProfit 700.21 In the preceding form Load event procedure, I declared a Double variable called dProfit that will hold its value so long as the current scope of execution is inside the procedure. More specifically, once...
Programming Challenges Lgk
1. Build a form with one text box and one command button. The text box should receive the user's name. In the Click event of the command button, write code to validate that the user has entered nonnumeric data and display the outcome in a message box. Test your program by entering numeric data into the text box. 2. Build a form with one text box and one command button. The text box should receive a number between 1 and 10. In the Click event of the command button, write code to validate that...
Getting User Input with Text Boxes
Text box controls receive all types of input from users such as dates, time, text, and numbers. VBA programmers that's you write code in procedures to collect the user input and process it. This may seem trivial, but it's not. Consider a simple application that requests a user enter two numbers, after which the user clicks a command button to add the two numbers. After adding the numbers, the program should display its output in a label control. Me.lblOutput.Caption Me.txtNuml.Value...
Opening a Sequential Data File
The first step in creating or accessing a data file is to open it. Microsoft provides an easy-to-use facility for opening a data file through the Open function. Open Filename For Input Output Append As Filenumber Len Record Length The Open function takes three parameters. Filename describes the name of the file you wish to open or create. Input Output Append is a list from which you pick one to use. Filenumber is a number from 1 to 511 that is used for referencing the file. Len is an optional...
Introduction to Jet SQL
Most databases, including Microsoft Access, incorporate one or more data languages for querying information and managing databases. The most common of these data languages is SQL Structured Query Language , which contains a number of commands for querying and manipulating databases. SQL commands are typically grouped into one of two categories known as data manipulation language DML commands and data definition language DDL commands. Microsoft Jet SQL follows a standard convention known as ANSI...
About Sequential File Access
Data files created with sequential file access have records stored in a file, one after another, in sequential order. When you access data files with sequential file access, records must be read in the same order in which they were written to the file. In other words, if you wish to access the 20th record in a data file, you must first read records 1 to 19. Sequential file access is useful and appropriate for small data files. If you find that your sequential file access program is starting to...
Creating Data Access Pages
You can create data access pages with either the Access Data Access Page wizard or by yourself in Design view. To create a data access page in design view, simply double-click the Create Data Access Page in Design View link from the Pages window in Figure 12.1. Data access pages created in Microsoft Access 2003 cannot be opened in Design view in older versions of Access. In other words, Access 2003 data access pages are not backward-compatible. When creating a new Access 2003 data access page,...
Code Reuse
Remember that Visual Basic and VBA are event-driven programming languages. This means VBA programmers could easily duplicate work when writing code in two or more event procedures. For example, consider a bookstore program that contains three separate places forms or windows a user could search for a book by entering a book title and clicking a command button. As a VBA programmer, you could easily write the same code in each command button's Click event. This approach is demonstrated in the...
The Vba Ide
If you've written programs in Visual Basic before, the VBA integrated development environment IDE should feel very familiar to you. If not, don't worry the VBA IDE is user friendly and easy to learn. For ease of use, I refer to the VBA integrated development environment as the Visual Basic Editor, or VBE, from now on. The VBE contains a suite of windows, toolbars, and menu items that provide support for text editing, debugging, file management, and help. Two common ways for accessing the VBE is...
Option Buttons
Often referred to as radio buttons, option buttons provide a user with a list of selectable choices. Specifically, the user can select only one option at a time. Individual option buttons comprise two controls, a label, and option button. Each has its own properties and can be managed during design time or runtime. After creating an option group either manually or with the Option Group Wizard, you should change the name of each option control to one that contains a meaningful description. This...
Option Group
The option group control is a container that logically groups controls such as option buttons, check boxes, and toggle buttons. Though not required, the option group provides a very effective wizard for grouping your controls inside the option group's frame. When first adding an option group to your form, Access initiates the Option Group Wizard. As shown in Figure 3.4, the first step in the wizard is to add label names for each item in your group. At this stage, it doesn't matter what control...
Introduction to Looping Structures
To loop, or iterate, computers need instructions known as looping structures, which determine such things as how many times a loop's statements will execute and by what condition the loop exits. Each programming language implements its own version of looping structures, but most languages, including VBA, support some variation of Do and For loops. Though the syntax of looping structures varies from language to language, looping structures share similar characteristics Loops are logical blocks...
He
The built-in record navigation toolbars are a great feature that allow a user to scroll through records, add, update, filter, and delete records, and even get help using a data access page. The record navigation toolbar is shown in detail in Figure 12.8. Parts of the record navigation toolbar. Parts of the record navigation toolbar. Creating a simple but useful data access page is that easy In the next section I show you how to create a more robust data access page by adding group levels.
Input Box
The input box also displays a dialog box, but allows a user to input information hence the name . Like the message box, the input box is created with a function call but takes seven parameters. InputBox Prompt, Title, Default, XPos, YPos, HelpFile, Context The most common InputBox parameters are Prompt, Title, and Default, where Prompt and Title behave similarly to the same parameters of the message box. The Default argument displays default text in the text box area of the input box. Also note...
Programming Challenges
1. Create a simple word processor that allows a user to enter text into a large text box. Hint Set the Enter Key Behavior property of a text box to New Line in Field. The user should be able to change the foreground and background colors of the text box using three command buttons representing three different colors. Also, the user should be able to change the font size of the text box using up to three command buttons representing three different font sizes. 2. Build a simple calculator...
Introduction to Microsoft Office Objects
In addition to objects such as DoCmd, Err, Debug, as well as objects found in the ADODB library, VBA provides the Microsoft Office Object model for use in the suite of applications found in Microsoft Office. Most of the Microsoft Office Objects can be used across the Microsoft Office suite of applications such as Microsoft Word, Microsoft Excel, and Microsoft Access. Some office objects, however, are application specific. To work with the examples in this chapter, ensure that your Access...
Chapter Summary Kwp
Data access pages are Web pages you create in Access. They allow users to view and work with a database over the Internet or an intranet. Sources for a data access page can be Microsoft Access, Microsoft SQL Server 6.5 or later, and Microsoft Excel. Microsoft Access stores data access pages in an Internet file format using the .htm extension. You can open data access pages in either Access or via Internet Explorer 5.01 with Service Pack 2 SP2 or later. Data access pages created in Microsoft...
Standard Modules
Access VBA supports two types of modules class and standard. Class modules are directly related to an object, such as a form or report. Form class modules contain event procedures for the associated controls and objects. Standard modules, however, have no association with an object. They store a collection of variables and user-defined procedures, which can be shared among your Access programs. You can add a standard module from the Visual Basic environment by selecting Insert, Module from the...
Compound If Blocks
So far, you've seen how to build simple and nested conditions using If blocks. There is, however, much more to consider if you plan to build more complex decision-making capabilities such as compound conditions into your VBA applications. To build compound expressions, VBA programmers can use the conditional operators And, Or, and Not. Conditional operators such as And, Or, and Not are considered reserved keywords and must be used in an expression. Otherwise, VBA will generate a compile error....
Info Dud
Next, add the following VBA code to your form class module. Dim remoteConnection As New ADODB.Connection Dim rsCategories As New ADODB.Recordset Private Sub Form_Load Connect SetRecordset End Sub Private Sub Form_Unload Cancel As Integer rsCategories.Close remoteConnection.Close MsgBox There was an error closing the database. amp Err.Number amp , amp Err.Description .Provider Microsoft.Jet.0LEDB.4.0 .Open C Documents and Settings mivine amp _ amp _ Northwind Sample Database Northwind.mdb MsgBox...
Loop While
Say I wanted to make sure the statements inside a loop execute at least once despite the loop's condition. The Loop While loop solves this dilemma by placing the loop's condition at the bottom of the loop MsgBox Guaranteed to execute once. Loop While x lt y Because the preceding loop's condition executes last, the statement inside the loop is guaranteed to execute at least once, and in this case, only once.
Working with Class Modules
Classes are the blueprints for an object. They contain the implementation details, which are hidden from users programmers who use your custom objects . In object-oriented programming with VBA, classes are implemented as class modules. Class modules do not exist in memory. Rather, the instance of the class known as the object does. Multiple instances of a single class can be created. Each instance object created from a class shares the same access to the class's methods and properties. Even...
Property Procedures
VBA provides property procedures for managing the attributes of a class, which are exposed internally for the class to use or exposed externally as object properties. Simply put, properties are just variables. You could simply declare variables in your class modules for your procedures to use, but that would defeat the purpose of object-oriented programming. To work with properties in VBA, you create variables of various scopes and use a combination of property procedures to manage them. VBA...
TwoDimensional Arrays
Two-dimensional arrays are most often thought of in terms of a table or matrix. For example, a two-dimensional array containing four rows and five columns creates 20 elements, as seen in Figure 6.5. Dim x 3,4 As Integer ' Two dimensional array with 20 elements. A two-dimensional array with 20 elements. The first index also known as a subscript in a two-dimensional array represents the row in a table. The second index represents the table's column. Together, both subscripts specify a single...
Option Statements
VBA has a few module level utility statements known as options that are used for naming conventions, string comparisons and other internal settings. First off, you may have already noticed the Option Compare Database statement located in the general declarations area of the VBE code window. Per Microsoft Access Help, Option Compare Database is defined as this statement results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons...
Balloon Object
With the help of the Assistant object, you can even create your own specialized balloons. Balloons are the graphical text area that the Office assistant uses to display information. To create custom balloons, simply access the NewBalloon property from the Assistant object. The NewBalloon property returns a new Balloon object that contains its own methods and properties for managing custom balloons. You can customize your balloons to include labels, check boxes, icons, and various button and...
SingleDimension Arrays
Using the keywords Dim, Static, Public, and Private, arrays are created just like any other variable. Unless Option Base 1 is specified, or dimensioned, with an explicit range, arrays by default begin with a zero base index. Dim myIntegerArray 5 As Integer ' Creates six Integer elements Dim myVariantArray 10 ' Creates eleven Variant elements Dim myStringArray 1 to 7 As String ' Creates 7 String elements In the preceding declarations, the number of elements in an array is determined during array...