Docstoc

Access Database Forms Employee Example Microsoft Access 2007 Forms Email training health

Document Sample
Access Database Forms Employee Example Microsoft Access 2007 Forms Email training health Powered By Docstoc
					Microsoft Access 2007
        Forms




        Email: training@health.ufl.edu
     Web Page: http://training.health.ufl.edu
                   Microsoft Access 2007: Forms
                                                          2.0 hours
Topics include formatting fields in a Form, Tab Controls, SubForms, Menu Forms,
startup options and Linking Forms. This workshop requires the completion of "Access:
Basics" and "Access: Intro to Forms and Reports."
Form 1: Patients .................................................................................................................. 1
   Formatting Tools ............................................................................................................. 1
   Tab Order Class Exercise................................................................................................ 1
Form 2A: Employees .......................................................................................................... 2
   Change the size properties: ............................................................................................. 2
   Change the format properties: ......................................................................................... 2
   Change Department: ....................................................................................................... 2
   Remove Form Header: .................................................................................................... 2
Combo box (lookup) ........................................................................................................... 3
Form 2B: Employees ......................................................................................................... 4
   Tab Controls.................................................................................................................... 4
   Add Fields to Pages ........................................................................................................ 4
   Format Fields on Forms .................................................................................................. 4
Subdatasheets ...................................................................................................................... 5
Form 3: Departments .......................................................................................................... 5
Form 4 & 5: Patient Visits .................................................................................................. 6
Form 6: Main Menu ............................................................................................................ 7
Exit Database Button Error ................................................................................................. 8
   The "fix".......................................................................................................................... 8
Start Up Options ................................................................................................................. 9
Linked Forms .................................................................................................................... 10




                                         Pandora Rose Cowart
                                         Training Specialist
                                         Information Technology Center

                                         Health Science Center                352-273-5051
                                         PO Box 100152                        prcowart@ufl.edu
                                         Gainesville, FL 32610-0152           http://training.health.ufl.edu
                                                                        Page 1
Form 1: Patients
Build Patient Form in Design View based on Patient Table.
   1. From the Create tab click on Form Design
   2. Open Property Sheet (F4)
         a. Set Record Source to Patients
   3. Resize grid to 5 inches wide by 2 inches tall

   4. Open Field List (Add exisiting fields from the Design tab)
         a. Add and rearrange the fields to match the form
            shown below

   5. Create Command Buttons         on the Design tab
         a. Be sure to give the buttons unique names

   6. Save as "Patients"




Formatting Tools
Remember the Arrange menu
in the design of your report has
several tools to help to make
your forms more visually
appealing.



Tab Order Class Exercise
    Swap First and Last
    Adjust Tab Order (from Arrange menu)
    Remove buttons from tab order (through tab stop property)


                                                                   Updated 1/20/09
Page 2
Form 2A: Employees
Select the Employee table and click the Form button to create a single column standard form.
Access will place you in the Layout view. From the Arrange tab, open the Property Sheet.




Change the size properties:
     Width All: 1.25”
     Height Picture only: 1.25"
Change the format properties:
     First: > (all caps)
     Last: > (all caps)
     Birthday: MMMM D (full month, single digit day)
Change Department:
     Delete Department ID
     Move to the Design View
     Add a combo box to the window ( )
     (See example on next page)
     Add Department Combo to stacked fields
            (Drag field into place, watch for the thin orange placement line)
Remove Form Header:
     Right click on grid and choose Form Header/Footer
     Click Yes to accept deletion




Updated 1/20/09
                                                    Page 3
Combo box (lookup)
Create a combo box ( ). Like the
command buttons this will launch a
wizard. Combo boxes are like lookups
between your tables.




 This is the most important screen. You
 must store the value in the right field. If
 you miss this step you will create an
 UNBOUND combo box.

                                               Updated 1/20/09
