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

Why does DCount return a wrong result using VBA?

100+
P: 176
I have what seems to me a strange problem. I have a continous form, toggle buttons of a [ToAdd] field, a text control, and a command's button caption. When I write the following in the text control's source:

=DCount("Id_Customer","Distribution","ToAdd=True")
I get the correct result all the time, but when I write in code -

Expand|Select|Wrap|Line Numbers
  1. Private Sub ToAdd_Click()
  2.     Me.Command8.Caption = Str(DCount("Id_Customer", "Distribution", "ToAdd=True"))
  3.     Me.Requery
  4. End Sub
, once I click on the toggle button, I get a result which is bigger by 1 than the correct result , when I click on the same toggle button again, the result I get is by 1 smaller then that correct result (that which is being shown in the text control).

When I click a toggle button of a different record/row, the result I get is always by 1 smaller than the correct count result.

Bewildered.
Does anyone know what is the source of this?
Thanks.
Dec 10 '06 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,615
Is it because the recordset hasn't been updated yet?
Perhaps the .Requery should come before the DLookup code?
It's hard to follow the logic fully as there is so much we don't know of your situation. That's the only thing I can think of from what you've said.
Dec 11 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Try removing the Str() function from around the DCount as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ToAdd_Click()
  2.     Me.Command8.Caption = DCount("Id_Customer","Distribution","ToAdd=True")
  3.     Me.Requery
  4. End Sub
  5.  
If it won't let you pass the number into the caption let me know.

Mary
Dec 11 '06 #3

100+
P: 176
Yes, the .Requery should've come before DCount. The trouble wasn't in getting the value into Caption, as MsgBox (Dcount...) had also beein returning the wrong value.

Thanks!

So, when the expression (Dcount...) is a source of a text control, and I click a command button that will alter the count, I need a Requery for that commands' button code. When the expression is in VBA, I need Requery after the expression. Is this means that VBA runs before the calculated controls of the form?

Thanks again for your help.
Dec 11 '06 #4

NeoPa
Expert Mod 15k+
P: 31,615
Unless you'd previously moved to another record, the VBA code in the ToAdd_Click() procedure would run first.
Dec 11 '06 #5

Post your reply

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