How to fix Error 10 in MS Access

This article explain Error Number 10: "This array is fixed or temporarily locked" and its resolution. This error usually occurs when VBA Code is not properly written.There are many circumstances when user would like to Re-dimension existing array variable to accomplish its purpose. But that could be done in numerous ways .This article explains a very simple a unique concept to illustrate this methodology.

In order to implement this concept first of all we have to create a form named "frmMain" as shown in figure fig 1.1

How to fix Error 10 in MS Access Fig-1.1


As shown in figure above form will contain a Button with caption Enter. When User Click on this button a VBA code that is associated with this button will executes and perform its corresponding action. As a result following message box will appears. Shown in figure 1.2

How to fix Error 10 in MS Access Fig-1.2


Now in next step above shown values in message box will be Re-dimensioned and an error will fire. All above process has been coded below.

How to fix Error 10 in MS Access Fig-1.3


In order to resolve this error array variable needs to be declared dynamically rather than statically. So in VBA code we need to replace array declaration with appropriate code i.e. array variable needs to be Re-Dimensioned using ReDim.

VBA associated with On Click Event of Button:-

Private Sub btnCmd_Click()
Dim sm_Arr(25) As Variant ' Declare array as dynamic for removing error i.e. Use ReDim inplace of Dim

Dim strValue As String
For i = 0 To UBound(sm_Arr)
sm_Arr(i) = i
strValue = strValue & i & ","
Next i
MsgBox "Value is :" & vbNewLine & strValue, vbInformation, "Number's"
another_sub sm_Arr()
End Sub

Public Sub another_sub(sm_Value() As Variant)
ReDim sm_Value(10)
For i = 0 To UBound(sm_Value)
sm_Value(i) = i
strValue = strValue & i & ","
Next i
MsgBox "Value is :" & vbNewLine & strValue, vbInformation, "Value's Re-Dimensioned"
End Sub


It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. ERP Makers will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.