I have been trying to run this code to get the caption of each control (a combobox in this example) on my form. But it keeps telling me "type mismatch". Please help. Thank you.
Private Sub Command266_Click()
Dim ctrl As MSForms.Control
Dim x As Long
x = 0
For Each ctrl In Me.Controls
Select Case True
Case TypeOf ctrl Is MSForms.ComboBox
MsgBox "control caption is" & ctrl.Caption, vbOKOnly, " test"
End Select
Next ctrl
End Sub
CodePudding user response:
"MS Access forms" are not "MSForms". MSForms is a separate UI technology that can be used, for example, to create UI windows for Excel.
You need to use the types Access.Control
and Access.ComboBox
instead. Unless you messed around with the order of references, you can just refer to them as Control
and ComboBox
.
In addition, ComboBox
controls in Access don't have a Caption
property. If you want to get the caption of the label associated with the combobox, you can use the Controls(0)
property.
Thus, the following code should work:
Dim ctrl As Control
Dim lbl As Label
For Each ctrl In Me.Controls
Select Case True
Case TypeOf ctrl Is ComboBox
Set lbl = ctrl.Controls(0)
MsgBox "control caption is " & lbl.Caption, vbOKOnly, "test"
End Select
Next ctrl