Lecture28 sections 9'19'7

1
Lecture-28 (sections 9.1-9.7)

• CSCI130-03A
• Instructor Dr. Imad Rahal

2
Forms

• Used to view, input and update data in a database
  one record at a time
• Non-professional users
• What about reports?
• Present data on screen one record at a time
• Allows for easier input and edit operations
• Based on a table (unlike reports which are based
  on queries)
• To create a form
• Return to the Database window
• Click on Form tab and select New, Form Wizard
• Select the table used as a basis for the form in
  addition to all fields to be included
• Forms for input should include all fields
• Forms for editing need only to include the edited
  fields

3
Forms

• Select one of the built-in layouts
• Can be edited or changed later on
• Choose the background pattern
• Type in a name for the form
• Change the form to look as youd like
• Add pictures, move elements, add common elements
  like menus, list boxes, or buttons
• To modify, the forms design either
• Select Modify on final window of Form Wizard
• Open form in Design mode
• Form window is split into 3 regions (header,
  detail and footer)
• VBA toolbox appears
• Build Employee form

4
(No Transcript)
5
Forms

• To add a header
• Place cursor on the boundary between header and
  detail
• Drag till you get proper space
• Add text to you header by
• Clicking on Label icon in the VBA toolbox
• Moving to the header
• Entering text
• At the bottom of the form, notice the number of
  the records we are viewing
• Use arrows to move from record to record for
  editing
• Click on the arrow followed by a to get a blank
  form to fill a new record

6
Macros in Access

• Two ways
• Using built-in actions (from a drop down menu)
• Create macro and then hook a button to it
• Using VBA
• Create button along with macro
• Access has built-in subroutines called actions
• segments of VB code written to perform common
  tasks
• A macro consists of one or more actions put
  together to accomplish a task for the user
• In general, Access macros are created using a
  menu system to choose the desired actions to be
  performed
• Macros are usually assigned a command button so
  they can be executed with a single mouse-click

7
(No Transcript)
8
Creating Macros

• Using built-in actions
• Select Macros from list of objects on the
  database window
• Choose New from menu bar
• Add desired actions along with their descriptions
• E.g. GotoRecord, Msgbox, OpenForm, OpenQuery,
  OpenReport, OpenForm, Quit, RunMacro, Save,
  Maximize, Close, etc
• OutputTo
• Save macro with an appropriate name
• Insert button (Cancel button wizard if it
  starts)
• Right Click ? Properties ?ALL Tab
• ? Change Name Caption (Use name conventions)
• (Scroll down to) ? On Click
• Click inside entry and then on down arrow
• (DO NOT CLICK on the three dots button)
• Select from list of available macros

9
Forms

• Build a form for table Departments
• Form Tab, Create Form Using Wizard
• Add a header with the following nice title
• CSCI 130 is the best course ever !
• Navigate through table records using arrows
• Update a record
• Move to next record in order to make the update
  effective
• Look at Departments table in datasheet view to
  see change
• Add a record (using gt button)
• Move to next record in order to make the
  insertion effective
• See Departments table in datasheet view

10
Practice Macros

• Create the following macros each with a
  corresponding button on the department form
• Macro1 Go to the last record and show a message
  box saying that we are currently on the last
  record (Click on button to test)
• Macro2 Runs Macro1 and Maximizes the display
  (Click on button to test)
• Macro3 Closes the form (Click on button to test)
• Macro4 Quits the database application (Click on
  button to test)
• Macro5 Saves database and opens the Departments
  table
• To test your macro
• Use the gt button at the bottom of your form to
  make space to input a new record
• Input a new department
• Click on button (go to the Table Departments and
  see of record was added) i.e. it has been saved
  by Macro

11
Practice Macros

• Macro6
• Reads 3 new records from an input (text) file to
  Departments table
• Create the text file with 3 new department
  records each on a row separated by commas
• All values must be enclosed between double quotes
• Save it in the same folder as your Access
  application
• First row in the imported file MUST include the
  names of your table fields enclosed between
  double quotes
• Use action TransferText (with transfer type set
  to Import Delimited)
• Specify file name with the full path
• Has Field Names property, when importing, MUST
  BE SET to YES
• And then writes alls records in the departments
  table to a spreadsheet
• use action TransferSpreadsheet (with transfer
  type set to Export)
• Specify Excel file name with the full path
• Has Field Names property should be set YES