Page 4
Form 2B: Employees
    Delete the Employee Form (or close without saving it).
    Create a new Form in Design View.
    Change the Record Source (in properties) to the Employee Table.
    Open the Field List and add the First and Last fields.
    Add a Tab Control ( ) from the controls on the Design tab.
    Adjust the grid to fit the form (about 4” by 3”)

Tab Controls
To Add/Delete/Reorder the pages, right-
click on the tab control.

To move the tab control, click and drag
from the upper right area where the new
pages would appear.

When you drag a field onto the page the
page area will darken. Make sure the area is
still dark when you drop your item to keep it
in that page.

Pages can be Named through their Name
property, but if the name is in use, use the
Caption property.

Add Fields to Pages
Department Page - Add the Dept ID, and create Department Combo box,
Personal Page - Add Birthday and Celebrate
Picture Page – Add Picture field

Format Fields on Forms

         First & Last: All Caps (Format property)
         Birthday: Full Month, Day (Format property) and Left Align (from Toolbar)
         Picture: Fit Frame to Image (Height and width properties, 1.25”x1.25”)




Updated 1/20/09
                                                                                                         Page 5
Subdatasheets
Beginning in Access 2000, Microsoft began to show a new column in the tables once a relationship had
been created. This new column indicates that a subdatasheet has been attached to the table. We can see
one here in the Department Table:




                                     Click the + to expand
                                   (show) the subdatasheet.
                                    Click the – to collapse
                                           (hide) it.




If you click on one of the plus signs in this new
column you will see data realted to that record.
You can enter data into both tables from this view.

                                                Access will assign the subdatasheet the first time a
                                                relationship is created. It is possible however for one table to
                                                be involved in multiple relationships. For examlple, one
                                                patient could have many appointments, many insurances,
                                                many doctors, many allergies and many medications. The
                                                first relationship that was created would be the one showing
                                                here.

                                                You can change the datasheet that is linked with this table
                                                under the Home tab, Record Menu, More Option, using the
                                                Subdatasheet option.



Form 3: Departments
Select the Patients table and click on the Form button on the Create tab. Because the Department
table had a subdatasheet attached, Access will automatically embed the table within the form.
                                                                        To hide columns, click on the
                                                                        column name, right click and choose
                                                                        Hide.
                                                                        Warning: If you embed a table in this
                                                                        manner, when you hide columns on
                                                                        the form, it will hide the columns on
                                                                        the table.
                                                                        Because of this we will only make the
                                                                        First and Last name columns wider
                                                                        to push the other data out of view
                                                                        Notice the double set of navigation
                                                                        buttons and record selectors.

                                                                                                    Updated 1/20/09
Page 6
Form 4 & 5: Patient Visits
Instead of building forms with imbedded tables, it's much better to imbed another Form. If your
relationships have been properly created you should be able to do this fairly painlessly through the Form
wizard (Create Tab, Forms Group, More Forms Menu, Form Wizard). This form will be created using
both the Patient table and the Visit Info table.

                                                                  Choose Table Patients.

                                                                 Select all six fields from Patients.

                                                                 Then Change the table to Visit Info.

                                                                 Select all the fields except Visit ID
                                                                  and Med Rec. (you may choose all
                                                                  the Visit Info fields and then pull back
                                                                  the two fields you don't want to
                                                                  include in the forms.)
                                                                  o We want to leave these two off
                                                                      because Visit ID is an
                                                                      AutoNumber, and Med Rec is
                                                                      already listed with the Patient.)

Because our tables have a one-to-many relationship, Access offers us these choices in created our
Patient Visits Form.
Form with Subform(s)
 - By Patients: Creates two forms: a
   main Patient Form with an
   embedded Visit Info form that
   will display multiple visits for that
   one patient.
  - By Visit: Creates one form – All
    Patient information and that one
    visit information displayed on the
    same screen, i.e. a billing screen
    at the end of an appointment.
Linked Forms
 - Linked Forms: Creates two forms:
    a Patient Form and a separate
    Visit Info Form. The Patient
    Form will have a button that will
    open the Visit Info form such that
    it will only display the visits for
    that one Patient.




