Northwind 2.0 Starter Edition: employees

These are important details about using the employee list form:

• Starting on the main menu, selecting Employees from the menu on the left displays a list of employees (sfrmMainMenu_EmployeeList) in the workspace on the right.

• Each employee’s ID is a hyperlink to open that employee’s record in a popup form to edit their details.

• To add a new employee, click the Add Employee button on the far right of the header row which opens a new employee record in a popup form (sfrmEmployeeDetail_OrderList) to add their details.

These are important details about using the employee details popup form:

• The employee details popup form (frmEmployeeDetail) enables editing and adding employee records.

• Three fields are required for all employees: First Name, Last Name, and Job Title.

• Default Access error messages alert you when one or more is not completed before trying to save the employee record. No validation is applied to other fields, such as email address and phone numbers. Users are responsible for proper formatting, such as (000) 000-000. Default Access validation on the dropdown control for Titles limits entries to a list of pre-selected values. You can add new Titles, again, using default Access behavior for value field controls. Selecting the dropdown on the form displays the edit icon.
  
  

• Employee pictures are stored in the table as an attachment field. You add new images, or change existing images, using the default Access behavior for attachment fields.
  
  

• If an employee has created orders, the subform on the right side of the employee detail popup lists those orders in Datasheet View.

• Any existing order for that employee can be edited by clicking the hyperlinked Order ID # in the Orders subform (sfrmOrderDetails).

The employee forms use both VBA and embedded macros in controls on forms to perform basic logic. They contain two stand-alone macros and several embedded macros.

Employee List

• The Employee List form (sfrmMainMenu_EmployeeList) gets its data from a simple query qryEmployeeList (from the RecordSource property). Basing a data entry form on a one-table query is a recommended practice.

• Controls on the employee list form are enabled, meaning you can select them, and locked, meaning they are read-only.

• The Title field, which is based on a multivalued field, is a dropdown or combo box. Its row source is a value list, which means the optional items available are contained directly within that field. The list has the Limit to List property set to "Yes" because this form is not intended for editing. The multivalued field is included only as an illustration of the feature, not as a recommendation.

• In employee list (sfrmMainMenu_EmployeeList), two Sub procedures on Key Up and Key Down events respond to a user’s cursor movements in the Datasheet View. They detect the direction of a cursor movement (left, right, above, or below) and call VBA functions that move the focus to the appropriate control.

• You edit existing employee records and add new ones in the details form (frmEmployeeDetail).

• You can open the employee details form from the employee list as an editable popup form in either of two ways:

  • By selecting an EmployeeID, which is formatted to appear as a hyperlink, opening the employee details form, and filtering it to only that employee record.

  • By selecting the Add Employee label on the form header to open the employee details form and move focus to a new record. All current records are in the form’s record source.

  Note that the two approaches can have different impacts on performance. Filtering a form to a single record minimizes the amount of data to be transferred from the table. With a small recordset, the difference may not be noticeable.

Employee details

• The employee details form (frmEmployeeDetail) gets its data from a simple query, qryEmployeeDetails (from the form’s RecordSource property). Basing a data entry form on a one-table query is recommended practice.

• The SELECT clause in qryEmployeeDetails uses the asterisk wildcard character (*) to select all fields in the table, whereas the SELECT clause in qryEmployeeList lists specific fields. Using the wildcard character allows new fields in an underlying table to be retrieved with no additional editing of the query. This is not possible with a list of specific fields. A list of specific fields restricts the recordset to only those fields needed in the display only form. Your design should be based on consideration of which approach is more appropriate to your requirement, although preference should go to the approach least likely to require future maintenance, which is the use of the wildcard character.

• Controls on the employee details form are enabled and not locked, meaning you can select as well as edit them.

• The Title field, which is based on a multivalued field, is a dropdown or combo box. Its row source is a value list, which means the optional items available are contained directly within that field. The list has the Limit to List property set to "No" because this form is intended for editing. Adding or editing titles uses the built-in Access method, so no code is required. The multivalued field is included only as an illustration of the feature, not as a recommendation.

• You can edit existing employee records and new ones in the details form.

• You can open the employee details form from the employee list as an editable popup form in either of two ways:

  • By selecting an EmployeeID, which is formatted to appear as a hyperlink, opening the employee details form and filtering it to only that employee record.

  • By selecting the Add Employee label on the form header to open the employee details form and move focus to a new record. All current records are in the form’s record source.

• Filtering a form to a single record minimizes the amount of data to be transferred to the form from the table. With a small recordset, such as the sample data, the difference may not be noticeable.

• The subform control (sfrmEmployeeDetail_OrderList) to the right of Personal Information displays orders for the currently selected employee (if any). The subform in the subform control is re-purposed from the Orders section. The built-in Access LinkMasterFields/LinkChildFields properties of the subform control synchronize and filter records displayed for each employee.

• In employee details (frmEmployeeDetail), two Sub procedures on the form’s On Current Event and On Close Event respond to those events:

  • On Current shows or hides the Add Employee label depending on whether the current record is an existing one, or when it is a record for a new employee, started but not yet saved.

  • On Close performs two maintenance actions. It re-queries the Recordset in the employee detail subform to update any additions or changes made in the details form. It re-synchronizes the display of employee counts in the main menu by calling that macro.

Macros

macMainMenu_UpdateSubs       This macro is stand-alone and runs when certain events occur, including the employee forms. Within macMainMenu_UpdateSubs, submacros are grouped according to the features to which they apply. For employees, the submacro synchronizes employee record count with the displayed count on the main menu.

Embedded macros    Employee forms are primarily driven by embedded macros contained within the form and editable only from the form. In the employee list (sfrmMainMenu_EmployeeList):

• EmployeeID hyperlink       Clicking the EmployeeID in the employee list form opens the Employee Detail form and sets focus to the selected employee record.

• Add Employee label      Clicking the Add Employee button opens the employee detail form (frmEmployeeDetail) and sets focus to a new record.

• Employee details           Clicking the Add Employee button on the header saves the current record and sets focus to a new record.