A Purchasers Order Form for MadamCurry

 

Some Background.  We just did a query that joined information from three different tables in the Madam Curry database.  Now 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 purchaser and their orders.

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.
  • purchasers
  • orders
  • books

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

  • bill_first_name
  • bill_last_name
  • title
  • date_of_purchase

You should also notice that we are sorting in ascending order by the bill_last_name.

After saving this query as qry_purchasers_orders 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 purchaser and the books they've ordered.  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_purchasers_orders. 
  • Then we move all of the four 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 orders for each purchaser.  So we select the
  • by purchasers

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 purchasers form and back.  This requires us to start by putting a command button on the purchasers 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_purchasers_orders with specific data that matches the purchaser in the purchases 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 id_purchaser from the purchasers form with the id_purchaser in the frm_purchasers_orders.  This will then display the orders for each current purchaser in the purchasers 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 purchasers form.  But these steps are much easier than what you've just been through.