Updated 1/20/09
                                                                                                       Page 7

                                                                                 Choose the Datasheet
                                                                                  Layout.
                                                                                 Choose the Office Style.
                                                                                 Name the Form "Patient
                                                                                  Visits".
                                                                                 Name the SubForm
                                                                                  "Patient Visits SubForm".
                                                                                 Format to your liking.
                                                                                  Remember to Remove
                                                                                  from the stack in order to
                                                                                  delete.


Form 6: Main Menu
Create a form in design view. We will not use this form to modify our data, so there will be no
record source set. This form’s purpose is to navigate our database with command buttons.




Four buttons should be created to open the four main Forms – Patients, Patient Visits,
Departments, and Employees. These are all Form Operations. The buttons should be set to
show ALL the records.

A fifth button should be created to quit Access, this is an Application option.

If you want your form to look like the one shown here, the title is a Label and the lines around
the buttons are boxes made with the rectangle tool,   , on the toolbox with a special effect of a
shadow found in the Design Controls,       .




                                                                                                  Updated 1/20/09
Page 8
Exit Database Button Error
When you are using a Office 2003 or earlier Access file in Office 2007 there will be a “Invalid
reference to the property Dirty” error when you create a "Exit Database" button. This will not occur in
2003, nor if you are using a 2007 file in Office 2007.

When the button is tested you will get this error message:




The "fix"
         Move to the design or layout view
         Select the button
         Open the Property Sheet
         Find the “On Click” property
         Click on the “build” button (…)



\

In the Visual Basic window find the line that states:
               If Me.Dirty Then Me.Dirty = False




Delete this line. Close and Save the Visual Basic window. You’re button should now work.


Updated 1/20/09
                                                                                                  Page 9
Start Up Options
When we first start this database, it would be helpful if the Main Menu form opened automatically.
From the Microsoft Button, Choose the Access Options, , choose the Current Database.




      The Application title will change the title bar from Microsoft Access to your database name.
      The Application Icon needs to be a *.ico file.
      The Display Form has a list of all your current forms. Change this option to your Main Menu.




Further down the window you have the ability to turn off the navigation pane. If you do decide not to
Display Navigation Pane on startup, you can bring it back by pressing the F11 key on the keyboard.

You can also limit the menus. This is something you want to be very careful with. It’s a great way to
limit what the “users” can do in your database, but it often makes things more difficult to work with.



                                                                                             Updated 1/20/09
Page 10
Linked Forms
On the Main Menu form we created buttons that opened a form and displayed all the records. On page 6
of this packet we created a form with a SubForm using the wizard. One of the options the wizard
offered was instead of a SubForm, we could create a Linked Form. Basically we have two independent
forms, but the first form has a button that opens only specified data on the second form.

In our Patient and Employee Database we
would like to have a button on our Employee
Form that will show us the Department Form
for an employee's specific department.

Add a button on the Department page of the
Employee Form to open the Department
form. Instead of opening the form and
showing ALL the records, choose the option
to "Open the form and find specific data to
display."



                                                        On the next step of the wizard, you need to
                                                        show Access what the link is between the two
                                                        forms. We can create relationships between
                                                        tables, but Access can't always how the forms
                                                        are related.
                                                        In this case it’s the Dept ID on both forms.
                                                        Once you have selected the proper fields,
                                                        click the join button in the middle to make
                                                        the match and it will appear across the
                                                        bottom in the matching fields.


When the new button is pushed and it opens the Department form, showing only the department the for
the current employee.

                                                                    The data is actually being
                                                                    Filtered.

                                                                    If you would like to see all the
                                                                    possible departments, turn off the
                                                                    filter by pressing the remove
                                                                    filter button,            .

                                                                    See the Select Queries
                                                                    documentation for more
                                                                    information about Filters.



Updated 1/20/09

				
DOCUMENT INFO
Description: Access Database Forms Employee Example document sample