Workshop 1: Access VBA, Design and Advanced Methods

Demonstrate building a read-only network database that lets users view data through a form while preventing any edits via VBA password protection.

Download the customers database, create a read-only access database, import the table and form, and convert the record source to a distinct SQL query to prevent edits.

Create an open subroutine for a form, assign the record source to a networked MS Access database using the on open event and code builder, including database path and password.

Lock the VBA project with a password to make the database read-only and prevent edits; compile to an executable and set the startup form to the customer list.

Open and examine the AddToList database by implementing a quick search combo box, adding records on the fly, and navigating records with the keyboard and filters.

Fix data quality in Access by creating a temporary fix table, making a unique values query, and running an update to standardize city names.

Create a quick search combo box in the form header, resize in overlapping windows, and configure the row source to show last name, first name, city, and state.

Modify the new combo box so selecting an item fills the form, using got focus and code builder. Debug with break key and fix the cell cust field name.

Learn to fix the combo box so it respects state, city, and name filters by updating the row source and SQL in Access VBA, with debugging tips.

Identify and fix a broken pull-down by debugging the row source SQL, using the immediate window to inspect the query, and correcting the where clause before the order by.

Add an after update routine for the select customer control to locate the record by fourth column customer id, enabling up-down navigation and a not-in-list flow to add new customers.

Learn to implement the not in list subroutine in Access VBA, handle input errors, enforce limit to list, and enable on-the-fly customer creation via a form.

Enhance the not in list routine by parsing comma separated names with in string and mid, splitting into last and first names, then add the record via recordset clone.

Open the customer form in dialog mode from the main form using cust ID as open args, then requery the recordset and set focus to last name in proper case.

Learn to prevent adding a customer when filters are active by checking the combo box row source in the not in list event, and prompting users to clear all filters.

Explore linking a customer form to its orders in Access, using a two-order button on the subform and top-form navigation to view related records.

Modify the customer form, replace the embedded macro with a VBA to order button on the main form, and pass the sub form's order number via temp vars.

Complete the to order button by passing the order number and customer ID to the orders form, filtering by order number when defined, otherwise by the customer ID.

Modify the to order button to detect the previous control on the main form or sub form using screen and control.name, displaying all customer orders or a specific order.

Modify customer form open routine to remove temp var on exit and go to sub form when a sub order number exists, and define sub form enter routine.

Introduce a custom subroutine in the subform to jump to a specified order by number, using a recordset find first, bookmark, and a forms syntax call from the main form.

Learn to inspect and modify table structures in Access by creating a processors table, populating it via an append query, and configuring a lookup combo to assign loans to processors.

Create a loan assignment form in Access VBA, select needed fields, apply a tabular layout, add an exit button, and prepare a query for the next processor ID.

Create a query to get the next processor id by using each processor's max assign date, sorted ascending to find oldest, and use a right join to include all processors.

Add an assign button (assign btn) to the form and code its on-click to verify assign date and processor ID; if unassigned, fetch them with now, close recordset, and refresh.

Adjust the access form data source to sort unassigned loans by assign date and loan number. Add an inactive flag to processors and use sql to exclude inactive processors.

Explore Access VBA design and advanced methods, reviewing SQL tricks like SELECT DISTINCT and row source, with password protecting VBA and not in list handling. Use combo boxes, query builder, and dialog pop-up forms to manage data securely and efficiently.