A Past Visits Form for MFB

 

Some Background.  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 visits.

If you want you should download the database for MFB.

 

mfb_clients.mdb

 

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.
  • clients
  • dependents

We also have four fields we want to have available.  Though, we could have easily included more.

  • last_name
  • first_name
  • address
  • zipcode
  • phone
  • child_name
  • age

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.

 

 

  • In the drop down that selects the data source we choose the qry_clients_dependents. 
  • Then we move all of the seven fields over to the right so they will all be available for the form. 
  • Then you should press the Next button.

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
  • by clients

and

  • Form with subform(s)

option.

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
  • Form Operations

and

  • Open Form

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.