VBA & Macro Problems

Fix MS Access Form Stops Writing Records to a Table via VBA

WPS Content Manager Oct 12, 2026 869 views

Question details

An MS Access form using VBA to insert records and retrieve identity values fails, and the current error handler only displays a generic message without specific details.

How to Fix MS Access Form Not Writing Records to Table

Product: Microsoft Access
Device & OS: not provided
Scenario: A user is attempting to insert data into an Access table via a VBA form, but the INSERT statement fails to execute and retrieve the new identity value.
Observed behavior: The database silently stops writing new records, and the VBA error handler suppresses the actual system error, making it difficult to identify the root cause.

Before you start

Ensure you have access to the VBA IDE (press Alt + F11) and that your Access database is not opened in 'Read-Only' or 'Exclusive' mode by another user on the network.

Solution 1Recommended

Enhance the VBA Error Handler and Debug the SQL Statement

Modify the VBA error handling routine to expose the exact error number and description, and print the generated SQL string for manual testing.

Generic error messages often mask the underlying issue. By exposing the true error details and capturing the exact SQL string the form generates, you can identify syntax errors, missing fields, or data type mismatches.

Update Error Handler

In your VBA code, change your generic message box to include specific error details. Modify it to read: MsgBox "Error " & Err.Number & ": " & Err.Description.

Enable Debug.Print

Add Debug.Print strSQL (replace strSQL with your actual query variable) right before the Execute command in your script.

Open Immediate Window

Press Ctrl + G in the VBA editor to open the Immediate Window. Trigger the form action again, and copy the generated SQL string that appears.

Test SQL in Access

Go to Create > Query Design in Access, close the tables dialog, switch to SQL View, paste the copied SQL statement, and click Run to reveal exact constraint or permission errors.

Enhance the VBA Error Handler and Debug the SQL Statement

Identify Hidden Issues: Running the raw SQL manually in Access often directly highlights structural issues like Primary Key constraint violations or Null values in required fields.

Solution 2

Verify External Data Sources and Import Processes

If your form relies on importing external files before writing to the table, verify the external file's location, content, and formatting.

Free Microsoft Office alternative

Simplify Your Data Management with WPS Spreadsheet

While Microsoft Access handles complex relational databases, maintaining VBA forms and debugging SQL scripts can become burdensome. For straightforward data entry, tracking, and reporting, WPS Spreadsheet offers a lightweight, highly compatible, and free alternative without the need for complex programming.

1. Export Access Data: Open your Microsoft Access table, right-click, and choose to export your records as an Excel workbook or CSV file.
2. Open with WPS Office: Launch WPS Spreadsheet and open your newly exported file.
3. Manage Data Easily: Use WPS Spreadsheet's built-in Data Validation and Form features to manage and update your records effortlessly without any VBA coding.

Seamless compatibility with Microsoft Excel formats (.xlsx, .csv, .xls).Built-in data validation and form controls without writing complex VBA code.Free, lightweight, and easy to use across Windows, Mac, and Linux.Familiar user interface requiring zero learning curve for Office users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA error handler only show a generic message?

If your error handler uses a hardcoded message instead of the Err object properties, it will mask the actual system error. Always include Err.Number and Err.Description in your catch blocks to see the exact issue.

What does 'Identity value cannot be retrieved' mean in Access VBA?

This usually occurs when an INSERT statement fails to execute properly. Because no new record was actually created in the table, there is no new AutoNumber (identity) value for the system to return to your form.

How can I test VBA generated SQL in MS Access?

Use Debug.Print in your VBA code to output the SQL string to the Immediate Window (Ctrl + G). Copy this text, create a new Query in Access, switch to SQL View, paste the code, and hit Run to see specific database engine errors.

Can an empty list box on my form cause the INSERT to fail?

Yes. If your VBA INSERT statement references a list box that is unexpectedly empty—often due to a failed background import process—the SQL may attempt to insert a Null value into a required field, causing the write operation to abort.

Excel & Data Problems