Creating a Custom Save Button for an MS Access Form.

When we use an MS Access form to create a new record, or update one that already exists, the process of saving that data is normally taken care of automatically "behind the scenes."  All we need to do is move to or create a new record, close the form, or close the database, and Access saves the original record for us.

Record Navigation Buttons: when a record is added or changed, moving to a new or different record 

via the navigation buttons, will save the origin form record automatically.

However, there are times when it may necessary to save a record manually.  Suppose, for example, we have two tables joined together in a one to many relationship. We create a new record on a form bound to a table on the one side of the relationship, and then click a button to open a new form bound to a table on the many side of the relationship.  The newly opened form is then used to create a sub-record for the parent record on the original form.

Parent and sub-forms: In this screenshot, the parent form "Work Session" is bound to the tbl_work_session table, and the "Entry Log" form is bound to the tbl_entry_log table.  There is a one to many relationship between the two tables, with tbl_work_session being on the one side of the relationship.

The problem in this scenario is that when we come to close the form for the sub-record (thereby saving the data to the underlying table on the many side of the relationship), the parent record on the original form has not, at this stage, been inserted into the underlying table on the one side of the relationship.  That is to say, Access has not got around to autosaving the parent record since the parent form has not moved to any other record, or been closed etc - all we have done is click a command button to open a new form.  As such we get an error message saying "You cannot add or change a record because a related record is required in [the parent] table"; this is because the rules of referential integrity have been broken (ie the sub-record in the many table requires a parent record in the one table).

However, it is simple to overcome this issue. We just need to save the parent record manually before trying to close the sub-record form.  A quick and easy way to do this is to click the pencil symbol on the top left of the parent-form, or the SAVE icon in the RECORDS group of the HOME ribbon. This will save the parent record, thereby allowing it's sub-record to be saved afterwards.

 The pencil symbol (above left) can be used to save the current record manually.  Alternatively, the SAVE icon in the RECORDS group of the HOME ribbon (above right) can also be used.

However, this is not particularly useful from the perspective of user-friendliness. That is to say, a user may not realise or understand that this action is necessary.  A better way of doing it would be to save the parent record automatically when the user clicks the command button to open the sub record form. As such, we need to alter the open form button (referred to as "CREATE NEW ENTRY" in our example above) so it also becomes a custom save button.  To do this, we just add the following line of VBA code to the buttons ON CLICK event:

Application.RunCommand acCmdSaveRecord

See the screenshot below for the full code:

VBA code to save the parent record before opening the sub-record form.

---

Creating a Custom Save Button from Scratch - A Tutorial

Here is a step by step tutorial for creating a custom save button from scratch on Access for Office 365:

1. Open the form in DESIGN VIEW.
2. Go to the CONTROLS group of the CREATE ribbon.
3. Click the MORE button () to the lower right of the control icons, and de-select the USE CONTROL WIZARDS icon when the additional options appear. 

   

   Above: additonal  options below control icons.

4. Click the BUTTON control icon () from the CONTROLS ribbon. The cursor will now change to a button icon when it enters the design grid.
5. Position the cursor at the desired location on the design grid, and click the left hand mouse button.  This will create the new button at the selected location.
6. Select the new BUTTON contol by left clicking it, and open the PROPERTIES sheet from the TOOLS group of the DESIGN ribbon (if it is not already open).
7. Click the EVENTS tab on the properties sheet, and left click the ON CLICK row of the events tab.
8. Click the ELIPSE button (three horizontal dots) which appears on the right hand side of the row.

   

   Above: the ON CLICK event row on the PROPERTIES sheet.

9. Select CODE BUILDER from the CHOOSE BUILDER dialog form which opens.  This opens the VBA editor.
10. Enter the following code in the CLICK sub procedure for the control button.

Above: The CLICK sub procedure for the new control button shown in the VBA editor.

Now when the user clicks the new button at runtime, any data s/he entered will be saved to the corresponding record in the forms underlying table.

Subforms - Viewing the One to Many Relationship in Action

In my last blog post we learnt how to create a One to Many Relationship between a Customer Table and an Orders Table.  We examined how Access was able to separate groups of orders according to which customer they belonged to.  This was possible due to the creation of a Primary Key Field in tblCustomer and a corresponding Foreign Key Field in tblOrder; and then creating a One to Many Relationship between the two tables. In this post we are going to examine how using a Subform enables us to view this One to Many Relationship in action.

To do this we will begin by creating the main form for the Customer Table (frmCustomer).  This will display each customer record, and corresponds to the One side of the One to Many Relationship.  We will then create a new form for the Orders Table (frmOrders) which will display in Datasheet View.  This is going to be used as the subform contained within frmCustomer.  It will display a list of orders for each individual customer. This corresponds to the Many side of the One to Many Relationship. The finished result will look like this:

The top section of the form shows the Customers Details.
The Subform below show the orders made by that Customer.

If you havn't already done so, you can create the tables and the relationship by following the instructions in the previous post on The One to Many Relationship.

Creating the Orders Form

The first step in this exercise is to create the orders form - that which will be used as the subform. We will call this frmOrders.

1. Select the CREATE TAB on the Access Ribbon.
2. Click the FORM DESIGN icon in the FORMS GROUP of that Ribbon.
   
   
   This opens up a blank Form Design Grid. (For more information about the form design grid, see my post on Creating an Access 2007 form from Scratch).
   
   
