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

Links

… …