Type mismatch

[Lewe22]

I am getting the error message 'type mismatch' at line 22 when running the code below:

Private Sub cmdSave_Click()
'On Error GoTo Err_cmdFacilitatorSave_Click
 
    'Check flag status
    If Me!txtFlag = 1 Then  '(New)
        '----------------------------------------------------------------------------------------------------
        MsgBox "New Facilitator"
        Me!txtID.SetFocus
        'Check to make sure the user has entered a facilitator ID
        If Me!txtID.Text = "" Then 'If they haven't.....
            MsgBox "You must ensure that you enter a facilitator ID (usually initials)."
            Me!txtID.SetFocus
        Else                       'If they have.....
            Dim dbsCTrack
            Dim rstNewFacil As Recordset
            Dim sqlNewFacil As String
 
            MsgBox Me.txtID
 
            Set dbsCTrack = CurrentDb
            sqlNewFacil = "SELECT * FROM tblFacilitator WHERE tblFacilitator.facil_id = '" & Me.txtID & "'"
            Set rstNewFacil = dbsCTrack.OpenRecordset(sqlNewFacil)
 
            MsgBox "Test"
 
            'Check to see if the facilitator already exists
            If rstNewFacil.RecordCount = 0 Then    'If they don't.....
                rstNewFacil.AddNew
                rstNewFacil(0) = Me.txtID
                rstNewFacil(1) = Me.txtName
                rstNewFacil(2) = Me.txtAdd1
                rstNewFacil(3) = Me.txtAdd2
                rstNewFacil(4) = Me.txtAdd3
                rstNewFacil(5) = Me.txtAdd4
                rstNewFacil(6) = Me.txtPcode
                rstNewFacil(7) = Me.txtPhone
                rstNewFacil(8) = Me.txtEmail
                rstNewFacil.Update
                rstNewFacil.Close
                MsgBox Me.txtID & " - " & Me.txtName & " has been created as a facilitator."
            Else    'If they do.....
                MsgBox "A facilitator already exists with the ID: " & Me!txtID & ". Please enter a different ID."
                Me!txtID.SetFocus
            End If
        End If
    ElseIf Me!txtFlag = 2 Then '(Edit)
'If statement continues.....

It is worth noting that the field txtID is just a plain text box with an input mask that allows a maximum of 3 characters but no less that 2.
I can't understand it.... I've even replaced the SQL at line 21 and hard coded a two character ID such as....

sqlNewFacil = "SELECT * FROM tblFacilitator WHERE tblFacilitator.facil_id = 'FT'"

but i still get the same error message. I don't know what i'm doing wrong as i've definately done something like this before.

Any help would be much appreciated!!

[FishVal]

Helo, Lewe22.

Try explicitely declare your variables using libnames.

Dim dbsCTrack As DAO.Database
Dim rstNewFacil As DAO.Recordset
Dim sqlNewFacil As String
 

Regards,
Fish

[NeoPa]

As a rule (very strong recommendation) you should always "Require Variable Declaration" in any VBA projects (From VBA Window {Alt-F11 from Access} use Tools / Options / Editor Tab / Require Variable Declaration).

This will insert "Option Explicit" automatically into all of your project modules. This will make the system complain whenever you use a variable that you haven't declared in the code. This may sound like it will cause you extra work. That would be a woeful misunderstanding!

Moving on to Recordsets in particular. As there are many overlaps between the two libraries (ADO & DAO) it is advisable to be explicit in any declarations to avoid ambiguity. Both for you and the compiler.

[Lewe22]

Thanks for the help FishVal - much appreciated, that sorted it out for me!

Thanks for the advice NeoPa - Will ensure I select the 'Require Variable Declaration' as you kindly suggest.

Great Stuff!

[NeoPa]

As this came up in here, and I think it's such an important issue anyway, I knocked up a little article that explains it in more detail (Require Variable Declaration).

[ADezii]

'A slightly different approach with Explicit Declarations

'On Error GoTo Err_cmdFacilitatorSave_Click
 
'Check flag status
If Me!txtFlag = 1 Then  '(New)
  '------------------------------------------
  MsgBox "New Facilitator"
  Me!txtID.SetFocus
    'Check to make sure the user has entered a facilitator ID
    If Me!txtID.Text = "" Then 'If they haven't.....
      MsgBox "You must ensure that you enter a facilitator ID (usually initials)."
      Me!txtID.SetFocus
    Else                       'If they have.....
      Dim dbsCTrack As DAO.Database
      Dim rstNewFacil As DAO.Recordset
      Dim sqlNewFacil As String
 
      MsgBox Me.txtID
 
      Set dbsCTrack = CurrentDb
      sqlNewFacil = "SELECT * FROM tblFacilitator WHERE tblFacilitator.facil_id = '" & _
                     Me.txtID & "'"
      Set rstNewFacil = dbsCTrack.OpenRecordset(sqlNewFacil)
 
      MsgBox "Test"
 
      'Check to see if the facilitator already exists
      If rstNewFacil.RecordCount = 0 Then    'If they don't.....
        With rstNewFacil
          .AddNew
            .Fields(0) = Me.txtID
            .Fields(1) = Me.txtName
            .Fields(2) = Me.txtAdd1
            .Fields(3) = Me.txtAdd2
            .Fields(4) = Me.txtAdd3
            .Fields(5) = Me.txtAdd4
            .Fields(6) = Me.txtPcode
            .Fields(7) = Me.txtPhone
            .Fields(8) = Me.txtEmail
          .Update
        End With
           rstNewFacil.Close
           Set rstNewFacil = Nothing
           MsgBox Me.txtID & " - " & Me.txtName & " has been created as a facilitator."
      Else    'If they do.....
        MsgBox "A facilitator already exists with the ID: " & Me!txtID & _
               ". Please enter a different ID."
        Me!txtID.SetFocus
      End If
    End If
ElseIf Me!txtFlag = 2 Then '(Edit)
'If statement continues.....