3. Select the whole form by clicking the square at the top left hand corner of the FORM DESIGN GRID  (where the horizontal and vertical rulers meet). When you do so, a smaller back square appears in the middle.
   
   
   
   
   
4. Click the PROPERTIES SHEET icon in the TOOLS group of the DESIGN ribbon.  
5. Select the DATA tab on the PROPERTIES SHEET.
6. Set the RECORD SOURCE property to tblOrder.
   
   
   
   
   
7. Click the ADD EXISTING FIELDS icon in the TOOLS group of the DESIGN ribbon.
8. Click the EXPAND button (the + sign in a small box) by tblOrders so the list of field opens out for that table (if it has not already done so).
   
   
   
   
   
9. Select the ItemOrdered, Date, and Price from the FIELD LIST by double clicking each one in turn. As we do so, they appear in the FORM DESIGN GRID like this:
   
   
   
   
   
10. We are now going to change the DEFAULT VIEW property of the form.  So select the FORM again by following step three above (if it is not already selected).
11. Open the PROPERTIES SHEET (if it is not already open).
12. The DEFAULT VIEW property is located on the second line down of the FORMAT tab of the PROPERTY SHEET.  Change the property to DATASHEET by clicking the DEFAULT VIEW field;  then clicking the arrow at the end of the box; and selecting that option from from the drop down list.
    
    
    
    
    We use the DATASHEET option here so that our subform displays as a list within the main form, thereby reflecting the One to Many Relationship that we want to show in action.

You can now close the form, saving it as frmOrders (if you have not already done so).


Creating the main Customer Form


Next we are going to create the main customer form to display the customer details.  This form will also hold the Orders Subform, thereby listing all the orders for each particular customer.  Steps one to nine below correspond to the steps we went through creating the orders form.  Step 10 onwards is new, and deals with the creation of the Subform Control.

1. Select the CREATE TAB on the Access Ribbon.
2. Click the FORM DESIGN icon in the FORMS GROUP of that Ribbon.
3. Select the whole form by clicking the square at the top left hand corner of the FORM DESIGN GRID  (where the horizontal and vertical rulers meet). When you do so, a smaller back square appears in the middle.
4. Click the PROPERTIES SHEET icon in the TOOLS group of the DESIGN ribbon. 
5. Select the DATA tab on the PROPERTIES SHEET.
6. Set the RECORD SOURCE property to tblCustomer.
7. Click the ADD EXISTING FIELDS icon in the TOOLS group of the DESIGN ribbon.
8. Click the EXPAND button (the + sign in a small box) by tblCustomer so the list of field opens out for that table.
9. Select the FirstName, Surname, Address1, City and PostCode from the FIELD LIST by double clicking each one in turn.
10. We are now going to create the Subform.  We are going to do this process manually so begin by deselecting the USE CONTROL WIZARDS icon from the CONTROLS group of the DESIGN ribbon. It is deselected when it is no longer highlighted in orange.
    
    
    
    
    
11. Click the SUBFORM icon in the CONTROLS group of the DESIGN ribbon.
    
    

    

    The SUBFORM icon is on the bottom row,
    highlighted in orange.

    The mouse pointer changes to the Add Subform Icon.
12. Take the Add Subform Icon to a location below the other text fields in the FORM DESIGN GRID, and click.  This creates an empty subform control on your main form.  At this stage it just looks like this:
    
    

    

    The empty Subform Contol is represented in this
     image by the unbound control labelled Child1.

    
    
13. It is a good idea at this point to resize the control to accomodate the subform.
14. Next we are going to set the Subform Control's properties to display tblOrders (which we created above) as the actual subform.  NB you might like to note the distinction here between the Subform Control and the Form which is displayed in that control. The later is a property of the former.
15. Click the Subform Control on the Form Design Grid to select it. The border changes to an orange highlight once it is selected.
16. Click the PROPERTIES SHEET icon in the TOOLS group of the DESIGN ribbon.  This will display the PROPERTIES SHEET for our Subform Control.
17. Select the DATA tab of the PROPERTIES SHEET.
18. Now set the SOURCE OBJECT property to tblOrders.  Notice how Access has filled in the LINK MASTER FIELDS property below to ID, and LINK CHILD FIELDS property to CustomerId.  The Master Field is the Primary Key Field (ID) of tblCustomer, and the Child Field is the Foreign Key field (CustomerId) in tblOrder.  These can be entered manually if necessary.  

This has now configured our Subform Control to display a list of orders for each individual customer.  At this stage you might like to tidy up the form, and enter any labels or dividing lines that you see fit. When you have done so, save the form as frmCustomer, and open it in FORM VIEW to see what we now have.  It should look something like this:

As you can see in the screenshot above, the customer, John Jones has three orders displayed in the Orders Subform.  As you move through each customer record in turn, you will see a different set of orders corresponding to the particular customer being viewed. You will see that each customer in frmCustomer contains the orders we entered when we worked through the exercise in the last blog post on the One to Many Relationship.  So to see the One to Many Relationship in action, just compare your results from frmCustomer with this screenshot of the data held in our two tables:

As such, by using a Subform we are effectively processing information from both related tables at once.  This makes your database application much more user friendly.  Try entering some new orders for the customers in this database, then look at the orders table to see them stored. Notice how Access enters the customerId in the orders table automatically so it know which customer it belongs to.  All this is part of the One to Many Relationship in action.