Dount Field Flickering?

[gheyman]

Can you not put more than one DCount formula on a form?

I have 10 fields ( 5 + 5) where I wanted to put 5 Dcount and 5 Dsum formulas.

Looking at 5 different Queries, so I wont the Dcount and Dsum on each Query.

There doesn't seem to be anything wrong with my formulas because if I only put one DCount and One Dsum it works perfectly.

When I put another DSum or Dcount in one of the other ten unbound text Boxes those Text boxes Flicker and show an #ERROR in the text. But not all flicker and show #ERRROR. One Dsum and Dcount still functions properly.

Is there a Limit???

 

[stumac]

There is no limit - however they can be inefficient and slow things down. If they error it can be for various reasons, Null values can cause issues so need to be handled within the formula.

Another way (if you need to use these functions) is to calculate them in VBA (on the on current event of a form) and set a text box to that value:

me.txtbox1 = Dcount("*","Mytable","Myfield = " & me.criteria)

if they are all being calculated from one table, I would recommend using a recordset to pull the values in from a query.

 

[gheyman]

There is no limit - however they can be inefficient and slow things down. If they error it can be for various reasons, Null values can cause issues so need to be handled within the formula.

Another way (if you need to use these functions) is to calculate them in VBA (on the on current event of a form) and set a text box to that value:

me.txtbox1 = Dcount("*","Mytable","Myfield = " & me.criteria)

if they are all being calculated from one table, I would recommend using a recordset to pull the values in from a query.

Thanks. It's not the formulas. I think it's something to do with other events happening on the form. I will try setting them in VBA instead of in the source. That sounds like it should work

 

[stumac]

Ok be aware if you use a field with the incorrect data type in DSUM you will get the error message - i.e. summing a text field.

If you use the wrong type of field in the criteria you will get that flickering....

i.e. DCount("*","MyTable","MyTextField = " & me.textfield)

Should be: DCount("*","MyTable","MyTextField = '" & me.textfield & "'")

EDIT: I would be surprised if the other form events cause the Error or the flickering. In my experience they just take a while to load.

 

[gheyman]

Thanks. Don't believe that's the issue.

If I but a DSUM and a DCount only in two of the 10 textboxes they work perfectly. Its when I add the third the flickering and error occurs. I can put two in any of the ten and it works. I am about to see if other code or events are triggering a problem.

 

[stumac]

It shouldn't error if the syntax is correct, it's possibly just the syntax of the third one that is causing it.

As a test, I just added 5 Dsums, 5 Dcounts & 5 DLookups to a form with complex code in the Oncurrent event that read data from a table containing 50k records and they all returned the data with no problems. as I cycled through the records the data took slightly longer to return but only by a tenth of a second.

 

[gheyman]

If I put the third one in and delete the first two, the "third" one works perfectly. I can check each one by deleting all the others and they each works if they are the only ones.

this is why I don't believe its a problem with the formula itself.

I am putting the formula in the Control Source of each Textbox: =DCount("RFPMgr_ID","qryPIDLibraryDashboard_UNDECLARED","Proposal_Status")

 

[stumac]

Your criteria looks incorrect - All you have is the field name , this should have an expression if its included i.e. = "proposal_status = 'Active'". For Dcount I would be inclinded to use "*" as the first argument, unless you have a specific reason to use a field. Obviously DSum must reference a numerical field as this argument.

 

[gheyman]

I don't need to count a specific value. I need to know how many records. This formula, when its the only one, produces the correct number.