Others

Fix an AutoNumber Prefix in a Microsoft Access Form

Jason MillerJason Miller Nov 14, 2026 868 views

Question details

The user needs an Access form to display an AutoNumber with a prefix, such as NJ10401, instead of showing only the numeric portion.

Product
Microsoft Access
Device & OS
not provided
Scenario
A form displays an AutoNumber field that should appear with a text prefix.
Observed behavior
The form displays only the numeric suffix even though a prefixed format may be set on the table field.
Before you start

Open the form in Design View and identify the text box bound to the AutoNumber field. Check an existing record's stored number so you can tell whether the desired display also requires an offset.

Solution 1Recommended

Apply the Prefix Format to the Bound Form Control

Use this when the stored AutoNumber already has the numeric value you want to display after the prefix.

An AutoNumber stores a numeric identifier; its Format property changes only how the value appears. A format set on the table field may not be applied to an existing form control.

1
Open the form in Design View

In the Navigation Pane, right-click the form and select Design View.

2
Select the bound text box

Click the text box that displays the AutoNumber, then open Property Sheet and check its Control Source on the Data tab.

3
Set the display format

On the Property Sheet's Format tab, enter "NJ"00000 in the Format property. A stored value of 10401 will display as NJ10401.

4
Save and test

Save the form, switch to Form View, and check an existing record to confirm that the prefix appears.

The stored value remains numeric: The prefix is only a display format. Keep the numeric AutoNumber as the key and use numeric values in related foreign-key fields.
Free Microsoft Office alternative

Work with Office Documents in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface for working with documents, spreadsheets, and presentations. It can help you move common Office files into a new workflow, but it does not fix Access form formatting or replace an Access database.

Open and edit Microsoft Word, Excel, and PowerPoint formats, including DOCX, XLSX, and PPTX.Use a familiar interface for everyday document and spreadsheet tasks.Work with tabular data exported from Access in a spreadsheet.Use a free, lightweight suite for common Office files.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the prefix appear in the table but not on the form?

An existing form text box may not use the table field's Format property. Open the form in Design View and set the bound text box's Format property on its Property Sheet.

Does adding a prefix change the AutoNumber stored in Access?

No. A format such as "NJ"00000 changes the displayed value only. The AutoNumber remains numeric for keys and relationships.

How can I display NJ10401 when the AutoNumber is 1?

Add a calculated text box and set its Control Source to ="NJ" & Format([Job Number]+10400,"00000"), replacing Job Number with your field name. This changes the display, not the stored value.

Will AutoNumber values always be consecutive?

No. AutoNumber values can have gaps, so use them as identifiers rather than relying on them for an uninterrupted sequence.