Workshop 3: Access VBA, Design and Advanced Methods

Explore using send keys in Access VBA to automate keystrokes, learn curly-bracket notation for special keys, and compare cinches with a safer alternative for targeting focus and the end.

discover how to use SelStart instead of SendKeys to position the insertion bar at the end of the comment box, with a single line and proper selection properties.

Refine the close button prompt in an Access VBA form by comparing a saved temp comment with the current comment, preventing unnecessary updates and prompting only when changes occur.

Learn how to use selstart and sellength to control text selection in access form text boxes, implement on click events, and tailor highlighting for address and phone fields.

Create a module to implement a stopwatch in Access VBA, using temp vars to track start time and date difference for elapsed minutes and seconds, then display the elapsed time.

Finish stopwatch in VBA by resetting start time to zero and computing elapsed time in minutes and seconds, and call the stopwatch from a macro using auto keys for shortcuts.

Create an Auto Keys macro in Access using sub macros and send keys syntax, enabling control t keystrokes to run a stopwatch.

Generate random values to populate the customer type field in a table, assigning one of three types (retail, online, wholesale) using a simple randomization method.

Run the plug customer types function to quickly generate and update customer type data in Access VBA, using a fast update query, random values, and repeated resets.

Discover how to build a three button form in Access VBA that presents three choices plus cancel, passes button captions back, and opens in dialog mode to speed data entry.

Create the open subroutine for a three-button form in Access VBA, validating form caption, user prompt, and button values, displaying messages when missing, and wiring click events for buttons 1–3.

Create an add customer button on the Access VBA form, configure allow additions and editing, and test the modal customer dialog with prompts and debugging.

Clean up the three button form by applying a thin border, disabling navigation and close controls, and resizing the text box to fourteen points, preserving its saved position.

Finish the add customer subroutine by wiring the three-button form to create a new record, set cust type with select case, and focus on company for quick data entry.

Back up the customer table, create a fixed phone worktable in Access, and build a fixed phone function to clean and validate numbers, handling dashes and parentheses.

Develop an Access VBA fix phone function by looping through each record's phone field, extracting digits with mid and ascii checks (48–57), and building a new cleaned phone string.

Finish the fixed phone function by editing and setting the new phone field from the new phone variable inside the loop, updating the recordset, and testing with a macro.

Learn to clean and update phone data in Access using update queries, the mid function, and a true/false mark workflow to refresh the customer table.

Turn the fixed phone tool into a generic utility that runs a make table query and related queries on any table with phone, new phone, and mark fields.

Review key VBA concepts, including sin keys, cell start, time function, date difference, auto keys, sub macros, and random value generation for records.