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.