Updating database tables
The new improved database
This lesson uses the new version of the MikeVideo database, MikeVideo2004.
You can download it now from the Downloads area.
Although still not 100% complete, the database has major improvements over the previous version. You will still have to do some work to get it fully functional. But if you've followed the lessons up to now, you should be able to figure out what to do to make it work better.
Start by studying the Relationships. You will see that some new tables have been added. That will allow us to keep track of late returns of movies, will calculate sales tax and will eventually let us track of sales of items such as used movies, popcorn and Pepsi.
This lesson is about updating tables in the database.
The simplest example of an update is changing the availability status of a movie.
All movies in stock have an availability status of True by default (Yes, the movie is available to be rented). When someone rents the movie we want to change that status to False. Then when the movie is returned we'll change it back to True.
Writing updates is a question of logic.
You must know how the transaction is processed, what data are affected and what effects will errors have. For example, you don't change the status upon entering a movie number. If you entered the wrong movie, the status will have been updated by the time you realize it. So, you will want to confirm the movie title before you do the update.
There are usually two ways to code the updates: using a macro or an event procedure. Both are used in the database.
Using a macro can be simpler in some ways because there is no code to write. You simply fill-in the blanks in the macro creation window.
Usually, the Set value operation will allow you to change the contents of fields in a form or in a query. For more complicated processing you may have to write a special query (Update or Append query) and then use the macro to execute that query when appropriate.
If you know a bit of Visual Basic writing an Event procedure may be the way to go.
An event procedure is a procedure written in VB code that is called when a given event happens to an object.
Updating the Availability status
Here's an example of an Event procedure.
In the Detail subform we will input the movie number and so on. A new column has been added for Quantity. For movies that will always be 1 by default because you can only rent 1 unit of a given movie number. But when we add other items, you may be able to buy 3 bags of popcorn. That will also work for that.
So, we determine that we will update the availability status on the LostFocus event of TextBox Quantity.
To be able to do this, availability must appear on the form and the query so that the change can go backwards: from the form to the query to the table.
At the moment the cursor leaves the Quantity textbox (focus is lost) the event procedure is called which sets Available = False.
As you can see, at least for this example, you don't need a PhD in programming to write this procedure!
For Returns, most of the work is done in the query.
We will calculate the number of days the movie was out then calculate the late charges, if any.
If you look at the query you will see that you can do calculations that are somewhat involved, just by using standard Access functions.
There are a few additional details to worry about in the Returns query.
A given movie will have been rented-out 100 times. When you run the Returns form, you only want to see the last time it was rented - that is obviously the one being returned.
You can obtain that in the properties of the query.
To update availability status for the movie returned, we use a macro.
We have the choice of applying late charges or not. If we decide to apply them, we must save the information in a table so that we can give the customer the details on his next visit.
We use an Append query launched by the macro to do that.