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 & "'"
            [B]Set rstNewFacil = dbsCTrack.OpenRecordset(sqlNewFacil)[/B]
            
            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.
[code=vb]
Dim dbsCTrack As DAO.Database
Dim rstNewFacil As DAO.Recordset
Dim sqlNewFacil As String
[/code]

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 misunderstandin g!

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
[CODE=vb]
'On Error GoTo Err_cmdFacilita torSave_Click

'Check flag status
If Me!txtFlag = 1 Then '(New)
'------------------------------------------
MsgBox "New Facilitator"
Me!txtID.SetFoc us
'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.SetFoc us
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.OpenR ecordset(sqlNew Facil)

MsgBox "Test"

'Check to see if the facilitator already exists
If rstNewFacil.Rec ordCount = 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.Clo se
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.SetFoc us
End If
End If
ElseIf Me!txtFlag = 2 Then '(Edit)
'If statement continues.....[/CODE]