Step Eight - Price and Quantity Form

We are going to create a another new form. This form will display information about the price of a product and details regarding quantity. Here is a general overview of what will be done:

  1. Place a Farmer Joe's Market title at the top of the form.
  2. Place a few fields from our table on the form.
  3. Create a Text Object that allows us to enter a number in.
  4. Create a Text Object that contains a calculation.
  5. Create a button to save the results.

The goal of the form is to show the cost price, mark up rate and retail costs. We will also show the quantity on hand. The user will also be able to enter the amount of new stock that arrives.

Creating the Form

To create a new form, click the Forms tab in the main window and click the New button.

We are going to use the Design View.

  1. Click the Design View in the list.
  2. Click the down arrow and select "tblProducts" from the list.
  3. Click the OK button.

Let's save the form right away.

  1. Click the Save button or select File, Save from the menu.
  2. The Save As window appears. Type in "PandQInfo".
  3. Click the OK button.

Creating a Title for the Form

We are going to create a title for our form that says "Price and Quantity Info:".

  1. In the Toolbox, click the button. The mouse pointer will change to .
  2. Click once near the top of the form. A blinking cursor will appear.
  3. Type in "Price and Quantity Info for:"
  4. Press the Return key or click once anywhere else on the form.

You can spruce it up by changing the font to Arial, change the size to 14 and underline the title.

Placing Fields from tblProducts table on Form

We are going to include the following fields from our table: ProductName, CostPrice, MarkUp, QuantityInStock.

  1. If the Field List box is not showing, click the Field List box button on the toolbar.
    The Field List box should be showing and list the fields in the Products table.
  2. In the Field List box, click and hold on ProductName.
  3. Drag the mouse below the title and release the mouse button.
  4. Delete the Label Object associated with ProductName. Click the Label Object to the right and press the Delete key on the keyboard.
  5. In the Field List box, click and hold on CostPrice.
  6. Drag the mouse to the left part of the form and release the mouse button.
  7. In the Field List box, click and hold on MarkUp.
  8. Drag the mouse below the CostPrice field and release the mouse button.
  9. In the Field List box, click and hold on QuantityInStock.
  10. Drag the mouse to the right of the CostPrice field and release the mouse button.

More than likely you will have to rearrange and move the fields around. Try and move them around so they look like this:

If you wanted to clean this up some, you could edit the Label Objects associated with the Text Objects (i.e. put in correct titles, spacing, etc).

Adding "Quantity Received" Text Object

We are now going to add a Text Object that will allow the user the enter in the amount of new stock that was received for the particular product.

  1. In the Toolbox, click the button. The mouse pointer will change to .
  2. Click once to the right of the MarkUp field.
  3. If the Properties box is not showing already, click the Properties button on the toolbar.
  4. In the Properties window, in the Name box, highlight and delete what is in there. Type in "QuantityReceived".
  5. In the Properties window, click once in the Format box. Click the down arrow and select "General Number" from the list.

Now we have to change the Label Object to the right of the field.

  1. Select the Label Object.
  2. Move the mouse pointer on the Label Object. When the pointer changes to click the left mouse button once. This places you in the editing mode.
  3. Use the arrow and backspace keys to remove the text currently there.
  4. Type in "Quantity Received".

Adding "Retail Price" Text Object

We are now going to add a Text Object that calculates the Retail Price of the product by multiplying the CostPrice by the MarkUp rate.

  1. In the Toolbox, click the button. The mouse pointer will change to .
  2. Click once below the MarkUp field.
  3. If the Properties box is not showing already, click the Properties button on the toolbar.
  4. In the Properties window, in the Name box, highlight and delete what is in there. Type in "RetailPrice".
  5. In the Properties window, click once in the Control Source. Type in

    =[CostPrice]*(1+[MarkUp])

    This is the calculation that gives the Retail Price. Normally the contents of the Control Source is the field name. You can place calculations in the Control Source as long as you start them with an "=" sign. Also, remember that any fields to be used from the table associated with the form must be spelled the exact same way as they are in the table and must be include in square brackets.

  6. In the Properties window, click once in the Format box. Click the down arrow and select "Currency" from the list. This displays the number with a dollar sign and two decimals.

Now we have to change the Label Object to the right of the field.

  1. Select the Label Object.
  2. Move the mouse pointer on the Label Object. When the pointer changes to click the left mouse button once. This places you in the editing mode.
  3. Use the arrow and backspace keys to remove the text currently there.
  4. Type in "Retail Price".

Adding "Total Quantity" Text Object

We are now going to add a Text Object that will add the QuantityInStock with the QuantityReceived to give us the TotalQuantity in stock. This number will saved later in the QuantityInStock field.

  1. In the Toolbox, click the button. The mouse pointer will change to .
  2. Click once below the QuantityReceived field.
  3. If the Properties box is not showing already, click the Properties button on the toolbar.
  4. In the Properties window, in the Name box, highlight and delete what is in there. Type in "TotalQuantity".
  5. In the Properties window, click once in the Control Source. Type in

    =[QuantityInStock]+[QuantityReceived]

    This is the calculation that gives the Retail Price. Normally the contents of the Control Source is the field name. You can place calculations in the Control Source as long as you start them with an "=" sign. Also, remember that any fields to be used from the table associated with the form must be spelled the exact same way as they are in the table and must be include in square brackets.

  6. In the Properties window, click once in the Format box. Click the down arrow and select "General Number" from the list.

Now we have to change the Label Object to the right of the field.

  1. Select the Label Object.
  2. Move the mouse pointer on the Label Object. When the pointer changes to click the left mouse button once. This places you in the editing mode.
  3. Use the arrow and backspace keys to remove the text currently there.
  4. Type in "Total Quantity".

Let's see what is there. To switch to the form view, click the View button on the far left of the toolbar.

There is a small problem right now. If you look at the Quantity Received field, there is nothing in there. Without having a value in that field, the Total Quantity field will not appear. Another problem that is once you enter a value in Quantity Received, once you switch to another record, this value carries over. This would be very hazardous in our situation where we are tracking inventory.

In the next section, we will look at how we can fix this problem.

Click the Save button or select File, Save from the menu.

[Back]

[Home]

[Forward]