Type Mismatch on Check Box Control (1 Viewer)

[Integrate]

I may missing the obvious but I can't seem to figure out what it is.
My code works fine in updating Outlook but after it has 'finished' running and I go back to VBA there is a "Run-time error '13': Type mismatch." Highlighting the line

  Set comp = Me.Controls(Choose(i, "tMade", "tTopCut", "tBowlCut", "tAssembled", "tGluedTop", "tPolished", _
    "tInstalled", "tGluedSink", "tPainted", "tDelivered"))

comp is a check box which if set to true I want the appointment to have "complete" added to the subject. That is working fine.
I have tried to find out if the Dim statement is incorrect to cause the error but no luck.

Dim comp As Control

Here is the whole code...in case that makes my mud seem clearer...

Private Sub cmdComplete_Click()
        Me.Dirty = False
 
If Me.chkAddedtoOutlook = True Then
    MsgBox "This appointment has already been added to Microsoft Outlook", vbCritical
    ' Exit the procedure
    Exit Sub
Else
    ' Add a new appointment.
' Use late binding to avoid the "Reference" issue
Dim olapp As Object ' Outlook.Application
Dim olappt As Object ' olAppointmentItem
Dim i As Integer
Dim ctl As Control
Dim cat As Control
Dim olfolder As Object
Dim stg As Control
Dim st As Control
Dim et As Control
Dim comp As Control

If isAppThere("Outlook.Application") = False Then
    ' Outlook is not open, create a new instance
    Set olapp = CreateObject("Outlook.Application")
Else
    ' Outlook is already open--use this method
    Set olapp = GetObject(, "Outlook.Application")
End If
Set olfolder = olapp.GetNamespace("mapi").PickFolder
     For i = 1 To 11
   
    Set olappt = olfolder.Items.Add ' olAppointmentItem
    Set ctl = Me.Controls(Choose(i, "DateTemplateMade", "DateofTopCut", "DateBowlCut", "dateassembletop", _
    "DateGlueTop", "DatePolishTop", "dateinstallpackers", "dategluesink", "datepaint", "datequalitycheck", _
    "deliveryinstalled"))
    Set cat = Me.Controls(Choose(i, "Templatemade", "TopCut", "BowlCut", "AssembleTop", "GlueTop", _
    "PolishTop", "InstallPackers", "Gluesink", "Paint", "Qualitycheck", "DeliveryInstall"))
    Set stg = Me.Controls(Choose(i, "Templatemade", "TopCut", "BowlCut", "AssembleTop", "GlueTop", _
    "PolishTop", "InstallPackers", "Gluesink", "Paint", "Qualitycheck", "DeliveryInstall"))
      Set st = Me.Controls(Choose(i, "sttm", "sttc", "stbc", "stat", "stgt", "stpt", "stip", "stgs", "stp", _
    "stqc", "stdi"))
    Set et = Me.Controls(Choose(i, "ettm", "ettc", "etbc", "etat", "etgt", "etpt", "etip", "etgs", "etp", _
    "etqc", "etdi"))
    Set comp = Me.Controls(Choose(i, "tMade", "tTopCut", "tBowlCut", "tAssembled", "tGluedTop", "tPolished", _
    "tInstalled", "tGluedSink", "tPainted", "tDelivered"))
    
    Rem check value of current control
      If Not Nz(ctl, "") = "" And Nz(comp, "") = True Then
              Rem control is neither Null nor empty string - create appointment
    
    Start = ctl
    With olappt
    ' If There is no Start Date or Time on
    ' the Form use Nz to avoid an error
     
    ' Set the Start Property Value
    .Start = Nz(ctl, "") & " " & Nz(st, "")
    ' Set the End Property Value
    .End = Nz(ctl, "") & " " & Nz(et, "")
    .Subject = Nz("Complete" & stg & " " & Me.JobName, vbNullString)
    .Mileage = Nz(Me.QuoteNo, vbNullString)
    .Location = Nz(Me.InstallAddress & ", " & Me.InstallAddress2 & ", " & Me.Town_City)
    .Body = Nz(Me.Notes, vbNullString)
    .Categories = cat
    
         .Save
    End With
    End If
    Next
   
    End If ' Release the Outlook object variables.
    Set olappt = Nothing
    Set olapp = Nothing    ' Set chkAddedToOutlook to checked
    Me.chkAddedtoOutlook = True
 
    ' Save the Current Record because we checked chkAddedToOutlook
      Me.Dirty = False
   
    ' Inform the user
    MsgBox "Appointment Added!", vbInformation
