A Clients Dependents Form for MFB


Some Background.  We just did a query that joined information from three different tables in the Madam Curry database.  First we will do something analogous for the Missoula Food Bank example using a join query on two tables together.  Then we will work with this in a slightly different form and create a form with a subform to illustrate the one-to-many relationship between each client and their dependents.

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
  • past_visits

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

  • client_id
  • last_name
  • first_name
  • address
  • zipcode
  • phone
  • date
  • interviewer_remark

After saving this query as qry_clients_past_visits 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 past visits.  We've also done the join query so that the information will contain much more than just foreign keys.

Since we have just done a couple other form developments that are analogous, we will leave out images for the intermediate steps.  Though, we will definitely describe most of the intermediate steps.

  • In the drop down that selects the data source we choose the qry_clients_past_visits. 
  • 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.

There are some more intermediate steps that should be familiar.  But you still need to be careful.



This step is important because we want to view the past visits for each client.  So we select the
  • by clients


  • Form with subform(s)


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


  • Open Form

then click on the Next button to get the next step.



We want to open the frm_clients_past_visits 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, though we are leaving out any image.

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.

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.