Workshop 6: Access VBA, Design and Advanced Methods

Create custom shortcut menus for form controls in Access VBA, linking them to a subroutine and leveraging right-click options to sort, filter, and find records.

Copy and paste the customer form, disable default shortcut menus on open, and create a subroutine in Access VBA to build the shortcut menu for controls such as customer ID.

Build a shortcut menu subroutine in Access VBA by creating an object-based menu, adding commands, and handling errors with On Error Resume Next, avoiding Office object library version issues.

Learn to build a temporary shortcut menu in Access VBA by creating a popup menu with buttons, assigning command IDs, enabling show popup, and managing menu name scope across subroutines.

Learn to design VBA menus using the with statement, create grouped commands for sort, find, and filter by selection, and enable menu behavior across different controls.

Modify menus by making the open routine create them, and add a VBA command that calls a show my message subroutine on the customer form.

Enable a three-state checkbox in Access by turning a yes/no field into a numeric value and using a switch expression to show yes, no, or unknown.

Explore creating a searchable list box with VBA, using an instant search linked to the customer form and key down handling for tab and enter via ASCII codes.

Modify the customer form in Access VBA by implementing key down logic that converts keypad input to ASCII codes and configure list boxes with a table query as row source.

Start customizing the listbox in Access VBA by creating a dedicated keydown routine and using listbox.selected(0) for the first, and list count minus one for the last.

Expand the listbox keydown routine by creating a shared list search and a locate subroutine to determine the current list position while handling letters and numbers via ascii codes.

Continue building the listfind function by handling matches with a recordset, updating the list position, and ensuring top item focus; close records and manage variable scope for next lecture.

Implement backspace in Access VBA by using a subroutine to trim the last character with Mid, map backspace to ASCII 8, and update the list search.

Configure a listbox in access vba to show customer columns id and name from a table query, update the row source, and use a recordset clone to jump to matches.

Learn how snapshots in Access protect data and boost network performance by displaying fixed data while disabling edits, and compare to Dynaset for editable, live data.

Hide objects in the navigation pane to reduce clutter and speed up Access. Mark items as hidden, toggle show hidden objects in navigation options, and bring them back when needed.

Create and manage custom navigation groups in Access by selecting object types, building an Inv group for order print and unassigned items, then drag and drop to organize objects efficiently.

Finish the partially completed database by enabling the subform to auto-calculate the remaining budget for each line as expenses are entered, and enable delete/add/edit with automatic recalculation on main form.

Create a running balance in the subform by filtering to the current job sequence and job id, then compute remaining budget as budget minus expenses.

Link the remaining budget field on the main form to the subform’s total expenses in Access VBA, compute remaining budget as budget minus total expenses, and trigger recalculations after update.

Review custom shortcut menus, including a customer ID menu and tri-state fields with a searchable list box linked to forms via VBA, and note running balance recalculation and design tips.