Creating your user interface

As we've already seen with Access, sometimes the developers have tried to make tasks easier for us (e.g. Macros, where we don't need to use VBA as the details are hidden from us).

This is also the case when creating your user interface - there are two options. You can create normal forms and design them with your own buttons etc., or you can use a switchboard, which looks after some of the details for you and tries to create a consistent user interface.

Both have their pros and cons, and it's often a matter of personal preference, so we'll try both and you can choose!

Before we start, take a look at some examples of User Interfaces in Access:

• Personal Organiser database
• Student enrolment database
• Performance tracking for an IT department (quite a complicated UI)
  

Might be interesting to compare these designs with some Tips about Access User Interface design... We'll work through a quick exercise from our text to see how a more complicated switchboard works.

Once finished, we'll then spend 20mins creating a UI without using a switchboard for our Police Fines database - so we can compare the pros and cons of both.

Access Challenge 2

A few weeks (Months?) ago now, we created a database to help our fictitious Police department record the Traffic Fines (Yes, of course all descriptions of police activity in this exercise are completely fictitious.) You sit down for an easy day at work playing solitaire, only to discover the following email in your inbox:

Hi again! The new Traffic Fines database is being utilised very well within the department, which is great as it's increased our revenue ;). A few of the officers have some suggestions though:

• Is it possible to have a form where we can enter multiple offences against a person at once? Sometimes when we pull people over for speeding, we find that they've got a few defects with their car, or have broken some other regulation... We'd like to enter these multiple fines against the one incident.
  
• Secondly, I hope we're not asking too much, but it'd be great when we do record multiple offences for an individual incident, to be able to see a total of the fines. For example, if Joe Blogs is pulled over for speeding (10 over the limit, $250), but we also find that his car is unregistered ($750) and he talks back to us a bit ($190 for abusing an officer), it'd be great to know that we've got a hat-trick worth $1190 straight away - kindof, calculated automatically on the form... is that possible?

We've got some new budget available to pay for the changes. Taa.

You'll need to get together in your team to:

1. Analyse the requirements to make sure you understand them - drawing the form on paper might be a good way to do this...
2. Review your Police Fines database design to see if the requirements can be satisfied with the current design - and if not, come up with a new design that can fulfil the requirements.
3. Make a copy of your Police Fines database and implement a working prototype together.

Datavalidation with Access Macros

There's actually a number of ways we can validate the data on our Access forms. DatabaseDev lists 5 different ways in the article, Validating Data in MS Access. Of these, we've already seen and used the first 4! Don't believe me? Take a read now and verify that you understand the first 4... (we can talk together in class about these).

Now we're ready to learn the 5th - and most useful way... using the Before Update event to run a Macro!

Read carefully through Input Validation with an Access Macro before using this tutorial to add input validation to your Yahoo! Movies database. For example,

1. Make sure that each new movie has a title and rating before it is submitted.
2. Make sure that each new Complex has a Name and Location before it is submitted.
   

Designing our databases

With the third assignment now out, it's time to get together and start designing our databases!

We'll spend some time this afternoon (half an to 1 hour?) helping each other to design our database tables and relationships, using two easy steps:

1. Create a simple list of every bit of data you will need to store (get feedback on this list in case other people come up with other ideas).
2. Group your bits of data into your primary tables!

Simple huh? Even if you've already started your database, it's still worth reviewing the design with everyone - as if you find a problem now, it will save you time later!

Macros in Access

Our new bit of learning for today will be using Macros in access. Macros in access are quite different to macros in Excel - for a start, you don't have to use VBA (but we might prefer to).

We'll be following an exercise from our text to learn about macros in Access, and we'll see how we can use them do a few nifty things in Access, like automatically updating combo-boxes after adding new information!

Access Challenge 1

We've run out of VBA challenges, so it's time to move on to our Access Challenges! Here's the first installment...

Few weeks ago, we worked through some exercises to create Forms with Subforms. After chatting last week, a few people are still a bit hazy about the details of creating forms with subforms. This challenge will give us an opportunity to practise using subforms.

So far, our Yahoo! Movie database allows us to enter a new screening of a movie at a certain cinema complex. We've created a nice form with dropdowns to help us select the Cinema complex and the movie, but it would be a bit of a pain to use if we had to add a whole list of screenings of a certain movie, or on the other hand, a whole list of movies for a certain Cinema complex! That's where subforms come in handy!

Your challenge is to open up your Yahoo! Movies database and:

• Create a form that allows you to add multiple new screenings for a certain movie.
• Create a second form that allows you to add multiple movie screenings for a certain Cinema Complex

Both of these forms will require a subform - and the easiest way to do that is to choose the right fields for your form - and the easiest way to do that is to plan your form on paper first! If you want to practise and revise first, see if you can find a tutorial on Subforms on Microsoft's Roadmap to Access 2003 training. Good luck!

Advanced Reports in Access

We're going to start learning about advanced reports in Access by investigating Microsoft's Roadmap to Access 2003 Training. Here you'll see a whole bunch of tutorials about different features of Access (a great place to start if you ever get stuck with your assignment!)

You might spot a tutorial called "Reports: Show off your data" - this is the activity we'll focus on first up! If you've done Cert II and feel confident creating basic reports you'll breeze through the first two sections. The new stuff will be seeing how we can customise reports with calculated fields as well as grouping data in our reports. We'll follow this up with a few exercises from our text.

If you finish really quickly, take a minute or two to Go online for more assistance with Microsoft Office Products (this is a short tutorial about how you can find help for Microsoft products on the Microsoft site).

Just in case you missed last weeks activities on more advanced queries like Parameter Queries, you might want to check out Using Parameters with Queries and Reports from Microsoft, as well as Calculating Totals with Access Queries.

Creating your Movie database

Once you're happy with your groups design, open up Access, create a new database called YahooMovies and have a go at:

1. Designing the required tables (fields, types, primary keys)
2. Setting the relationships for your tables (remember: Enforce referential integrity!)
3. Add some test data for your primary tables, then for your other tables.
4. Create some basic forms for your primary tables, then your other tables (you may need subforms)
5. Convert any appropriate fields into Combo boxes so that, even though your form stores the an ID in the table, it displays user-friendly information in the combo box.
6. Create a query that displays all the Cinemas that each movie is screening in.

A database for Yahoo Movies

Do you ever find that, when you want to see a specific movie, you have to check all the cinema ads to find where it is playing? This isn't such a problem for the block-busters, but for smaller arty films it can be quite difficult to find out where you can see a film!

Imagine that we're being paid big bucks by Yahoo! Australia to design a database system with a difference. Rather than the normal listings in the paper that are sorted by Cinema first and then by movie, this one will allow us to also create a list showing, for each movie, all the cinemas where the movie is showing!

We'll divide into groups of 3, and it'll be up to your group to decide on what fields you need, and how to design your tables. This'll help us remember alot of the issues of database normalization! If we have time, we'll have a go at creating our databases.

VBA Challenge 4 - an improved guessing game

Take a few minutes to go over your VBA guessing game from the last challenge. Step through the code (using Excel's Debugging tools) and make sure that it works as you think it should.

Your challenge is to modify your guessing game so that it remembers how many guesses a player takes to get the number. When they guess the correct answer, it should respond with "Correct! It took you 6 guesses." (or however many it actually took them!)