Workshop 2: Access VBA, Design and Advanced Methods

Explore setting subform colors in Access VBA, using RGB values and hexadecimal codes via the color builder. Discover how colors reset when navigating between subforms and the main form.

Learn to set a subform header back color in Access VBA using on exit and on enter events, RGB and hex values, and form references.

Finish placing codes and final testing guides you through adding on enter and on exit subroutines in Access VBA, converting hex colors to RGB, and preparing for queries.

Create year over year comparisons by building a sales by quarter query, then compute last year sales and the difference to reveal quarterly trends.

Create a make-table query on the sales table to generate a sales by quarter table from year and quarter extracted from report date, then sum sales by year and quarter.

Learn to build a get last year query in Access by using DLookup to pull total sales from a quarterly table, apply year minus one, and format results as currency.

Format the last year sales field to the right using the format property with currency. Then compute the sales difference by subtracting last year sales from total sales.

Add running totals and page totals to an Access report using VBA, initializing values in the report header and page header, and updating them on each detail print.

Learn how to set the starting auto number for the customer id and append existing customer data using a design/append query in Access, matching fields and handling gaps.

Open the Back to Record database, navigate orders with the top two order buttons, and return to the correct subform; exiting preserves the subform position for later reopening.

Use access vba to enable the customer form and log exit values by storing customer id and order number in a dedicated table, with subform context detection.

Modify the open routine to use values from the last custom table, set temp vars for customer ID and sub order, validate the recordset, and clear the table.

Explore the call list design challenge in Access, redesigning customer and contact data into a one-to-many structure with unlimited contacts and a sequential rotation system.

Learn to add VBA code to the edit button, toggle edit mode, and refresh the form, while using a separate sequence field to sort subforms reliably.

Build a VBA function to test for no contact records on a customer, return a value, and gate the add button, using a recordset clone and D functions for sequencing.

Create a subroutine to renumber the sequence field for all records after deletions, ensuring the call list remains ordered by looping through the record set and updating each sequence.

Implement delete button logic in the access vba interface by recording the current sequence, confirming deletion, performing the delete, renumbering the sequence, and repositioning the subform to the current record.

Build the change to function by setting the combo box row source to the current customer’s sequences in ascending order via the query builder, on got focus.

Build the lost focus and start the after update sub in Access VBA to renumber sequences, validate selections, and outline the renumbering logic with Excel visualization.

Discover a step-by-step approach to renumbering in Excel that demonstrates zeroing a target item, resorting, and applying a decrease sequence before introducing an increase sequence to finalize updates.

Develop the decrease sequence subroutine to decrement sequence values from the end of the recordset, starting by setting the sequence to zero and using the old sequence as stopping value.

create the increase sequence subroutine in access vba, updating the sequence field by locating the current record, incrementing the sequence, applying changes, and reordering the recordset.

Test and debug an Access VBA after update routine by stepping through code with F8 and F5, using breakpoints, and resolving sequence errors for reliable data updates.

Perform final testing and debugging in Access VBA by stepping through code, simulating errors, renumbering sequences, and verifying fixes through hands-on trial runs.

Review subform colors with hexadecimal codes and enter/exit events, and apply year-over-year comparisons via queries, plus running totals and print events for totals and auto numbers.