Solved

DCount mismatch criteria error

Posted on 2010-09-01
3
498 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:ghaphis
  • 2
3 Comments
 
LVL 75
ID: 33582986
Is Surveyid  Text or Numeric?  IF ... Text then try

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

mx
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 33582993
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
 
LVL 75
ID: 33583009
typo ... extra &

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now