How to reference a control on a form in a DCount on the same form?

[Amy Badgett]

I need to count the number of occurrences of a particular employee based on the type of occurrence selected in a control drop down box. I am using a DCount on the same form to try to accomplish this.

Can anyone help me with the syntax of this DCount:

countOfOccurrence: DCount("Resolution","tblIssues","[tblIssues.Resolution] = Forms![subFormOccurrence].[drpResolution]")

Or does anyone have a better idea about how to do this?

[Stewart Ross]

Hi Amy. Three things. First, there is a mistake in the way you are referring to the resolution field in your DCount:

[tblIssues.Resol ution] should be
[tblIssues].[Resolution], but you don't need to qualify it at all, so just [Resolution] will be fine.

Second, is the type of resolution selected from your combo control a text value or a number? As you have it at present you are treating this value as a number. If it is text, you need to enclose the value in single quotes.

Third, the control reference is being provided to DCount as part of the WHERE string, and this will defer its interpretation until the DCount is run. This can fail, depending on where and when the DCount is run. It is normal to pass the current value of the control into the WHERE clause instead, as you will see in the final example below (treating the comparison as text).

countOfOccurrence: DCount("Resolution","tblIssues","[Resolution] = '" & Forms![subFormOccurrence].[drpResolution] & "'")

If the resolution really is a number, then you won't need the single quotes at all:

countOfOccurrence: DCount("Resolution","tblIssues","[Resolution] = " & Forms![subFormOccurrence].[drpResolution])

If you are referring to a control on a subform, the normal way to refer to the control is like this:

Forms![Your Main Form Name]![Subform Name].Form![Subform control name]

I note that this is not what you have posted above, and I did not include the full syntax in my examples to keep them brief. Keep this in mind when trying to resolve the issues mentioned above - you will need to use the correct syntax for retrieving the value of drpResolution or the Where clause will simply not work at all.

I don't know the name of your main form, so all I can give is an exampple of what might be needed, and you would need to substitute the actual name of your main form accordingly:

countOfOccurrence: DCount("Resolution","tblIssues","[Resolution] = '" & Forms![Your MainForm Name]![subFormOccurrence].Form![drpResolution] & "'")

-Stewart

[Amy Badgett]

Thank you, Stewart, that is exactly what I needed.