Fix Microsoft Access Error 6 Overflow When an AutoNumber Exceeds 32767
Question details
The user needs to resolve Microsoft Access run-time error 6 Overflow when a ParticipantServiceID AutoNumber reaches 32768, but VBA variables or parameters still use the Integer data type.
- Product
- Microsoft Access VBA
- Device & OS
- not provided
- Scenario
- A ParticipantServiceID AutoNumber field exceeds the VBA Integer limit of 32,767 and is passed to a VBA function.
- Observed behavior
- Access displays run-time error 6: Overflow because VBA declares the ID parameters and variables as Integer and uses CInt instead of CLng.
Open the Microsoft Access VBA editor and search for LookUpAlreadyExists, ParticipantServiceID, IncidentID, CInt, and related Integer declarations. Confirm that the AutoNumber field is intended to grow beyond 32,767 before changing the data types.
Change All Related VBA ID Parameters and Variables from Integer to Long and Replace CInt with CLng
Use this solution when ParticipantServiceID reaches 32768 or higher and Access raises run-time error 6 because the value exceeds the VBA Integer limit.
VBA Integer variables can only store values up to 32,767. Access AutoNumber fields commonly continue beyond that limit, so all related ID variables and parameters must use Long, and CInt conversions must be replaced with CLng.
In Microsoft Access, press Alt+F11 to open the VBA editor, then press Ctrl+F and search for LookUpAlreadyExists, ParticipantServiceID, and CInt.
Change every related ID parameter in the function declarations from Integer to Long. For example, change Function LookUpAlreadyExists(ByVal ParticipantID As Integer, ByVal IncidentID As Integer) As Boolean to Function LookUpAlreadyExists(ByVal ParticipantID As Long, ByVal IncidentID As Long) As Boolean.
Find CInt(Me.ParticipantServiceID.Value) and replace it with CLng(Me.ParticipantServiceID.Value) so the AutoNumber value is handled as a 32-bit Long instead of a 16-bit Integer.
In the VBA editor, click Debug, select Compile VBA Project, save the module, and then rerun the form or process that previously produced the overflow error.
Check for Remaining Integer Declarations and Treat AutoNumber Gaps as Normal
Use this follow-up check to prevent the same overflow from reappearing in related ID parameters or conversions.
Need a Lightweight Office Suite Without Access VBA Overflow Issues?
WPS Office is a free, lightweight Microsoft Office alternative that handles everyday Word, Excel, and PowerPoint files without the cost or complexity of Access VBA. It keeps familiar formatting and makes it easy to open, edit, and save common Office documents.
Frequently Asked Questions
Why does Access show run-time error 6 when an AutoNumber reaches 32768?
Access shows run-time error 6 because the VBA code is storing or passing the value as an Integer. VBA Integer has a maximum value of 32,767, so an AutoNumber value of 32768 overflows it. The related variable or parameter must be declared as Long.
Which VBA declarations need to change from Integer to Long?
Change every related ID parameter and variable from Integer to Long. This includes the LookUpAlreadyExists and LookUpAlreadyExists2 function declarations, especially parameters such as ParticipantID and IncidentID.
Should I replace CInt with CLng for AutoNumber values?
Yes. Replace CInt(Me.ParticipantServiceID.Value) with CLng(Me.ParticipantServiceID.Value). CLng converts the value to a Long, which can hold AutoNumber values above 32,767.
Are large, nonsequential AutoNumber values a sign of data corruption?
No. AutoNumber fields are not sequential counters. Gaps and nonsequential values are normal and do not indicate a problem with the database.
Does WPS Office include a Microsoft Access replacement?
WPS Office does not include an Access equivalent. For spreadsheet-based data, WPS Spreadsheets can open and save Excel-compatible files and offers a free alternative for many common list, lookup, and reporting tasks.
