Welcome back to our course on Access 2016.
In this section and the next couple of sections we’re going to look at Datasheet View in
quite a bit more detail.
Now I pointed out earlier on that you can design in Datasheet View and you can do data
maintenance in Datasheet View.
But I think it’s fair to say that it isn’t really the most suitable way of doing either
of those things.
Having said that, many people like to use Datasheet View.
And one advantage of using Datasheet View is that you can do both design and data maintenance
using one view and one set of tools.
So what I’m going to do in this first section is to look at doing design in Datasheet View
and then in the other two sections I’m going to concentrate on the data aspects.
We’re not going to need the Navigation Pane for a little while so let’s just collapse
that over to the left.
And let’s select the Fields tab in the Table Tools on the Ribbon.
Now if you look at the left-hand group on the Fields tab you have a Views group with
a Views button.
And of course I’ve already pointed out that at the bottom here you’ve got a choice of
Datasheet View or Design View.
Now in fact you can toggle between those two views just by using the top half of the button.
So bearing in mind that we’re looking at design aspects here let’s look at that Add
and Delete group.
Now you’ve already seen that I can add a new field at the end of the field list over
there on the right using that click to add column.
But if I say selected the Country column, the country field, I could add a field of
one of these types just by clicking on the button in the Add and Delete group.
So if I wanted a short text field click there and I have a short text field with a default
name of Field 1 added to the right of the Country column which is the column that I
had selected.
Now some of the most popular data types are shown here with single buttons.
So there’s Number, Currency, Date and Time, Yes/No. if I click on More Fields I have a
very, very long list of potential fields that I can add using this approach.
The other button that you’ll see in the Add and Delete group is the Delete button.
And if I want to delete a field I select it.
I get a warning there about permanently deleting the selected field, confirm, and that field
is gone again.
So that’s a straightforward way of adding and deleting fields to a table.
Let’s move on now to the next group, that’s Properties.
I mentioned quite a bit earlier on in the course that in Datasheet View you don’t
really have the full power that you have in Design View but you can certainly do a lot
to maintain the properties of the fields in a table.
So let’s select the TripName column, the trip name field.
And some of the controls in the Properties group are enabled.
So for instance if I click on Name and Caption I could change the name of the field or the
description of the field.
I could add a caption which would appear in certain situations, as we’ll see later on
in the course.
I can also change the field size for the trip name or I could set a default value.
Currently there isn’t a default value for trip name.
Notice that the other properties are not enabled.
Let me now select the Country field.
Now the Country field, as you know, is enabled as a short text lookup.
So the Modify Lookups control is enabled.
Click on Modify Lookups.
I could actually go in here and maintain my value list for this particular lookup field.
Let’s look now at Description.
A description is a long text field.
Again I could change something like the name and caption but I also get the option of memo
settings.
Now I haven’t talked about memo settings before but there are two.
One of them is append only.
And if you set a long text field to be append only that means that you can only add text
to it.
You can’t change what’s there already.
This is sometimes used where people want to keep say a running log of something that’s
happened and all they’re going to do is append to the text that’s already in this
field.
The second option there of making it rich text means that it will be formatted as HTML
and therefore you can record formatting within that field as well.
So for instance, you could have bold text or you could change the font in certain places.
You could put in italic text and so on.
So I’m not going to apply either of those memo settings to our description field.
Now let’s take a look at the Formatting group.
And let’s start with DurationDays.
Now this is a number field and it’s formatted as a general number.
But I could change its formatting.
For instance if I wanted to show a dollar sign it turns it into a currency field.
And as you can see it’s a currency field currently with two decimal places.
Note the two buttons immediately under the word Currency at the moment.
I can increase or decrease the number of decimal places.
So increasing, decreasing, right down to zero if necessary.
And then I could even return it back to being a general number and lose the dollar sign.
And let’s assume that I wanted to remove the cents in PriceMinimum.
Just select that field.
I can remove the cents like that.
Of course I could do the same for PriceMaximum as well.
Now note the formatting options and how they work will depend primarily on the data type.
So in this case we have a data type of currency and then within that you can choose the appropriate
formatting.
So if you wanted our local default currency formatting it’s there.
If you wanted to format this currency as euros you could do it like that.
But let’s go back to our default currency.
The last group on the Fields tab is Field Validation.
And you have three checkboxes there.
Is this field required?
Must it be unique?
Is this field indexed?
And of course all of these should have been set correctly by me earlier on in the course.
There’s also a button there which shows me the validation, if any, on this particular
field.
And of course the validation that we had on PriceMinimum, the currently selected field,
is greater than zero.
Now there’s a dialog here, Expression Builder, and we’re going to be looking at the Expression
Builder in quite a bit of detail later on in the course.
But you can build up some pretty complex validation rules.
In fact you can build up all sorts of complex things because you won’t only use the Expression
Builder for validation.
But as I say, more of that later.
Apart from looking at the rule which in this is a very simple one, greater than zero, you
can also maintain the field validation message.
So here it’s Minimum price must be positive.
Now I mentioned earlier on that sometimes you need validation to consider more than
one field at a time.
And you can actually use the validation control here to set up record validation.
So this is validation that includes more than one field in a record.
So let’s just do as an example the validation that I mentioned earlier and that is that
the minimum price can’t be more than the maximum price.
So again click on that dropdown but this time not Field Validation rule but Validation rule.
Now to set up the Validation rule we’re going to use some of the items that are in
this Expression category section here.
And first of all we’re going to use price minimum.
So double click that.
That is the value in the PriceMinimum field.
And to indicate that it’s one of the fields in the record it’s in square brackets.
And our requirement is that PriceMinimum is less than or equal to PriceMaximum.
So that’s our Validation rule.
Now let’s put in a validation message.
Click on Validation Message and I’ll type a message in.
Minimum price cannot be greater than maximum price.
Let’s try that out with say the Louisiana Explorer.
Let’s change the minimum price from $2880 to $3880 and then click on one of the other
records.
There we are, Minimum price cannot be greater than maximum price.
So what we’ve done there is to set up a record validation rule.
And I think I’m going to leave that record validation rule set up.
So I need to put Louisiana Explorer back the way it was otherwise I’ll have invalid data.
Let’s turn our attention now to the Table tab on the Ribbon.
I’m really only going to talk briefly about some of the groups on this tab.
There are three middle groups there, Before Events, After Events, Named Macros.
These are all associated with macro code and VBA code that may run or be run as a result
of certain events occurring.
So for example, if a user makes a booking you may need various other information in
the database updated in response to that booking, such as the availability of a trip, for example,
or you may need to trigger an invoice being sent to the customer either to charge them
for their deposit or the balance or at least to inform them of the details of their booking.
As a result of these triggers you may have various other things happen in your Access
database.
Now as I mentioned right at the beginning we’re not really covering programming in
any great detail on this course.
We will look at macros a little later on.
The group on the right, Relationships, Relationships are a topic that we are going to cover in
some detail on this course and we’re coming back to those in a few sections from now.
On the left, the Table Properties, relate to things like sorting and filtering in the
table but we’re going to look at those in the next section.
So for this section that’s it and I’ll see you then.