Session 5 - Goodbye Access; Hello Excel

Sadness for many, but for some, I think perhaps a sigh of relief.

Final Bits

So, today I will impart some final bits on Access and of course give you all the details for your ASSESSMENT TASK.

Calculated Fields

The last of the special Access field types is Calculated Fields!! You can probably guess from the name what this means...

Rules:

    The calculation cannot include fields from other tables or queries.
    The results of the calculation are read-only.

You can also use any of the in-built Access functions

Here is an example. In our Video Vidiots database, we want to be able to keep track of how long each rental is.

To do this, we need to take the Returned date and subtract the date borrowed - this will give us the loan period in days.

Step 1: Add the calculated field to the table.

Step 2: When the expression builder window appears, put in the calculation for the field.

Click OK and make sure that you save the new table design.

Step 3: Add the field to your form. I added mine to the Rentals and the Returns Subform

Now view the form and see that the calculation works!!

Basic Coding - Advanced Search on Form

This is optional as it does require a little bit of coding - just a taste of what you can do!! It doesn't really matter whether you understand it all, so long as you can duplicate it to suit your circumstances.

What this will enable you to do is to find the first record that matches the criteria and then click through the records that match until you find the one that you are after. This uses the principle of Find First and Find next.

Events

The use of macros and coding in Access requires you to understand what an event is - an event is anything that you can do as a user i.e. click on something, type something, load something etc. The list of available events for an item on a form are within the properties for that item.

To start with, I am going to make sure that the Headers and Footers for the form are displayed -  I want to add a search box within the form footer.

To display headers and footers, right-click on the Detail bar.

 

I minimised the Header, but increased the size of the Footer so that I could add some search boxes and button etc.

In the Footer, add 2 text boxes - they should both be unbound.

In the Properties Sheet, make sure you give them meaningful names (and remember what they are).

The Membership one is called SrchMembership
The Surname one is called SrchSurname

You will also need to add a Command Button - this will be where we will run the macro from. If this button is clicked (The EVENT), then the macro will run.

Once you have your text boxes and your command button, you are ready to type in your first bit of code. We will assume that we want to include the wildcard in the search so that it is not relying on an exact match.

Select the button and make sure that the properties sheet is displayed. Click on the drop down arrow next to On Click and select [Event Procedure]. Click on the ... button.

You will then be taken to the Visual Basic editor. Don't worry too much about it at this stage, just type in the same as you I have using the names that you have called your text boxes and fields etc.

 

Save and test your form - does it find the first record that matches your criteria? You can leave it at this, or you can add some more "Tweaks".

In my example, I have added a Find Next command Button, with the view that you can scroll through a list of "matches" until you find the one that you are looking for. The code is identical except for the FindNext instead of FindFirst. Because we have included the like and * wildcard, the match does not have to be exact - one letter is enough. The easiest way is to cut and paste the code and change the FindFirst to FindNext.

Bookmark will return the user to the current record.

Slightly More Advanced

You can add multiple buttons to search using different fields. I allowed a search on either membership number or by last name. I used a test to determine which of the fields had a value entered into it and created the search accordingly. Here is my code.

SEE SEPARATE DOCUMENT WITH ASSESSMENT TASK DETAILS.

Options - turning off stuff

If you were creating this for someone to use who does not know Access, you would customise it so that the user would only be able to do the things that are in your menu.

You will find all of the things that you can turn on or off in the Options for the Current Database. Turn off all of the things that you don't need as well as setting the Man Menu to be the form that displays when the database is opened.

Of course, turning off stuff also means that YOU are unable to access some of the tools that you may need to make changes to your database. To open the database so that you have all the essentials available, hold down the Shift key while double clicking the file to open it.

EXCEL Basics

It is expected that you can perform the following things in Excel before we move onto some more advanced Excel stuff.

• Create a New Spreadsheet
• Add Text, numbers and Dates into Cells
• Use the AutoFill tool
• Use formulas to perform calculations
• Understand when to use absolute and relative addresses in formulas
• Format the spreadsheet to look pretty
• Create a basic chart
• Use some basic Functions

If you are confident with the above, you do not need to stay for the quick refresher task