Display Selected Inventory Parts in a Microsoft Access List Box
Question details
The user needs to select inventory items in a combo box or multi-select list box and display each selected part's number, description, vendor, and cost in another list or form.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- An inventory form lets users choose parts and review their details in a separate results control.
- Observed behavior
- The results list or form should show only the selected inventory parts and update when selections are added or removed.
Open the form in Design View and identify the Parts table's unique key and the controls used for selecting and displaying parts. The examples below assume a numeric PartID key, a Parts table, a Vendors table linked by VendorID, and a local temporary selection table.
Synchronize a multi-select list box with a temporary selection table
Use this approach when users select several parts at once and the results must reflect the current selection.
Store selected PartID values in tmpSelectedParts, then join that table to Parts and Vendors to retrieve the displayed details. Keeping only the keys avoids duplicating descriptions, vendor information, and costs; if you need a snapshot of those values, store them in the temporary table instead.
Choose Create > Table Design. Add PartID as a Number field with Field Size set to Long Integer, make it the primary key, and save the table as tmpSelectedParts. This example assumes Parts.PartID is an AutoNumber or Long Integer key.
Open the inventory form in Design View. Select the source list box, open its Property Sheet, and name it lstParts. Set Row Source Type to Table/Query and Row Source to SELECT PartID, PartNumber, PartDescription FROM Parts ORDER BY PartNumber;. Set Bound Column to 1, Column Count to 3, and the first column's width to 0 to hide the key. On the Other tab, set Multi Select to Simple or Extended.
Choose Create > Query Design, switch to SQL View, and enter: SELECT p.PartNumber, p.PartDescription, v.VendorName, p.Cost FROM (tmpSelectedParts AS s INNER JOIN Parts AS p ON s.PartID = p.PartID) LEFT JOIN Vendors AS v ON p.VendorID = v.VendorID ORDER BY p.PartNumber;. Save it as qrySelectedParts. Replace the field names with your actual names.
In the form's Design View, add or select the results list box and name it lstSelectedParts. Set Row Source Type to Table/Query, Row Source to qrySelectedParts, and Column Count to 4. Adjust Column Widths so the part number, description, vendor, and cost are visible.
Add a command button named cmdShowSelected. In Property Sheet > Event > On Click, select [Event Procedure], click the build button, and use this procedure: Private Sub cmdShowSelected_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim item As Variant Set db = CurrentDb db.Execute "DELETE FROM tmpSelectedParts", dbFailOnError Set rs = db.OpenRecordset("tmpSelectedParts", dbOpenDynaset) For Each item In Me.lstParts.ItemsSelected rs.AddNew rs!PartID = Me.lstParts.ItemData(item) rs.Update Next item rs.Close Set rs = Nothing Set db = Nothing Me.lstSelectedParts.Requery End Sub Open the form in Form View, select parts, and click the button. Clicking it after deselecting parts also removes those parts from the results.

Add parts one at a time from a combo box
Use this approach when users choose one part per action and build a cumulative results list.
Review Exported Inventory Lists with WPS Spreadsheet
WPS Office is a free, lightweight alternative for everyday document and spreadsheet work, with a familiar interface and Microsoft Office format compatibility for easy migration. Export the selected-parts query from Access to Excel to review the inventory in WPS Spreadsheet. WPS does not run Access databases, forms, or VBA selection logic.

Frequently Asked Questions
Why does my multi-select list box return Null instead of the selected part?
A multi-select Access list box does not expose all selected items through its Value property. In the form's VBA module, loop through lstParts.ItemsSelected and use lstParts.ItemData(item) to retrieve each selected row's bound key.
Can I show the selected parts in a form instead of another list box?
Yes. Open the results form or subform in Design View and set its Record Source to qrySelectedParts. After changing tmpSelectedParts, call Requery on that form. For a subform control named subSelectedParts, use Me.subSelectedParts.Form.Requery.
Should I store vendor names and costs in the temporary table?
Store the part key and join to Parts and Vendors when you want current inventory values. Store PartNumber, PartDescription, VendorID, and Cost in the temporary table when you need a snapshot taken at selection time. Use VendorID rather than the vendor name as the relationship key.
Why does the results list still show parts I deselected?
Changing the source list's selection does not automatically update its temporary table or the results query. Click the Show Selected button to rebuild tmpSelectedParts from ItemsSelected, then call Me.lstSelectedParts.Requery. To refresh automatically, place the synchronization logic in the source list box's After Update event.
Can WPS Office open the Access database and run this selection form?
No. WPS Office does not run Access .accdb or .mdb forms and database logic. In Access, select qrySelectedParts and choose External Data > Export > Excel, then open the exported .xlsx file in WPS Spreadsheet to review the results.




