How to Select a Microsoft Access Record with VBA and Add It to a Cart
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.

- 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.
Ensure you have a basic understanding of Access VBA and that your database contains a primary 'Products' table and a target 'OrderDetails' table.
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.
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.
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;
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'.
Add 'Me.YourListBoxName.Requery' to the end of your VBA procedure so the list box immediately updates to show the newly added item.
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.

Implement an OrderDetails Subform (Alternative Best Practice)
Instead of an unbound list box, use a continuous subform bound directly to your OrderDetails table for robust multi-item cart management.
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.

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.




