A Past Visits Form for MFB
We just did a query that joined information from two different
tables in the Missoula Foodbank database. We will do another
query somewhat like this that relates clients with their past
If you want you should download the database for MFB.
|So we start out by developing the query we need. First I will show the QBE design view with the necessary tables, fields and sort.|
|Notice we have three tables that reflect their
relationships between keys and foreign keys.
We also have four fields we want to have available. Though, we could have easily included more.
After saving this query as qry_clients_dependents we execute it to get a resultset. We will use this resultset and query to develop a form that reflects the one-to-many relationship between each MFB client and their dependents. We've also done the join query so that the information will contain much more than just foreign keys.
So now we develop this form using the form wizard. The first step we see in this form is the following.
This will take you to step 2 of the wizard that is illustrated by the following image.
|This step is important because we want to view
the dependents for each client. So we select the
Then you should click on Next.
Because we are trying to reflect a one-to-many relationship you should choose the datasheet view on the next step.
Then you are likely to modify several of the properties on the form that is created in design view as shown in the next image.
|Now that we have this form with subform, we need
to be able to navigate to it from the clients form and back.
This requires us to start by putting a command button on the
clients form. When we do this, it starts up another wizard.
The first step of this wizard asks us what sorts of things we want to do. These are illustrated in the following image.
|So you want to do
then click on the Next button to get the next step.
|We want to open the frm_clients_dependents with
specific data that matches the client in the clients form so
you want to select the option displayed above.
Now you need to select how to determine the specific data so you need to match based on some fields as demonstrated in the next image.
|Specifically, you want to match the client_id
from the clients form with the client_id in the
frm_clients_dependents. This will then display the orders for
each current client in the clients form.
There are some other steps in the wizard, but I have tried to illustrate the most important and the least obvious. For example, you get to choose things like the background and other incidentals.
So finally, you can also adjust the properties on this command button to get a nice look.
When you actually navigate you should see something like the following.
|You also need to place a command button on this form to navigate back to the original clients form. But these steps are much easier than what you've just been through.|