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
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