Receiving Type mismatch error in MS Access-CodePudding

I tested my code with static value

Set rs = CurrentDb.OpenRecordset("SELECT mail FROM UsersData WHERE depid = 2")

and it worked, but when I use the full statement

Set rs = CurrentDb.OpenRecordset( _
  "SELECT mail FROM UsersData WHERE depid IN (SELECT depid FROM CyclesDefinitions WHERE cycledefid = " _
  & Me.Combo135.Value & " AND rank = " & Me.Combo202.Value) & ")"

it generates an error i don't know what it means

(Compile Error: Type mismatch) and then nothing happens.

full code below: (everything works as intended and tested, but this line)

Private Sub Command15_Click()
    Dim dbs As Database
    Dim qdf As QueryDef
        
    Set dbs = CurrentDb
    
    Dim StrSqls As String
    
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT mail FROM UsersData WHERE depid IN (SELECT depid FROM CyclesDefinitions WHERE cycledefid = " & Me.Combo135.Value & " AND rank = " & Me.Combo202.Value) & ")"
    'Set rs = CurrentDb.OpenRecordset("SELECT mail FROM UsersData WHERE depid = 2")
    Dim ToMails As String
       
    'Check is a flag on form to make sure record is not inserted more than once in Cycles Table
    If Me.Check137.Value = False Then
        DoCmd.RunCommand acCmdSaveRecord
        
        dbs.Execute "INSERT INTO Cycles (scinvid, cycledefid) VALUES (" & Me.Combo200.Value & ", " & Me.Combo135.Value & ");"
        dbs.Close
        Me.Check137.Value = True
        If Not (rs.EOF And rs.BOF) Then
           rs.MoveFirst 'Unnecessary in this case, but still a good habit
           Do Until rs.EOF = True
            ToMails = rs(0) & ";" & ToMails
            rs.MoveNext
            Loop
        Else
            MsgBox "There are no Emails recorded for such department"
        End If
        rs.Close 'Close the recordset
        Debug.Print ToMails
        Set rs = Nothing 'Clean up
        
        DoCmd.SendObject acSendNoObject, , , ToMails, , , "Test Subject", "Test Message", True
        
        'objRecordset.Open ("SELECT depid FROM CyclesDefinitions WHERE cycledefid =" & Me.Combo135.Value & " AND cycleranktracking =" & Me.Combo202.Value)
                
        'UserDepartmentID = DLookup("depid", "UsersData", "[username]= '" & fOSUserName & "'")
            
        DoCmd.GoToRecord , , acNewRec
        Me.Combo83.Requery
        Combo83.RowSource = "SCINVSearch"
    ElseIf Me.Check137.Value = True Then
        DoCmd.GoToRecord , , acNewRec
        Me.Combo83.Requery
        Combo83.RowSource = "SCINVSearch"
    End If
End Sub

CodePudding user response：

Set rs = CurrentDb.OpenRecordset( _
  "SELECT mail FROM UsersData WHERE depid IN (SELECT depid FROM CyclesDefinitions WHERE cycledefid = " _
  & Me.Combo135.Value & " AND rank = " & Me.Combo202.Value) & ")"

You have placed the closing brace at the wrong place. It should be

Set rs = CurrentDb.OpenRecordset( _
  "SELECT mail FROM UsersData WHERE depid IN (SELECT depid FROM CyclesDefinitions WHERE cycledefid = " _
  & Me.Combo135.Value & " AND rank = " & Me.Combo202.Value & ")")

Your version tries to append a ")" to the Recordset instead of the SQL string. This generates the Type mismatch error.