اموزش کامل Access 2010-En

[A M I R]

[h=2]Modifying Tables

[h=4]Validation Rules

A validation rule is a rule that dictates what information can be entered into a field. When a validation rule is in

place, it is impossible for a user to enter data that violates that rule. For example, if we were asking the user to

input a state name into a table with contact information, we might create a rule which limits the valid responses to

U.S. state postal codes. This would prevent users from typing something that wasn't actually a real state postal code.




In the example below, we will apply that rule to our Customers table. It's a fairly simple validation rule-- we'll just

name all of the valid responses a user could enter, which will mean the user can't type anything else into the

record. However, it's possible to create validation rules that are much more complex. For detailed information

on how to write validation rules, review this official Microsoft office tutorial on creating validation rules.

​

 

[A M I R]

[h=2]Modifying Tables

[h=4]To Create a Validation Rule:

Select the field you wish to add a validation rule to. For our example, we'll set a rule for the State field.
On the Ribbon, select the Fields tab and locate the Field Validation group. Click the Validation drop-down command and select Field Validation Rule.

The Expression Builder dialog box will appear. Click the text box and type in your validation rule.

In our example, we want to limit data in the State field to actual state postal codes. We'll type each of the valid

responses in quotation marks, and separate them with the word Or, which lets Access know that this field can accept

the response "AL" or "AK" or "AZ" or any of the other terms we've entered.

Once you're satisfied with the validation rule, click OK. The dialog box will close.

Click the Validation drop-down command again. This time, select Field Validation Message.

The Enter Validation Message dialog box will appear. Type the phrase you would like to appear in an error message

when a user tries to enter data that violates the validation rule. Your message should let the user know what data is permitted.

When you're satisfied with the error message, click OK.

The validation rule is now included in the field. Users will be unable to enter data that violates the rule.

Simple validation rules can be written exactly like query criteria. The only difference is that query criteria search for

data, while an identical validation rule either permits or rejects data. To see examples of query criteria​

 

[A M I R]

[h=2]Modifying Tables

[h=3]More Table Options [h=4]Calculated Fields and Totals Rows





Adding calculated fields and totals rows to your table lets you perform calculations using your table

data. A calculated field calculates data within one record, while a totals row performs a calculation on an

entire field of data. Whenever you see a subtotal for one record, you are looking at a calculated field. Likewise, a grand total at the

bottom of a table is really a totals row.

​

 

[A M I R]

[h=2]Modifying Tables

[h=3]Challenge!
If you haven't already, download our sample database and open it.

Open the Products Table and change the data type for the description field to Memo.

Add a field with the Attachment data type and name it Images.

Open the Order Items table. For the Quantity field, create the following field validation rule: >0. This will require users to enter a quantity greater than 0.

Set a field validation message for the same field. It should say something that reminds the user to enter a number greater than 0.

Test your field validation message by typing 0 into a cell in the Quantity field.

​

 

[A M I R]

[h=2]Creating Forms

[h=3]Introduction

Creating forms for your database can make entering data much more convenient. When you create a form, you can design it in

a way that works with your database and makes sense to you.


In this lesson, you will learn how to create and modify forms. You'll also learn how to use form

options like design controls and form properties to make sure your form works exactly the way you want it to.

​

 

[A M I R]

[h=2]Creating Forms

[h=3]Creating Forms
Access makes it easy to create a form from any table in your database. Any form you create from a table will let

you view the data that's already in that table and add new data. Once you've created a form, you can also

modify it by adding additional fields and design controls such as combo boxes.

​

 

[A M I R]

[h=2]Creating Forms

[h=4]To Create a Form:
In the Navigation Pane, select the table you would like to use to create a form. You do not need to open the table.
Select the Create tab on the Ribbon and locate the Forms group. Click the Form command.

Your form will be created and opened in Layout View.

To save the form, click the Save command on the Quick Access Toolbar. When prompted, type a name for the form and then click OK.

