Issues related to DSum VBA

[sandya]

Hello Experts!
Can anyone please solve these issues in my project?

Issue-1
When I opened the form “Total Purchases Value” field is showing null for some time please look at the attached screenshot and It takes 5 to 10 min to update, please look at the attached screenshot.
In this case I used VBA “DSum ” on Form_Load event given as

TotalPurchases.ControlSource = "=Nz(DSum(""[Amount]"",""[PurchasesStock_QryFinal]"",""[Transaction]='Credit Purchase'""), 0)"

In the above code query name/Transaction type is correct. But I can’t understand why it takes so much time to update.

Can anyone please suggest me changes in the above VBA code??


Issue-2
I used a VBA code of “DSum” as below.

TotalPurchases.ControlSource = "=Nz(DSum(""[Amount]"",""[PurchasesStock_QryFinal]"",""[Transaction]='Bill Cancelled'""), 0)"

In the above code I gave [Transaction]='Bill Cancelled'. The [Transaction] types are Credit Purchase/Cash Purchases/Purchase Return/Damage Return/Bill Cancelled etc. I want all transactions except “Bill Cancelled” can anyone please tell me how to write the code using “DSum”.

 

[KenHigg]

If I remember correctly domain functions (Dsum(),Dmax, etc) should only be used when there are not many rows in the dataset. Having said that you may try indexing the lookup fields and see if that speeds things up...

 

[sneuberg]

In the above code I gave [Transaction]='Bill Cancelled'. The [Transaction] types are Credit Purchase/Cash Purchases/Purchase Return/Damage Return/Bill Cancelled etc. I want all transactions except “Bill Cancelled” can anyone please tell me how to write the code using “DSum”.

Why not just

TotalPurchases.ControlSource = "=Nz(DSum(""[Amount]"",""[PurchasesStock_QryFinal]"",""[Transaction][COLOR="Red"]<>[/COLOR]'Bill Cancelled'""), 0)"

 

[sneuberg]

Hello Experts!
Can anyone please solve these issues in my project?

Issue-1
When I opened the form “Total Purchases Value” field is showing null for some time please look at the attached screenshot and It takes 5 to 10 min to update, please look at the attached screenshot.
In this case I used VBA “DSum ” on Form_Load event given as

TotalPurchases.ControlSource = "=Nz(DSum(""[Amount]"",""[PurchasesStock_QryFinal]"",""[Transaction]='Credit Purchase'""), 0)"

In the above code query name/Transaction type is correct. But I can’t understand why it takes so much time to update.

Can anyone please suggest me changes in the above VBA code??

I don't know if it will help but you could try a Recalc after the control source assignment. Just add

Me.Recalc

If that doesn't cure it I suggest creating (temporarily) a command button with the code

MsgBox Nz(DSum(""[Amount]"",""[PurchasesStock_QryFinal]"",""[Transaction]='Credit Purchase'""), 0)

So you can find out if the computation is actually taking that long.

 

[sandya]

@sneuberg Thank you so much both issues resolved.

1st issue solved by using Me.Recalc
2nd issue also resolved with "<>"

 

[sneuberg]

One thing I forgot to mention is that I believe Recalc saves the current record. This shouldn't normally cause any problems in a Form Open as long as the data in the first record doesn't get botched by something else. Botched in a way that would cause a validation error message when saved. So just be aware of this in case it does happen.