Solved

DCount mismatch criteria error

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

929 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

20 Experts available now in Live!

Get 1:1 Help Now