​

 

[A M I R]

[h=2]Creating Forms

[h=4]About Subforms
If you created a form from a table whose records are linked to another table, your form probably includes a

subform. A subform is a datasheet form that displays linked records in a table-like format. For instance, the

subform included in the Customers form we just created displays linked customer orders.




We probably don't need to include this subform, since we really just want to use the Customers form to

enter and review contact information. If you find that you don't need a subform, you can easily delete it. Simply click it and press the delete key.

However, subforms aren't always useless. Depending on the content and source of your form, you might find that

the subform contains useful information, as in the example below. In our Orders form, the subform contains the

name, quantity, and price of each item contained in that order, which is all very useful information.

​

 

[A M I R]

[h=2]Creating Forms

[h=3]Adding Additional Fields to a Form
When you use the Form command on an existing table, all of the fields from that table are included in that form. However, if you

later add additional fields to that table, those fields will not automatically show up in existing forms. In situations like this, you

can add additional fields to a form.





​

 

[A M I R]

[h=2]Creating Forms

[h=4]To Add a Field to a Form:
Select the Form Layout Tools Design tab, then locate the Tools group on the right side of the Ribbon.
Click the Add Existing Fields command.

The Field List pane will appear. Select the field or fields to add to your form.

If you want to add a field from the same table you used to build the form, simply double-click the name of the desired field.

To add a field from a different table:

Click Show All Tables.
Click the plus sign + next to the table that contains the field you wish to add.
Double-click the desired field.

The new field will be added.

You can also use the above procedure to add fields to a totally blank form. Simply create a

form by clicking the Blank Form command on the Create tab, then follow the above steps to add the desired fields.

​

 

[A M I R]

[h=2]Creating Forms

[h=3]Adding Design Controls



Design Controls set restrictions on the fields in your forms. This helps you better control how

the data is entered into your forms, which in turn helps keep the database consistent.

​

 

[A M I R]

[h=2]Creating Forms

[h=4]Combo Boxes

A combo box is a drop-down list that you can use in your form in place of a field. Combo boxes limit the


information that a user can enter by forcing them to select onlythe options that you have specified.


Combo boxes are useful for fields that have a limited number of possible valid responses. For instance, you might use a

combo box to make sure that people only enter a valid U.S. state while entering an address, or that they only choose

products that already exist in your database while placing an order.


​

 

[A M I R]

[h=2]Creating Forms

[h=4]To Create a Combo Box:

In Form Layout view, select the Form Layout Tools Design tab and locate the Controls group.
Select the Combo Box command, which looks like a drop-down list.

Your cursor will turn into a tiny crosshairs and drop-down list icon

. Move the cursor to the place where

you would like to insert the combo box, and click. A yellow line will appear to indicate the location where your

combo box will be created.


In our example, the combo box will be located between the City field and the Add to Mailing List? field.

The Combo Box Wizard dialog box will appear. Select the second option, I will type in the values I want, then click Next.

Type in the choices you would like to appear in your drop-down list. Each choice should be on its own row.


In our example, we are creating a combo box for the Add to Mailing List? field in our form, so we will

enter all of the possible valid responses for that field. Users will be able to select one of three choices


from our finished combo box: "No," "Yes- Weekly," and "Special Offers Only."

If necessary, resize the column so that all your text is visible. Once you are satisfied with your list, click Next.

Select Store that value in this field, then click the drop-down arrow and select the field where you would

like selections from your combo box to be recorded. After making your selection, click Next.

Enter the label, or name that will appear next to your combo box. Generally, it's a good idea to use the name of the field that you chose in the previous step.

Click Finish. Your combo box will appear on the form. If you created your combo box to replace an existing field, you should delete the first field.

In our example, you might notice that we now have two fields with the same name. These two fields send


information to the same place, so we don't need them both. We'll delete the one without the combo box.

