DCount mismatch criteria error

Hello,

I have a form with a tab control and two subforms inside separate tabs. The subforms save data into two different tables. The data outside the subform saves data to a third table.

I am using DCount code so that it checks whether the last subform has been filled before the form unloads. If the surveyid (primary key) from the main form is found in the subform's table, then it should allow the form to unload. If there is no match in the table, the form should not unload.

The code below executes correctly when there are no rows in the table, but when it finds matching rows in the table I get the error "Data type mismatch in criteria expression".  Is there something wrong with my code?

Private Sub form_unload(Cancel As Integer)
    If IsNull(Me.Surveyid) Then Exit Sub

    If DCount("*", "DeathTable", "surveyid = " & Me.Surveyid) > 0 Then
    Cancel = 0
    Exit Sub
    Else
    If DCount("*", "DeathTable", "surveyid = " & Me.Surveyid) = 0 Then
        MsgBox "Missing record in Death Table!"
        Cancel = 1
        Exit Sub
        Else
        Cancel = 0
    End If
    End If
   
End Sub
ghaphisAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
surveyId could be Text data type

Private Sub form_unload(Cancel As Integer)
    If IsNull(Me.Surveyid) Then Exit Sub

    If DCount("*", "DeathTable", "surveyid = '" & Me.Surveyid &"'") > 0 Then
    Cancel = 0
    Exit Sub
    Else
    If DCount("*", "DeathTable", "surveyid = '" & Me.Surveyid & "'") = 0 Then
        MsgBox "Missing record in Death Table!"
        Cancel = 1
        Exit Sub
        Else
        Cancel = 0
    End If
    End If
   
End Sub



take note that your dcount on two lines are the same..


0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Is Surveyid  Text or Numeric?  IF ... Text then try

If DCount("*", "DeathTable", "surveyid = " & &chr(34) & Me.Surveyid & Chr(34) ) > 0

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
typo ... extra &

If DCount("*", "DeathTable", "surveyid = " &  chr(34) & Me.Surveyid & Chr(34) ) > 0
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.