How to Use an Access Form Button to Duplicate Records Without Refreshing
Question details
The user wants to add or duplicate specific records in a Microsoft Access form using a command button without triggering a form refresh or relying on a continuous form.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Entering multiple related records (such as keeping the same part number but changing the quantity) seamlessly using a VBA command button.
- Observed behavior
- Instead of using a continuous form layout, the goal is to save the current form data, generate a new record, and retain selected values via a single button click.
Ensure you have design-view access to your Microsoft Access database and that macros or VBA code execution are enabled in your Trust Center settings.
Use VBA and DefaultValue to Duplicate Specific Form Values
Assign a VBA click event to a command button to seamlessly save the current record, copy specified values, and move to a new record.
This method utilizes the DefaultValue property to temporarily store the values you wish to carry over to the new record. Once the new record is created, these values are populated automatically without refreshing the entire form.
Open your form in Design View. Go to the Form Design tab, select the Button control, and draw it on your form. Cancel the wizard if it appears.
Right-click the newly created button, select 'Build Event', choose 'Code Builder', and click OK to open the VBA editor.
Inside the click event procedure, insert the code `DoCmd.RunCommand acCmdSaveRecord` to ensure the active record is saved first.
Store the current control values to their DefaultValue properties. For example, use `Me.PartNumber.DefaultValue = """" & Me.PartNumber.Value & """"` to temporarily hold the part number.
Use `DoCmd.GoToRecord , , acNewRec` to navigate to a new blank record. Afterwards, clear the DefaultValue properties (e.g., `Me.PartNumber.DefaultValue = ""`) so future manual records start entirely blank.
Assign Values Directly via Variables After Creating a New Record
Alternatively, you can assign VBA variables to hold the current values, jump to the new record, and write the values directly into the fields.
Looking for a Lightweight Alternative for Your Office Tasks?
While WPS Office does not include a relational database program like Microsoft Access, it offers a powerful, free alternative for Word, Excel, and PowerPoint with advanced VBA and macro support for your complex data management and spreadsheet needs.
- 1. Download WPS Office: Download and install the free WPS Office suite from the official website.
- 2. Open WPS Spreadsheet: Launch WPS Spreadsheet to manage tabular data efficiently using built-in data forms and advanced formulas.
- 3. Enable the Developer Tab: Access the Developer tab to write custom VBA macros and automate your daily data entry workflows.
Frequently Asked Questions
Why does my Access form refresh entirely when clicking a button?
By default, certain macros or a Requery command will refresh the entire form or database. Using targeted VBA commands like DoCmd.GoToRecord ensures you only move to a new record without triggering a full visual refresh.
Can I copy only specific fields like Part Number but leave Quantity blank?
Yes. In your VBA code, simply set the DefaultValue or variable assignment only for the controls you want to duplicate. Any controls ignored in your VBA script will remain perfectly blank on the new record.
What is the difference between a continuous form and a single form for data entry?
A continuous form displays multiple records simultaneously in a scrolling list format, whereas a single form displays only one record at a time. The VBA duplicate button method is ideal for single forms to maintain a clean interface while quickly generating sequential data.
