Click the Form Wizard and this time, we will choose fields from a couple of different tables. These fields will now move to Selected Fields. Here, we want to retrieve data from a couple of different objects. We can also choose from options on how we want to arrange our form. If we want to create a flat form, we can choose to arrange by tblTasks , which will create that single form, with all the fields laid out in flat view as shown above.
However, if we want to create a hierarchical form based on that one-to-many relationship, we can choose to arrange our data by tblProjects. In the above window, we have the option to include a subform for tblTasks , or we can make that a linked form.
This linked form is where tblProjects will have a button that will launch that second form filtered to the project that we have selected in that underlying projects form.
Let us now select the Form with subform s , and then click Next. In the following screen, you can choose a layout for your subform. The Datasheet View gets selected by default. The Datasheet View is similar to Table View. In the following screen, you need to provide a name for your forms.
Enter the name you want and click Finish. Access will give you a preview of what your form looks like. On top, you have the controls on your main form, which is from our Projects table.
As you go down, you will see a subform. It's like a form within a form. You may also want to create a specific kind of form. For this, you can click on the More Forms drop-down menu. These are typically bound forms; select the object that you would like to be bound to that form.
This does not apply to the Modal Dialog forms. To create this type of form, you will need to select the object in navigation pane first. Let us select tblEmployees here. This type of form is divided in equal halves, either vertically or horizontally.
One half displays a single item or record, and the other half displays a list or a datasheet view of multiple records from the underlying data source.
Let us now select tblEmployees in the navigation pane and then on Create tab. Select Split Form option from More Forms menu and you will see the following form in which the form is divided vertically. We have learnt several ways to create simple data entry forms. Although the forms ease the process of data entry; these may not serve other purposes that you would want. They may not be catchy or user-friendly for what you have intended.
It is merely a generic term used to describe any object on a form or report that displays data, performs actions or items used for decorations such as a line. In other words, a control is just about anything that is placed on a form or report. We will now look at the different aspects of making a form presentable and understand how to edit and modify a form. Let us now open our Multiple Items form which lists out all employees from tnlEmployees.
As you can see a list of employees, but doesn't really look like a very user friendly list. Our controls are oversized. They are too big, spaced apart and do not provide a very useful list view.
To edit the appearance of your controls on this form, you have two form views that you can use. In the Home tab, click the View drop-down. You have the Layout View or the Design View, and both of these views are used to edit your form. The Form View opens up by default; this is the view you will use to interact with or edit the underlying data source.
At the top of Access, you will see an area marked Form Layout Tools with three tabs — Design tab, an Arrange tab, and a Format tab and each of these tabs have different options for formatting the look or appearance of the form. When you take your mouse and click on any one of these controls, you will notice that Access will highlight a given area of that form and all controls within that area are shaded a light orange whereas the actual control that you select will be shaded darker than the previous one or have a darker orange border around where you click.
Here you can resize your controls as you want by clicking and dragging your mouse to resize the height or width or both of that one control. On this particular form, when you resize any single control, you also change the size of the rest of your controls on your form, this is because of how these controls are grouped.
Let us now adjust all the fields the way you want by using the click and drag function of the mouse. That is one quick way of editing the height and width of controls in your form from this Layout View. In Access, there are some basic ways to format your forms by using built-in themes, colors, and font styles, customizing fill colors and shading alternate rows. Let us now open frmEmployees. The forms that Access creates are plain and simple. They have a blue bar on the top and a white background.
If you want to see how else you can stylize these forms, you can go to the Design View or Layout View and explore some of the options you have on the Design tab in the Themes area. If you click on the Themes' drop-down gallery, you have many pre-created themes to try out from. Hovering your mouse over any one of them will give you a preview of changing things like colors and font sizes and the actual font used.
To apply a particular style, simply click your mouse on it and you can see what that looks like. If you like the theme but you want to change the colors, you can adjust the colors by going back to the Themes group on the Design tab and choosing the color you like. You can also create custom colors to match your company's colors.
Similarly, you also have a series of font styles to choose from. You can choose one from the many that come prebuilt with the Office Suite or you can customize those fonts, choosing a specific heading font, a body font and even creating a custom name for that font group and saving. Let us go back to frmEmployees. In this form, you will see that every alternate row is shaded light gray.
The formatting option is referred to as Alternate Row Color and if you want to adjust that in a multiple form, go to the Design View. Select that detail section and then go to the Format tab and in background group you should see an option for Alternate Row Color. You can change the colors for alternate rows.
To see what that looks like, simply go to the Form View or the Layout View. If you don't want any shading at all, you can choose No Color as your Alternate Row Color and that is more the traditional look from earlier versions of Access. Access includes a Navigation Control that makes it easy to switch between various forms and reports in your database.
A navigation form is simply a form that contains a Navigation Control. Navigation forms are a great addition to any desktop database. We have already used navigation pane to navigate through all of the Access objects we create, such as forms, queries, tables, etc. If you want to create your own navigational structure and make it easier for the users to find the specific objects that they really need, you can build navigation form, which is a form that uses a navigation control so users can use or view forms and reports right from within that main navigation form.
Let us now take a simple example in which we will create the navigation form. For this, go to the Create tab; in the Forms group, you will see this navigation drop-down menu.
In this menu, you will see different layouts for how to arrange your forms and reports that you would like to embed on this navigation form.
There is one with horizontal tabs, one with vertical tabs — where all the tabs are aligned to the left, a vertical tabs layout where all the tabs are aligned to the right. There is a horizontal tabs layout that has two levels to it, so if you have a lot of objects that you want to display across the top, you can make use of this. You can have one where you have both horizontal tabs and vertical tabs, either aligned to the left or to the right.
In the following example, we will be using Horizontal Tabs and Vertical Tabs. To create that layout or that navigation form, simply click on it, and Access will create an unbound form, with a navigation control on it. To add objects to this navigation form, the easiest way to do is through your layout view, by simply dragging and dropping objects to where you want them to appear.
Let us now drag frmProjects form from the navigation pane and drop it on the [Add New] on the top. Similarly, drag frmAuthers form from the navigation pane and drop it to the left of the Add New Button. Now, you will see that your other project buttons have disappeared from the left and that is because they are attached to Project tab.
All the buttons you view on the left are linked to whatever you have selected up the top. Now with the Employee tab selected, let us drag employees-related information to the left. Now we have project information on one tab, employee information on the other. Similarly, you can add more tabs as per your requirements. As you can see that the name of the tabs is not appropriate, so let us start renaming some of these tabs to make them more user-friendly.
The easiest way is to double-click on any tab or any button on the left and rename it as shown in the following screenshot. When you enter data in any form, it can be quicker and easier to select a value from a list than to remember a value to type.
A list of choices also helps ensure that the value entered in a field is appropriate. A list control can connect to existing data, or it can display fixed values that you enter when you create the control. In this chapter, we will cover how to create a combo box in Access. A combo box is an object or control which contains a drop-down list of values that the user can select from. Let us now take a simple example of creating a combo box. We have created a form for an employee as shown in the following screenshot.
We now want to create a combo box for Phone type because we know that phone type should be either Home, Cell or Work. This information should be available in the dropdown list and the user need not type this information. Let us now go to the Design View for this form.
Select the Phone Type field and press delete. Let us now select Use Control Wizards option from the Controls menu and then Select the Combo Box Control from the menu as shown in the following screenshot.
Now, draw the combo box where you want and when you release your mouse then you will see the Combo Box Wizard dialog box.
Here you have different option for data; let us select the 2 nd option wherein, we will add the values and click Next. You can see that combo box is created but it is not aligned to other field.
Let us do that first by selecting all fields and then go to the Arrange tab. In this chapter, we will be covering the SQL view. Whenever you create a query in query design, Access automatically creates the SQL query for you. This actually retrieves data from the tables.
To see how your query is created in sql when you create it in query design, let us open your database. You can now see all the employee information as query result. To view the SQL, go to the Home tab. You can see the SQL query which is generated by Access automatically.
This helps retrieve data from two tables. One especially useful formatting tool in Access is the ability to apply Conditional Formatting to highlight specific data. Let us take a simple example of conditional formatting. We have a list of all of the projects in this database and we have also got a couple of new fields like the On Time Status and the Number of Late Tasks.
This form is created from another query. In this query, we have a join between a table and a query that will display the count of due dates or how many projects have tasks that are overdue.
We also have a calculated field here that uses the IF function to determine whether or not the count of the due date is greater than zero. It will then display the words Late if the project is late or On Time if that specific project does not have any overdue tasks.
In this example, we will be using the above form to understand how you can use Conditional Formatting to highlight specific pieces of information. We will now highlight every single project that is currently running late.
To apply Conditional Formatting to one field or more than one field, we will need to switch over to the Layout view. On that Format tab, you should see a group called Control Formatting and a special button for Conditional Formatting.
Let us now click on Conditional Formatting. You will now see a Conditional Formatting Rules Manager and currently we have no rules applied to this control. Let us now create a new rule by clicking on the New Rule button. You will now see a New Formatting Rule dialog box. We will first specify the type of rule we will be creating and here we have two options.
The first option is to check the values in the current record or to use an expression , and the second option is to compare this record with the other records. We now have only one of two values in our form; either On Time or the word Late and that is from the given query. We can now set our Conditional Formatting, how we want this field to look like if the word Late appears in that field.
Let us now change the font color to red and make it bold, italic and underline , and that's our conditional rule. Let us now click Ok and then click Apply , and Ok again. You can see that the word Late is formatted now. This is one example of how to create a very basic conditional format rule. Let us take another example.
Here, we will make the title or the name of the project red and bold, italic and underline. Select the project name control on your form. We will now go back to our Format tab and click on Conditional Formatting and create a new rule for that specific control as shown in the above screenshot. Here, we will not be checking the value of the current field we have selected, but we will be checking it against another field on this form. Select Expression Is in the first combo box and then click on … button at the end as in the above screenshot.
In the Expression Categories, you have every single object that is on this form. Doubleclick on CountofDueDate. This will send the reference to that control or that field up to our expression builder and condition if it is greater than zero. Now, click Ok. Let us now look at another example of conditional formatting. Let us assume, we want to see which projects are more late or have more late tasks than other late projects. Select the Conditional Formatting option.
Let us further change the Bar color to red. We want our shortest bar to represent the lowest value and the longest bar to represent the highest value. Let us now click Ok and then, click Apply and Ok again.
You can now see Conditional Shading applied as in the above screenshot. Let us now go to the Form view. In this chapter, we will be covering Controls and Properties available in Access. We will also learn how to add controls to forms.
Controls are the parts of a form or report that you use to enter, edit, or display data. Controls let you view and work with data in your database application. There are different kinds and types of controls you can create, but all of which will fall into one of the two categories — bound or unbound.
You can create different types of controls in Access. You can use these controls to interact with the data stored in your database, but you can also have unbound text boxes. Calculated controls will perform some kind of calculation based on an expression that you write and that data is not stored anywhere in your database.
Hyperlink creates a hyperlink on your form to something else. It can either be a web page or even another object or place within your database. You can also create a web browser control and navigation control, groups, page breaks, combo boxes. You can create charts, lines, toggle buttons, list boxes, rectangles, check boxes, unbound object frames, attachments, option buttons, subforms and subreports, bound object frames and even place images on your form.
Let us now look at a simple example of some of these controls by creating a new blank form. Go to the Create tab in the forms group and click on Blank Form. On the Property Sheet, click on the drop-down arrow and make sure Form is selected, and then go to the Data tab. On the Data tab, you will see that the Record Source remains blank.
Let us assume, we want to create a form that's going to be tied to two different tables in our database. Now click on … button. It will further open its own query builder.
Select the tables that contain the data you want to display; click on the Add button and then close this dialog box. Let us now select all the fields from tblEmployees and drag to query grid, and similarly add all the fields from tblHRData.
You can now see the query as its Record Source. We have now bound this form to an object in our database, in this case qryEmployeesData. We can now start by adding some controls to this form and to add any one of the controls, go to the Design tab and view your options from the controls group. From the Controls menu, you will see that the Use Control Wizard is highlighted as in the above screenshot.
This little button has that highlighted box around it by default. This means that the control wizards are turned on. This is like a toggle switch.
When you click on the toggle switch the wizards turn off. Clicking it again will turn the wizards on. Let us now click on the Label and drag this label and enter Employee Information and then go to the Format tab to format it as in the following screenshot. You can choose to apply a bold style or change the font size of text inside that label etc.
This control appears inside the detail section of your form. It makes more sense to position this label inside the form header section, which is not visible yet.
Right click anywhere in the background of that form and choose Form Header as in the above screenshot. Move this control into the Form Header area. Let us now create some other controls from the Design tab. At this point, Let us say, we want to distribute all our fields into two different tabs.
From the control menu, you can see the Tab Control which will create tabs on your form. Click on the tab control and draw it on your form in the detail section as in the above screenshot.
If we want to view some information that's stored in our underlying query, click on the Add Existing Fields option from the Ribbon in Tools group to view all the available fields from our underlying query. To bring the existing fields to the page, select all the fields available in the Field List as in the following screenshot. You can go through and make any adjustments to the size and width of these controls to get it looking the way you like.
Once you are done with formatting click on the Save As icon to save the form with the name you want. The tab control is breaking up our controls or our fields into two screens to make viewing and adding information easier. In this chapter, we will be covering the basics of reports and how to create reports. Reports offer a way to view, format, and summarize the information in your Microsoft Access database.
For example, you can create a simple report of phone numbers for all your contacts. A report consists of information that is pulled from tables or queries, as well as information that is stored with the report design, such as labels, headings, and graphics.
The tables or queries that provide the underlying data are also known as the report's record source. If the fields that you want to include all exist in a single table, use that table as the record source.
If the fields are contained in more than one table, you need to use one or more queries as the record source. We will now take a simple example to understand the process of creating a very simple report. For this, we need to go to the Create tab.
Before clicking on the Report button to create a basic report, make sure the proper query is selected. In this case, qryCurrentProjects is selected in your navigation pane. Now click on the Report button, which will generate a report based on that query. You will see that the report is open in Layout view. This provides a quick way to adjust the size or width of any of your fields that you see on the report.
Let us now adjust the column widths to make everything fit in a better way. This was a very quick way to create a very simple report. You could also make minor changes and adjustments from the report design view.
Enter a name for your report and click Ok. If you want to view what this report will actually look like, in Print Preview, you can go back to the View button and click on Print Preview to see what this report would look like when printed either on paper or as a PDF.
Using the tools on the lower right-hand corner, you can zoom in or zoom out. You also have some buttons on the Print Preview tab that appear automatically when you switch to Print Preview.
In the zoom section, you've got a view for one page, two pages; or if you have a longer report, you can view four pages at once, eight pages or twelve pages. You can also adjust simple things such as the size of the paper that you are using to print, the margins for your report, the orientation, the number of columns, page set up, etc. And that is how you can create a very quick simple report using the Report button on the Create tab. Report Design is another method for creating a quick report in Access.
This will create a blank report and open it directly to the Design View, allowing you to change the control source and add fields directly to the Design View of the report. It will open a blank report or an unbound report, meaning this report is connected to no other object in our database.
On the Design tab in the Tools group, select the Property Sheet. This will open up the Property pane. On the Data tab, assign a record source to this report, to connect it to a database object as in the following screenshot. Select qryLateProjects from the drop-down and now, the next step is to go through and add some fields to this report by clicking on Add Existing Fields list button on the Design tab.
Drag the fields to you report as in the above screenshot. Go the Arrange tab, and in the Table group, you have a couple of options to choose from. There is a stacked layout and a tabular layout, which is a layout that is very similar to a spreadsheet.
Let us select the tabular layout. You can see that it moves all of the labels up to the page header area. These labels will appear only once at the top of every page and the data query will repeat for every record in the Details section. Now, you can go through and make some adjustments to make your ProjectName field wider.
As you can see in the above screenshot, there is a lot of space between Detail section and Page Footer. Let us drag the Page Footer up to reduce the space as in the following screenshot.
We will now go to the Design tab and click on the View button and choose Report View. You can now see that some project names are not complete; you can adjust this with either the design view, or you can use the layout view to do that.
In this chapter, we will learn how to format reports. You will find that there are a lot of similarities between formatting reports and formatting forms but there are a few tools and tricks that are specific to reports. Let us now look into the concept of report sections and grouping.
For this, we need to open a report that we created in the last chapter. Here, we will see how some of this information is displayed on the report. You can see that there's not a lot to look and here only page header, details section, and page footer are visible. You can add an additional couple of sections very easily. Let us select that option and go back to the Report View.
You can see it just adds a little colored area at the very top of the report. In the Design View, expand that area by hovering the mouse right at the top of that page header divider, clicking and dragging down.
This will add more area to the report header. In the Report View, you will now see more area at the very top of the report as in the following screenshot.
The Report Header and Footer controls appear at the top of the first page of that report. The report footer controls what you see at the very last page at the bottom of the report.
Anything that is placed in the Page Header will appear at the top of every page. Likewise, anything that is placed in the Page Footer will repeat at the bottom of every page. Now you can add additional grouping levels, and to do that, you want to make sure you have the group sort and total area turned on. In the Grouping and Totals section of the Design tab, click on the Group and Sort button which will open Group, Sort, and Total area at the bottom as shown in the following screenshot. You can now add additional groups or grouping on any control that you have in your report.
Let us now click on Add a Group. In the above screenshot, you can see the underlying control source for report with the project name, task title, due date and percent complete. Let us now say we want to group all of our late tasks by project, so select Project Name from the list.
Now you will see an additional grouping level ProjectName Header above your details section. Instead of the project name appearing alongside each individual task that is late, we can now move this control up to this project name header. You can now go to the Report View and see the adjustments made to group the things in the report as in the following screenshot. Every single project has its own little level on its own line, and then underneath that area you will see all of the tasks that are late for the above project.
With that changed now, you can go through and start formatting your report in the Layout View. If you want to make the project name bigger, then drag the line below and change the font size to 20 in the Format Tab.
To remove the border around the control, click on the Shape Outline in the Format tab as in the following screenshot. Let us now go back to the Report View. You will now see that the labels appear directly above the controls that they describe. If you notice, you will see that there is no space between the tasks and the next project name. There should be additional space in between. For that, we need to add ProjectName Footer. You will now find all the options for how to group and sort by project name as in the following screenshot.
Here, we have a property called Without a Footer Section. Let us change that option by clicking on that little arrow next to the words Without a Footer Section. As you can see in the above screenshot that the ProjectName Footer section beneath the Details section is added. This will act as the footer for that project name group. To remove shading or the appearance of background for alternating areas in both the Details section and in the Project Name area, go to the Design View again.
Let us change the Details section first by clicking on the detail divider and then open Properties sheet. You can now see how the report looks. To see how your data will print page by page, let us go to the Print Preview. In this chapter, we will be working with Built-in Functions. In Access, there are close to a hundred built-in functions and it is almost impossible to cover every single one of them. In this chapter, we will cover the basic structure, syntax, and use some of the more popular functions, and also the pitfalls, so that you can go exploring some of the other functions on your own.
A function is a VBA procedure that performs a task, or calculation, and returns a result. Functions can generally be used in queries, but there are other places that you can use functions. You can also use functions in an expression when you create a calculated field, or use the functions inside form or report controls.
You can use functions even in macro arguments. Functions can be quite simple, requiring no other information to be called, or, simply reference one field from a table or query. On the other hand, they can also get quite complicated, with multiple arguments, field references, and even other functions nested inside another function.
The Date function is designed to return the current system date. This function does not require any function arguments or additional information. All you have to do is write the name of the function and those open and close parentheses. The Time Function returns the current system time only and the Now Function returns both the current system date and time. Depending on the data that you want to track, or store, or query, you have three built-in, easy-to-use functions to help with that task.
Let us now open your database and create a new query using query design and add tblProjects and tblTasks. You can now see all the different tasks from all projects. The criteria starts with an operator greater than symbol , followed by an equal to symbol and then Date Function. When we run this query, all the tasks will occur either on today's date or in the future as in the following screenshot.
Let us now say this query needs to be more flexible in terms of the dates it is pulling starting this week. We do have a couple of different tasks that began this week, that are not showing up in this current list, because of our criteria. It's looking at start dates that are equal to today or above. If we want to view the tasks that started this week, that have not yet completed or should complete today, let us go back to the Design View.
Here, we will add some additional information to these criteria. In fact, we want it greater than or equal to today's date minus seven days. The DateDiff Function returns a Variant long , specifying the number of time intervals between two specified dates.
In other words, it calculates the difference between two dates, and you get to pick the interval by which the function calculates that difference. Let us now say we want to calculate our authors' age. For this, we first we need to create a new query and add our authors table and then add FirstName, LastName, and the BirthDay fields.
We can calculate people's age by calculating the difference between their date of birth, or birthday and whatever today's date is. The Format Function returns a string, containing an expression formatted according to instructions contained in a format expression. Here is the list of user-defined formats which can be used in Format function.
Type the Format Function. The first function argument will be an expression, which can be almost anything. Basic tasks for an Access desktop database. Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve?
Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback.
Thank you for your feedback! Create your own database apps easily in formats that serve your business best. PC only. Tailor your custom apps to your business and your customers, easily editing as needed to meet evolving needs.
Using Visual Basic for Applications, automate business processes and create more useful forms and reports. Integrate data between Access and line-of-business apps using the Access connector library to generate aggregated visuals and insights in the familiar Access interface.
Microsoft Access is available for PC only. Learn more. The most up-to-date version of Microsoft Access is always available with a Microsoft subscription.
0コメント