Solved

DCount mismatch criteria error

Posted on 2010-09-01
3
500 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Add records to a form to a table 11 37
Delete Records on a Form in Microsoft Access 5 39
backup programme - VBA 3 24
Criteria for Date for DCount 4 17
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

785 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