How to Temporarily Remove and Restore Access Form Record Sources
Question details
The user needs to dynamically remove the record sources from open Microsoft Access forms to perform backend table replacements, and then restore the original record sources while preserving the exact screen positions of the forms.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A database developer needs to release table locks by clearing the RecordSource of open forms via VBA, replace the underlying table, and rebind the forms without losing the users' UI layouts.
- Observed behavior
- When changing the record source, the form's screen position, current record position, or active filters may be lost. Additionally, VBA errors occur when trying to write to the WindowLeft and WindowTop properties.
Before running VBA scripts that modify form properties or replace backend tables, ensure you have created a complete backup of your Access database to prevent accidental data loss or corruption.
Use VBA to Loop Through Forms and Store Properties in Variables
Capture the RecordSource, WindowLeft, and WindowTop properties of all open forms into memory before making backend changes, avoiding Design View modifications.
Instead of opening forms in Design View (which can cause locking issues in a multi-user environment), loop through the active Forms collection using VBA at runtime.
Because WindowLeft and WindowTop are read-only properties, you must read them into variables and later restore the form's position using the DoCmd.MoveSize method.
In your VBA module, declare arrays, a Collection, or a custom class to store the Name, RecordSource, WindowTop, and WindowLeft properties of each open form.
Use a 'For Each' statement to loop through the Access.Application.Forms collection.
For each form, save its current properties to your variables, then set its RecordSource property to an empty string ("") to release the table lock.
Execute your backend table replacement or update procedures while the forms are unbound.
Loop through your variables, set each form's RecordSource back to its original SQL string or table name, and use 'DoCmd.MoveSize WindowLeft, WindowTop' to restore its exact screen coordinates.
Preserve the Current Record Position and Filter
If resetting the RecordSource causes users to lose their active record or applied filters, capture these specific properties alongside the screen position.
Simplify Your Document Management with WPS Office
While Microsoft Access is a specialized database tool, you often need to export database reports to spreadsheets or text documents. WPS Office is a highly compatible, free alternative to Microsoft Office that easily handles your exported data, spreadsheets, and presentations.
- 1. Download WPS Office: Visit the official WPS website to download the free installation package for your Windows, Mac, or Linux device.
- 2. Open Exported Data: Launch WPS Spreadsheet and seamlessly open any .csv or .xlsx files generated by your database.
- 3. Analyze and Report: Use pivot tables, formulas, and charts in WPS Spreadsheet to visualize and analyze your database extracts without compatibility issues.
Frequently Asked Questions
Why do I get a VBA error when trying to set WindowLeft and WindowTop?
WindowLeft and WindowTop are read-only properties in Microsoft Access VBA. You can read them to find out where a form is located, but to change a form's position, you must use the DoCmd.MoveSize method.
Does changing a form's RecordSource trigger VBA events?
Yes. Modifying the RecordSource at runtime can trigger form events such as OnLoad, OnCurrent, and OnResize. Ensure your event handlers include error checking to handle scenarios where the RecordSource is temporarily blank.
Is it better to open Access forms in Design View to change properties?
No. Programmatically opening forms in Design View in a multi-user environment often leads to locking errors and UI flickering. It is a best practice to modify properties like RecordSource dynamically at runtime.
Can I use this method for subforms?
Yes, but you access a subform's RecordSource through the Form property of the Subform control (e.g., Me.SubformControlName.Form.RecordSource). Subforms do not have their own screen position independent of the main form.
