Access 2007 Tutorial 3.1. Forms and Subforms

In this video, we will be creating forms for a database. Forms allow us to create a front
end to the database that simplifies and hides the inner workings of the database, such as
the many primary and foreign keys that will be meaningless and confusing to most of our
database users. I am looking at that database tools relationships
view of my database and I have four tables. I have a cast table that has foreign keys
to the movie and the actors tables. This cast table acts as an association table, establishing
the many to many relationship between movies and actors.
I will be creating my forms by clicking on the create ribbon, selecting the more forms
drop down, and selecting form wizard. I will select the director table, adding the director
last name and first name. After clicking on next, I want to keep the columnar form layout
selected and again, click on next. I have the opportunity to select a style for my form
and will click on next. And at this point, I can change the title for my form. I am leaving
the title as it is and I will click on finish. So this has created a form for the directors
that are in my database. I can click on the next record and move through my database.
I can click on the last record and jump to the last director. Or I can click on the first
record button. If I want to add a new director, I can click on the new blank record button
and enter some information. Now, we will be creating a form that will
be a bit more complicated. Again, I'm opening the create ribbon, selecting more forms, form
wizard. I will be selecting the movie table, and from the movie table, the title, the rating,
the year, and the director ID for the movie table. I will then select the cast table and
the actor ID. Now you might be asking yourself, why not
just select the actor last name and first name, instead of selecting actor ID from the
cast table. So, I am going to show you what will happen if we actually did that. Notice
that it is not easy to select an actor that already exists in the database. So, using
foreign keys, we can easily create comboboxes that will allow us to begin typing in the
name of the actor and immediately have it bring up the appropriate record. After clicking
on next, I will elect to view my data by movie, so it will show the movie information, and
it will then show the cast records underneath. I will continue to use a form with a subform
and then click on next. I want to see this in tabular arrangement, so I will select tabular
and then I will click on next. I will continue using Access 2003 style. For the subform name,
I want to know what form is using my subform, so I will be putting in the word movie in
front of the cast subform name, and then I will click on finish.
You will notice at this point, that it shows my movie titles, the MPAA rating, the form
has the director ID but we don't see the director name, it also has the actor IDs that appeared
in that movie, but it doesn't have the actor names. If my underlying table had used lookup
fields, I may see the director and actor names. Since I do not have lookup fields on the underlying
tables, I will now need to alter the comboboxes so that meaningful data appears in the comboboxes.
So I will now alter my form, entering design view. And at this point, I can begin editing
my form. I will select the director ID combobox and right clicking on this combobox, I will
open the properties pane and choose the data that is displayed in this field, selecting
the data tab and clicking on row source. I will click on the ellipsis button for the
row source and create a query that will populate that field. I want to query the director table.
I will use my director ID to populate the query and also pull down the director last
name. I will then make this field a little wider. I will type in the ‘&’ sign, then
type a single quote, comma, and then another single quote. A single quote is just an apostrophe.
So, I have created a string of text that contains a comma and a space, and this will be appended
on the end of the directors last name. I will then hit space and add another ‘&’ sign,
and type the field name for the director firstname. I will run my query to see how it looks. I
see all the different directors with the last name followed by the first name. My query
looks good. Notice that I have this word right here, EXPR1, expression one. When I looked
at my query results, this is the field name that was returned for this field that I have
created. So I return to design view and change the EXPR1 field name to the word director.
I will now close out of this query. It asks me, "Do you want to save the changes made
to the SQL statement and update the property?" I will click on yes.
And I can now see inside this box, the SQL statement for the query that I just created.
I will return to the format tab and make sure that I am pulling back two columns. I am pulling
back the ID field from the director table and I am pulling back the director name and
the director first name that are appended to each other. So that is correct. While I
am displaying the information I want in the dropdown, I want to make sure I am hiding
that first column which is my key. I do not need to see my key, and I do not need to show
my key on the form, so what I will put in, is a zero and then double quotes. This means
that the first column will be zero inches. I will then add a semicolon to indicate that
other columns are following and I do not care what length the second column is. I will now
look at my form in form view. When I look at my form, I see the director
ID. If I want to, I can click and I can select another director. Notice that the word director
appears right here, this is where we replaced EXPR1 with the word director. As I move through
the different records in my database, you will see that it shows each director for each
movie. I can replace any of these directors if I want to.
I will now return to design view and this time, I will select the actor ID combobox
and perform the exact same operation. Since we have already performed this procedure once,
for the sake of time, we will move through it quickly. We use the row source field in
the data tab to build our query that populates the combobox with data.We bring back two fields
from our query, with the first field being the primary key to match the data on our form
and the second field containing last name comma first name. We look at the format tab
and make sure that we have a column count of two and that we are hiding the field that
contains the primary key. Returning to form view, I can see the actors
that are in these movies.I see a problem with my data. James Phelps is the actor that plays
Ron Weaslee in the Harry Potter movies, but he should not be appearing in this movie.
So I will need to add a delete button that will delete that record out of the cast table,
so I do not have to manually find and delete the correct record from the cast table which
associated this actor with this movie. I will enter design view for my form. I will
click on the subform. I need to move my mouse over the corner of the subform and wait for
the cursor to change into a double headed arrow. This indicates that I am resizing the
subform, so I can then resize the form, making room for my delete button. Now that I have
resized the subform, I need to resize the canvas that appears within the subform. I
will wait until my cursor changes into a vertical bar with arrows on each side and then click
and drag, extending the canvas. I now have room to add my delete button. I will go to
the form design tools design ribbon, click on the button icon, and then click in my subform,
adding a button. I will select a record operations button that will delete my record.
I will click on next. At this point I can choose what kind of icon I would like, or
I can choose to have text. I want the little trash can icon. I click on next. I can give
my button a name, but I am not going to worry about that unless I am plan on using my button
in a macro, so I will click on finish. So I have created a little trash can icon. I
want to resize my icon and then I will drag the bottom edge of my canvas and make my form
a little tighter right here, and then take a look at it in form view.
So notice that I have a little delete button here next to each of my actors. If I click
on this delete record button what it is actually deleting, is not the actor record from the
actor table, but it is actually deleting the cast record from the cast table. So I will
delete this record and click on yes. Notice that I will not be able to undo this delete
operation. Even though it looks like a recycle bin icon, that is a misnomer, because I am
actually deleting the record permanently from my database.
So now that the record is deleted. If I look at the Harry Potter movie, I can see that
James Phelps still appears in my Harry Potter movies. So I have not deleted him from the
database, I have only deleted the cast record that had associated him with the wrong movie.
I can delete that record with a lot less work than what it would have taken otherwise. So
as you can see, creating a form greatly simplifies maintaining and accessing data in your database.
I want to briefly mention one other thing, when we are in design view, and we click on
our subform, and we go to the data tab in properties, we can see these properties: Source
object, link master fields, link child fields. These three properties actually control how
the subform and the form filter the records that appear within the subform so if you decide
to build forms with subforms without using the form wizard, knowing how to use these
properties may become very important.