From the web page
http://www.cs.uregina.ca/~pvh/edu/software/access/
Access 97 Tutorial
Microsoft Access 97 is a 32-bit relational database management
system (RDBMS). Access combines its database capabilities with
the ability to build a complete front-end user application. This
differs from Visual Basic and Powerbuilder as these programs
require additional programming to integrate database management
facilities in to the application.
Access duplicates in a PC environment what used to be using C
and a lot of SQL. Although you can still write your own SQL
statement, there is a windows driven query facility that makes
creating queries much easier. Such not to be limited to a PC
environment, Access also has the option of allowing you to
connect to any databases on client/server database like an
Oracle server.
Step Number Description
One Creating a New Database.
Two Creating a new table and entering values.
* List of Items on Screen
* Data Types
* The Market Database
* Close the Table
* Entering Values in the Table
Three Creating a new query.
* Creating Queries
* Selecting the Fields
* Running the Query
* Sorting the Results
* Showing a field
* Removing a field from the query
* SQL View
* Save the Query
* Closing the Query window
Four Specific criteria for a query
* Simply Query Criteria
* Dealing with Numbers
* Dealing with Text
Five Advanced criteria for a query
* OR Conditional Query
* AND Conditional Query
* Calculation Query
Six Creating Forms
* Creating the Form
* Form Screen
* Adding a Label Object
* Adding a Text (Control) Object
* Form View
* Moving in the Form View
* Saving the Form
Seven Modifying Objects on Forms
* Selecting Objects
* Resizing Objects
* Moving Objects
* Changing the Font and Alignment of an Object
* Changing the Colour of an Object
* Removing an Object
* Properties of an Object
Eight Categories Form
* Creating the Form
* Creating a Title for Form
* Creating a Combo Box for the Categories
* Removing Repeating Values from the Combo Box
Nine Categories Form (con't)
* Creating a List Box for the Categories
* Linking the selection from the Combo Box to the List Box
Ten Price and Quantity Form
* Creating the Form
* Creating a Title for the Form
* Placing Field from Products table on Form
* Adding "Quantity Received" Text Object
* Adding "Retail Price" Text Object
* Adding "Total Quantity" Text Object
Eleven Price and Quantity Form (con't)
* Initializing a Field
* Adding a "Save" button
* Adding a "Close" button
Twelve Linking the Forms Together
* Opening the Categories Form
* Adding the Price and Quantity Button
Created by Jason Armbruster under the direction of
Pauline Van Havere [log in to unmask]
----------
Step One - Creating a Database
When Access is started, the following screen appears:
Selecting Blank Database will create a new database in the
location that you specify and proceed to the Access main window.
Selecting Database Wizard will create a new database in the
location that you specify and will start the Wizard. The Wizard
is a step-by-step process that assists you in creating a new
database. You pick different items from lists that Access will
present to you. After selecting all the items, Access goes and
automatically generates the tables, forms, etc necessary for the
database.
Selecting Open an Existing Database allow you to open a database
that has been already created. The bottom part of the window
will list the most recent databases that have been used. To open
one, double click the file name or click the file name once and
click the OK button. If the file is not in the list, double
click "More Files . . ." and the Open dialog window will open
allowing you to switch to appropriate drive and directory where
the database is stored.
We are going to select Blank Database and click the OK button.
The Open dialog window appears:
We are going to create this on the H: drive.
* In the Save in: box, click the down arrow and select the
entry where the line ends in "(H:)".
* For the File Name box, highlight the entry (db1.mdb) and
type in "market".
(you can leave the .mdb extension off and Access will add it
for you automatically)
* Click the OK button.
The database has now been created in the H: drive. The main
window for Access should now be showing.
Tip: Even though you have not done anything with your
database yet, a new file has been created. This means if you
decided that you did not want to continue with this database,
you should go in to the Windows Explorer and delete the
market.mdb file.
----------
Step Two - Creating a Table
Now that we have created the database, we want to create a table
to put information in to.
To create a new table, click the Table tab on the main window
and click the New button .
The New Table window will appear:
Here is an explanation of the five options listed.
Option Description
Datasheet View Places you in the datasheet view with
predefined fields. This method allows for direct data entry in
to the table.
Design View Allows you to create the fields necessary,
allowing for different options to be set (name, data type, etc).
Table Wizard Leads you through laid out steps that assist you
to develop the new table.
Import Table Import information in to the database and sets
up the database according to the fields in the imported file.
Link Table Link to a table that exists in a different
database.
We are going to use the Design View. Click the Design View in
the list and click the OK button.
The following Design View screen will appear:
Each row in the screen represents a field that has a field name,
data type and optional description.
List of Items on the Screen
Item Description
Field Name Name of the field. Can have spaces.
Data Type Type of data that is to be stored. Can be things
like text, numbers, data/time, memo, etc.
Description A description of the field. This is optional and
will show in the status bar when the user is in that field on a
form or in the datasheet view.
Primary Key Sets the field to be a primary key (ensures that
no two records contain the same value for that field).
Field Size Allows you to set the size of the field (i.e.
limit text entries to 10 characters).
Format Format in which to display the values for the field to
the users. (i.e. show a postal code with the space in the
middle).
Decimal Places Set the number of decimal places to show for a
numeric field.
Input Mask Restrict the data that the user can enter. Used to
ensure correct data entry. (i.e. for a postal code, you want to
make sure that the first character entered is a letter and not a
number.
Caption If you want a different name to appear for the column
header in the datasheet view or the label in form view. (i.e.
originally call field "emp#" but you want the labels to say
"Employee Number"
Default Value A value to be entered in to the table if the
user does not enter one.
Validation Rule Can be used to validate the information the
user enters. (i.e. make sure the value is always less than 100)
Validation Text Allows you to set the message that appears if
the validation rule fails. If nothing is entered for this value,
Access will display a default message.
Required If turned on, the user must enter something in this
field in order to save the record.
Data Types
Most of the items above are optional. If you enter nothing in
them, your table will still funtion properly. Something that can
not be avoided is selecting a data type. This distinguishes the
type of information to be stored in the table.
Item Description
Text A character string with a maximum length of 255
characters.
Memo A character string with a maximum length of 64,000
characters. Usually used for descriptions of items (i.e.
describing a truck would probably require more than 255
characters). A memo field cannot be used as a primary key.
Number Various types of numbers. There are many different
subtypes to select from in Field Size. They are Double (15
places, 8 bytes), Single (7 places, 4 bytes), Long Integer (-2.1
to +2.1 billion, 4 bytes, no decimals), Integer (-32 to +32
thousand, 2 bytes, no decimals) and Byte (values 0 to 255, 1
byte, no decimals).
AutoNumber Access will automatically generate a number for
this field. Usually used as a unique identifier for primary key
purposes.
Yes/No Used to store logical results from True or False
results. Be careful as -1 stands for Yes and 0 stands for No.
Date/Time Used for storing dates and times. The format can be
changed in the Format box.
Currency Used for storing numbers (not necessarily in a
dollar format). Can hold 15 places to the left of the decimal
and four places to the right. Takes up 4 bytes.
OLE Object Used for OLE compliant applications (i.e. linking
a portion of a spreadsheet to the table)
Hyperlink Used for storing web addresses.
The Market Database
Now that we looked at the main items in the Design View window,
let's create a few of our own entries. The example we are going
to use is Farmer Joe's Market.
In the table below is a list of the different fields we are
going to create for our table. To create a new field, move to
the Field Name in a blank row and type the field name . Even
though you can have spaces in the name, we will not include any
in our field names.
Field Name Description Data Type Other Information
ProductName Name of the product. Text Field Size of 30
Category Category product falls in. Text Field Size of
20
Distributor Company where we buy the product. Text
Field Size of 50
CostPrice Price we buy product for. Currency
MarkUp Amount we increase the price from cost. Number
Field Size of Single
QuantityInStock Number of units in stock. Number Field
Size of Single
We want the Product Name to be our primary key as we will never
have a product with the exact same name as another. To set the
Product Name as the primary key:
* Place the mouse on the selection box beside Product Name
(looks like a gray stub) and the mouse will change to a .
When it changes, click the mouse button.
* Click the Primary Key button.
Now that we have all the fields entered in the table, we have to
save the table. To save the table:
* Click the or select File, Save from the menu.
* Type following screen appears. Type in Products in the Table
Name box.
Click the OK button.
Close the Table
To close the table, click the button or select File, Close from
the menu.
Entering Values in Table
In the Access main window, you should see an entry called
"Products" under the Table section. To enter values in the
table, click the table name (Products) and click the Open
button. The datasheet view will open. It looks like a
spreadsheet-like format.
Each row represents one record and each column represents the
specific field. Here is a list of keys for entering values:
Key Description
Tab Moves to the next field to the right. If you are in the
last field for the record, when the Tab key is pressed, it will
move you to the next record below.
Shift - Tab Moves to the next field to the left. If you are
in the first field for the record, it will move you to the next
record above the current record.
Arrow Keys Moves in the direction of the key pressed.
Enter Moves to the next field to the right. If you are in the
last field for the record, it will move you to the next record
below the current record.
Whenever you leave the current record (the row you are one), the
record will be saved. Ex. If you are in the fourth field and
make a change, if you hit the Tab key, it will move to the next
field to the right, but does not save the record. If you press
the Up or Down arrow key, this will move to a different record
and the changes to the current record will be saved.
Here is a list the entries to enter:
ProductName
Category
Distributor
CostPrice
MarkUp
QuantityInStock
Apples
Fruit
Eatery Fruits
$0.25
0.33
65
Coke
Pop
Coca-Cola Ltd.
$0.93
0.15
64
Dad's Oatmeal
Cookies
Dad's Cookies Ltd.
$3.47
0.3
22
Diet Coke
Pop
Coca-Cola Ltd.
$0.93
0.15
24
Diet Pepsi
Pop
PepsiCo Ltd.
$0.88
0.15
33
Double Fudge
Cookies
Nabisco Ltd.
$2.57
0.22
15
Fruit Loops
Cereal
Kellogs Ltd.
$3.58
0.25
40
HoneyCombs
Cereal
Post Ltd.
$3.14
0.23
33
Oranges
Fuit
Eatery Fruits
$0.30
0.25
40
Oreo
Cookies
Christie Ltd.
$2.30
0.18
20
Pepsi
Pop
PepsiCo Ltd.
$0.87
0.15
79
Rice Kripsies
Cereal
Kellogs Ltd.
$3.97
0.17
45
To close the table, click the button or select File, Close from
the menu.
----------
Step Three - Creating a Query
Queries allow you to be selective about the information to be
retrieved. For example, maybe we only want to see all the
products in the Pop category that have a cost price less than
.80.
Access has many types of queries you can run, but we will
concentrate on the Select1 Query. The big advantage Access has
is that you build queries through a graphical interface. Before
when information was to be retrieved from the database, the user
had to type in a big line SQL code to get the information.
Access avoids this method by allowing you to basically "click
and drag" the fields you want to report on. After you select all
the field necessary for the query, Access builds the SQL in the
background, sends it to the database and returns the result in a
datasheet format.
Creating the Query
To create a new query, click the Query tab on the main window
and click the New button .
The New Query window will appear:
We are going to use the Design View. Click the Design View in
the list and click the OK button.
Now that have pick the type of query we want, we have to select
which tables are going to be a part of the query. The Show Table
window should be showing:
This screen lists the possible tables and queries (yup, you can
base a query on a saved query) to select from to build the
query. We only have the one table. Click the Products name in
the Table Tab area and click the Add button. This adds the table
to the query. Click the Close button.
You should now see the Products table in the Select Query window.
Selecting the Fields
We now have to choose which fields we want in the query. There
are two ways:
* Drop-down list box
In the first blank column, click once in the Field row. If it
now already showing, a small down arrow will appear by to the
right of the white box. Click the down arrow. This will list the
fields that can be selected.
Click ProductName in the list.
* Click and drag from Table box
In the Select Query, you can see the Products table (let's call
it the Product table box). In this box, it lists all the fields
in the Product table that can be selected for the query. To
select the field, click and hold the left mouse button on the
Category field in the Product table box. While holding the mouse
button down, drag the mouse on top of a blank column and release
the mouse button.
You should now have the ProductName and Category fields selected
in the Field row of the two columns.
Running the Query
Even though we have not entered any criteria (restrictions like
only the Pop entries), we can execute the query. To run the
query, click the Run button or select Query, Run from the menu.
The following screen should appear:
This is called a dynaset. It is a temporary table that is
created and contains the values from your query. Be careful
though as any changes performed to the data will be reflected
back in the Products table. Why would you want to do a change
then? For example, let's say we know we made a typing error on
the ProductName for one of the products in the Pop category. We
could write a query to look for just the Pop category and scan
through the dynaset for the ProductName to correct. This would
be much easier than looking through hundreds of entries in the
main table.
To return to the Design window, click the Design button or
select View, Design View from the menu.
Sorting the Results
The results can be sorted in Ascending (A to Z) or Descending (Z
to A) order.
To sort the ProductName in Ascending order, click the white Sort
box in the ProductName column. A small down arrow will appear to
the right of the box. Click the down arrow and select Ascending
from the list.
To remove the sorting, click the down arrow in the Sort box and
select (not sorted).
Showing a Field
By default, when you select a field to have in the query, it
shows in the dynaset when the query is run. But, there may be an
instance where you may want a field in your query but not to
show up in the query. For example, let us say that someone wants
a list of products that cost us more than $1 but they do not
need to know the prices for the product (they just care about
the products that are more than $1). In this case, you would
include the ProductName and CostPrice, with CostPrice having a
criteria of greater than one. If you click the Show box under
the CostPrice column, this would prevent the price from being
displayed in the dynaset, but you would only get the products
that are more than one dollar showing because of the criteria
entered for CostPrice.
Removing a field from the query
Sometimes you may have no need for a field you selected. To
remove the Category field, use one of the following:
* Move the mouse pointer to the top of the Category column.
The mouse pointer will turn to a . When it switches to the
down arrow, click the mouse button once and the column will
become highlighted. Press the Delete key on the keyboard or
click the Cut button on the toolbar.
* Click once anywhere in the Category column and select Edit,
Delete Column from the menu.
SQL View
As mentioned previously, the main advantage that Access has is
the graphical user interface for creating queries. The SQL code
for the query is automatically generated in the background.
If you want to see the SQL code that is generated by the query,
select View, SQL View from the menu. This will show you the SQL
code. You can make modifications in this window if you know SQL.
BUT, as soon as you make any changes, you can not go back to the
Design View.
To return to the Design View, select View, Design View from the
menu.
Save the Query
To save the query:
* Click the Save button or select File, Save from the menu.
* The Save As window appears:
Type in "List of Products".
* Click the OK button.
Closing the Query window
To close the query window, click the button or select File,
Close from the menu.
----------
Step Four - Specific Criteria for a Query
The main purpose of queries is to see the a specific subset of
data from a table. If we did not enter in any criteria, would
have something that is the same as the datasheet view for the
tables.
Simple Criteria Query
We are going to create a new query.
* Click the Query tab in the main window (if not already
there) and click the new button.
* Click Design view and click the OK button.
* Click the Product table entry.
* Click the Add button.
* Click the Close button.
* In the first column, click the Field box and then click the
down arrow. Select ProductName from the list.
* In the second column, click the Field box and then click the
down arrow. Select CostPrice from the list.
* In the third column, click the Field box and then click the
down arrow. Select Category from the list.
This gives us the fields we want to use for the query. Now we
are going to enter some criteria so that we only get a specific
subset of information returned in the query.
Let's say we only want the entries in the "Pop" category.
* In the Criteria box in the Category column, type in "Pop"
(without the quotations and not case sensitive).
* Press the down arrow key on the keyboard.
See what happen to the entry. Access automatically puts
quotations around the phrase because it knows that it is a text
string. So, you have the option of leaving them on or you can
type them in yourself.
Run the query by clicking the Run button or select Query, Run
from the menu.
You should see the following results:
To return to the Design window, click the Design button or
select View, Design View from the menu.
Dealing with Numbers
There are many different ways the we can deal with numbers. The
most commons ones are when we are looking for values less than
something or values that are greater than something.
Let's look for all products that have a CostPrice less than 90
cents.
* Once in the Design View, delete the "Pop" criteria for
Category by highlighting the Criteria box and pressing the
Delete key on the keyboard.
* In the the Criteria box in the CostPrice column, enter in "<
.9".
* Run the query by clicking the Run button or select Query,
Run from the menu.
On the status bar of the dynaset window, it will say how many
records fit the criteria. There are 12 records in our table, but
from this query we just ran, we only see 8, so we know the query
worked. Also, if you quickly scan down the CostPrice category,
you should notice that all the values are greater than 90 cents.
To return to the Design window, click the Design button or
select View, Design View from the menu.
Let's try another one. Now we are looking for all the products
that cost between 2 and 3 dollars.
* Once in the Design View, delete the "<.9" criteria for
CostPRice by highlighting the Criteria box and pressing the
Delete key on the keyboard.
* In the the Criteria box in the CostPrice column, enter in
"between 2 and 3". (yup, that's right, you do enter in the
word "between")
* Run the query by clicking the Run button or select Query,
Run from the menu.
You should only have two entries appearing:
Dealing with Text
We already dealt with handling a simple text query in the first
example. This query works well if you are looking for exact
values. What if we want to looking for a string inside the text
(i.e. all entries that have the letter 'o' in them).
In these instances, we use the wildcard option. This uses a
asterick. What this does is looks for zero or many characters in
substitute Let's look for all entries that end with the letter
'e'. For this, we wan to look at all entries where there are no
characters before or many characters before the 'e' on the end.
* Once in the Design View, delete the "Pop" criteria for
Category by highlighting the Criteria box and pressing the
Delete key on the keyboard.
* In the the Criteria box in the ProductName column, enter in
"*e" (without quotations).
* Press the down arrow key on the keyboard. The "*e" turns to
"Like *e". Access does this for you automatically. It
converts it into the way that it needs to run the query.
* Run the query by clicking the Run button or select Query,
Run from the menu.
You should get three rows returned in the dynaset:
To return to the Design window, click the Design button or
select View, Design View from the menu.
Now let's look for entries where there is the letter 'o'
anywhere in the text.
* Once in the Design View, delete the "ProductName" criteria
for Category by highlighting the Criteria box and pressing
the Delete key on the keyboard.
* In the the Criteria box in the ProductName column, enter in
"*o*" (without quotations).
* Run the query by clicking the Run button or select Query,
Run from the menu.
To return to the Design window, click the Design button or
select View, Design View from the menu. Once in the Design View,
delete the "ProductName" criteria for Category by highlighting
the Criteria box and pressing the Delete key on the keyboard.
If you want to quit and take a break, close Access by clicking
the button or select File, Exit from the menu. When Access asks
you to save the query, click the No button.
----------
Step Five - Advanced Criteria for a Query
We will now look at more advanced queries using and / or
conditions. We will also look at Calculation Queries.
If you are coming back from a break, create a new query with the
ProductName, CostPrice and Category fields.
OR Conditional Query
The OR condition allows us to select different sets of criteria
for the same field. For example, we are looking for all products
that have the letter 'b' or the letter 'k' anywhere in the text.
* If you haven't done so already, delete any criteria that is
there already.
* In the the Criteria box in the ProductName column, enter in
"*b* or *k*" (without quotations).
* Press the down arrow key on the keyboard. The phrase turns
to "Like "*b*" Or Like "*k*"". Access does this for you
automatically. It converts it into the way that it needs to
run the query.
* Run the query by clicking the Run button or select Query,
Run from the menu.
You should have the following results:
AND Conditional Query
The AND condition allows us to set the criteria for the query
based on multiple fields. For example, we are looking for all
products that are in the Cookies category and more than $3.00.
* If you haven't done so already, delete any criteria that is
there already.
* In the the Criteria box in the Category column, enter in
"cookies" (without quotations).
* In the the Criteria box in the CostPrice column, enter in ">
3" (without quotations).
* Run the query by clicking the Run button or select Query,
Run from the menu.
You should have the following results:
To return to the Design window, click the Design button or
select View, Design View from the menu.
We can also have an AND within the criteria for one field. For
example, we want all the products that have the letter 'o' and
the letter 'e' in them. So, in order for them to show up in the
the dynaset they have to have BOTH criteria to qualify.
* If you haven't done so already, delete any criteria that is
there already.
* In the the Criteria box in the ProductName column, enter in
"*e* and *o*" (without quotations).
* Run the query by clicking the Run button or select Query,
Run from the menu.
You should have the following results:
To return to the Design window, click the Design button or
select View, Design View from the menu.
Calculation Query
A calculation query allows us to perform arithmetic and string
calculations on the field in the query. Now why would you want
to do this? Well, sometimes there is no need to store a fields
in a table as it can be calculated by other fields that are
stored in the table. For example, in a Wages table for
employees, there is a field "HoursWorked" and another called
"WageRate". Every month we need to get what the Gross Amount
will be for their paycheque. It would make no sense to create a
new field in the table design for Gross Amount as we already
have the two fields that make up that field value. So in order
to save space in the database, we would not create a new field
in the table design, but we would do a calculation query to
calculated the amount of Gross Amount by multiplying HoursWorked
by WageRate. Eliminating these unnecessary fields is called
normalization.
Let's do an example. We will calculate the amount of tax on our
products.
* If you haven't done so already, delete any criteria that is
there already.
* In one of the blank columns to the right, click once in the
Field box.
* This is optional. I find it to be quite a pain to type a lot
information in that tiny box. While the blinking cursor is
in the Field box, move the mouse pointer on top of the Field
box and click the RIGHT mouse button. A pop-up menu will
appear. Select Zoom from the list. The Zoom window will
appear. This just makes it easier to type information in as
the area and the font is larger.
* Type in the following (an explanation will follow):
TotalCost:[CostPrice] * 1.14
* Click the OK button. The field box should look like this
now:
* Run the query by clicking the Run button or select Query,
Run from the menu.
* To return to the Design window, click the Design button or
select View, Design View from the menu.
Now, what does this mean.
Item Description
[Total Cost] This is the name of the expression. This is what
will appear as the column title in the dynaset.
: This signifies the end of the expression name and the
beginning of the calculation.
[CostPrice] This is the field from the table we want to
select. Important items here:
* Name has to be in square brackets. This signifies that the
value is from a table.
* Name has to be spelled the exact same was as it is in the
Table design. (if it is not, when the query is run, it will
pop up a screen asking for value for the Field Name typed
in).
* Arithmetic operator.
1.14 Static value that can be typed in.
To close the query window, click the button or select File,
Close from the menu. Click the No button when it asks to save
query.
----------
Step Six - Creating Forms
There are two ways to enter information in to the database.
There is the Datasheet view in the Table area. With this option,
you can see several records at one time, depending on the size
of the screen. The other option is using a Form. Forms allow
users to enter and view one record at a time. This is usually
less confusing for the user as there is not as much clutter on
the screen to confuse them. The forms can be customized by
changing everything from the type of font to the colors used.
Creating the Form
To create a new form, click the Forms tab in the main window and
click the New button .
The New Form window will appear:
We are going to use the Design View. Click the Design View in
the list. Click the down arrow and select "Products" from the
list. Click the OK button .
Form Screen
Here is the display of the Form Screen:
The gray background area is the work area where objects will be
placed. The toolbar below the menu shows list of different
options to select from. The Toolbox is a display of the
different types of objects that can be placed on the background.
Adding a Label Object
A label object is the equivalent to a title. It a static object,
so it does not change as you go from one record to the next.
Since we are dealing with Farmer Joe's Market, let's add in a
title for it on our form.
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once near the top of the form. A blinking cursor will
appear.
* Type in "Farmer Joe's Market"
* Press the Return key or click once anywhere else on the
form.
We now have a label that looks like this:
Adding a Text (Control) Object
A Text Object will be the one that is most commonly used. These
types of fields are the ones that change depending on the which
record you are on. They can handle data from the table itself or
can contain calculations in them. There a two different ways we
can use Text Objects.
The Toolbox Button
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once below the "Farmer Joe's Title".
* The following show appear:
* Take the mouse pointer and place it on top of the white
'Unbound' box. When the pointer changes to click the left
mouse button once.
* Type in "ProductName" and press the Enter key on the
keyboard.
The white box represents the information that is stored in that
field in the table. Later when View this, this field will show
us all the different products we have as we go from one record
to the next. Using this method, you have to manually modify both
the Text Object and the Label Object (this is the one that says
"Text1"). You can modify the Label Objects by following steps 4
and 5 above.
The Field List box
* If the Field List box is not showing, click the Field List
box button on the toolbar.
* The Field List box should be showing and list the fields in
the Products table:
* Move the mouse pointer on top of ProductName in the Field
List box.
* Click and hold the mouse button on ProductName. While
holding the button down, drag the mouse pointer below the
other field that we created.
* Release the mouse button.
Using this method, the Text Object and Label Object are filled
in automatically.
The Label Object is filled in with whatever the field name is
unless the Caption option for that field in the Table Design was
changed.
Form View
So far we have been working in the Design View, which really
does not show a nice impression of the work you have been doing.
The Form View shows the actual information that is stored in the
tables.
To switch to the form view, click the View button on the far
left of the toolbar.
The Form View for the form should be displayed.
Moving in the Form View
Here is a list of the different ways to move around in the Form
View.
Item Description
First record in the table.
Previous record in the table.
Next record in the table.
Last record in the table.
First record in the table.
Ctrl - Home First record in the table.
Ctrl - End Last record in the table.
Page Up Previous record in the table.
Page Down Next record in the table.
Tab Go to the next field to the right.
Shift - Tab Go to the next field to the left.
Arrow keys Goes to the field in which ever direction you
press. If you are in the last field on form, by pressing the
down or right arrow will go to next record.
Home First field on the form.
End Last field on the form.
To return to the Design window, click the Design button or
select View, Design View from the menu.
Saving the Form
* Click the Save button or select File, Save from the menu.
* The Save As window appears:
Type in "ProductList".
* Click the OK button.
----------
Step Seven - Modifying Objects on Forms
Now that we know how to put objects on the form, we will look at
how you can modify them in different ways so they give the form
a nicer appearance. Things to look at: selecting objects,
resizing, moving, changing fonts, changing colors and removing
objects.
Selecting Objects
You probably already know by now how to select an object. You
click on it once and small resizing squares will appear on the
edges and corner of the object.
After selecting an object, then you can perform what ever action
on it. But, what if you want to change a lot of fields at once
(i.e. change the fonts of all the fields to Arial) this method
would become quite cumbersome.
To select multiple fields at one time, hold the SHIFT key down
on the keyboard as you click the fields you want to select. If
you select a field that you did not mean to, just click the
field one more time while holding the shift key down.
Resizing Objects
There will be instances where the size of the object will not be
big enough. This could be where the product name is longer than
the object box or changing the font size to something larger
than the box is currently sized for.
To resize an object:
* Select the object (click ProductName).
* After selecting the object, there should be a series of
boxes on the sides and corners of the object. These are
called Resizing Boxes. Place the mouse on one of the boxes.
* The mouse pointer will change to a double headed arrow. Once
it changes, click and hold the moues button down.
* While holding the mouse button down, drag the mouse in the
direction the object is to be resized. When doing this a
faint gray outline will follow the mouse. This indicates how
big the object will be.
* Release the mouse button when the gray outline is the size
you need.
Even the gray workarea can be resized:
* Move the mouse pointer on top of the edge of the gray
workarea.
* The mouse pointer will change to . Once it changes, click
and hold the mouse button down.
* While holding the mouse button down, drag the mouse in the
direction the area is to be resized. When doing this a faint
gray outline will follow the mouse. This indicates how big
the object will be.
* Release the mouse button when the gray outline is the size
you need.
Moving Objects
There are going to be times where you want to move the objects
around to get a better look.
To move a Label Object:
* Select the "Farmer Joe's Market" title.
* Move the mouse pointer anywhere on top of the box until the
mouse pointer turns into a hand pointer.
* Once the mouse pointer changes, click and hold the mouse
button.
* While holding the mouse button down, drag the mouse in the
direction the object is to be moved. As you drag the mouse
around, a rectangle will follow the mouse. Position this
rectangle where the object is to be moved.
* Release the mouse button.
For a Text Object, the process is a little different. If you
follow the procedure above, both the Text and Label objects will
move at the same time.
If this is what you are looking for, then great!! But many
times, you are looking to move the Text Object a little closer
to the Label Object. Let's move the ProductName closer to its
Label Object.
* Select the ProductName object. After doing this, you will
see the normal resizing boxes as mentioned previously. But,
if you look in the upper left corner of the objects, you
should see larger boxes. We will use these boxes to move the
objects independent of one another.
* Move the mouse pointer on top of the big box for the Text
Object until the mouse pointer turns into a pointed finger
pointer.
* Once the mouse pointer changes, click and hold the mouse
button.
* While holding the mouse button down, drag the mouse in the
direction the object is to be moved. As you drag the mouse
around, a rectangle will follow the mouse. Position this
rectangle where the object is to be moved.
* Release the mouse button.
Changing the Font and Alignment of an Object
Access comes with a wide variety of fonts, it will be more than
likely that you do not want the same boring font for everything.
To change the font of an object, select the object and use one
of the options on the toolbar.
Here is a list of some of the different options:
Item Description
Type of font (Arial, Times New Roman, etc)
Size of the font.
Make the font bolded.
Make the font italicized.
Make the font underlined.
Make the font left aligned so text lines up on the left side of
the object.
Make the font center aligned so text is centered within object.
Make the font right aligned so text lines up on the right side
of the object.
Changing the Colour of an Object
There is a wide assortment of colours to select from for the
objects.
The process is similar to that of changing the font.
* Select the object.
* Click the down arrow for the Font Colour or the Background
Colour.
* Click the colour you want.
Removing an Object
To delete an object:
* Select the object.
* Press the Delete key on the keyboard or click the Cut
button.
Properties of an Object
The Properties window lists all the specific details about an
object. Items like the name of the object, colours used, font,
size, etc. All the things we have been talking about (changing
fonts or colours) can all be done through this Properties
window, but as you can see, it is much easier just to use
options on the toolbar.
To see the Properties for an object:
* Select the object (ProductName).
* Click the Properties button on the toolbar.
Here is what the Properties window looks like:
If you want to see the Properties window for another object,
just click the object and the Properties window will
automatically change (you do not have to click the Properties
button again if the Properties window is showing).
To close the form window, click the button or select File, Close
from the menu. Click the Yes button when it asks to save the
form.
This would be a good place to take a break if you are looking
for one. To close Access, click the button in the upper right
corner or select File, Exit from the menu.
----------
Step Eight - Categories Form
We are going to create a new form that will allow us to select a
category and show us all the products that are in that category.
Here is a general layout of what will be done:
* Place a Farmer Joe's Market title at the top of the form.
* Create a Combo Box (drop-down box) to show a list of the
different categories to select from.
* Create a List Box to show which products are in the category
we selected in the Combo box.
Creating the Form
To create a new form, click the Forms tab in the main window and
click the New button .
We are going to use the Design View.
* Click the Design View in the list.
* Click the down arrow and select "Products" from the list.
* Click the OK button.
Let's save the form right away.
* Click the Save button or select File, Save from the menu.
* The Save As window appears. Type in "Categories".
* Click the OK button.
Creating a Title for Form
We are going to create a title for our form that says "Farmer
Joe's Market". Then we will change the font to Arial, change the
size to 14 and underline the title.
Creating Title
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once near the top of the form. A blinking cursor will
appear.
* Type in "Farmer Joe's Market"
* Press the Return key or click once anywhere else on the
form.
Changing the Font
* If the Label Object is not selected (does not have the
resizing boxes), click it once to select it.
* Click the down arrow on the Font Type box on the toolbar.
* Scroll through the list and click Arial.
Changing the Size
* If the Label Object is not selected (does not have the
resizing boxes), click it once to select it.
* Click the down arrow on the Font Size box on the toolbar.
* Scroll through the list and click 14.
After changing the size of the title, you will have to resize
the box surrounding the title.
Underlining the Title
* If the Label Object is not selected (does not have the
resizing boxes), click it once to select it.
* Click the Underline button on the toolbar.
Remember, if you do not like where the Label Object is
positioned, you can move it to a more suitable position.
Have a look at what we've done so far. To switch to the form
view, click the View button on the far left of the toolbar. To
return to the Design window, click the Design button or select
View, Design View from the menu.
Save the Form
Click the Save button or select File, Save from the menu.
Notice that you did not have to type in a name for the form
again. Access knows the name of the form from when we previously
saved it, so it automatically knows where to save the new
information.
Creating a Combo Box for the Categories
We are now going to create a Combo Box (drop-down list box) to
show the different categories to select from. This section will
look at creating the Combo Box and changing the query the Combo
Box is based on.
Creating the Combo Box
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once under the title.
Using the Combo Box Wizard
Once you clicked the mouse, the Combo Box Wizard begins. The
Wizard is a step by step, hand help process to completing a
process. What happens is the Wizard will ask a question, you
answer the question and click the Next button. You go through
this process until the Wizard has collected all the information
necessary. You can usually tell you are at the end of the Wizard
when a finished checkered flag appears. Once the Wizard gets all
the information, it automatically goes and creates the object
for you. Sounds like a lengthy process, but in the end, it saves
you a lot of time.
* 1. We want to get the categories from the Products table.
Click "I want the combo box to look up the values in a table
or query" and click the Next button.
* A combo box can display information from a table or query
that you have previously developed. We want the categories
from the Products table. In the View area, click the Tables
option. Click the Products table in the list and click the
Next button.
* The next step lists all the fields that are in the table or
query that you can select from. Click the Category field on
the left and click the button. This will put the field on
the right side in the Selected Fields area. Any fields in
this area are displayed in the Combo Box. Click the Next
button.
* The next step does not really apply to us. This is for the
instance where you have multiple fields in the Combo Box. It
allows you to resize the columns. Click the Next button.
* In this step, you have the option of where to store the
information that the user selects from the Combo Box. If you
were using this form to retrieve information for the table,
you could "Store that value in this field:" and select a
field to store the item they pick from the Combo Box. We
will not be storing the value. Click "Remember the value for
later use." and click the Next button.
* This is the last step in the Wizard as indicated by the
finish flag. This is for contents the Label Object that is
to appear to the right of the actual Combo Box. It is a
title that lists what the Combo Box is for. Type in "Select
a category:" and click the Finish button.
You should now see two new objects in front of you. One is the
Label Object and the other is the Combo Box. The objects may
need to be resized and moved.
Have a look at what we've done so far. To switch to the form
view, click the View button on the far left of the toolbar. Try
it out! Click the down arrow and look at the selections.
You probably noticed that we have repeating values. We will look
at how we remove the repeating entries in the next section.
To return to the Design window, click the Design button or
select View, Design View from the menu.
Removing Repeating Values from the Combo Box
When we Viewed our Combo box, we saw repeating values (four
Pop's, three Cookie's, etc). In our case, we do not want these
repeating values.
* Select the Combo Box.
* If the Properties box is not showing already, click the
Properties button on the toolbar.
* In the Properties window, in the Name box, highlight and
delete what is in there. Type in "CategorySelected".
This gives the object a better name for us to reference than
Combo4.
* Click once on the Row Source box.
This box contains the query that populates the Combo Box.
* Click the button. This will take you in to the SQL Builder.
* By default, the Combo Box Wizard always includes the Primary
Key field as part of the query to populate the Combo Box. We
will not use this value so let's remove it. Highlight the
ProductName column and press the Delete key on the keyboard.
* Select View, Properties from the menu.
* Click once in the Unique Values box. Click the down arrow
and select Yes from the list.
This will eliminate the repeating values.
* Close the SQL Builder window, click the button. Click the
Yes button when it asks to save the changes made in the SQL
Builder.
This will return you to the Form Design window.
* Back in the CategorySelected Combo Box Properties window,
change the Column Count to 1.
The reason is was two was because the Wizard originally
selected the Categories and ProductName fields.
* In the Column Width, change the value to 1.
Now, let's look and see how things went. To switch to the form
view, click the View button on the far left of the toolbar.
Click the down arrow and look at the selections.
The repeating values have been removed and we only see one of
everything.
To return to the Design window, click the Design button or
select View, Design View from the menu.
Click the Save button or select File, Save from the menu.
----------
Step Nine - Categories Form (con't)
We are going to continue to build our Categories Form.
Now it is time to create the List Box to show which products are
in the category we selected in the Combo box.
Creating a List Box for the Categories
We are now going to create a List Box to show the different
products and their distributors.
Creating the List Box
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once just below the Combo Box created previously.
Using the Combo Box Wizard
Once you clicked the mouse, the List Box Wizard begins. This
Wizards works the same as the one for the Combo Box.
* We want to get the products and distributors from the
Products table. Click "I want the list box to look up the
values in a table or query." and click the Next button.
* A List Box can display information from a table or query
that you have previously developed. We want our information
from the Products table. In the View area, click the Tables
option. Click the Products table in the list and click the
Next button.
* The next step lists all the fields that are in the table or
query that you can select from. Click the ProductName field
on the left and click the button. Also, click the
Distributor field on the left and click the button. This
will put the fields on the right side in the Selected Fields
area. Any fields in this area are displayed in the List Box.
Click the Next button.
* This step allows us to adjust the width of the columns for
the display in the List Box. By default, Access wants to
"Hide key column". We want the key column to be displayed
(this is the ProductName field). Click the "Hide key column
(recommended)" box. Once you do this, the ProductName column
should appear. Click the Next button.
If you wanted to resize the column to make it wider or skinnier,
you put the mouse pointer on the black line that divides the
column titles.
The pointer will change to . When it changes, click and hold the
mouse button. Next, drag in the direction you want to resize the
column and release the mouse button when you get the size you
need.
* In this step, we select which value to that identifies the
row we select. Click "ProductName" and click the Next button.
* In this step, you have the option of where to store the
information that the user selects from the List Box. If you
were using this form to retrieve information for the table,
you could "Store that value in this field:" and select a
field to store the item they pick from the List Box. We will
not be storing the value. Click "Remember the value for
later use." and click the Next button.
* This is the last step in the Wizard as indicated by the
finish flag. This is for contents the Label Object that is
to appear to the right of the actual List Box. It is a title
that lists what the Combo Box is for. Type in "Products and
Distributors" and click the Finish button.
You should now see two new objects in front of you. One is the
Label Object and the other is the List Box. The objects may need
to be resized and moved. Move the Label Object to be above the
List Box.
Have a look at what we've done so far. To switch to the form
view, click the View button on the far left of the toolbar. You
should see the following list:
To return to the Design window, click the Design button or
select View, Design View from the menu.
Linking the selection from the Combo Box to the List Box
Now that we have both of these objects created, let's link them
together. The goal is whenever we select a category from the
Combo Box, we want the List Box to change and only show the
products and distributors from the category selected.
* Select the List Box just created.
* If the Properties box is not showing already, click the
Properties button on the toolbar.
* In the Properties window, in the Name box, highlight and
delete what is in there. Type in "ProductSelected".
This gives the object a better name for us to reference than
List6.
* Click once on the Row Source box.
This box contains the query that populates the Combo Box.
* Click the button. This will take you in to the SQL Builder.
* Since we want to restrict the entries shown in the List Box
depending of the value selected in the category Combo Box,
we have to include the Category field in the query. In the
first blank column, click once in the Field row. If it now
already showing, a small down arrow will appear by to the
right of the white box. Click the down arrow. This will list
the fields that can be selected. Click Category in the list.
* Now that we have the Category field in our query, we have to
state the criteria. The criteria that we want to use is the
Combo Box field called CategorySelected that is found on the
Categories form.
In the criteria box for the Category field, type in:
Forms![Categories]![CategorySelected]
You should have something now that looks like this:
* Close the SQL Builder window, click the button. Click the
Yes button when it asks to save the changes made in the SQL
Builder.
This will return you to the Form Design window.
If you switch to the Form View and try this out....... nothing
will work correctly. We have to specify one more thing. We have
to tell Access to re-run the query every time we select a new
category.
* Select the CategorySelected Combo Box.
* If the Properties box is not showing already, click the
Properties button on the toolbar.
* Click the All tab in the Properties window.
* Scroll down the list until you see "On Change"
Anything that starts with "On ..." in the Properties window
represents a conditional action you can force to be carried out.
These areas allow you to program what can happen in certain
conditions. In our example, we want the query for the
ProductSelected List Box to be re-queried every time the
CategorySelected Combo Box changes.
* Click once on the On Change box and click the button.
* The following window appears:
Click Code Builder from the list and click the OK button.
* The Class Module window appears. This is the area where you
do the programming in.
The item we want to change is the ProductSelected List box. Type
in:
ProductSelected.
* As soon as you type in the period, a list box pops up of the
different properties that can be changed for the object
selected.
Scroll through the list and click Re-query.
* Close the Class Module window by clicking the button in the
window.
Now let's try it out. Switch the Form View and select a
category. Every time you pick a new category, the List Box
should change.
To return to the Design window, click the Design button or
select View, Design View from the menu.
Click the Save button or select File, Save from the menu.
This would be a good place to take a break if you are looking
for one. To close Access, click the button in the upper right
corner or select File, Exit from the menu.
----------
Step Ten - Price and Quantity Form
We are going to create a another new form. This form will
display information about the price of a product and details
regarding quantity. Here is a general overview of what will be
done:
* Place a Farmer Joe's Market title at the top of the form.
* Place a few fields from our table on the form.
* Create a Text Object that allows us to enter a number in.
* Create a Text Object that contains a calculation.
* Create a button to save the results.
The goal of the form is to show the cost price, mark up rate and
retail costs. We will also show the quantity on hand. The user
will also be able to enter the amount of new stock that arrives.
Creating the Form
To create a new form, click the Forms tab in the main window and
click the New button .
We are going to use the Design View.
* Click the Design View in the list.
* Click the down arrow and select "Products" from the list.
* Click the OK button.
Let's save the form right away.
* Click the Save button or select File, Save from the menu.
* The Save As window appears. Type in "PandQInfo".
* Click the OK button.
Creating a Title for the Form
We are going to create a title for our form that says "Price and
Quantity Info:".
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once near the top of the form. A blinking cursor will
appear.
* Type in "Price and Quantity Info for:"
* Press the Return key or click once anywhere else on the
form.
You can spruce it up by changing the font to Arial, change the
size to 14 and underline the title.
Placing Fields from Products table on Form
We are going to include the following fields from our table:
ProductName, CostPrice, MarkUp, QuantityInStock.
* If the Field List box is not showing, click the Field List
box button on the toolbar.
The Field List box should be showing and list the fields in
the Products table.
* In the Field List box, click and hold on ProductName.
* Drag the mouse below the title and release the mouse button.
* Delete the Label Object associated with ProductName. Click
the Label Object to the right and press the Delete key on
the keyboard.
* In the Field List box, click and hold on CostPrice.
* Drag the mouse to the left part of the form and release the
mouse button.
* In the Field List box, click and hold on MarkUp.
* Drag the mouse below the CostPrice field and release the
mouse button.
* In the Field List box, click and hold on QuantityInStock.
* Drag the mouse to the right of the CostPrice field and
release the mouse button.
More than likely you will have to rearrange and move the fields
around. Try and move them around so they look like this:
If you wanted to clean this up some, you could edit the Label
Objects associated with the Text Objects (i.e. put in correct
titles, spacing, etc).
Adding "Quantity Received" Text Object
We are now going to add a Text Object that will allow the user
the enter in the amount of new stock that was received for the
particular product.
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once to the right of the MarkUp field.
* If the Properties box is not showing already, click the
Properties button on the toolbar.
* In the Properties window, in the Name box, highlight and
delete what is in there. Type in "QuantityReceived".
* In the Properties window, click once in the Format box.
Click the down arrow and select "General Number" from the
list.
Now we have to change the Label Object to the right of the field.
* Select the Label Object.
* Move the mouse pointer on the Label Object. When the pointer
changes to click the left mouse button once. This places you
in the editing mode.
* Use the arrow and backspace keys to remove the text
currently there.
* Type in "Quantity Received".
Adding "Retail Price" Text Object
We are now going to add a Text Object that calculates the Retail
Price of the product by multiplying the CostPrice by the MarkUp
rate.
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once below the MarkUp field.
* If the Properties box is not showing already, click the
Properties button on the toolbar.
* In the Properties window, in the Name box, highlight and
delete what is in there. Type in "RetailPrice".
* In the Properties window, click once in the Control Source.
Type in
=[CostPrice]*(1+[MarkUp])
This is the calculation that gives the Retail Price. Normally
the contents of the Control Source is the field name. You can
place calculations in the Control Source as long as you start
them with an "=" sign. Also, remember that any fields to be used
from the table associated with the form must be spelled the
exact same way as they are in the table and must be include in
square brackets.
* In the Properties window, click once in the Format box.
Click the down arrow and select "Currency" from the list.
This displays the number with a dollar sign and two
decimals.
Now we have to change the Label Object to the right of the field.
* Select the Label Object.
* Move the mouse pointer on the Label Object. When the pointer
changes to click the left mouse button once. This places you
in the editing mode.
* Use the arrow and backspace keys to remove the text
currently there.
* Type in "Retail Price".
Adding "Total Quantity" Text Object
We are now going to add a Text Object that will add the
QuantityInStock with the QuantityReceived to give us the
TotalQuantity in stock. This number will saved later in the
QuantityInStock field.
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once below the QuantityReceived field.
* If the Properties box is not showing already, click the
Properties button on the toolbar.
* In the Properties window, in the Name box, highlight and
delete what is in there. Type in "TotalQuantity".
* In the Properties window, click once in the Control Source.
Type in
=[QuantityInStock]+[QuantityReceived]
This is the calculation that gives the Retail Price. Normally
the contents of the Control Source is the field name. You can
place calculations in the Control Source as long as you start
them with an "=" sign. Also, remember that any fields to be used
from the table associated with the form must be spelled the
exact same way as they are in the table and must be include in
square brackets.
* In the Properties window, click once in the Format box.
Click the down arrow and select "General Number" from the
list.
Now we have to change the Label Object to the right of the field.
* Select the Label Object.
* Move the mouse pointer on the Label Object. When the pointer
changes to click the left mouse button once. This places you
in the editing mode.
* Use the arrow and backspace keys to remove the text
currently there.
* Type in "Total Quantity".
Let's see what is there. To switch to the form view, click the
View button on the far left of the toolbar.
There is a small problem right now. If you look at the Quantity
Received field, there is nothing in there. Without having a
value in that field, the Total Quantity field will not appear.
Another problem that is once you enter a value in Quantity
Received, once you switch to another record, this value carries
over. This would be very hazardous in our situation where we are
tracking inventory.
In the next section, we will look at how we can fix this problem.
Click the Save button or select File, Save from the menu.
----------
Step Eleven - Price and Quantity Form (con't)
We are going to continue to build our Price and Quantity Form.
In this section we will:
* Fix our Quantity Received problem.
* Add a button to save our record.
* Add a button that will close the form.
Initializing a Field
Currently the problem with the Quantity Received field is that
there is no value in there when we first view the form. Also,
the value we do enter is being cascaded across each record we
look at. Here is how we can initialize a field every time you
switch to a different record.
* Click once in the gray non-working part of the window (see
below).
* If the Properties box is not showing already, click the
Properties button on the toolbar.
* Scroll down through the list until you come to "On Current".
This control is executed every time you switch to a
different record.
* Click once on the On Change box and click the button.
* The following window appears:
Click Code Builder from the list and click the OK button.
* The Class Module window appears. This is the area where you
do the programming in.
* Type in:
[Quantity Received] = 0.
This will reset the field every time the record is changed.
* Close the Class Module window by clicking the button in the
window.
Switch to the Form View and see what happens.
As you can see, the value 0 appears in the Quantity Received
field and now the Total Quantity field can be calculated and has
a number in it.
Adding a "Save" button
We are now going to add a button to save the changes we do to
the record.
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once just below the Retail Price field.
* The Button Wizard will begin.
* The first step allows you to pick the type of action to be
performed when the button is clicked. In the Categories
section, click Record Operations. In the Actions section,
click Save Record. Click the Next button.
* This step allows to select if you want a picture in your
button or text. Click the Text option. Click the Next
button.
* This step allows you to assign a name for the button. This
the Name field you will find in the Properties window. Type
in "SaveButton" and click the Finish button.
This button will now save any changes that have been made
(CostPrice). But what about Total Cost. This field is not a
field in our table. This new value has to be assigned
QuantityInStock to have correct numbers.
* Click the SaveButton object.
* If the Properties box is not showing already, click the
Properties button on the toolbar.
* Scroll down through the list until you come to "On Click".
* Click once on the On Change box and click the button.
* You should see some code there already. Access has
automatically generated this code as you were using the
Button Wizard. Click the line above the "DoCmd......" line.
Type in:
[QuantityInStock] = [TotalQuantity] <press return key>
[QuantityReceived] = 0
This will assign the new value to QuantityInStock before the
record is saved and initialize the QuantityReceived field.
* Close the Class Module window by clicking the button in the
window.
Adding a "Close" button
We are now going to add a button to save the changes we do to
the record.
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once just below the Total Quantity field.
* The Button Wizard will begin.
* The first step allows you to pick the type of action to be
performed when the button is clicked. In the Categories
section, click Form Operations. In the Actions section,
click Close Form. Click the Next button.
* This step allows to select if you want a picture in your
button or text. Click the Text option. Click the Next
button.
* This step allows you to assign a name for the button. This
the Name field you will find in the Properties window. Type
in "SaveButton" and click the Finish button.
You now have a button to close the form.
Click the Save button or select File, Save from the menu.
This would be a good place to take a break if you are looking
for one. To close Access, click the button in the upper right
corner or select File, Exit from the menu.
----------
Step Twelve - Linking the Forms Together
Now that we have these two forms, let's link them together.
The goal here is once the user selects a product from the List
Box, they will click a button and the Price and Quantity form
will open.
Opening the Categories Form
* Click the Forms tab in the main window.
* From the list show, click Category.
* Click the Design button.
Adding the Price and Quantity Button
Now that the form is open, we have to add the button.
* In the Toolbox, click the button. The mouse pointer will
change to .
* Click once to the right of the List box.
* The Button Wizard will begin.
* The first step allows you to pick the type of action to be
performed when the button is clicked. In the Categories
section, click Form Operations. In the Actions section,
click Open Form. Click the Next button.
This step allows you to select which form you want to open when
the button is clicked. Click PandQInfo from the list and click
the Next button.
When the PandQInfo form opens, we only want to see the
information for the product that was selected in the List Box.
Click the "Open the form and find specific data to display.
Click the Next button.
In this step, we have to pick the fields in each form that
provide the common link between the two. In the Categories
section, click ProductSelected. In the PandQInfo section, click
ProductName. Click the Next button.
This step allows to select if you want a picture in your button
or text. Click the Text option and type in "Price and Quantity"
in the text box. Click the Next button.
This step allows you to assign a name for the button. This the
Name field you will find in the Properties window. Type in
"PandQButton" and click the Finish button.
Switch the Form View and try it out. Pick a category. Pick a
product. Click the button.
Click the Save button or select File, Save from the menu.
That's it for now!!!! Good luck on the projects!
----------
End of Document
VICUG-L is the Visually Impaired Computer User Group List.
To join or leave the list, send a message to
[log in to unmask] In the body of the message, simply type
"subscribe vicug-l" or "unsubscribe vicug-l" without the quotations.
VICUG-L is archived on the World Wide Web at
http://maelstrom.stjohns.edu/archives/vicug-l.html
|
