Macro is a set of several commands in the language Visual Basic, which allows you to simplify or program operations with the database. Typically, macros are written to automatic mode, so there is no need to know Visual Basic. Macros program routine operations, making it convenient to create Main menu application system and submenus, organize data search, etc.
Typically, macros are associated with the “event response” of Access 2007 controls. Events determine what happens when a button is clicked, a database field is entered or exited, or a form or report is opened or closed.
Creating buttons to launch forms
Let’s create a “Button Panel” form to call some forms Buyer, Product And Order our database. To do this, select the tab Creation on the DBMS toolbar, and then click the button Macro . A window will appear on the screen Macro1. It contains a table with two fields. The first of them is intended for macro commands, the second for arguments.
Click on the bookmark on the database panel Form, select the “Buyer” form and drag it into the first row of the macro definition table into the column Macro command the text will appear OpenForm, The name of the form will appear on the right side of the window. Let’s close the macro and save it with the name “Buyer”.
Comment. You can directly select a macro from the list of macro commands OpenForm, and then in the parameters (at the bottom of the screen) specify the “Buyer” form.
Now let's create a form to place the buttons. Select a tab Creation in the database window , then the command Blank form. Switching to design mode, drag the “Buyer” macro we created onto the form. There will be a button saying Buyer. When viewing the list, you will notice that in the property Button press the macro name “Buyer” is recorded.
Close the form and save it with the name “Button Panel”. To check the work, open the form and click the button Buyer. The “Buyer” form we created earlier will appear on the screen, with which you can view and enter customer data.
Exercise
- Create buttons in the “Button Panel” form Product And Order for the “Product” and “Order with combo box” forms.
- Add the title Basic data entry forms to the “Button Panel” form.
- Set different type design for form elements.
Macros. Automatically run a macro when opening a database
When opening an Access database, it is convenient to display a certain form, for example, a panel of application control buttons. Let’s make sure that when we open our database, the form “ Button panel”.
To do this, select the tab Creation, press the button Macro, This will cause an empty macro window to appear in design mode. Click on the field in the column Macro command, select a macro command from the list OpenForm. In the Form Name field, specify the “Button Panel” form. Let’s close the macro and save it with the name “AutoExec”.
Now let's close our database and open it. This will automatically open the “Button Panel” form. To open the database without automatically opening this form, you must hold down the Shift button while opening the database.
Now let's cancel automatic start“Button panels”. To do this, rename the macro “AutoExec” to “AutoExec1.
Exercise is to implement an autorun feature for your database.
Using the main button form, you can navigate through database forms and reports. The database user does not need to view the entire database structure. Using forms, the user can make changes to tables and then receive reports taking into account the new data. Queries and tables are not elements of the main button form.
To create the main button form, you need to use the Button Form Manager tool. If this tool is not on the Database Tools tab, you can add it to the Panel quick access.
To do this, on the File tab, select Options, then Quick Access Toolbar. After this, on the Settings tab of the Quick Access Toolbar, select the “Working with Databases” Tab line from the drop-down list and select Button Form Manager in the list of commands (Fig. 1. 31).
Creating the main button form:
1. open the database;
2. go to the “Button Form Manager” (Fig. 1. 32);
3. If you are prompted to confirm the creation of the button form, click the “Yes” button.
1. further work will be done with a standard push-button form;
2. click the “Change” button;
3. click the “Create” button (Fig. 1. 33).
1. in the “Text” field, enter the text of the name of the first button of the button form, and then select a command for it in the Command field;
2. to create a second button, use the “Create” command, after which the “Edit button form element” window for the new button opens (Fig. 1. 34).
We get the main button form (Fig. 1. 35).
To change or delete any of the created buttons, select its name in the “Elements of this button form” list and click the “Change” or “Delete” button. If you need to change the order of button form elements, select the element in the list and use the Up or Down buttons.
When you've finished creating your button form, click the Close button.
To have the pushbutton open when you open the database, select the pushbutton name in the Switchboard Manager dialog box and click the Default button.
When you create a switchboard using the Switchboard Manager, a "Switchboard Items" table is created. This table describes the text and actions of the created form buttons. If you later need to make a change to the button form, you must first delete the corresponding “Switchboard Items” table and then make the changes.
Reports
Reports are used for formatted data output to printing devices. When creating a report, MS Access always operates with only one single table or query. If you need to combine information from several tables and/or queries in one report, you must first collect the desired data in one query.
The report structure consists of sections and controls. Designing a report consists of creating the structure of its sections and placing controls within these sections, as well as defining relationships between these elements and fields of tables or database queries.
The structure of the finished report differs from the structure of the form in the increased number of sections. In addition to header, note, and data sections, a report may contain header and footer sections. If the report spans more than one page, these sections are needed to print service information such as page numbers. The more pages a report occupies, the more important the role of the data printed through these sections. If grouping is applied to some report fields, the number of report sections increases because the group headings are formatted in separate sections.
Editing the report structure is performed in Design mode. Editing techniques are the same as for forms. In this case, controls act as design elements, since a printed report is not an interactive object, unlike electronic forms.
The easiest way to familiarize yourself with the report structure is by creating an auto-report and then opening it in Design mode.
The report structure consists of five sections: report header, header, data area, footer, and notes. The header section is used to print the general title of the report.
The header section can be used to print subheadings if the report has a complex structure and spans many pages. You can also place column numbers (page numbers) here, if this is not done in the footer.
The data area contains controls related to the contents of the fields of the database tables. These controls receive data from database tables, which can be used for printing.
The footer section is used for the same purposes as the header section.
The notes section is used to provide additional information.
Auto report is the simplest type of report (Fig. 1. 36). To create an auto-report, you need to select the object for which the report is being created in the Navigation panel and open the Create, Report tab.
In this case we get electronic view report containing all the fields of the object.
Using the Report Wizard.
In this case, a simple custom report is created in four steps:
· object selection;
· in the Reports group, select Report Wizard;
· select fields for the report;
· select the required field sorting order;
· select the type of report layout.
In this case, we obtain an electronic form of the report containing only the required fields of the object (Fig. 1. 37).
Section 2. Laboratory workshop
Usually, to work with the application, they create special form, which is called the Main Switchboard and contains buttons that allow you to select the main functions or subsystems of the application. Such a form can be created, as usual, using the Form Builder, or you can use a special wizard called the Swithboard Manager. A Main Switchboard was created in the Northwind database without using a wizard. Let's see how to create a similar form using the Button Form Manager.
Select menu command Service, Utilities, Button Form Manager(Tools, Database Utilities, Swithboard Manager).
Since the database does not contain any button forms (at least not known to the wizard), a dialog box appears asking whether to create a button form. Click Yes.
Now the wizard creates a table “Switchboard Items” and a button form (Switchboard), which we will look at later. After this, a dialog box appears with a list of button form pages (Fig. 14.15).
Rice. 14.15. Dialog window Button Form Manager
This window lists all pages of the button form (there may be several of them). So far, only one page has been created, and we see it in the list. To create on it necessary buttons, press the button Change(Edit). A dialog box (Edit Switchboard Page) opens (Fig. 14.16), containing a list of form elements.
Rice. 14.16. Dialog window Changing a button form page
In this window, you can add, edit, and delete elements from the selected form page. You can also change the order of their placement on the page (using the buttons Down(Move down) and Up(Move up)).
In field Button name(Swithboard Name) We can change the standard name if necessary. The list of form elements is still empty and only one button is available Create(New). To create the first element, click this button. The Edit Switchboard Item dialog box appears (Fig. 14.17).
Rice. 14.17.
In field Text(Text) enter the name of the button to be created, for example Products. Second field Team(Command) allows you to define the action that will be performed after clicking this button. The list of these actions is presented in Fig. 14.17. There are 8 of them in total. This can be opening a form in the mode of adding records or editing, opening a report, running a macro or VPA procedure, launching the wizard to edit a button form, moving to another button form and exiting the application. Let's select, for example, Open form to edit(Open Form in Edit Mode). Then in the next field you need to enter the name of the form to open, “Products List” (Fig. 14.18) and click the button OK.
Comment
The name of the last field in the form in Fig. 14.18 changes depending on the selected command in the second field. If the action selected in the list does not require specifying an argument, the third field disappears altogether.
Rice. 14.18. Dialog window
Similarly, we will add buttons for other application objects: “Types” (Categories), “Suppliers” (Suppliers), “Orders”. Let's create a button Reports, which will open the Sales Reports Dialog form and an exit button from the application Exit. Let's close the dialog box Changing a button form page(Edit Switchboard Page) by clicking the button Close(Close).
The number of buttons on one form page is limited to 8. This does not mean that you cannot create more of them, you just have to do it manually. However, there is another way to overcome this limitation. If the number of actions that you would like to place on buttons exceeds 8, you can group some of the actions on a separate page of the button form. For example, let's create another page of our button form and place customer action buttons on it. To do this, in the dialog box Button Form Manager(Switchboard Manager) click button Create(New). In the dialog box that appears (Fig. 14.19), enter a name new page Clients and press the button OK.
Rice. 14.19.
Next, in the page editor, we will create elements in a well-known way: Customers, Customer stickers, Customer phone numbers, Customer orders, which open the corresponding forms. Don't forget to create a button that will allow you to return to the Main Switchboard. To do this you need to associate the command with it Go to button form(Go to Switchboard) (Fig. 14.20). Close this window to return to the dialog box Button Form Manager(Switchboard Manager). You should have two pages appear in the list of pages.
Now you need to edit the first page - add an element to it that can be used to go to the second page. To do this, select the page (Main Switchboard) in the list and click the button Change(Edit). In the page editor window, add a new Clients element, also using the command Go to button form(Go to Switchboard).
Rice. 14.20.
Now both pages are linked. You can close the wizard window and see what we have done by opening the Switchboard form. A window similar to the one shown in Fig. should appear. 14.21.
Rice. 14.21.
You can check how the buttons work. For example, if you press the button Clients, The contents of the form will change and you will see the second page of the form. The form doesn't look very attractive, but now you can go to Design mode and style it appearance, using all the tools of the Constructor.
When you open this form in Design mode, you will not see its elements; the form seems completely empty. However, if you open the module associated with this form, you will see a fairly large amount of code that organizes the form to work properly. Moreover, for this purpose, information stored in a special table is used, which we mentioned at the beginning of the section: Switchboard Items(Pushbutton elements). You will see this table in the list Tables(Tables) windows Database(Database). In this case, it will look something like the one shown in Fig. 14.22.
Rice. 14.22. Table "Pushbutton Form Elements"
The first column contains the page ID, the second column contains the number of the element on the page. This number determines the order in which elements will appear on the page. The third column contains the text that appears to the right of the button. The last two columns define the command associated with the element: the command code (as we remember, there are 8 of them) and its argument. Code 0 corresponds special element, which specifies the title of the page and defines its beginning.
Thus, using the Switchboard Manager, you can create a hierarchical system of switchboard pages, which can be an alternative to the usual hierarchical menu. True, unlike a menu, this form is not constantly visible on the screen; it can be covered by other forms. If you want to allow the user to open several forms and reports at once, you will have to create a button on the toolbar that would allow you to display the Main Switchboard at any time.
The main button form is created for the purpose of navigating through the database, i.e. it can be used as the main menu of the database. The elements of the main button form are form and report objects.
Queries and tables are not elements of the main button form. Therefore, you can use macros to create Query or Table buttons on a button form. First, macros “Open Query” or “Open Table” with unique names are created in the database window, and then buttons for calling these macros are created in button form.
You can create several button forms for one database. Buttons should be grouped on button form pages in such a way that it is clear to the user in which button forms certain commands can be executed (queries, reports, data entry and editing). It should be noted that subordinate button forms must have return buttons to the main button form.
The technology for creating button forms is as follows:
1) create a main button form (GKF) page;
2) create the required number of pages of subordinate push-button forms (for example, forms for data entry, for reports, for requests, etc.);
3) create elements of the main button form;
4) create elements for push-button report forms and forms for entering or changing data;
5) create macros for queries or for tables with unique names;
6) create elements for push-button query forms or tables.
The structure of button forms can be presented as follows.
To create the main button form and its elements, you need to open the database (for example, “Student Performance”) and execute the command Access Options - Settings - Select commands from / “Working with Databases” tab – select Button Form Manager and add it to the Quick Launch Toolbar Settings.
If the button form has not been created previously, a dialog box will open "Button Form Manager"
in which you need to click the “Yes” button, thereby confirming the creation of the button form. As a result, the Main Button Form page will be created.
Next, you can create three more button form pages: Data Entry Forms, Reports and Queries. To do this, click on the “Create” button and in the window that appears, enter the name of the new page “Data Entry Forms”
and click on the “OK” button. The “Data Entry Forms” button form page will be created. In a similar way, you need to create two more pages, in the end we get four pages of push-button forms, which are displayed in the “Push Button Forms Manager” window.
After this, we create GCF elements; to do this, in the “Push Button Form Manager Window”, select the “Main Push Button Form” page and click “Change”, a new window “Changing Push Button Form Pages” will open.
In this window, click on the “Create” button, a new window “Changing a button form element” will open.
in which we do the following:
enter the text: Forms for data entry;
select the command from the drop-down list: Go to button form;
select a push-button form from the list: Entering data into forms, click on the “OK” button.
The “Edit Pushbutton Form Page” window displays the “Data Entry Forms” button form element.
Using a similar method, you need to create the elements: “Queries” and “Reports”, and then the element (button) “Exit DB”.
As a result, all elements of the main button form will be displayed in the “Edit Button Form Page” window.
The button form appears in the list in the database window area on the Forms tab in the Objects panel, and the Switchboard Items table appears in the lists on the Tables tab. Double-clicking on the "Pushbutton Form" text will open the Main Pushbutton Form.
In order for this form to be displayed when starting the database, you must run the Access Options/Current Database command, in the View Form window, select “Push Button Form” from the drop-down list, and uncheck the Status Bar checkbox. You can also enter a title and icon for the application.
At this point, the creation of push-button forms is not yet complete, since there are no elements on the subordinate push-button forms (Data Entry Forms, Reports, Queries).
Creating Sub-Push Button Forms: Reports and Forms
To create push-button form elements "Data Entry Forms", you must launch the database (for example, "Student Progress" with the main push-button form) and execute the command Tools / Utilities / Push-button Form Manager.
The Button Form Manager window will open. Then you need to select “Data Entry Forms” in the “Push Button Forms Manager” window
and click on the Change button, the “Change button form page” dialog box will open.
in which we perform:
enter the text: Students;
select the command from the drop-down list: Open form for editing;
select a button form from the list: Students, click on the “OK” button.
The "Edit Button Form Page" window displays the "Students" element. After creating the “Student Groups” element using the same technology, it will also be displayed in the window.
Next, you need to create a button to return to the main push-button form, to do this, click on the “Create” button and in the window that appears, select the command “Go to push-button form”, select “Main push-button form” and enter “Go to main push-button form”. Click on the “OK” button
and close the “Change button form page” window.
Let's launch the main button form in the database window, in which we click on the "Data Entry Forms" button, the "Data Entry Forms" will open.
The creation of elements for the “Reports” button form is carried out using the same technology, and the result will be:
Creating a Sub-ButtonForm: Queries
To create elements of the subbutton form "Queries", you need to run the database (for example, "Students' progress" with the main button form) and create macros "Open Query" or "Open Table" with unique names, and then create buttons in the button form to call these macros.
When creating a macro "Student Performance" you need to select the "Macros" tab and click on the "Create" icon, the "Macro" dialog box will open. In this window, select the "Open Query" macro action from the drop-down list and select the query name "Student Progress" from the drop-down list.
then click on the "Close" button. In the window that appears, "Save changes to the layout or structure of the Macro1 object?" Click on the "Yes" button. The “Save” window will open, in which you need to specify the name of the macro “Student Progress” and click on the “OK” button.
The macro name will be saved in the list of macros in the DB window. After creating and saving the "Parametric_query" macro, the names of the created macros will be displayed in the database window.
To create button form elements for the “Student Progress” request, you must execute the command Service / Utilities / Button Form Manager. The Button Form Manager will open.
Then you need to select “Queries” in the “Pushbutton Form Manager” window and click on the Edit button, the “Edit Pushbutton Form Page” dialog box will open.
In this window, click on the “Create” button, a new window “Changing a button form page” will open.
in which we perform:
enter the text: Report: Student performance;
select the command from the drop-down list: Run macro;
select the button form from the list: Student Progress, click on the “OK” button.
The “Edit Button Form Page” window will display the “Report: Student Progress” button form element. After creating the “Parametric_query” element using the same technology, it will also be displayed in the window.
Next, close the “Change button form page” and “Push button form manager” windows. In the DB window, on the “Forms” tab, click on the “Push Button Form” inscription, and the Main Push Button Form will open. In the Main button form, click on the Requests button, and the “Requests” button form will open.
Thus, a button form "Requests" has been created, which is a subordinate button form.
In this lesson, the user will learn what form creation capabilities the mode offers Constructor, will see how to add controls to a form, and will also look at how to create a main button form that will allow you to combine forms from different tables with each other.
Topic: Technologies for searching and storing information
Lesson: Creating the main button form and controls
In the previous lesson we looked at the mode of working with forms Layout. There is another mode for working with forms, it is called Constructor. In this mode, we can place additional elements on the form, the so-called controls or dialog box elements. These are well-known elements to us: buttons, lists, list boxes. We can create all these elements in the mode Constructor.
As soon as we go into mode Constructor form, a tab immediately appears on the ribbon Constructor, and on it we will see all the controls available to us (Fig. 1). It is these elements that we can place on forms.
Rice. 1
Let's see how we can add these elements to the form in practice. To do this, let's go to Design mode (Fig. 2), then the Design tab will automatically appear on the ribbon, and we will see all the available elements (Fig. 1).
Let's select an element button(Fig. 3), right-click on it and mark the dimensions of the future button on the form (Fig. 4). A button creation dialog box will automatically appear where we can select a category and action (Fig. 5). For example, find the entry, and click Next. On the button we can place either text or the corresponding standard image or the overview icon (Fig. 6). Let's click next and we will see that the next step is to set the name of the button, after which we click Finish (Fig. 7). The button has appeared, we can adjust the dimensions and switch to the mode Form in order to see how the button will be reflected on the form and try out its action (Fig. 8). Let's click on it; yes, indeed, as it should have been, a window appeared Search and replace(Fig. 9). Let the surname Blok be the example, let the search be carried out in the category “museum-apartments of St. Petersburg”, let the match be in any part of the field and look through all records (Fig. 10). Let's click next, the button actually works correctly, and the information we need is found in the table (Fig. 11).
Rice. 2
Rice. 3
Rice. 4
Rice. 5
Rice. 6
Rice. 7
Rice. 8
Rice. 9
Rice. 10
Rice. eleven
In addition to command buttons, you can also place lists on the form. Selecting from a list provides faster access to the desired entry. Let's see how you can create a drop-down list on a form.
Let's create another control on the form and switch to the mode Constructor and select the control Combo box(Fig. 12), let us designate its location and dimensions (Fig. 13). After this a dialog box will appear Creating Combo Boxes, in which we can choose how the combo box will receive the value, select, for example, the third option and click next (Fig. 14). Let's select a field Name and click Next (Fig. 15) and Finish. But we see that the title field already existed, it turned out to be an overlay, but we can simply delete the second title. Let's switch to mode Form, to try out the list. The list opens, we select a name museum-apartment A.S. Pushkin(Fig. 16) and we see that there is a transition to her picture (Fig. 17). That is, the combo box works.
Rice. 12
Rice. 13
Rice. 14
Rice. 15
Rice. 16
Rice. 17
If there is not one table in the database, but several and, accordingly, several forms, then you can create main button form, containing buttons, and by clicking on the buttons we can load a particular form.
Let's look at the steps involved in creating the main button form.
If the database has several tables and a corresponding number of forms, then it makes sense to create a main button form, which will serve as a kind of screen saver for the database in which all other forms can be loaded. Click on the tab on the ribbon Working with databases(Fig. 18), select the button form manager command there, and the message appears: Cannot find the button form in this database, so we press the button Create(Fig. 19). A dialog box will appear Button Form Manager, in which we can select the command Change, a window will appear Changing a button form page. Let's create the first button on it (Fig. 20). Let the text of this button be museum apartments and the command will be executed Open form for modification, and the form is . Let's create another button, which we'll call writers, when clicked the command will also be executed Open form for modification and shape writers(Fig. 21). Let's close open ones dialog boxes, and we will see in the list of objects that new forms and corresponding tables have appeared (Fig. 22).
Let's open the button form. By default, it has this design (Fig. 23), there are two buttons on it, when you click on the first button, you go to the form museum-apartments of St. Petersburg, by clicking on the second one we will open the form writers, i.e. the button form works correctly. Now let's try to change its standard design, go to the mode Constructor, and we will see the properties window here. Thus, we can select any of the elements and change, for example, the background color (Fig. 24). And then we can place, for example, an image (Fig. 25). To do this, select an arbitrary image on our computer and go to the mode Form. Now our main button form has taken the following form (Fig. 26). You may not necessarily have an image of a computer, but the drawing that you chose at the previous stage. We can note that our design has changed for the better.
Rice. 18
Rice. 19
Rice. 20
Rice. 21
Rice. 22
Rice. 23
Rice. 24
Rice. 25
Rice. 26
Bibliography
- Ugrinovich N.D. Computer science-9. - M.: BINOM. Knowledge Laboratory, 2012.
- Gein A.G., Yunerman N.A. Computer science-9. - M.: Education, 2012.
- Solovyova L.F. Computer Science and ICT. Textbook for 9th grade. - St. Petersburg: BHV-Petersburg, 2007.
- Lessons-tva.info().
- Office.microsoft.com().
- Lessons-tva.info().
Homework
- What operating modes does the Form object support?
- How to add controls to a form in MS Access?
- What is the main button form?
- Is it possible to visually edit forms?
Loading...