Switch to Form view to test your combo box. Simply click the drop-down arrow and verify that the list

contains the correct choices. The combo box can now be used to enter data.

If you want to include a drop-down list with a long list of options and don't want to type them all out, create a

combo box and choose the first option in the combo box wizard, I want to get the values from another

table or query. This will allow you to create a drop-down list from a table field.

Some users report that Access malfunctions while working with forms. If you have a problem

performing any of these tasks in Layout view, try switching to Design View.



​

 

[A M I R]

Creating Forms

Customizing Form Settings with the Property Sheet







The Property Sheet is a pane containing detailed information about your form and each of

its components. From the Property Sheet, you can make changes to every part of your form, both in terms of function and appearance.




The best way to familiarize yourself with the property sheet is to open it and select various options. When

you select an option, Access will display a brief description of that option on the bottom left border of the program window.

Pay close attention as you modify your form and its fields. It's possible to make very subtle changes

with the Property Sheet. Because there are so many options, it can sometimes be hard to remember

which one you used to modify each aspect of your form.

​

 

[A M I R]

[h=2]Creating Forms

[h=4]Modifying Form Settings

There are far too many options in the Property Sheet to discuss them all in detail. We'll review two useful

ones here: hiding fields, and setting fields with dates to automatically fill in the current date. Practicing

these procedures should give you a sense of how to work with other Property Sheet settings, as well.

​

 

[A M I R]

[h=2]Creating Forms

[h=4]To Hide a Field:


In either Layout or Design view, select the Design tab and locate the Tools group. Click the Property Sheet command.

The Property Sheet will appear in a pane on the right. On the form, select the field you wish to hide. In our

example, we'll hide the Customer ID field, since we don't want any of our users to try to edit it.

In the Property Sheet, click the Format tab and locate the Visible option on the third row.

Click the drop-down arrow in the column to the right, and select No.

Switch to Form view to verify that the field is hidden.
​

 

[A M I R]

[h=2]Creating Forms

[h=4]To Set a Field to Auto-fill with the Current Date

In either Layout or Design view, select the Design tab and locate the Tools group. Click the Property Sheet command.

The Property Sheet will appear in a pane on the right. On the form, select the field you would like to automatically

fill in the current date. This must be a field with the date data type. For our example, we'll select the Pickup Date field on our Orders form.

In the Property Sheet, click the Data tab and select the Default Value field in the fourth row. Click the
Expression Builder button

that appears in the column to the right.

The Expression Builder dialog box will open. In the Expression Elements list, click the words Common Expressions.

In the Expression Categories list, double-click Current Date.

The expression for Current Date will be added. Click OK.

Switch to Form view to verify that the expression works. When you create a new record

with that form, the date field that you modified will automatically fill in the current date.

​

 

[A M I R]

[h=2]Creating Forms

[h=3]Challenge!


If you haven't already, download our sample database and open it.
Create a form from the Customers table.
Delete the subform.
Create a combo box.

Add the following choices to the combo box:

• Raleigh
• Durham
• Hillsborough
• Cary
• Chapel Hill
• Garner
• Charlotte

Store the value in the City field and label the combo box NC City List. Click Finish when you are done with the Combo Box wizard.

• Switch to Form view and click on the drop-down arrow in the combo box you created. You should see a list of cities.

 

[A M I R]

[h=2]Formatting Forms

[h=3]Introduction

After creating a form, you might want to modify its appearance. Formatting your forms can help make your database

look consistent and professional. Some formatting changes can even make your forms easier to use. With Access 2010's

formatting tools, you can customize your forms to look exactly the way you want.


In this lesson, you will learn how to add command buttons, modify form layouts, add logos and other images, in addition to changing form colors and fonts.

​

 

[A M I R]

[h=2]Formatting Forms

[h=3]Formatting Forms
Access offers many options that let you make your forms look exactly the way you want. While some of

these options, like command buttons, are unique to forms, others may be familiar to you.

​