Relationship Between Access and Visual Basic
Access 2010 and Visual Basic work as two separate but interlinked products in your Access application. Access handles the storage of the Visual Basic project (both the source code and the compiled code) in your desktop database (.accdb or .mdb) or project (.adp) file, and it calls Visual Basic to manage the editing and execution of your code.
Because Access tightly links your forms and reports with class modules stored in the Visual Basic project, some complex synchronization must happen between the two products. For example, when you open a form module and enter a new event procedure in the Visual Basic Code window, Access must set the appropriate event property to [Event Procedure] so that both the form and the code are correctly linked. Likewise, when you delete all the code in an event procedure, Access must clear the related form or control property. Therefore, when you open a form or report module from the VBE window, you'll notice that Access also opens the related form or report object in the Access window.
When Access first began using Visual Basic (instead of Access Basic) in version 7 (Access for Windows 95), it was possible to end up with a corrupted Visual Basic project or corrupted form or report object if you weren't careful to always compile and save both the code and the form or report definition at the same time when you made changes to either. It was particularly easy to encounter corruption if multiple developers had the database open at the same time. This corruption most often occurred when Access failed to merge a changed module back into the Visual Basic project when the developer saved changes.
Microsoft greatly improved the reliability of this process when it switched in version 9 (Access 2000) to saving the entire Visual Basic project whenever you save a change. However, this change means that two developers can no longer have the same database open and be working in the code at the same time. This also means that your Access file can grow rapidly if you're making frequent changes to the code and saving your changes.
When you are making multiple changes in an Access application, we recommend that you always compile your project when you have finished changing a section of code. (Click Compile on the Debug menu in the VBE.) You should also save all at once multiple objects that you have changed by clicking the Save button in the VBE window and always responding Yes to the Save dialog box message that Access shows you when you have multiple changed objects open.
When you create a new database, you typically begin by creating several database objects such as tables, forms, and reports. Eventually, you reach a point where you have to add some programming to automate certain processes and tie your database objects together. This article helps orient you to the programming tools in Access.
In this article
What is programming?
In Access, programming is the process of adding functionality to your database by using Access macros or Visual Basic for Applications (VBA) code. For example, suppose that you have created a form and a report, and you want to add a command button to the form that, when clicked, opens the report. Programming, in this case, is the process of creating a macro or VBA procedure and then setting the command button's OnClick event property so that clicking the command button runs the macro or procedure. For a simple operation, such as opening a report, you can use the Command Button Wizard to do all the work, or you can turn off the wizard and do the programming yourself.
Note: Many Microsoft Office programs use the term "macro" to refer to VBA code. This can be confusing to Access users because, in Access, the term "macro" refers to a named collection of macro actions that you can assemble by using the Macro Builder. Access macro actions represent only a subset of the commands available in VBA. The Macro Builder gives you a more structured interface than the Visual Basic Editor, enabling you to add programming to controls and objects without having to learn VBA code. You should remember that in Access Help articles, Access macros are referred to as macros. Conversely, VBA code is referred to as VBA, code, a function, or a procedure. VBA code is contained in class modules (which are part of individual forms or reports and typically contain code just for those objects) and in modules (which are not tied to specific objects and typically contain "global" code that can be used throughout the database).
Objects (such as forms and reports) and controls (such as command buttons and text boxes) have various event properties to which you can attach macros or procedures. Each event property is associated with a specific event, such as clicking the mouse, opening a form, or modifying data in a text box. Events can also be triggered by factors outside of Access, such as system events, or by macros or procedures that are attached to other events. Your database can get complex if you add many macros or procedures to several event properties of many objects, but in most cases, you can achieve the results that you want by using very little programming.
Top of Page
Should I use macros or should I use VBA code?
The decision to use macros, VBA, or both depends primarily on how you plan to deploy or distribute the database. For example, if the database is stored on your computer and you are the sole user, and if you are comfortable using VBA code, you might decide to use VBA to perform most of your programming tasks. However, if you intend to share your database with other people by locating it on a file server, you might want to avoid using VBA for security considerations.
You should base your decision to use either macros or VBA code on two concerns: security and the functionality that you want. Security is an issue because VBA can be used to create code that either compromises the security of your data or can harm files on your computer. When you use a database that was created by someone other than yourself, you should enable VBA code only if you know the database comes from a trustworthy source. When you create a database that will be used by other people, you should try to avoid including programming tools that require the user to specifically grant trusted status to the database. General techniques for avoiding the need for users to trust your database come later in this section.
To help ensure the security of your database, you should try to use macros when you can and use VBA programming only for operations that cannot be performed by using macro actions. Furthermore, you should try to use only macro actions that don't require granting trusted status to the database in order to run. Limiting the use of macro actions in this manner lets users to be confident that the database has no programming that could harm the data or other files on their computers.
Beginning in the Access 2010 release, Access contains many new macro actions that enable you to build more powerful macros than you can build by using earlier versions of Access. For example, you can now create and use global temporary variables by using macro actions, and you can handle errors more gracefully by using new error-handling macro actions. In earlier versions of Access, these kinds of features are available only by using VBA. In addition, you can embed a macro directly into the event property of an object or control. An embedded macro becomes a part of the object or control and stays with the object or control if it is moved or copied.
Macros provide an easy way to handle many programming tasks, such as opening and closing forms and running reports. You can quickly and easily tie together the database objects (forms, reports, and so on) that you have created because there is little syntax that you must remember. The arguments for each action are displayed in the Macro Builder.
In addition to the increased security and ease of use that macros provide, you must use macros to perform the following tasks:
Assign an action or set of actions to a key. This requires creating a macro group named AutoKeys.
Carry out an action or a series of actions when a database first opens. This requires creating a macro named AutoExec.
Note: The AutoExec macro runs before any other macros or VBA code, even if you have designated a startup form in the Access Options dialog box and attached a macro or VBA code to that form's OnOpen or OnLoad event.
For more information about how to build macros, see the section Understand macros.
You should use VBA programming instead of macros if you want to do any of the following:
Use built-in functions, or create your own functions Access includes many built-in functions, such as the IPmt function, which calculates an interest payment. You can use these built-in functions to perform calculations without having to create complicated expressions. By using VBA code, you can also create your own functions either to perform calculations that exceed the capability of an expression or to replace complex expressions. In addition, you can use the functions that you create in expressions to apply a common operation to more than one object.
Create or manipulate objects In most cases, you will find that it is easiest to create and modify an object in that object's Design view. In some situations, however, you might want to manipulate the definition of an object in code. By using VBA, you can manipulate all the objects in a database, in addition to the database itself.
Perform system-level actions You can carry out the RunApp action in a macro to run another program (such as Microsoft Excel) from within Access, but you can't use a macro to do much else outside of Access. By using VBA, you can check to see whether a file exists on the computer, use Automation or Dynamic Data Exchange (DDE) to communicate with other Microsoft Windows-based programs, such as Excel, and call functions in Windows dynamic-link libraries (DLLs).
Manipulate records one at a time You can use VBA to step through a set of records, one record at a time, and perform an operation on each record. In contrast, macros work with entire sets of records at one time.
Top of Page
Use the Command Button Wizard to perform common programming tasks
If you are adding a command button to a form, the Command Button Wizard can help you get started with programming. The wizard helps you create a command button that performs a specific task. In an Access (.accdb) file, the wizard creates a macro that is embedded in the OnClick property of the command button. In an .mdb or .adp file, the wizard creates VBA code, because embedded macros are not available in those file formats. In either case, you can then modify or enhance the macro or VBA code to better suit your needs.
In the Navigation Pane, right-click the form to which you want to add the command button, and then click Design View.
On the Design tab, click the down arrow to display the Controls gallery, and then ensure that Use Control Wizards is selected.
On the Design tab, in the Controls gallery, click Button.
In the form design grid, click where you want the command button to be placed.
The Command Button Wizard starts.
On the first page of the wizard, click each category in the Categories list to see which actions the wizard can program the command button to perform. In the Actions list, select the action that you want, and then click Next.
Click either the Text option or the Picture option, depending on whether you want text or a picture to be displayed on the command button.
If you want text to be displayed, you can edit the text in the box next to the Text option.
If you want a picture to be displayed, the wizard suggests a picture in the list. If you want to select a different picture, select the Show All Pictures check box to display a list of all the command button pictures that Access provides, or click Browse to select a picture that is stored elsewhere.
Enter a meaningful name for the command button. This is an optional step, and this name is not displayed on the command button. However, it is a good idea to enter a meaningful name so that when you need to refer to the command button later (for example, if you are setting the tab order for controls on your form), it will be much easier to differentiate between the command buttons. If the command button closes the form, for example, you might name it cmdClose or CommandClose.
Access places the command button on the form.
If you want to see what the wizard "programmed" for you, follow these optional steps:
If the property sheet is not already displayed, press F4 to display it.
Click the Event tab in the property sheet.
In the On Click property box, click the Build button .
Access starts the Macro Builder and displays the macro that the wizard created. You can edit the macro if you want (for more information about how to edit a macro, see the section Understand macros). When you are finished, on the Design tab, in the Close group, click Close to close the Macro Builder. If Access prompts you to save the changes and update the property, click Yes to save the changes or No to reject the changes.
On the Design tab, in the Views group, click View, and then click Form View. Click the new command button to confirm that it works as you expected.
Top of Page
A macro is a tool that enables you to automate tasks and add functionality to your forms, reports, and controls. For example, if you add a command button to a form, you associate the button's OnClick event property to a macro that contains the commands that you want the button to perform each time that it is clicked.
It is helpful to think of Access macros as a simplified programming language in which you create code by building a list of actions to perform. When you build a macro, you select each action from a drop-down list and then fill in the required information for each action. Macros enable you to add functionality to forms, reports, and controls without writing code in a VBA module. Macros provide a subset of the commands that are available in VBA, and most people find it easier to build a macro than to write VBA code.
You create a macro by using the Macro Builder, which is shown in the following illustration.
To display the Macro Builder:
On the Create tab, in the Macros & Code group, click Macro.
Top of Page
Understand VBA code
Like macros, VBA lets you add automation and other functionality to your Access application. You can extend VBA by using third-party controls, and you can write your own functions and procedures for your own specific needs.
A quick way to get started with VBA programming is to first build an Access macro and then convert it to VBA code. Instructions for doing this are included in the section Convert macros to VBA code. This feature creates a new VBA module that performs the equivalent operations in the macro. It also opens the Visual Basic Editor so that you can start modifying the procedure. When you are working in the Visual Basic Editor, you can click keywords and press F1 to start Access Developer Help and learn more about each keyword. You can then explore Access Developer Help and discover new commands to help you perform the programming tasks that you want.
Top of Page
Convert macros to VBA code
You can use Access to automatically convert macros to VBA modules or class modules. You can convert macros that are attached to a form or report, whether they exist as separate objects or as embedded macros. You can also convert global macros that are not attached to a specific form or report.
Note: You can add Visual Basic for Applications (VBA) code to a Web database; however, you cannot run that code while the database is running in a Web browser. If your Web database contains VBA code, you must first open the Web database by using Access before you can run the code. To perform programming tasks in a Web database, use Access macros instead.
Convert macros that are attached to a form or report
This process converts to VBA any macros that are referred to by (or embedded in) a form or report (or any of its controls) and adds the VBA code to the form or report's class module. The class module becomes part of the form or report and moves with the form or report if it is moved or copied.
In the Navigation Pane, right-click the form or report, and then click Design view.
On the Design tab, in the Tools group, click either Convert Form's Macros To Visual Basic or Convert Report's Macros To Visual Basic.
In the Convert form macros or Convert report macros dialog box, select whether you want Access to add error handling code to the functions it generates. Also, if you have any comments in your macros, select whether you want to have them included as comments in the functions. Click Convert to continue.
If no class module exists for the form or report, Access creates one and adds a procedure to the module for each macro that was associated with the form or report. Access also changes the event properties of the form or report so that they run the new VBA procedures instead of the macros.
To view and edit the VBA code:
While the form or report is still open in Design view, if the property sheet is not already displayed, press F4 to display it.
On the Event tab of the property sheet, click in any property box that displays [Event Procedure], and then click the build button . To view the event properties for a specific control, click the control to select it. To view the event properties for the entire form or report, select Form or Report from the drop-down list at the top of the property sheet.
Access opens the Visual Basic Editor and displays the event procedure in its class module. You can scroll up or down to view any other procedures that are in the same class module.
Convert global macros
In the Navigation Pane, right-click the macro that you want to convert, and then click Design View.
On the Design tab, in the Tools group, click Convert Macros To Visual Basic.
In the Convert Macro dialog box, select the options that you want, and then click Convert.
Access converts the macro and opens the Visual Basic Editor.
To view and edit the VBA code:
In the Visual Basic Editor, if the Project Explorer pane is not displayed, on the View menu, click Project Explorer.
Expand the tree under the name of the database in which you are working.
Under Modules, double-click the module Converted Macro- macro name.
The Visual Basic Editor opens the module.
Attach a VBA function to an event property
When you convert a global macro to VBA, the VBA code is placed in a standard module. Unlike a class module, a standard module is not part of a form or report. You will most likely want to associate the function with an event property on a form, report, or control so that the code runs exactly when and where you want. To do this, either you can copy the VBA code into a class module and then associate it with an event property, or you can make a special call from the event property to the standard module by using the following procedure.
In the Visual Basic Editor, make a note of the function name. For example, if you converted a macro named MyMacro, the function name will be MyMacro().
Close the Visual Basic Editor.
In the Navigation Pane, right-click the form or report with which you want to associate the function, and then click Design View.
Click the control or section with which you want to associate the function.
If the property sheet is not already displayed, press F4 to display it.
On the Event tab of the property sheet, click the event property box with which you want to associate the function.
In the property box, type an equal sign (=) followed by the name of the function — for example, =MyMacro(). Be sure to include the parentheses.
Save the form or report by clicking Save on the Quick Access Toolbar.
In the Navigation Pane, double-click the form or report, and test it to see that the code runs as it should.
You now know the basic steps to add VBA code to your database. This article describes only the basics of how to get started; there are many excellent reference books and online resources that can help you build your programming skills.
Top of Page
Microsoft Access includes a number of features to allow database developers to create data entry forms, reports and queries that can guide users in querying existing data in the database and entering new data. Various query builder and wizard tools ease this development process. Even with these tools, however, there may be situations where the developer would like to add even more functionality not already provided by the MS Access development tools. For example, the developer may wish to automatically correct bad data before it is saved in the database, show an alert if a business rule is violated, help the user navigate from one data entry form to another or launch a report with some custom filters. The way to accomplish such customization is by adding code using the Visual Basic for Applications programming language.
Visual Basic for Applications (VBA) is the programming language used in the Microsoft Office family of software products such as Excel and Access. The VBA language is derived from “Basic” language invented in 1964 that became popular on personal computers in the 1970’s. The “Visual” part of the name was added by Microsoft to reflect the use of the Basic programming language in Microsoft Windows.
There are many tutorials that introduce Basic programming. This tutorial will focus on a small subset of features of the VBA language as used in MS Access.
The next page introduces Events in Microsoft Access.
Tags: Access, Access 2013, Access 2016, SQL, VBA, Visual Basic for ApplicationsSours: http://holowczak.com/visual-basic-for-applications-microsoft-access/
dWelcome to Microsoft Access Database VBA Programming
VBA stands for Visual Basicfor Applications (the application being of course Microsoft Access) and is the technology and tools used to program and automate this application.
It’s not only used just within the framework of Microsoft Access Database but other applications too including Microsoft Excel, Microsoft Word, Microsoft Outlook to name a few.It has the power to communicate with other applications beyond the Microsoft range and even the Microsoft Windows operating system across other platforms.So, learning the principles of VBA using Access as the tool environment will stand you in good stead for the other applications should you wish to program and code them in the future.The only difference between other applications when wanting to use VBA will simply be learning to load and work with different libraries.Typical reasons why you would use Microsoft Access VBA include being able to:
- Manage smaller re-usable procedures (globally).
- Create you own defined functions.
- Hold variables in memory (globally).
- Attach code to an event i.e. a button on a form.
- Manage more powerful procedures that an Access macro lacks.
- Communicate with other applications and platforms.
- Gracefully handle errors (when they happen).
You do not need to be a programmer to learn Access VBA (but it helps if you have some knowledge). The more important factor in order to successfully program with Access VBA is to be
knowledgeable within the framework of Microsoft Access generally (namely tables, queries, forms, reports and macros) and know its powerful features to identify where VBA should take over and improve on functionality.
So Where Do You Start & Learn Microsoft Access VBA?
The following links below will give you an overview of the basic introducing you to the tools and concepts required. In fact, it applies to other Microsoft Office applications and related Visual Basic programming envrionments too.
How to get to the Access VBA Programming Environment
Components of the VBE (Visual Basic Editor)
The Code Window in More Detail
Types of VBA Procedures
Microsoft Access VBA Programming eBook
This is only the beginning of a long and eventful journey into the world of Access VBA. To learn more and master the true power of Microsoft Access you may wish to invest in either a book, a course or even consider my eBook
Access for visual basic
MS Access 2010: VBA Environment Introduction
This MSAccess tutorial explains the VBA environment in Access 2010 (with screenshots and step-by-step instructions).
See solution in other versions of MSAccess:
What is VBA?
This is what the VBA environment looks like in Microsoft Access 2010:
VBA standards for Visual Basic for Applications and is the language embedded within your database in Access 2010.
You use VBA whenever you do one of the following:
- Create a new function.
- Create a new subroutine.
- Define a global variable.
- Place code behind an event procedure such as the "On Click" event of a command button.
- Execute the RunCode action in a macro.
These are just some of the examples of when you might be running VBA code.
Introduction to Microsoft Access and VBA
Introduction to Microsoft Access
A database is a list of values stored somewhere. A computer database is a list or a group of lists stored in a computer disc such as a hard drive. Microsoft Access is an application used to create and manage computer databases.
Practical Learning: Starting Microsoft Access
Starting a Project
Microsoft Access is is equipped with all the tools you will need to start a project, to end it, and to distribute it. As is the case for every application, to use Microsoft Access, you must launch it, which is done from the menu on the Start button of Microsoft Windows. When it starts, Microsoft Access displays two sections:
The left side displays a list of previously used files. The right side displays a list of example databases. To start with a project from scratch, you can click Blank Desktop Database. This would bring up a dialog box:
In the dialog box, specify the desired name of the file and click Create.
Practical Learning: Creating a Database
- In the middle section, click Blank Desktop Database
- In the right section of the screen, set the File Name to Exercise1
- Click Create
The Microsoft Access Interface
The Quick Access Toolbar
The left side of the title bar displays the Quick Access Toolbar . The Quick Access toolbar containss other useful buttons.
The Work Area
To assist you with necessary operations, Microsoft Access is equipped with the Ribbon. By clicking File, you can access menu items to save or open one:
To return to the previous window, you can click the Back button .
Under the title bar, Microsoft Access displays a long bar called the Ribbon (or ribbon), whose contents and sections depend on what is currently going on in Microsoft Access:
The Structured Query Language (SQL)
The primary language to perform the operations on a database is called the structured query language, or SQL.
|SQL can be pronounced Sequel or S. Q. L. In our lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in "A SQL statement" instead of "An SQL statement". Also, in these lessons, we will regularly write, "The SQL" instead of "The SQL language", as the L already represents Language.|
As it is common with other computer languages, the SQL has with its syntax, vocabulary, and rules. The SQL is equipped with keywords that tell it what to do and how to do it. Microsoft Access supports many ways of using, interpreting, or applying the SQL.
A SQL Statement
Code based on SQL is referred to as a SQL statement. In Microsoft Access, to open a window you can use to create or use a SQL statement, on the Ribbon, click Create. In the Queries section, click Query Design. This would display the Show Table dialog box. Click Close.
To open the window that allows you to write code:
- Right-click the middle of the Query window and click SQL View
- On the Ribbon, in the Results section of the Design tab of the Ribbon, click the SQL button
- On the Ribbon, in the Results section, click the arrow of the View button and click SQL View
- On the status bar of the Query window, click the SQL View button
This would display a window with a default line of code. This is referred to as the Query window� In it, you can either edit its existing code or delete the default code and replace it with your own code.
When writing a SQL statement, the SQL is not case-sensitive. This means that the words Case, case, and CASE represent the same thing. This applies to keywords of the SQL or words that you will add in your statements.
After writing your SQL statement, you can execute it to see the result. To do this:
- In the Results section of the Ribbon, click the Run button
- In the Results section of the Ribbon, click the Run button
- In the status bar of Microsoft Access, click the Datasheet View button
After viewing the result, to return to the Query window:
- Right-click the title bar of the window and click SQL Code
- In the Results section of the Home tab of the Ribbon, click the arrow of the View button and click SQL Code
The most fundamental operator used in the SQL is called SELECT. This operator is primarily used to display a value to the user. In this simple case, it uses the following formula:SELECT Value;
The value on the right side of SELECT must be appropriate. It can be a number. Herei is an example:SELECT 48;
When you write a SQL statement, the semicolon is optional. This means that you can omit it.
SELECT This AS
When you execute a SQL statement in the Query window, its results are displayed in a spreadsheet. To be able to recognize a value, the top section of a column of the spreadsheet displays a label, called a caption. For example, the above would produce:
If you create a simple SELECTValue; Microsoft Access assigns a default caption to the column. The SQL allows you to specify a caption that would be used for the value. This is done using the following basic formula:SELECT Value As Caption;
The words SELECT and AS are required. As mentioned already, SELECT would be used to specify a value and AS in this case allows you to specify a caption of your choice.
The caption can be made of a word but the word cannot be one of the SQL's keywords. Here is an example:SELECT 48 AS Age;
This would produce:
You can also use non-literal characters or digits in the caption. If the caption is made of a combination of words, you can concatenate them to create one word. Here is an example:SELECT 24.85 AS HourlySalary;
This would produce:
If you want the caption to display different words, you can include them between an opening square bracket "[" and a closing bracket "]". Here is an example:SELECT 25.05 AS [Hourly Salary];
This would produce:
Instead of displaying one column, you may want to display more. To do this, separate them with commas. Here is an example:SELECT 42.50 AS [Weekly Hours], 25.05 AS [Hourly Salary];
This would produce:
Besides a number, the value of a SELECT expression can be a string. If it is, you can include it between single-quotes. Here is an example:SELECT 'Martens, Laurent' AS [Employee Name];
In the same way, you can mix number-based and string-based columns.
SQL as Data Definition Language
The structured query language comes in two broad parts. The data definition language (DDL) is the SQL part used to create the objects of a database.
Introduction to Forms and Reports
A form is a rectangular object that hosts other objects and allows a database user, also called a user, to interact with the application. There are various ways you can create a form. If you want to immediately use the form, on the Ribbon, click Create. In the Forms section, click Form View.
If you want to prepare the form to receive other objects, this is referred to as design. To open a form for design, on the Ribbon, click Create. In the Forms section, click Form Design. When a form is in design, you can add objects to it.
Practical Learning: Creating a Form
- On the Ribbon, click Create
- In the Forms section of the Ribbon, click Blank Form
Opening a Form to View
The Form View is the view used for regular operations on a form. To open a form in Form View:
- In the Nativation Pane:
- Double-click the name of the form
- Right-click the form and click Open
- If the form is opened in another type of view:
- In the Nativation Pane, double-click the name of the form
- On the Ribbon, click Home, Design, or Datasheet (this depends on the type of view). In the Views section (on the left of the Ribbon), click the arrow button under View, and click Form View
- On the status bar, click the Form View button
Opening a Form for Design
The design of a form is done by displaying the object in Design View. To present a form in Design View:
- If the form is currently closed, in the Navigation Pane, right-click the form and click Design View
- If the form is currently opened:
- In the Navigation Pane, right-click the form and click Design View
- Right-click its title bar or tab and click Design View
- Right-click it (somewhere on its body) and click Design View
- On the Ribbon, click Home. In the Views section, click the arrow button under View and click Design View
- On the right side of the status bar, click the Design View button
The Form View
If the form is currently displaying in Design View and you want to switch it to Form View:
- In the Navigation Pane:
- Double-click the name of the same form
- Right-click the name of the same form and click Open
- On the Ribbon, click either Design or Home, in the Views section:
- Click the View button
- Click the down-pointing button under View and click Form View
Practical Learning: Displaying a Form for Design
- Right-click the tab of the form and click Design View
A report is a rectangular object that allows a user to print some values, that is, to sent a designed object to the printing device. Like a form, a report hosts the objects that present the values to be printed. There are various ways to create a report. To start a simple report, on the Ribbon, click Create. In the Reports section, click Report View.
To start a report that will host some objects, which referred to as design, on the Ribbon, click Create. In the Reports section, click Report Design. If the report is currently closed or it displays in another view, you can switch the view using the same techniques applied to a form.
Practical Learning: Creating a Report
- On the Ribbon, click Create
- In the Forms section of the Ribbon, click Report Design
The Sections of a Form or Report
The Detail Section
The Detail section is the primary host of the controls on a form or report. The section is primarily characterized by its height. To visually change the height of the Detail section, click and drag the lower border of the Detail section:
The Form Header and the Form Footer
A form can be equipped with a Form Header and a Form Footer sections. To add these sections, right-click the middle of the form and click Form Header/Footer:
A report can be equipped with a Report Header and a Report Footer sections. To add these sections, right-click the body of the report and click Report Header/Footer:
You can heighten or reduce, sometimes completely, a section to your liking. This allows you to keep one section and hide the other.
Practical Learning: Creating the Sections of a Form
- Click the Form1 tab
- While the form is displaying in Design View, right-click the body of the form and click the Form Header/Footer
The Size of a Form or Report
The Width of a Form or Report
The width of a form or that of a report is the distance from its left border to its right border. All sections use the same width. To widen or narrow a form or report, click and drag the right border:
The Height of a Form or Report
The height of a form or report is controlled by its sections.
The Width and Height of a Form or Report
To change both the width and the height of a form or report at the same time, position the mouse on the lower-right corner:
Then click and drag left, right, up, down, or diagonally.
Automatically Sizing the Form to Fit Its Content
If you want to resize a form enough to show its whole content, on the Ribbon, click Home. In the Window section, click Size To Fit Form.
A Windows control, or simply called a control, is an object positioned in the body of a form to allow a database user to interract with the database.
Microsoft Access supports various types of controls.
Adding a Control to a Form or Report
To manually add a control to a form or report, the form or report must be opened in Design View. When that happens, the Ribbon displays various objects in its Controls section. To add a control to a form or report, click it in the Controls section of the Ribbon, and click the desired section of the form or report. You can then move and position the control as you see fit.
Practical Learning: Adding a Control to a Form
- In the Controls section of the Ribbon, click the Text Box
- Click the wide area on the formu
Microsoft Visual Basic Fundamentals
You can create a completely functional database using only Microsoft Access. In some cases, to get a more complex database, you have to write code. To support this, Microsoft Access ships with, and installs, a programming environment named Microsoft Visual Basic. This is a (smaller) variant of the popular Microsoft Visual Basic language and environment but has everything necessary to create and manage a complex database.
Practical Learning: Starting Microsoft Access
- Start Microsoft Access
- In the list of files, click Exercise1 from the previous lesson (if you don't see it, find and open it)
Opening Microsoft Visual Basic
In order to access Microsoft Visual Basic, you must first create or open a database in Microsoft Access. Then, you can open Microsoft Visual Basic from Microsot Access. You have various options. To launch Microsoft Visual Basic:
- On the Ribbon:
- Click the Create tab. In the Macro & Code section, click the Visual Basic button. In some cases, in the same Macro & Code section, you can also click either the Module or the Class Module button
- Click the Database Tools tab. In the Macro section, click the Visual Basic button
- Display the form in the Design View. Depending on what you are trying to do, right-click either the form itself or the control on it and click Build Event... In the Choose Builder dialog box, either double-click Code Builder or click Code Builder and click OK
Any of these actions would open Microsoft Visual Basic:
Practical Learning: Opening Microsoft Visual Basic
- On the Ribbon, click Database Tools
- Click the Visual Basic button
Primary Accessories for Programming and Code Writing
When using a database, you are in fact using two applications to create a final product. Microsoft Access is used to design the necessary objects for your product. This means that Microsoft Access is used for its visual display of objects. On the other hand, Microsoft Visual Basic is used to handle code that enhances the functionality of your application.
The code you will write is made of small instructions written in plain English, in a version referred to as the Visual Basic language. The instructions you write must be translated in a language the computer can understand. This is done by a program called a compiler. That program works behind the scenes so you will almost never be concerned with it (but you should know that it exists.
A comment is a piece of text in a code section that the database engine would not consider when reading your code. As such, a comment can be written any way you want.
In Visual Basic, the line that contains a comment can start with a single quote. Here is an example:Private Sub Form_Load() ' This line will not be considered as part of the code End Sub
Alternatively, you can start a comment with the Rem keyword. Anything on the right side of rem, Rem, or REM would not be read. Here is an example:Private Sub Form_Load() ' This line will not be considered as part of the code Rem I can write anything I want on this line End Sub
Comments are very useful and it is strongly suggested that you use them regularly. They can never hurt your code and they don't increase the size of your database. Comments can help you and other people who read your code to figure out what a particular section of code is used for, which can be helpful when you re-visit your code after months or years of not seeing it.
Fundamentals of Modules
A module is a file that holds programming code or pieces of code in a Visual Basic application. There are three types of modules you will use.
A Module from a Form or Report
From Microsoft Access, if you use a form or a report to open its code, Microsoft Visual Basic automatically creates a new module for the form or report and displays it:
Practical Learning: Creating a Form-Based Module
- To return to Microsoft Access, on the main menu of Microsoft Visual Basic, click File and click Close and Return to Microsoft Access
- On the Ribbon, click Create
- In the Forms section, click Form Design:
- Right-click the tab of the form (Form1) and click Save
- Set the name as Central
- Click OK
- To start a form module, with the form opened in Design View, on the Ribbon, in the Tools section of the Design tab, click the View Code button
- To return to Microsoft Access, on the Standard toolbar, click the View Microsoft Access button
A Module from Scratch
As another category, you can create a module that is independent of any form or report. You can then write any code you want in that file. To create a module:
- In Microsoft Access, on the Ribbon, click Create. In the Macro & Code section, click Module
- In Microsoft Visual Basic:
- On the main menu, click Insert -> Module
- On the Standard toolbar, click the arrow of the Insert Module button and click Module
The names of modules are cumulative. This means that the first module would be named Module1; the second would be Module2, etc. It is a good idea to have names that are explicit especially if your application ends up with various modules. To give a custom name to a module, you must save it. This would prompt you to name the module. You can accept the suggested name or type your own and press Enter.
Practical Learning: Creating an Independent Module
- In Microsoft Access and on the Ribbon, click Create
- To start a module, on the Macro & Code section, click the Module button
Introduction to the Events of a Control
An event is an action that occurs on a control. Examples of actions include clicking a button, or typing text, or moving the mouse. Except for the label (or for static controls), every control supports some events. When an action occurs, the control is said to fire the event.
A default event is the event the control is more likely to fire. For example, a button is more likely to be clicked; so the default event of a button is the click event.
There are various ways you can launch an event for a control. As one option, in Microsoft Access, right-click the control on a form or report and click Build Event... In the Event Builder dialog box, click Code Builder and click OK. If you are already working in Microsoft Visual Basic, in the Object combo box, select the control. In the Procedure combo box, select the control.
Practical Learning: Introducing Controls Events
- Right-click an unoccupied area of the form and click Build Event...
- In the Choose Builder dialog box, click Code Builder
- Click OK
- In the Object combo box of Microsoft Visual Basic, select Form
- Close Microsoft Visual Basic
- Close Microsoft Access
- When asked whether you want to save, click No
Microsoft Visual Basic Child Windows
The Microsoft Visual Basic application is equipped with various windows you can use to create and manage the necessary objects. The windows are dockable. This means that they can be moved on the screen to other locations.
The Project Window
The Project window displays the coding segments for the objects of your project. If the Project Explorer is not displaying and you need it, on the main menu, click View -> Project Explorer.
The Project window is usually positioned on the left of the window. To move it, click its title bar under the Standard toolbar, hold your mouse down and drag it to the desired location. To position it back to its previous location, double-click its title bar. To expand or collapse the folders tree, click the Toggle Folders button.
Introduction to the Properties Window
In the Microsoft Visual Basic Environment, the Properties window is used to specify or change some characteristics:
The Code Editor
The Code Editor is the area where you will write code. It is the largest section of the Microsoft Visual Basic programming environment. It is mainly made of three sections:
On top, there are two combo boxes. To know the name of a combo box, you can position the mouse on it and a tool tip would come up:
The Object combo box allows you to select a particular object and access its actions. The Procedure combo box allows you to select an action, related to the object in the Object combo box.
The Code Editor is equipped with a vertical and a horizontal scroll bars.
There are two small buttons on the left side of the horizontal scroll bar. The Full Module View button is used to display the code with an object. The Procedure View button will display the procedures associated with the database.
The Options Dialog Box
The Code Editor uses default colors to show the code. To customize these colors, you can use the Editor Format property page of the Options dialog box. To access it, on the main menu, click Tools and click Options...:
The Immediate Window
The Immediate window is used to test code when necessary. To display it, on the main menu of Microsoft Visual Basic, lick View -> Immediate Window
Practical Learning: Using Microsoft Visual Basic Windows
- To display the immediate window, on the main menu of Microsoft Visual Basic, click View and click Immediate Window
- To use it, in the Immediate window, type ?now and press Enter
- To return to Microsoft Access, on the Standard toolbar, click View Microsoft Access
Microsoft Access Databases
There are various types of databases you can use in Microsoft Access. You can create a database from scratch. You can use some objects that ship with Microsoft Access to create a database. You can open either a database you previously created or one made by someone else.
Visually Creating a Microsoft Access Database
There are various ways you can create a database. To visually start a database from scratch, after launching Microsoft Access, click Blank Desktop Database. In the right section, accept or change the name of the database. If you want to create a contemporary database, either omit or add the .accdb extension. If you want to create a database that is compatible with earlier versions of Microsoft Access, you must add the extension .mdb.
After specifying the name, to specify a folder of your choice, under File Name and on the right side of the name of the database, click the Browse button . This would open the File New Database dialog box. You can click the arrow of the Save In combo box to select a drive such as (A:), (C:), etc. After selecting the drive, you can either select an existing folder or create a new folder by clicking the Create New Folder button on the right side of the Save In combo box. You can also use a directory on the network as the repository of the new database.
Creating a Database Using a Template
Microsoft Access ships with a few sample databases you can use and customize. To create a database from a template, after launching Microsoft Access, in the middle section, locate and click the desired sample.
Deleting a Database
If you have a database you don't need anymore, you can delete it. To delete a database, in My Documents, in Windows Explorer or another file management application:
- C can click the database to select it and press Delete
- Right-click the database and click Delete
A warning message would be presented to you to confirm what you want to do.
Closing a Database
You can close a database without closing Microsoft Access. To do this, on the Ribbon, click File and click Close.
Closing Microsoft Access and Visual Basic
The version of Microsoft Visual Basic used in Microsoft Access is "For Applications". It is related to Microsoft Access. When you are in the Code Editor of Microsoft Visual Basic, you can get back to Microsoft Access either from the View Microsoft Access button on the Standard toolbar or by clicking the Microsoft Access button on the Taskbar. The shortcut to get back to Microsoft Access is Alt + F11.
You can close Microsoft Visual Basic any time and keep Microsoft Access running. To do this, on the Standard toolbar of Microsoft Visual Basic, click the View Microsoft Access button to get back to the database. On the other hand, if you close Microsoft Access, Microsoft Visual Basic will be closed also.
Since Microsoft Access shares the same functionality you are probably familiar with from using other applications, you can close it easily.
- To close Microsoft Access, you can click the Office Button and then click Exit Access
- To close Microsoft Access from its title bar, you can click its Close button
- To close Microsoft Access like any regular window of the Microsoft Windows applications, you can press Alt + F4
- To close Microsoft Access using mnemonics, you can press Alt, F, X
Practical Learning: Ending the Lesson
- To close Microsoft Access, click File and click Close
- Yellow pages pa
- Specialty care careers
- Deloitte engineer salary
- Silverado 2015
- Lizard drawing cute
- Blizzard box amazon
- Goddess birthday meme
I had seen most of these toys and knew what they were for, but Emily froze, her eyes widening. I dont know what all this is. she said in confusion. - Let me help you figure it out. - I responded willingly.