MS Access VBA Form Type Mismatch on Control

Question

I'm working on a pop up box (userform) in Access and the idea is the user will check the days of the week they want daily work tasks to appear. But I'm getting a type mismatch error on my line For Each c In Me.Controls and I can't figure out why. I am able to use this same code in Excel VBA with another userform without a problem but I'm guessing something is getting lost in translation??

Here is the full code...

Private Sub CheckBox1_Click()
End Sub

Private Sub CheckBox2_Click()
End Sub

Private Sub CheckBox3_Click()
End Sub

Private Sub CheckBox4_Click()
End Sub

Private Sub CheckBox5_Click()
End Sub

Private Sub CheckBox6_Click()
End Sub

Private Sub CheckBox7_Click()
End Sub

Public Property Get IsCancelled() As Boolean
IsCancelled = cancelled
End Property

Private Sub OkButton_Click()
Dim c As Control
Dim StrSQL As String

For Each c In Me.Controls '<----- Errors out with Type mismatch
If TypeOf c Is MSForms.CheckBox Then
If c Then
Select Case c.Name
    Case "CheckBox1"
        'Monday
        Hide
        'Change Notice
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Change Notice', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),2),Date()), 'Frontiera')")
        'Daily Checks
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Daily Checks', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),2),Date()), 'Frontiera')")

    Case "CheckBox2"
        'Tuesday
        Hide
        'Change Notice
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Change Notice', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),3),Date()), 'Frontiera')")
        'Daily Checks
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Daily Checks', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),3),Date()), 'Frontiera')")


    Case "CheckBox3"
        'Wednesday
        Hide
        'Change Notice
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Change Notice', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),4),Date()), 'Frontiera')")
        'Daily Checks
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Daily Checks', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),4),Date()), 'Frontiera')")


    Case "CheckBox4"
        'Thursday
        Hide
        'Change Notice
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Change Notice', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),5),Date()), 'Frontiera')")
        'Daily Checks
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Daily Checks', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),5),Date()), 'Frontiera')")


    Case "CheckBox5"
        'Friday
        Hide
        'Change Notice
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Change Notice', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),6),Date()), 'Frontiera')")
        'Daily Checks
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Daily Checks', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),6),Date()), 'Frontiera')")


    Case "CheckBox6"
        'Saturday
        Hide
         'Change Notice
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Change Notice', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),7),Date()), 'Frontiera')")
        'Daily Checks
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Daily Checks', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),7),Date()), 'Frontiera')")


    Case "CheckBox7"
        'Sunday
        Hide
         'Change Notice
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Change Notice', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),8),Date()), 'Frontiera')")
        'Daily Checks
        CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID]) VALUES ('Daily Checks', 'Daily Task', 'Ginny''s', '(2) Normal', '0', DateAdd('d',8-Weekday(Date(),8),Date()), 'Frontiera')")

    Case Else
    End Select
    End If
    End If
  Next c
End Sub

Private Sub CancelButton_Click()
  OnCancel
  End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = VbQueryClose.vbFormControlMenu Then
    Cancel = True
    OnCancel
End If
End Sub

Private Sub OnCancel()
cancelled = True
Hide
End Sub

Answer 1

Access has its own controls that have nothing to do with MSForms.

If the form is a MSForms form use

Dim c As MSForms.Control
...
If TypeOf c Is MSForms.CheckBox Then
...

If the form is an Access form

Dim c As Control
...
If TypeOf c Is CheckBox Then
...

Also, if you are using only Access forms, you can remove the Reference to MSForms in the VBA code editor in menu Tools > References.