End Sub

Any help and suggestions are greatly appreciated.

 

[Mihail]

I counted only 10 options in that line of code for the Choose statement.

 

[Integrate]

Thanks
so simple but I guess I'd been looking at it for too long.
thanks for your help

 

[Mihail]

Thanks
so simple but I guess I'd been looking at it for too long.
thanks for your help

This happen any time. Glad to help you.

 

[Integrate]

Do you possibly know if it is possible to replace or delete existing appointments within this code?
Jobs are added to the calendar as appointment then a check box is ticked when each stage is complete. The client wants the appointment to change from the job type to have "Complete" added to the front of the appointment. It was set to delete and re-enter an appointment for each individual stage but he now wants to be able to update an entire job with one click

Basically: If stage = complete, then delete existing appointment and replace with appointment where subject = "Complete......"

There are 11 stages, so is it possible to incorporate this in the code?

 

[Mihail]

Sorry, but I don't understand.
Maybe if you will show us what you have until now (your DB).....

 

[Integrate]

I have tried to attached the db but the files are too big, over the 2mb I'm allowed. Is there any other way I could send them?

I attached a screen print of the db for now. Originally (WBT v3) all dates are added to outlook by clicking the button at the top of the form next to the Added to Outlook tick box.

Then once a stage (eg Template Made) was complete they put a tick in the tick box next to that stage. The after update procedure was to delete the appointment in outlook and replace with basically the same appointment but with "Complete" added to the subject.

The undo button arrow next to the tick box deleted that one appointment from outlook completely.

So each stage was updated in Outlook separately using code:

Private Sub Made_AfterUpdate()
 Dim objOutlook As Outlook.Application
 Dim objNamespace As Outlook.NameSpace
 Dim objFolder As Outlook.MAPIFolder
 Dim objAppointment As Outlook.AppointmentItem
 Dim lngDeletedAppointements As Long
 Dim strSubject As String
 Dim dteStartDate As Date
 Dim CurrentForm As String
 Dim olfolder As Object

 '******************************** Set Criteria for DELETION here ********************************
 dteStartDate = Me.Controls("DateTemplateMade") & " " & Me.Controls("sttm")


 '************************************************************************************************
 Set objOutlook = Outlook.Application
 Set objNamespace = objOutlook.GetNamespace("MAPI")
 Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)
 Set olfolder = objOutlook.GetNamespace("MAPI").PickFolder

 For Each objAppointment In olfolder.Items
   If objAppointment.Mileage = Me.QuoteNo And objAppointment.Start = dteStartDate Then
        objAppointment.Delete
          lngDeletedAppointements = lngDeletedAppointements + 1
   End If

 Next

 
 Set objAppointment = olfolder.Items.Add
 
 Start = dteStartDate
 With objAppointment
    .Start = Nz(Me.DateTemplateMade, "") & " " & Nz(sttm, "")
    .End = Nz(Me.DateTemplateMade, "") & " " & Nz(ettm, "")
    .Subject = "Complete" & " " & Nz(Me.templatemade & " " & Me.JobName, vbNullString)
    .Mileage = Nz(Me.QuoteNo, vbNullString)
    .Location = Nz(Me.InstallAddress & ", " & Me.InstallAddress2 & ", " & Me.Town_City)
    .Body = Nz(Me.Notes, vbNullString)
    .Categories = Nz(Me.templatemade, vbNullString)
           .Save
    End With
    
    

 
    ' Save the Current Record because we checked chkAddedToOutlook
      Me.Dirty = False
   
    ' Inform the user
    MsgBox "Appointment Updated!", vbInformation

The client wants to be able to tick more than one stage as complete and then to refresh all entries in outlook at once instead of it updating every time they click a tick box.

I hope that makes sense...? Sorry if I am not explaining myself very well

 

[Mihail]

Sorry but I am not enough skilled to help you with this.

Note that the size of the DB can be reduced either:
1) by doing a Compact and Repair from Access
2) by ZIPped it
3) by removing records and/or reports, forms, tables, records etc.
4)... by using all the methods

I'm sure that someone know the answer for your question, so upload the DB.
My guess is to use a loop but... not for sure.

 

[Integrate]

thanks for your help Mihail. I will repost as a new thread and see how I get on.

Really appreciate your input