CIS 217, Spring 2000

1
Review for Final Exam

• Exam is at 805pm
• Ill be on campus at 715 and if the room is open
  we can begin early
• Exam is cumulative
• Start studying by reviewing all questions on the
  first exam written and hands on
• You WILL see the some of the same questions again
• Practice joins and IIF statements
• Translate a spreadsheet to a normalized database
  study the slides from the first night example
  carefully!!!!!!!!

2
Same Format as the First Exam

• Short Answer
• Multiple Choice
• True False
• Hands-on Portion
• Review all class notes

3
Example Short Answer

• All Short answers from Exam 1
• Discuss similarities between functions and
  procedures
• Functions return values and procedures do not
• Both can receive arguments, contain loops and
  logic structures, calculate values and call other
  procedures or functions
• When should you consider using macros? Name to
  pros to using macros and two cons.
• You or the other administrators of the database
  are not programmers and your need for customizing
  actions is minimal you can use macros.
• Think of the future of the application and if it
  will not be come more automate macros may be
  appropriate

4
Example Short Answer

• Pros Easy to write, Easy to run them from
  events because you just select them from a drop
  down list
• Cons No debugging options No variables
  Difficult to create complex logic structures
• Give an example of how you could use an unbound
  form to create a filter for a report.
• Create an unbound form which contains unbound
  controls where the user can enter various
  criteria. For example, create a combo box filled
  with customer names which allows the user to
  select one customer. Create a button to open the
  form using the DoCmd object with a simple filter
  such as customerIDme.MyComboBox.

5
Example Short Answer

• Give an example of how you could use conditional
  formatting on a report
• Conditional formatting allows for a particular
  control, ie a text box, to be formatted
  differently depending on the value of the text
  box or of an expression. For example a field
  could be bolded if the value in the field for a
  particular record was above a certain amount

6
Example Short Answer

• What kind of code might you program behind a
  form. Discuss events and the general function of
  the code for at least three things.
• Use the BeforeUpdate event to validate changes
  made by a user. You can confirm that the changes
  should be made with a message box
• Use the AfterUpdate event to notify the user that
  changes were made to a record
• Use the OnClick event of a button to open another
  form or report
• Use the OnClick event of a button to change the
  form to be allow edits to prevent accidental
  changes
• Use the AfterUpdate event of an unbound combo box
  to jump to particular records

7
Example Short Answer

• Name two ways to link Access with Excel and
  discuss the features of each way.
• You can attach an excel file to Access as a
  linked table. The data is stored in the excel
  file and can be edited by users of Excel or
  Access. You can not change the table structure
  from Access but you can reference the table in
  queries and reports
• You can create a link from Excel using MS Query
  as a link. Select DataGet External DataCreate
  New Query and follow the prompts to create the
  link. This data will be copied into the Excel
  file as it is at that moment. It will not be
  updated unless the user selects DataRefresh
  Data. Charts can be based off of the data in
  Excel and updated easily when needed.
• You can write code to create an excel object and
  copy data from Access into the excel file cell by
  cell. The data is a static copy of the data at
  the time the code is run and can not be refreshed
  without running the code again.

8
Example Short Answer

• What features does Access have to allow me to
  incorporate my data with the Internet? Discuss
  each.
• Ability to save data as a static HTML file. Snap
  shot of my data in time. Very easy to create.
  Formats from reports are saved nicely.
• Ability to create dynamic links to my data using
  the an IDC or ASP format depending on my server.
  More complicated to create, but my data will be
  re-queried each time showing all changes made to
  the data since the last time the web page was
  viewed.
• I can create hyperlinks in my forms and tables to
  access web pages and email addresses.
• DataAccessPages can be used if my users will be
  using IE5 and have Office

9
Example Short Answer

• Be able to write on paper a basic function which
  receives parameters and creates a concatenated
  string such as the FullName function
• Function FullName(First as string, Last as
  string) as String
• FullName first Last
• End Function
• Be able to covert a simple IIF statement to an If
  Then Else structure which returns a value in a
  variable
• IIf(MyDatagt5,Greater Than 5,Less Than or
  Equal to 5)
• Turns into
• If MyDatagt5 then
• MyVariableGreater Than 5
• Else
• MyVariableLess Than or Equal to 5
• End if

10
Hands-On

• You must answer one of the following two
  questions
• Be able to edit an existing unbound form which
  creates a basic filter
• Add another field to the form and incorporate
  that field into the existing filter using the
  and operator
• Existing code
• DoCmd.OpenRerport myreport,acprintpreview,,myf
  ield me.myfirstfield
• New Code
• DoCmd.OpenRerport myreport,acprintpreview,,myf
  ield me.myfirstfield and
  mysecondfield me.secondfield

11
Hands-On

• Be able to add a command button that opens
  another form showing related records similar to
  the Payment History button in Callahan Chapter 10
• You may use the wizard
• Bonus Question Reset the page number variable
  Page to 1 on an existing report..
• In the appropriate group header add the following
  code to an event procedure in the OnFormat event
• Page1