Prevent Duplicate List Box Values in Microsoft Access with VBA
Question details
Prevent a text box value from being added to an Access list box more than once.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A user enters values in a text box above a list box on an Access form.
- Observed behavior
- The form needs to check existing list box values before adding an entry, with an optional table-level safeguard against duplicate records.
Open the form in Design View and check the names of the text box and list box in their Property Sheets. Determine whether the list box uses a value list or gets its rows from a table, because each requires a different approach.
Check a Value List for Duplicates with VBA
Use the text box's AfterUpdate event to check existing entries before adding a value to a single-column value list.
This example assumes an unbound text box named txtEntry and a single-column list box named lstValues with Row Source Type set to Value List. It compares values without regard to letter case and ignores leading and trailing spaces.
In Design View, select the list box and open Property Sheet > Data. Set Row Source Type to Value List, and confirm its Name on the Other tab.
Select the text box, open Property Sheet > Event, and choose [Event Procedure] for After Update. Click the ellipsis button to open the VBA editor.
Replace the generated event procedure with the following code, changing txtEntry and lstValues if your controls have different names: Private Sub txtEntry_AfterUpdate() Dim inputValue As String Dim existingValue As Variant Dim i As Long inputValue = Trim$(Nz(Me.txtEntry.Value, "")) If Len(inputValue) = 0 Then Exit Sub For i = 0 To Me.lstValues.ListCount - 1 existingValue = Me.lstValues.Column(0, i) If Not IsNull(existingValue) Then If StrComp(Trim$(CStr(existingValue)), inputValue, vbTextCompare) = 0 Then MsgBox "This value is already in the list.", vbExclamation Me.txtEntry.Value = Null Exit Sub End If End If Next i Me.lstValues.AddItem inputValue Me.txtEntry.Value = Null End Sub
Save the form, open it in Form View, and enter a value in the text box. Move out of the text box to trigger AfterUpdate, then try entering the same value again.

Enforce Unique Values in the Access Table
Use a unique index when the list box displays values stored in a table and duplicates must be blocked at the database level.
Manage Office Files with WPS Office
WPS Office is a free, lightweight, easy-to-use alternative for everyday documents, spreadsheets, and presentations. Its familiar interface and Microsoft Office format compatibility make it easier to move existing files into your workflow. It does not replace Access or fix Access VBA.

Frequently Asked Questions
Where do I add the VBA code in Access?
Open the form in Design View, select the text box, and go to Property Sheet > Event > After Update. Select [Event Procedure] and click the ellipsis button to open its VBA procedure.
Why does AddItem fail for my list box?
AddItem is intended for a list box whose Row Source Type is Value List. Check Property Sheet > Data; if the list is table-backed, insert the record into its source table and requery the list box instead.
Does the VBA check treat different capitalization as a duplicate?
Yes. The example uses StrComp with vbTextCompare, so entries such as Apple and apple match. It also trims leading and trailing spaces before comparing.
What if Access will not let me create the unique index?
Check the table field for existing duplicate values and resolve them before setting Indexed to Yes (No Duplicates) in table Design View.
Does a unique table index protect a value list?
No. A unique index protects values stored in the indexed table field. A standalone value list needs its own duplicate check, such as the AfterUpdate procedure.




