VBA & Macro Problems

Prevent Duplicate List Box Values in Microsoft Access with VBA

Ayan MasoodAyan Masood Nov 20, 2026 869 views

Question details

Prevent a text box value from being added to an Access list box more than once.

Prevent Duplicate List Box Values in Microsoft Access with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Check the list box settings

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.

2
Open the event procedure

Select the text box, open Property Sheet > Event, and choose [Event Procedure] for After Update. Click the ellipsis button to open the VBA editor.

3
Add the duplicate check

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

4
Test the form

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.

Check a Value List for Duplicates with VBA
Scope of this example: AddItem modifies the list box's value list; it does not insert a record into a table. The example is intended for simple values without value-list delimiters such as semicolons.
Free Microsoft Office alternative

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.

Use spreadsheets to organize and review lists of values.Work with Microsoft Office formats including DOCX, XLSX, and PPTX.Navigate documents, spreadsheets, and presentations in a familiar interface.Use a lightweight office suite for everyday editing.
microsoft office alternative - wps office

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.