Others

How to Select a Microsoft Access Record with VBA and Add It to a Cart

Nimra MalikNimra Malik Nov 2, 2026 870 views

Question details

The user needs to add a selected product to a shopping cart list box using Microsoft Access VBA, and manage quantities and order details properly.

Select a Microsoft Access Record with VBA and Add It to a Cart
Product
Microsoft Access
Device & OS
not provided
Scenario
Building a custom shopping cart interface in MS Access where users select product records to compile a multi-item order.
Observed behavior
Needs a reliable method to temporarily store selected product records and display them in a list box before submitting the final order.
Before you start

Ensure you have a basic understanding of Access VBA and that your database contains a primary 'Products' table and a target 'OrderDetails' table.

Solution 1Recommended

Use a Temporary Cart Table Bound to a List Box

Temporarily insert the selected products into a local Cart table and use this table as the list box's RowSource to display the pending order.

This approach avoids complex unbound list box management. By using a temporary table linked with an INNER JOIN to your Products table, you ensure the list box always displays the latest selected items accurately.

1
Create a temporary table

Create a new local table named 'Cart' in your database. It should contain at least a 'ProductID' column, and optionally a 'Quantity' column if users can order multiple items.

2
Configure the list box RowSource

In the property sheet of your list box, set the RowSource to a SQL query: SELECT Cart.ProductID, Products.Product FROM Cart INNER JOIN Products ON Cart.ProductID = Products.ProductID;

3
Insert the selected record via VBA

In your form's VBA module, write an SQL INSERT INTO statement to add the selected ProductID into the Cart table when the user clicks 'Add to Cart'.

4
Requery the list box

Add 'Me.YourListBoxName.Requery' to the end of your VBA procedure so the list box immediately updates to show the newly added item.

5
Process and clear the order

When the order is finalized, run an Append Query to copy records from the Cart table into your main OrderDetails table, then execute a DELETE query to empty the Cart table and requery the list box.

Use a Temporary Cart Table Bound to a List Box
Quantity Management: To manage quantities easily, bind a text box to the Quantity field of the Cart table. You can use VBA to update this value based on the current list box selection.
Free Microsoft Office alternative

Manage Documents Efficiently with a Free Office Suite

While Microsoft Access requires advanced VBA for database building, WPS Office offers a free, lightweight, and highly compatible solution for all your standard word processing, spreadsheet, and presentation needs without complex coding.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx).Lightweight installation that runs smoothly on almost any desktop or mobile device.Familiar, intuitive user interface for a seamless migration from Microsoft Office.Built-in robust PDF toolkit for reading, editing, and converting documents.
microsoft office alternative - wps office

Frequently Asked Questions

Why is the product picture not showing in my Access list box?

List boxes in Microsoft Access do not natively support displaying images or OLE objects. If you need to display product images, you must use a continuous form or subform that contains a bound image control.

How do I empty the cart list box after checkout using VBA?

You can clear the cart by deleting the underlying data. Execute the command CurrentDb.Execute "DELETE * FROM Cart", then immediately call Me.YourListBoxName.Requery to refresh the empty list box.

Should I use a list box or a subform for an Access shopping cart?

While a list box works for simple selections, a continuous subform is highly recommended for multi-item shopping carts. Subforms easily handle quantity adjustments, data editing, and complex data relationships without extensive custom VBA code.