In the previous post, we demonstrated a simple way in which developers can get into trouble whilst data modelling. We had to create a many-to-many relationship between Films and Actors which involved creating a linking table and utilising two one-to-many relationships. So, how do we enter data in the linked table? In this post we will show you the best system for entering data in a table that serves as a linking table for a many-to-many relationship.
If you would like to follow along with this post please download the database below.
In the database, we have created a form called frmFilmsDataEntry. It is a simple data entry form and is bound to the Films table.
In order to work with linkFilmsActors (the table that links Films and Actors) we are going to embed a sub-form in frmFilmsDataEntry. Let’s create the sub-form.
Click once and highlight linkFilmsActors in the navigation pane:
Click on Datasheet in the Create>Forms>More Forms section of the Ribbon:
You will have something that looks like this:
Change the datasheet to design view.
Now we are going to show you how to create the necessary relationship…
Using a sub-form bound to a main form is the best way to create a structure that enables you to enter data into a link table. The process is logical for the end-user and the linked field in the sub-form is hidden so no mistakes can be made.
I know you will answer this later – I will ask anyway.
A combo box of films and actors can be huge. Which makes this way
less desirable. I suppose not understanding the under-workings of access is causing most of my confusion.
Cheers
For a large dataset you could try creating a text box into which you can type your search critieria and then run a “Like” query on the table with the data in.
You can do this using VBA or by running a simple query.