By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,287 Members | 1,665 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,287 IT Pros & Developers. It's quick & easy.

DCount on Form AfterUpdate Filter

P: 6
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Compfilter_AfterUpdate()
  2.     If IsNull(Me.Compfilter) Then
  3.         Me.FilterOn = False
  4.     Else
  5.         Me.Filter = "[Sites.Site] = """ & Me.Compfilter & """"
  6.         Me.FilterOn = True
  7.     End If
  8. 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:
Expand|Select|Wrap|Line Numbers
  1. =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.
Apr 4 '14 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 15k+
P: 31,768
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]).
Apr 4 '14 #2

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

Expand|Select|Wrap|Line Numbers
  1. =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?
Apr 4 '14 #3

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

Expand|Select|Wrap|Line Numbers
  1. Dim strVal as String
  2. strVal = [Forms]![Sites]![Compfilter]
  4. 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.
Apr 4 '14 #4

Expert Mod 15k+
P: 31,768
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?
Apr 6 '14 #5

P: 6
Thanks guys - sorted. The code was fine, I'd mistyped a field name... (sigh)
Jun 17 '14 #6

Expert Mod 15k+
P: 31,768
That's fine. We've all been there. As long as the problem was found and fixed then the process worked ;-)
Jun 22 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.