How to Copy Fields to a New Record with DLookup in Access VBA
Question details
The user needs to create a new record in an Access form by copying specific values from the current record using VBA and the DLookup function, but is encountering compile errors.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Clicking a form button to duplicate selected fields into a newly created record via VBA code.
- Observed behavior
- The VBA code triggers compile errors, and occasionally the application closes unexpectedly. This typically occurs because field names containing spaces are not properly bracketed or form controls are incorrectly referenced.
Always create a backup copy of your Access database before modifying or testing new VBA code, as unresolved syntax errors can sometimes cause the application to close unexpectedly.
Correct the DLookup Syntax and Form Control References
Resolve compile errors by properly enclosing field names with spaces in square brackets and using the 'Me' keyword to reference form controls.
In Microsoft Access VBA, spaces in field names or table names break the code syntax unless they are wrapped in square brackets. Additionally, explicitly referencing your form's controls ensures that VBA retrieves the correct values during runtime.
Locate any field names in your DLookup statement that contain spaces, such as 'Procurement Contact E-Mail' or 'Complete Bids List'.
Enclose these field and table names in square brackets so Access processes them as a single entity. The updated string should look like this: DLookup("[Procurement Contact E-Mail]", "Complete Bids List", "ID=" & currentID).
Prefix your form controls with the 'Me' keyword (for example, Me.Company_Bidding). This prompts IntelliSense to recognize the control and ensures the code explicitly targets the current active form.
In your button's OnClick event, write the VBA logic to go to a new record (using DoCmd.GoToRecord) and assign the retrieved DLookup values directly to the corresponding form controls.
Use an SQL Append Query Alternative
If copying multiple fields via DLookup becomes too complex or slow, use an SQL INSERT statement executed via VBA.
Looking for a Free Alternative to Microsoft Office?
While Microsoft Access is a powerful database tool, many data tracking, data entry, and VBA automation tasks can be efficiently handled using robust spreadsheets. WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office, complete with advanced spreadsheet features and macro support.
Frequently Asked Questions
Why do I get a compile error when using DLookup with spaces in the field name?
Access VBA misinterprets spaces in field or table names as the end of a variable or command. You must enclose names containing spaces in square brackets, such as [Procurement Contact E-Mail], to tell the compiler to treat the entire phrase as a single identifier.
What does the 'Me' keyword do in Access VBA?
The 'Me' keyword acts as a shortcut that refers to the specific instance of the class or form where the VBA code is currently executing. It helps prevent naming conflicts and triggers IntelliSense to display a dropdown of available controls, like Me.ControlName.
Why did my Access database close unexpectedly after fixing a VBA error?
This can happen if the database state becomes corrupted in memory or if an unhandled error triggers a fatal crash in the Access runtime. Compiling the database via the VBA editor (Debug > Compile) and using the 'Compact and Repair' utility often resolves these stability issues.
