Run-time error 438
Run-time error 438 – Object doesn't support this property or method. – (Microsoft Access) This problem often occurs when you Loop through a set of controls with a For Next Loop. It happens because you are examining the properties of a control. However not all controls have the same properties.
Different Controls, different Properties
If you open a property sheet for a text-box and look under the data tab there are a number of properties including “Enabled” & “Locked”.. However if you open a property sheet for a label and look under the data tab you will see that there are no properties listed. In other words a “Label” does not have the properties”Enabled” & “Locked”. If you try and access a property that does not exist for any particular control then you will generate an error and your code will cease to run.
Text Box Properties
Label Properties
… …
A Technique to Exclude Objects
A Technique I often use, is to exclude certain types of controls from the For Next Loop. This technique prevents the code from attempting to examine an object that does not have the properties you are testing. To do this you add an extra step to your loop, a step that tests for the type of control. If the control is a label, commonly a type of control that serves no purpose from a coding point of view, you can exclude Label Controls from any further processing.
… …
A Couple of VBA Examples
Code Snippet 1 below, examines the control type in the Select Case Statement, you can easily add or remove different types of control from your test routine by un-commenting the particular control type from the commented out part of the Select Case Statement… This code is designed to remove the commonly used naming convention, a three digit prefix from the control name, then check to see if what’s left constitutes a “duplicate name”. For example if you had a combobox named:- cboDate and you had a text box named:- txtDate then the code would return:- “Date in the variable:- “strFoundControlName”. This is a handy piece of code if you want to associate two controls together via the Controls’ Names.
… …
'Code Snippet 1
Dim strFoundControlName As String
Dim strNamePart As String
Dim Ctrl As Control
Dim X As Integer
For Each Ctrl In Me.Controls
Select Case Ctrl.ControlType
Case acComboBox ,acTextBox ', acListBox, acOptionButton, acOptionGroup, acToggleButton, acLabel, acCheckBox
If Right(Ctrl.Name, Len(Ctrl.Name) - 3) = strNamePart Then
X = X + 1
strFoundControlName = Ctrl.Name
End If
End Select
Next Ctrl
Code Snippet 2 below, uses “TypeOf” in an IF Statement instead of “Ctrl.ControlType” as demonstrated above. This example only selects one particular type of control and in this case will only check the checkboxes on your form:-
'Code Snippet 2
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is CheckBox Then
If Ctrl = False Then Form_frmQC.sFrmWinTblFaults.Visible = True
End If
Next Ctrl
