Access change list box to combo box
Bound Combo Boxes Introduction A combo box is a Windows control that holds a list of text items. The user can select one item from the list. Microsoft Access supports bound and unbound combo boxes. The application also provides the tools to help you create and configure a combo box using either the Lookup Wizard or the Combo Box Wizard. A Bound Combo Box A combo box is said to be bound if its values come from a field of a table. Such a field must be the foreign key that represents the records of a related table. The easiest way to create a bound combo box is to use a wizard because you would let Microsoft Access configure everything behind-the-scenes after you have just made some selections in the wizard. The combo box can be created in a table where it is referred to as a lookup field, or in a form where it is a formal combo box. If a lookup field is created in a table, it can be added to a form or report where it would be created as a combo box. If only a value was provided for the foreign key of a table, then you must formally create a combo box for it.
The Characteristics of a Lookup Field/Combo Box In some cases, if you create your lookup field of combo box using the wizard, some factors may not produce the expected result. Fortunately, you can modify the configuration of the control. Many options are available. The configurations of the lookup field can be found in the Lookup section of the bottom part of the table in Design View. The configuration of a combo box of a form can be found in the Data or the All tab of the Property Sheet. The available characteristics or properties are:
An Unbound Lookup Field/Combo Box Introduction A combo box (or a lookup field) is said to be unbound if it holds its own list of values instead of the values comming from a column of a table. Microsoft Access provides the means of creating the controls and adding the values. As mentioned for the bound combo box (or a lookup field), you can manually create and configure the control or you can use a wizard. To easily add a lookup field to a table, use the Lookup Wizard. To easily add a combo box to form, use the Combo Box Wizard. In both cases, in the first page of the wizrd, click the second radio button. If you had created a lookup field in a table, you can drag its item from the Field List to a form or report, in which case it would become a combo box, Of course, the control would receive the same name as the table's column. If you create a combo box on form using the Combo Box Wizard, the control would receive a name that starts with Combo. An example would be Combo21.
The Characteristics of a Lookup Field/Combo Box As mentioned previously, after creating a lookup field or a combo box using the wizard, you can customize it. In fact, you cab configure the field or the combo box by setting its properties. The configurations of a lookup field are done in the Lookup section of a table in Design View. The configuration of a combo box of a form are done in the Property Sheet. The properties are:
Combo Box Events When using a combo box, depending on the characteristics of the control, if the user types a value that is not in the list, the control would fire an event named On Not List. You can use this event to display a message to the user and to take an appropriate action. Page 2The Web Browser Introduction A web browser is a control that shows a file. The file can be a regular picture or a web address that is identified as a Uniform Resource Locator (URL). A web browser is very flexible with the type of document it can show. Still, you must follow some rules to prepare the document. The primary type of document intended for a browser is a webpage. You probably already know how to create such a document. A browser can also be asked to display a picture. The web browser of Microsoft Windows supports various or all types of pictures, including those with the extensions .bmp, .jpg, .jpeg, .gif, .png, etc. A web browser can be added only to a form. To get it, after displaying the form in Design View, in the Controls section of the Design tab of the Ribbon, click the Web Browser Control
Characteristics of a Web Browser Probably the most important detail of a web browser is the document it displays. This is specified by the Record Source property. You cannot create a web browser on a table. Instead, you can create a text-based field, then use it as the record source of a browser. When performing data entry, enter the name of the file with extension, the complete path and name of the file, or the URL of the web page that the browser would display.
Web Browser Events The web browser has many events appropriate for its functionality:
We already know that you can submit the path of a file or a URL to it. When a file path or a URL is given to a web browser, before it processes it, the control fires an event named On Before Navigate. If there is no problem in this event, the control shows the file or the web page. When the control has finished displaying the document, the web browser fires the On Document Complete event. If there is a change on the document, the control fires an On Progress Change event. When a web browser has received a file path or a URL, it makes an attempt to show that file or the web page. If it encounters a problem, it fires an On Navigation Error event. At any time, and if you allow it, the user can change the document the control is displaying. When a new document must be displayed, the control fires an On Updated event. Attachments Introduction The OLE Object provides a convenient means of adding an external file to a record. One of the problems with it is that it can add only one object. Sometimes you want to associate many documents to one record and you may have a different number of objects for different records. Microsoft Access solves this problem through an attachment. An attachment is a technique of associating one or more files to a record.
Creating an Attachment Field You can create an attachment field in the Datasheet View or in the Design View of a table. To create an attachment in the Datasheet View:
If creating a table in Design View and if you are creating a new column, to configure it to hold one or more attachments, specify its Data Type as Attachment. If a field has already been created and the records have been added to the table, you cannot change the data type of a field to Attachment. You would receive an error when you try to save the table: In other words, you can specify the Attachment type only if you are creating a new field. After creating an attachment field on a table, you can configure it on a related form. In the Controls section of the Ribbon, the attachment is represented
Using an Attachment You can add the attachment(s) either in the Datasheet View of a table or in the Form View of a form. Once the object is displaying, you can either double-click the field or right-click the field and click Manage Attachments. This would open the Attachments dialog box: In the Attachments dialog box, to create an attachment, click the Add button. This would open the Choose File dialog box. It behaves like the Open dialog box. When using it, select the file or object and click Open. The selected object would be added to the Attachments list view. In the same way, you can add the other objects one at a time. Alternatively, to add many objects, in the Choose File dialog box:
To remove an item from the collection, click it in the Attachments list box and click Remove. Once you are ready with the list of attachments, click OK. After the attachments have been created, to access those of a record, navigate to that record. Then, right-click the placeholder of the attachment. A menu would come up. Among other menu items, the menu has a Forward and a Back options:
Page 3Expressions Fundamentals Introduction to Expressions and Operations An expression, also called an operation, is a technique of combination of a value and an operator, two values and an operator, etc. The value can be a known value or it can come from a field of a table. Based on this, to create an expression or to perform an operation, you need at least one value or field and a symbol. A value or field involved in an operation is called an operand. A symbol involved in an operation is called an operator.
A unary operator is one that uses only one operand. An operator is referred to as binary if it operates on two operands.
Introduction to Constants A constant is a value that does not change. Examples of constants are algebraic numbers because they never change. Therefore, any number you can think of is a constant. Every letter of the alphabet is a constant. In the same way, any word you can think of is a constant. Common Operators The Assignment Operator = In order to provide a value to an existing field, you can use an operator called assignment and its symbol is "=". It uses the following formula: Field/Object = Value/Field/ObjectThe operand on the left side of the = operator is referred to as the left value:
The operand on the right side of the operator is referred to as the right value. It can be a constant, a value, an expression, the name of a field, or an object. There are various ways you can use the assignment operator. For example, in the Property Sheet for the control, in its Control Source, write an expression that assigns the existing field. In some other cases, the assignment operator will be part of a longer expression.
Positive and Negative Values An algebraic value is considered positive if it is greater than 0. A value is referred to as negative if it is less than 0. To express a negative value, it must be appended with the - symbol. Examples are -12, -448, -32706. Besides a numeric value, the value of a field or an object can also be expressed as being negative by typing a - sign to its left. For example, -txtLength means the value of the control named txtLength must be made negative. Algebraic Operators The addition is used to add one value or expression to another. It is performed using the + symbol. The formula to follow is: Value1 + Value2The multiplication allows adding one value to itself a certain number of times. The multiplication is performed with the * symbol. The formula to follow is: Value1 * Value2The division is used to get the fraction of one number in terms of another number. Microsoft Access supports two types of results for the division operation. If you want the result of the operation to be a natural number, use the backlash "\" as the operator. The formula to follow is: Value1 \ Value2This operation can be performed on two types of valid numbers, with or without decimal parts. After the operation, the result would be a natural number. The second type of division results in a decimal number. It is performed with the forward slash "/". The formula to follow is: Value1 / Value2After the operation is performed, the result is a decimal number.
The Exponentiation: ^ Exponentiation is the ability to raise a number to the power of another number. This operation is performed using the ^ operator (Shift + 6). It uses the following mathematical formula: yxIn Microsoft Access, this formula is written as y^x and means the same thing. Either or both y and x can be values or expressions, but they must carry valid values that can be evaluated. The Remainder Operator: Mod The division operation gives a result of a number with or without decimal values. Sometimes you will want to get the value remaining after a division renders a natural result. The remainder operation is performed with the Mod keyword. The formula to follow is: Value1 Mod Value2Microsoft Access Operators The Period Operator: . We already know that a property is something that characterizes or describes an object. An example of a property is the width of a control or the text it contains. To access the property of an object, type the name of the object, followed by a period, followed by the name of the property you need. The formula to follow is: Object.PropertyNameThe property you are trying to use must be a valid property of the object. The Square Brackets Operator: [] To name our objects so far, in some cases we used a name made of one word without space. Actually, you can use spaces or some special characters in a name. Unfortunately, when such names get involved in an expression, there would be an error or the result would be unpredictable. To make sure Microsoft Access can recognize any name in an expression, you can/should include it between an opening square bracket "[" and a closing square brackets "]". Examples are [© Year], [Soc. Sec. #], or [Date of Birth]. Even if the name is in one word, to be safe, you should (always) include it in square brackets. Examples are [Country], [FirstName], or [SocialSecurityNumber]. Therefore, the =txtLength expression can be written =[txtLength].
The Collection Operator: ! The objects used in Microsoft Access are grouped in categories named collections. For example, the forms of a database belong to a collection of objects named Forms. The reports belong to a collection of objects named Reports. The data fields belong to a collection named Fields. The controls on a form or report belong to a collection named Controls To refer to a particular object in an expression, use the exclamation point operator "!". To do this, type the name of the collection followed by the ! operator, followed by the name of the object you want to access. For example, on a form, if you have a text box named txtLength and you want to refer to it, you can type [Controls]![txtLength]. Therefore, the =txtLength expression can be written =Controls!txtLength, and =[txtLength] can be written =Controls![txtLength] or =[Controls]![txtLength]. The name of the collection is used to perform what is referred to as qualification: the name of the collection "qualifies" the object. In other words, it helps to locate the object by referring to its collection. This is useful in case two objects of different categories are being referred to. In a database, Microsoft Access allows two objects to have the same name, as long as they do not belong to the same category. For example, you cannot have two forms named Employees in the same database. In the same way, you cannot have two reports named Contracts in the same database. On the other hand, you can have a form named Employees and a report named Employees in the same database because each object belongs to a different collection. For this reason, when creating expressions, you should (strongly) qualify the object you are referring to, using its collection. An example would be Forms!Employees which means the Employees object of the Forms collection. If the name of the form is made of more than one word, or for convenience, you must use square brackets to delimit the name of the form. The form would be accessed with Forms![Employees]. To refer to a control placed on a form or report, you can type the Forms collection, followed by the ! operator, followed by the name of the form, followed by the ! operator and followed by the name of the control. An example would be Forms!People!LastName. Using the assignment operator that we introduced earlier, if on a form named People, you have a control named LastName and you want to assign its value to another control named FullName, in the Control Source property of the FullName field, you can enter one of the following expressions: =LastName =[LastName] =Controls!LastName =[Controls]![LastName] =Forms!People!LastName =[Forms]![People]![LastName]These expressions would produce the same result. The Parentheses Operator: () Parentheses are used in two main circumstances: in expressions (or operations) or in functions. The parentheses in an expression help to create sections. This regularly occurs when more than one operators are used in an operation. Consider the following operation: 8 + 3 * 5 The result of this operation depends on whether you want to add 8 to 3 then multiply the result by 5 or you want to multiply 3 by 5 and then add the result to 8. Parentheses allow you to specify which operation should be performed first in a multi-operator operation. In our example, if you want to add 8 to 3 first and use the result to multiply it by 5, you would write (8 + 3) * 5. This would produce 55. On the other hand, if you want to multiply 3 by 5 first then add the result to 8, you would write 8 + (3 * 5). This would produce 23. As you can see, results are different when parentheses are used on an operation that involves various operators. This concept is based on a theory called operator precedence. This theory manages which operation would execute before which one. Introduction to Functions Overview A function is a task that must be performed to produce a result on a table, a form, or a report. It is like an operation or an expression with the first difference that someone else created it and you can just use it. For example, instead of the addition operator "+", to add two values, you could use a function. In Microsoft Access, you cannot create a function. You can only use those that have been created and exist already. These are referred to as built-in functions.
If you had to create a function (remember that we cannot create a function in Microsoft Access; the following sections are only hypothetical but illustrative of the subject of a function), a formula you would use is: FunctionName() EndThis formula is very simplistic but indicates that the minimum piece of information a function needs is a name. The name allows you to refer to this function in other parts of the database. The name of the function is followed by parentheses. As stated already, a function is meant to perform a task. This task would be defined or described in the body of the function. In our simple syntax, the body of the function would start on the line under its name and would stop one the line just above the End word. The person who creates a function also decides what the function can do. Following our simple formula, if we wanted a function that can open Solitaire, it could appear as follows: FunctionExample() Open Solitaire EndOnce a function has been created, it can be used. Using a function is referred to as calling it. To call a simple function, you would just type its name. A function produces a result. This is also stated that a function returns a value. Based on this, the result of a function can be provided for further use and assigned (passed) to a field or to another function. To display the result of a function in a field, you can access the field's Control Source property, use the assignment operator "=", and type the name of the function followed by parentheses. This can be done as follows:
The person who creates a function also decides what kind of value the function can return. For example, if you create a function that performs a calculation, the function may return a number. If you create another function that combines a first name and a last name, you can make the function return text that represents a full name. Arguments and Parameters When asked to perform its task, a function may need one or more values to work with. If a function needs a value, such a value is called a parameter. The parameter is provided in the parentheses of the function. The formula used to create such a function would be: ReturnValue FunctionName(parameters) EndOnce again, the body of the function would be used to define what the function does. For example, if you were writing a function that multiplies its parameter by 12.58, it would appear almost as follows: Decimal FunctionName(Parameter) parameter * 12.58 EndWhile a certain function may need one parameter, another function would need many of them. The number and types of parameters of a function depend on its goal. When a function uses more than one parameter, a comma separates them in the parentheses. The formula used is: ReturnValue FunctionName(Parameter1, Parameter2, Parameter_n) EndIf you were creating a function that adds its two parameters, it may appear as follows: NaturalNumber AddTwoNumbers(Parameter1, Parameter2) Parameter1 + Parameter2 EndOnce a function has been created, it can be used in other parts of the database. Once again, using a function is referred to as calling it. If a function is taking one or more parameters, it is called differently than a function that does not take any parameter. We saw already how you could call a function that does not take any parameter and assign it to a field using its Control Source. If a function is taking one parameter, when calling it, you must provide a value for the parameter, otherwise the function would not work (when you display the form or report, Microsoft Access would display an error). When you call a function that takes a parameter, the parameter is called an argument. Therefore, when calling the function, we would say that the function takes one argument. In the same way, a function with more than one parameter must be called with its number of arguments. To call a function that takes an argument, type the name of the function followed by the opening parenthesis "(", followed by the value (or the field name) that will be the argument, followed by a closing parentheses ")". The argument you pass can be a constant number. Here is an example:
The value passed as argument can be the name of an existing field. The rule to respect is that, when Microsoft Access is asked to perform the task(s) for the function, the argument must provide, or be ready to provide, a valid value. As done with the argument-less function, when calling this type of function, you can assign it to a field by using the assignment operator in its Control Source property. If the function is taking more than one argument, to call it, type the values for the arguments, in the exact order indicated, separated from each other by a comma. As for the other functions, the calling can be assigned to a field in its Control Source. All the arguments can be constant values, all of them can be the names of fields or objects, or some arguments can be passed as constants and others as names of fields. Optional Arguments and Default Values We have mentioned that, when calling a function that takes an argument, you must supply a value for the argument. There is an exception. Depending on how the function was created, it may be configured to use its own value if you fail, forget, or choose not, to provide one. This is known as the default argument. Not all functions follow this rule and you would know either by checking the documentation of that function or through experience. If a function that takes one argument has a default value for it, then you don't have to supply a value when calling that function. Such an argument is considered optional. Whenever in doubt, you should provide a value for the argument. That way, you would not only be on the safe side but also you would know with certainty what value the function had to deal with. If a function takes more than one argument, some argument(s) may have default values while some others don't. The arguments that have default values can be used and you don't have to supply them. The Expression Builder Introduction To assist you with writing an expression or calling a (built-in) function and reducing mistakes, Microsoft Access is equipped with a dialog box named the Expression Builder. The Expression Builder is used to create an expression or call a function that would be used as the Control Source of a field. Using the Expression Builder To access the Expression Builder, open the Property Sheet for the control that will use the expression or function, and click its ellipsis button
The Expression Builder is resizable: you can enlarge, narrow, heighten, or shorten it, to a certain extent. Under the title bar, there is the Calculated Control link. If you click that link, a Help window would come up. Under the link, there is an example of an expression. The main area of the Expression Builder is a rectangular text box used to show the current expression. If you already know what you want, you can directly type an expression, a function, or a combination of those. To show a reduced height of the Expression Builder, click the << Less button. The button would change to More >>:
To show the whole dialog box, click More >>. Under the text box, there are three boxes. The left list displays some categories of items. Some items in the left list appear with a + button. To access an object, expand its node collection by double-clicking its corresponding button or clicking its + button. After you have e xpanded a node, a list would appear. In some cases, such as the Forms node, another list of categories may appear. To access an object of a collection, in the left list, click its node. This would fill the middle list with some items that depend on what was selected in the left list. Here is example:
The top node is the name of the form or report on which you are working. To access a function, first expand the Functions node. To use one of the Microsoft Access built-in functions, in the left list, click Built-In Functions. The middle list would display categories of functions. If you see the function you want to use, you can use it. If the right list is too long and you know the type of the function you are looking for, you can click its category in the middle list and locate it in the right list. Once you see the function you want in the right list, you can double-click it. If it is a parameter-less function, its name and parentheses would be added to the expression area:
If the function is configured to take arguments, its name and a placeholder for each argument would be added to the expression area:
You must then replace each placeholder with the appropriate value or expression. To assist you with functions, in its bottom section, the Expression Builder shows the syntax of the function, including its name and the name(s) of the argument(s). To get more information about a function, click its link in the bottom section of the Expression Builder. A help window would display. Here is an example: Depending on the object that was clicked in the left list, the middle list can display the Windows controls that are part of, or are positioned on, the form or report. For example, if you click the name of a form in the left list,the middle list would display the names of all the controls on that form. To use one of the controls on the object, you can double-click the item in the middle list. When you do, the name of the control would appear in the expression area. Some items in the middle list hold their own list of items. To show that list, you must click the item in the middle list. For example, to access the properties of a control positioned on a form, in the left list, expand the Forms node and expand All Forms:
Then, in the left list, click the name of a form. This would cause the middle list to display the controls of the selected form. To access the properties of the control, click its name in the middle list. The right list would show its properties: To get help while using the Expression Builder, you can click Help. After creating an expression, if you are satisfied with it, to submit it, click OK. To abandon whatever you have done, click Cancel or press Esc. The Calculated Field of a Table You can create a field in a table so that the field would hold an expression. To do this, start the table in either the Datasheet View or the Design View:
In both cases, the Expression Builder would come up. You can then type or create the desired expression in the top text box. Page 4
Introduction to Data Fields A Database From Scratch Creating a database from scratch consists of adding the necessary objects one at a time. To create a database from scratch, start Microsoft Access and click Blank Desktop Database.
Introduction to Tables A table is a technique to organize a list of values in categories. Here is an example:
The categories are organized vertically, as columns. They are also called fields. A field is an object used to host, hold, or store a piece of information of a database. The fields of a table are used to store data. The values in each set display horizontally, each value in a column. A set of values in a horizontal arrangement is called a record: The group of records of a table is called a record set. Table Layout The Table's Tab A table is a rectangular: As an option, you can remove the tabs. To do this, click File and click Options. In the left frame, click Current Database. In the right frame, click the Tabbed Documents radio button and remove the check mark on the Display Document Tabs check box:
After making the selection, you can click OK. You will be asked to close and reopen the database: Which you should do. On the left side of its top section, a table presents an icon The Table System Buttons If you want, you can make the tables of a database appear as single documents. To do this, open the Access Options dialog box from the File category. In the left frame, click Current Database. Under Document Window Options in the right frame, click the Overlapping Windows radio button:
When you click OK, you will be asked to close and reopen the database (which you should do).
A Table as a Datasheet A table is primarily an arranged list of columns and rows, each column and each row intersect to create a rectangular box called a cell: The cell is actually the object that holds data of a table. Introduction to Table Creation Overview To create a table, on the Ribbon, click Create. In the Tables section, click Table From the Fields tab, you can make selections.
The Name of a Table A table must have a name. There are two main ways you can specify the name of a table, either when you save it for the first time or if you decide to rename it. To save a table you have just created:
A table can have almost any name: Employees, 2&&4DG, Pestes, verTT#@tg, etc. There are suggestions you should follow:
Creating a Table in SQL As a computer language, the SQL is equipped to perform all basic and necessary operations of a database. As such, it can be used to create a table. In the SQL, to create a table, you start your statement with the CREATE TABLE expression followed by the desired name of the table as follows: CREATE TABLE table-nameTable Management The Tables in the Navigation Pane When you create a table and save it by giving it a name, Microsoft Access creates a section labeled Tables in the Navigation Pane and displays the name of thew new table in that section. In the same way, you can have as many tables as possible in the Tables section. Here is an example: You can create other object and they will have their sections. Each section is expandable and collapsible. To expand a section, click the header of that section. To the same to collapse a section. The Properties of a Table To let you get some information about a table, Microsoft Access provides a dialog box that allows you to know the name of the table you are accessing as well as the date and time when it was created or modified. To display the Table Properties dialog box, right-click the table in the Navigation Pane and click Table Properties. Here is an example:
Opening a Table To open a table in Datasheet View, first locate it in the Navigation Pane then:
Any of these actions causes the table to display in Datasheet View in the central area of the screen. When a table is displayinig in Datasheet View, in the Views section of the Ribbon, the View button displays the Design View button Closing a Table After using a table, you can close it. To close a table:
Selecting a Table In order to use a table, some operations require that you (or rather the user) first select(s) it:
Renaming a Table To rename a table, in the Navigation Pane, right-click the name of the table and click Rename.
Deleting a Table To remove a table from your database, in the Navigation Pane:
In each case, you will receive a warning to confirm. Therefore, before deleting a table, make sure you really want to get rid of it. When in doubt, do not delete it.
Introduction to the Columns of a Table Creating a Column in the Datasheet View A table does not exist without a column. If you start a table in Datasheet View, Microsoft Access creates and gives it one default column. Once a table displays in Datasheet View, you can start entering data into its cells. If you do this, whenever you enter data under a column, that column receives an incremental name: the first column on the right side of ID would be named Field1, the second would be Field2, and so on. You can give meaningful names to the columns of your table. Naming a Column A column can have almost any name. To name a column:
Any of these actions would put the name of the column into edit mode. You can then type the new desired name or change the existing name. Using a Sample Field When you start a table in the Datasheet View, the Ribbon becomes equipped with a new tab labeled Fields. The sample fields and their configurations are distributed in various sections. One of the sections of the Fields tab is named Add & Delete: Microsoft Access provides many pre-configured fields you can add to your table. These ready-made sample columns are referred to as a Field Templates. To use a field template, while the table is displaying in Datasheet View:
Introduction to the Fields of a Table in SQL As you have probably seen by now, every table must have at least one field (or column). In the SQL, the list of columns of a table starts with an opening parenthesis "(", ends with a closing parenthesis and an optional semi-colon ");". If the table will be made of more than one column, you can separate them with commas. The formula to follow is: CREATE TABLE table-name (column1, column2, column_n);To make the statement easier to read, and because some columns can be long, you can create each on its own line. The syntax would become: CREATE TABLE table-name ( column1, column2, column_n )To create a column, you specify its name, followed by its data type, and some possible options. Therefore, the syntax of creating a column is: column-name data-type, optionsThe name of a column can be in one or many words. If you put space after the first word, the database engine would treat the next word as a data type. For example, the following statement would produce an error: CREATE TABLE Employees (Last Name )If you want to use space in a column name, include it between an opening square bracket "[" and a closing square bracket "]". The above statement would be changed to: CREATE TABLE Employees ([Last Name] )After the name of the column, you must provide a data type. The most fundamental data type in the SQL is named Text (remember that the SQL is not case-sensitive; this means that TEXT, Text, and text are valid). Here is an example of creating a table with four text-based fields: CREATE TABLE Employees (EmployeeNumber TEXT ,FirstName Text ,LastName text ,HourlySalary TExt );To actually create the table, you must execute the code. Introduction to Records Data Entry Fundamentals A table's cell holds one particular unit of data and all cells on a range belong to the same record: Data Entry on a Table To perform data entry on a table, you can click a cell under a column header and type a value: In some cases, the field of the most left column is marked with (New): There are three kinds of fields or cells the user will face: a field in which the user can type data, a field that displays a list as a combo box the user has to select from, and a field that does not receive input from the user. After setting the data in a particular field, you can click another cell and type the desired data.
SQL and Data Entry In the SQL, data entry is performed using the INSERT INTO expression, followed by the table name, followed by the VALUES keyword, and followed by the values in parentheses. If the table is made of only one column, you can include the desired value in the parentheses. If the table is made of more than one column, you can separate the values with commas. The syntax to use is: INSERT INTO table-name VALUES(column1, column2, column_n)The table-name attribute must be the name of an existing table of the current database. If the name is wrong, the SQL would consider that the table you are referring to does not exist. Consequently, you would receive an error. The VALUES keyword indicates that you are ready to list the values of each field of the record. The values of the columns must be included in parentheses. In the above syntax, the value of each field of the column must be entered in the exact order of the columns as they were created in the table. If the data type of a column is a text type, include its value between either single or double quotes. Here is an example: When you execute the code, you will receive a message that you are about to add a record. Read it and click Yes. Columns Maintenance in the Datasheet View Selecting a Column Some operations will require that you select a column but it depends on the operation you want to perform. In some cases, when any cell under a column has focus (for example if the caret is blinking in a cell of a column), the column is considered to be selected. Otherwise:
Changing the Width of a Column To change the width of a column:
Best Fitting the header of a Column Instead of manually resizing a column, you can ask Microsoft Access to take care of that. To do that, right-click a column header and click Field Width. In the Column Width dialog box, click Best Fit. Moving a Column To move a column in Datasheet View, first select it. Click and hold your mouse on it. Then, start dragging left or right in the desired direction. While your mouse is moving, a thick vertical line will guide you. Once the vertical line is positioned to the desired location, release the mouse: To move a group of columns, first select them as we reviewed earlier. Click and hold the mouse on one of the selected columns. Start dragging left or right in the desired direction until the thick vertical guiding line is positioned in the desired location, then release the mouse: Inserting a Column To insert a column, right-click the column that will succeed it and click Insert Field
Renaming a Column To rename a column, you must put it into edit mode. To do this:
Once the name is in edit mode, type the desired name and press Enter.
The Visibility of a Column A column can be hidden from a table so the user would not see it. To hide a column, you can drag the right border of its column header completely to its left border. When the vertical guiding line reaches the left border, release the mouse: the column would be hidden from the table: To hide one or a group of columns, you can right-click and click Hide Fields. Revealing a Hidden Column You can right-click any column and click Unhide Fields. This would open the Unhide Columns dialog box. Here is an example: To hide a column, clear its check box. To reveal a column or a group of columns previously hidden, right-click any column header on the table and click Unhide Column. In the Unhide Columns dialog box, put a check mark on each column you want to show. Deleting a Column To remove a column from a table:
Any of these actions would present a warning to confirm whether you still want to delete the column(s). Fundamentals of Designing a Table Introduction Besides the Datasheet View, another way to create a table is by designing it. This is done in the Design View:
Mostly you, the database developer, have access to the Design View of a table. The user will hardly, if ever, use that view. A table in Design View is divided in two sections: one in the upper area and another in the bottom: To work on in a section, using the mouse, you can just click. To switch from one section to another, press F6. If a table is currently opened in Design View, you can change its view. To switch a table from Design View to Datasheet View:
The Property Sheet of a Table When designing a table, Microsoft Access provides a window that allows you to specify or manage some characteristics of a table. This is the role of the table Property Sheet: To display the Property Sheet of the table, right-click any part of the table in Design View and click Properties.
The Name of a Field As you may know already, a column must have a name. In the Design View:
Introduction to Data Types Overview A data type represents the kind of information that a particular field should, would, or must hold. Microsoft Access provides all the types or categories of values necessary for a database. Setting the Data Type of a Field To make your database efficient, in some circumstances, or depending on the project (or customer), you should exercise as much control as possible on data entry. This aspect is mostly controlled at two levels: tables and forms. We know that, when creating a table in Datasheet View, to create a column, you can click Click to Add, and select a field type from the list:
Type a name for the column. After creating a column, if you want to change it, on the Ribbon, click Fields. In the Formatting section, click the arrow of the Data Type combo box and select from the list. Besides the Datasheet View, you can use the Design View of a table to specify the type of value that a field can hold. To do this, after specifying the column name, click the arrow of the corresponding box in the Data Type column and select the desired type: Data Type Properties To further control how data is entered in the database, you can configure a field's characteristics. To control the characteristics of a field in the Datasheet View of a table, click any field under the column header. On the Ribbon, click Fields. Use the options in the Properties, the Formatting, and the Field Validation tabs: To control the properties of a table in the Design View, after selecting a data type in the Data Type column, in the lower section of the table, you can format or further configure the field. The lower part of the table Design View is made of two sections: the property pages on the lower left and the properties help section on the lower right: The kind of Data Type you set for a field in the upper section controls what displays in the lower section of the view. The General tab controls the features of the selected data type. The options in the General property page depend on the data type that was specified: Fields Management in Table Design View Renaming a Column To rename a field in Design View, click it and type the new name. Inserting a Column In a table's Design View, you can insert a new field at any position. You can also add a new field to the end of the table:
Moving Columns in the Design View of a Table The columns of a table can be moved to have a different sequence of fields in the Design View of a table. To do this, click the button on the left side of the column. Click that button again but hold your mouse down. Drag it up or down until the guiding horizontal line is positioned where you want:
Release the mouse Deleting a Column You can delete a column or a group of columns of a table in Design View. To get rid of a column, right-click the button on its left and click Delete Rows.
Table and Columns/Fields Maintenance in SQL Adding a Column The formula to add a column or field to a table is: ALTER TABLE table-name ADD COLUMN column-name optionsHere is an example: ALTER TABLE Employees ADD COLUMN EmploymentStatus textChanging a Data Type The formula to change a column is: ALTER TABLE table-name ALTER COLUMN column-name optionsHere is an example: ALTER TABLE Employees ALTER COLUMN EmploymentStatus IntegerDeleting a Column In SQL, to delete a column, the formula to follow is: ALTER TABLE table-name DROP COLUMN column-name;The ALTER TABLE and the DROP COLUMN expressions are required. The table-name is the name of the table that holds the column you want to delete. The column-name is the name of the column you want to remove from the table. Delete a Table In the SQL, to delete a table, use the DROP TABLE command. The formula to follow is: DROP TABLE table-name;The table-name parameter must be a valid name of a table of the current database. There are two significant issues with the DROP TABLE command: it does not warn you and it is not reversible. Hee is an example: DROP TABLE Customers;
|