Dcount question

[jpkeller55]

Simple syntax question (for the expert that is). I have a form with a check box that makes a page on a tab appear if it is checked. The page contains a subform with records related to the record from the main form (related by QuestID). I am trying to add code to the after update event of the checkbox that will give a message that the checkbox cannot be unchecked if records exist on the subform. The query for the subform is qryParameters. I am trying to use the Dcount function to count the records in the subform but having some problems getting it to work correctly. My problem is with the criteria portion of the statement.

Attached is the different codes I have tried:

Dim intHolder As Integer
intHolder = DCount("*", "qryParameters", "QuestID = Me.QuestID") 'added
If intHolder > 0 Then
MsgBox "Parameters exist for this question. Please delete parameters first", vbInformation, "Parameters Exist"

Getting a run-time error '2001' "You cancelled the previous operation"

What am I doing wrong with the criteria? Thanks, Jim

[ajetrumpet]

there are two things. first, put a field in the first arg:

intHolder = DCount("FIELD NAME", "qryParameters", "QuestID = Me.QuestID")

second, always concatenate out field names in criteria statements (they are treated just like vars):

intHolder = DCount("FIELD NAME", "qryParameters", "QuestID = " & Me.QuestID)

and actually, people get this confused all the time. Remember, when you type in "me." in code, the intellisense should always drop down (unless it's shut off, which it never should be). If it doesn't, that's what tells you that the syntax is wrong.

Is your example, you would probably not have gotten a dropdown if you were typing it inside of quotes.

also remember, concat syntax is different for numbers/dates vs. strings. those are the two primary categories in Access.

[jpkeller55]

Thanks Adam...you are right, I did not get the autocomplete after typying Me. That is how I knew the problem existed there. I changed the syntax and that seems to be working now.

As for the "*", I did have a field name in there but in looking at other posts, I saw where Pbaldy suggested that it was more efficient to use the "*" instead of a field name. See post: https://www.accessforums.net/access/...oad-10844.html post #8. I tried it and it worked so not sure what the best approach is on that.

[ajetrumpet]

it doesn't really matter, Jim. Unless you're doing sophisticated stuff that involve NULLS and other infrequencies and/or complex calculations, it's a matter of theory, which is what I'm sure Paul's post is mostly about.

For your purposes, I would guess what you have is fine.

[jpkeller55]

Adam, thanks once again for your help!

Just as a note of clarification, I changed the code to the Before Update event on this with additional code to undo the edit which is making this work much more like intended.

Dim intHolder As Integer
        intHolder = DCount("*", "qryParameters", "QuestID = " & Me.QuestID)
        If intHolder > 0 Then
        MsgBox "Parameters exist for this question. Please delete parameters first", vbInformation, "Parameters Exist"
             Cancel = True
             Me.ParameterBasedQuestion.Undo
            Else
       End If