DCount on Form AfterUpdate Filter

[shooshed]

I have a form called Sites, based on a table named Sites, which is filtered to show results for just one Site. The code used for the filter is:

Private Sub Compfilter_AfterUpdate()
    If IsNull(Me.Compfilter) Then
        Me.FilterOn = False
    Else
        Me.Filter = "[Sites.Site] = """ & Me.Compfilter & """"
        Me.FilterOn = True
    End If
End Sub

In another table (called Log of Checks) there are lots of records each of which is attributed to a Site, and each has a Status of "Pend" or "Comp". On my Sites form I want to add a field showing a count of the records in this Log of Checks table with the status of "Pend" but only for the filtered Site.

I've addded a textbox to my Sites form with the following code and nothing is happening:

=DCount("*","log of checks","[Status]='Pend' AND [Site] = '[Forms]![Sites]![Compfilter]'")

Can anyone help? I'm quite new to Access and have been working on this for a week now.

[NeoPa]

In your first bit of code line #5 refers to [Sites.Site]. It should be [Sites].[Site].

In your second, the direct reference to the form control doesn't need quotes around it. Quotes are only needed for literal values and not references ([Forms]![Sites]![Compfilter]).

[shooshed]

Thanks NeoPa. Have amended line 5 as you suggested. Also removed quotes so code is now:

=DCount("*","log of checks","[Status]='Pend' AND [Site]=[Forms]![Sites]![Compfilter]")

The count now shows a count of all checks with a status of "Pend" (2289) instead of just for the filtered site.

Any suggestions?

[mcupito]

You could try putting the [condition] in a variable and evaluating it in the immediate window in debug mode.

Dim strVal as String
strVal = [Forms]![Sites]![Compfilter]

debug.print strVal

We can see what the condition is at this point because obviously the DCount isn't utilizing it correctly.

Another thing I am seeing is to include "Option Explicit" at the top of the module.

[NeoPa]

I can only really say that the code appears fine. If it's not working with what you have then I would expect there to be something wrong with something that hasn't yet been shared with us.

First of all check that all the references are correctly spelled. From your first (clearly explained) post it seems they are.

Do you refresh or requery the TextBox or the form when the value in [Compfilter] is changed?

[shooshed]

Thanks guys - sorted. The code was fine, I'd mistyped a field name... (sigh)

[NeoPa]

That's fine. We've all been there. As long as the problem was found and fixed then the process worked ;-)