Others

How to Add Multiple Serial Numbers to Access Tables using VBA or Append Queries

Emma Brown Oct 10, 2026 869 views

Question details

The user needs to process and insert a pasted list of serial numbers into Access tables with validation, duplicate checks, and calculated expiration dates with a single button click.

How to Add Multiple Serial Numbers to Access Tables using VBA or Append Queries

Product: Microsoft Access
Device & OS: not provided
Scenario: Adding a batch of serial numbers to a database table from either plain text input or a linked table, while applying business rules like calculating dates and preventing duplicates.
Observed behavior: The user wants to streamline the data entry process into a one-click action that automatically loops through the list, validates each item, and appends the required transaction records.

Before you start

Before running VBA scripts or append queries, ensure you have verified the target tables have the correct data types for serial numbers and expiration dates.

Solution 1Recommended

Use VBA to Process Plain Text Serial Numbers

Ideal for scenarios where users paste a list of plain text serial numbers (one per line) directly into a form.

By utilizing VBA, you can easily parse a block of text, evaluate each serial number, calculate relevant dates, and append records to your Access tables.

The Split function allows you to break the pasted text into an array based on line breaks, making it simple to loop through.

Capture the pasted text

Create an unbound text box on your Access form to receive the pasted list of serial numbers and a command button to trigger the process.

Split the text into an array

In your button's OnClick VBA event, use the Split function (e.g., items = Split(listText, vbCrLf)) to convert the delimited list into an array.

Loop and process each item

Use a For loop (For x = LBound(items) To UBound(items)) to iterate through each serial number in the array.

Validate and calculate

Within the loop, check if the serial number string length is valid, calculate the expiration date using DateAdd, and use DCount to check the database for duplicates.

Insert records

Execute an INSERT INTO SQL statement via CurrentDb.Execute to append the validated serial number and transaction records to the target tables.

Use VBA to Process Plain Text Serial Numbers

Handling Delimiters: Ensure you clean up the input strings by trimming whitespace and removing excess carriage returns before inserting them into the database.

Solution 2

Use an Append Query for Data in Linked Tables

Use this method if the serial numbers are already stored in a linked table or an imported Excel spreadsheet.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Your Daily Office Tasks?

While WPS Office does not include a database management tool like Microsoft Access, it offers excellent free alternatives for Word, Excel, and PowerPoint. If you process lists of serial numbers in spreadsheets before importing them to databases, WPS Spreadsheet provides powerful data validation and batch processing features.

1. Download WPS Office: Visit the official WPS website and download the installation package for your operating system.
2. Install the application: Run the installer and follow the on-screen instructions to complete the setup process.
3. Start processing data: Open WPS Spreadsheet to clean, organize, and format your serial number lists before appending them to your database.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight and fast, utilizing minimal system resources for smooth data handling.Built-in advanced formulas and data processing tools for cleaning data before database imports.Familiar tabbed user interface, ensuring a seamless migration and zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

How can I prevent duplicate serial numbers when importing?

When using VBA, you can use the DCount function to check if the serial number already exists in your table before executing the insert statement. In an append query, ensure the target field is set as a Primary Key or has a Unique Index, which will automatically reject duplicate entries.

What is the best way to handle carriage returns in a pasted list?

Before splitting your string in VBA, you can use the Replace function to standardize line breaks, or simply split directly using vbCrLf (Carriage Return + Line Feed) as the delimiter for the Split function.

Can I calculate expiration dates directly in an Access Append Query?

Yes, you can use built-in functions like DateAdd directly in the SELECT clause of your INSERT INTO statement to calculate expiration dates dynamically based on the current date or another field in the linked table.