Why does DCount return a wrong result using VBA?

[Michael R]

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 -

Private Sub ToAdd_Click()
    Me.Command8.Caption = Str(DCount("Id_Customer", "Distribution", "ToAdd=True"))
    Me.Requery
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.

[NeoPa]

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.

[MMcCarthy]

Try removing the Str() function from around the DCount as follows:

Private Sub ToAdd_Click()
    Me.Command8.Caption = DCount("Id_Customer","Distribution","ToAdd=True")
    Me.Requery
End Sub
 

If it won't let you pass the number into the caption let me know.

Mary

[Michael R]

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.

[NeoPa]

Unless you'd previously moved to another record, the VBA code in the ToAdd_Click() procedure would run first.